Как найти дубликаты записи в таблице по нескольким столбцам?

Question

Вводные данные

Таблица со зданиями и координатами (в оригинальной таблице более 100 тыс. строк):

CREATE TABLE table1(
  building_id int,
  region varchar(55),
  street varchar(55),
  housenumber varchar(55),
  pos_x float(100),
  pos_y float(100)
);

INSERT ALL
    INTO table1(building_id,region, street, housenumber, pos_x, pos_y ) VALUES(1, 'Moscow','Lenina', '1a', 45.45, 55.55 )
    INTO table1(building_id,region, street, housenumber, pos_x, pos_y) VALUES(2, 'Spb','Mira', '20', 45.00, 55.00 )
    INTO table1(building_id,region, street, housenumber, pos_x, pos_y) VALUES(3, 'Moscow','Lenina', '1a', 45.00, 55.00 )
    INTO table1(building_id,region, street, housenumber, pos_x, pos_y) VALUES(4, 'Moscow', 'Lenina', '1a', 45.45, 55.55 )
    
SELECT * FROM dual;

Ссылка на Fiddle http://sqlfiddle.com/#!4/fa8ec6/1

Вывод

Building_ID	region	street	housenumber	pos_x	pos_y
1	Moscow	Lenina	1a	45.45	55.55
2	Spb	Mira	20	45	55.55
3	Moscow	Lenina	1a	45	55.55
4	Moscow	Lenina	1a	45.45	55.55

Ожидаемый результат

Building_ID	region	street	housenumber	pos_x	pos_y
1	Moscow	Lenina	1a	45.45	55.55
4	Moscow	Lenina	1a	45.45	55.55

Нужно вывести дубли по региону, улице, дому и координатам одновременно, т.е. должны остаться только ID 1 и 4. Для меня получилось проблемно, т.к. тип координат FLOAT и они никак не хотят фильтроваться.

Пытался сделать через конструкции NOT Exist и GROUP BY вместе с Having. Всё равно не фильтрует как положено. В голове и на бумаге не могу понять, как эту солянку из данных выцепить. Помогите, пожалуйста!

Answer 1

С помощью HAVIG BY и EXISTS:

SELECT *
FROM table22 A
WHERE EXISTS (
  SELECT COUNT(*)
  FROM table22 t 
  WHERE a.region = t.region
    AND a.street = t.street
    AND a.housenumber = t.housenumber
    AND a.pos_x = t.pos_x
    AND a.pos_y = t.pos_y
    HAVING COUNT(*) > 1  
)

С помощью оконной фукнции

SELECT x.building_id, x.region, x.street, x.housenumber, x.pos_x, x.pos_y FROM (
  SELECT 
      t.*
    , COUNT(*) OVER(PARTITION BY t.street, t.region, t.housenumber, t.pos_x, t.pos_y ) cnt
  FROM table22 t
) x
WHERE cnt > 1;

Answer 2

with dups as
(SELECT region,street,housenumber,pos_x,pos_y
FROM table1 t
group by region,street,housenumber,pos_x,pos_y
having count(*) > 1)
select * from table1 join dups using(region,street,housenumber,pos_x,pos_y)
;

Answer 3

SELECT region, street, housenumber, pos_x, pos_y, count(*)
FROM table1
GROUP BY region, street, housenumber, pos_x, pos_y
HAVING count(*) > 1;

БЛОГ НА HUSL

Как найти дубликаты записи в таблице по нескольким столбцам?

Вводные данные

Вывод

Ожидаемый результат

Ответы (3 шт):