SQL: Как найти дубликаты на основе двух полей?

Question

У меня есть строки в таблице базы данных Oracle, которые должны быть уникальными для комбинации двух полей, но уникальное ограничение не установлено для таблицы, поэтому мне нужно найти все строки, которые сами нарушают ограничение, используя SQL. К сожалению, мои скудные навыки SQL не подходят для этой задачи.

В моей таблице есть три соответствующих столбца: entity_id, station_id и obs_year. Для каждой строки комбинация station_id и obs_year должна быть уникальной, и я хочу выяснить, есть ли строки, которые нарушают это, сбрасывая их с помощью SQL-запроса.

Я попробовал следующий SQL (предложенный этим предыдущим вопросом), но он не работает для меня (я получаю столбец ORA-00918, неоднозначно определенный):

SELECT
entity_id, station_id, obs_year
FROM
mytable t1
INNER JOIN (
SELECT entity_id, station_id, obs_year FROM mytable 
GROUP BY entity_id, station_id, obs_year HAVING COUNT(*) > 1) dupes 
ON 
t1.station_id = dupes.station_id AND
t1.obs_year = dupes.obs_year

Может кто-нибудь подсказать, что я делаю не так и / или как это решить?

Answer 1

SELECT  *
FROM    (
        SELECT  t.*, ROW_NUMBER() OVER (PARTITION BY station_id, obs_year ORDER BY entity_id) AS rn
        FROM    mytable t
        )
WHERE   rn > 1

Answer 2

SELECT entity_id, station_id, obs_year
FROM mytable t1
WHERE EXISTS (SELECT 1 from mytable t2 Where
       t1.station_id = t2.station_id
       AND t1.obs_year = t2.obs_year
       AND t1.RowId <> t2.RowId)

Answer 3

Я думал, что многие решения здесь были громоздкими и сложными для понимания, так как у меня было ограничение первичного ключа из 3 столбцов, и мне нужно было найти дубликаты. Так вот вариант

SELECT id, name, value, COUNT(*) FROM db_name.table_name
GROUP BY id, name, value
HAVING COUNT(*) > 1

Answer 4

Измените 3 поля в начальном выборе, чтобы быть

SELECT
t1.entity_id, t1.station_id, t1.obs_year

Answer 5

Перепишите ваш запрос

SELECT
t1.entity_id, t1.station_id, t1.obs_year
FROM
mytable t1
INNER JOIN (
SELECT entity_id, station_id, obs_year FROM mytable 
GROUP BY entity_id, station_id, obs_year HAVING COUNT(*) > 1) dupes 
ON 
t1.station_id = dupes.station_id AND
t1.obs_year = dupes.obs_year

Я думаю, что неоднозначная ошибка столбца (ORA-00918) была из-за того, что вы были selectстолбцы, имена которых появляются как в таблице, так и в подзапросе, но вы не указали, хотите ли вы это от dupes или из mytable (с псевдонимом как t1).

Answer 6

Не могли бы вы создать новую таблицу, содержащую ограничение уникальности, а затем копировать данные по строкам, игнорируя сбои?

Answer 7

Вам необходимо указать таблицу для столбцов в главном меню. Кроме того, предполагая, что entity_id является уникальным ключом для mytable и не имеет отношения к поиску дубликатов, вам не следует группировать его в подзапросе dupes.

Пытаться:

SELECT t1.entity_id, t1.station_id, t1.obs_year
FROM mytable t1
INNER JOIN (
SELECT station_id, obs_year FROM mytable 
GROUP BY station_id, obs_year HAVING COUNT(*) > 1) dupes 
ON 
t1.station_id = dupes.station_id AND
t1.obs_year = dupes.obs_year

Answer 8

Я удивлен, что здесь нет ответов, использующих CTE (Common Table Expression)

      WITH cte as (
  SELECT 
     ROW_NUMBER() 
     OVER(
     PARTITION BY Last_Name, First_Name order by BIRTHDATE)
        AS RN,
     Employee_number, First_Name, Last_Name, BirthDate,
     SUM(1)
     OVER(
     PARTITION BY Last_Name, First_Name
     ORDER BY BIRTHDATE ROWS BETWEEN UNBOUNDED PRECEDING 
                             AND UNBOUNDED FOLLOWING)
         AS CNT
    FROM
        employment)
   select * from cte where cnt > 1

Это не только найдет дубликаты (только по имени и фамилии), но и сообщит вам, сколько их.

Answer 9

SELECT  *
FROM    (
        SELECT  t.*, ROW_NUMBER() OVER (PARTITION BY station_id, obs_year ORDER BY entity_id) AS rn
        FROM    mytable t
        )
WHERE   rn > 1

Quassnoi является наиболее эффективным для больших столов. У меня был этот анализ стоимости:

SELECT a.dist_code, a.book_date, a.book_no
FROM trn_refil_book a
WHERE EXISTS (SELECT 1 from trn_refil_book b Where
       a.dist_code = b.dist_code and a.book_date = b.book_date and a.book_no = b.book_no
       AND a.RowId <> b.RowId)
       ;

дал стоимость 1322341

SELECT a.dist_code, a.book_date, a.book_no
FROM trn_refil_book a
INNER JOIN (
SELECT b.dist_code, b.book_date, b.book_no FROM trn_refil_book b 
GROUP BY b.dist_code, b.book_date, b.book_no HAVING COUNT(*) > 1) c 
ON 
 a.dist_code = c.dist_code and a.book_date = c.book_date and a.book_no = c.book_no
;

дал стоимость 1271699

в то время как

SELECT  dist_code, book_date, book_no
FROM    (
        SELECT  t.dist_code, t.book_date, t.book_no, ROW_NUMBER() OVER (PARTITION BY t.book_date, t.book_no
          ORDER BY t.dist_code) AS rn
        FROM    trn_refil_book t
        ) p
WHERE   p.rn > 1
;

дал стоимость 1021984

Таблица не была проиндексирована....

Answer 10

  SELECT entity_id, station_id, obs_year
    FROM mytable
GROUP BY entity_id, station_id, obs_year
HAVING COUNT(*) > 1

Укажите поля для поиска дубликатов в SELECT и GROUP BY.

Это работает с помощью GROUP BY чтобы найти любые строки, которые соответствуют любым другим строкам на основе указанных столбцов. HAVING COUNT(*) > 1 говорит, что нам интересно видеть только те строки, которые встречаются более 1 раза (и, следовательно, являются дубликатами)