Удалить дубликаты на основе критериев длины строки

Задний план

Удалите повторяющиеся названия городов из временной таблицы в зависимости от длины названия.

Проблема

Следующий запрос возвращает 350 000 строк:

select
  tc.id,
  tc.name_lowercase,
  tc.population,
  tc.latitude_decimal,
  tc.longitude_decimal
from
  climate.temp_city tc
inner join (
  select
    tc2.latitude_decimal,
    tc2.longitude_decimal
  from
    climate.temp_city tc2
  group by
    tc2.latitude_decimal,
    tc2.longitude_decimal
  having
    count(*) > 3
) s on 
  tc.latitude_decimal = s.latitude_decimal and
  tc.longitude_decimal = s.longitude_decimal

Образец данных:

940308;"sara"            ;;-53.4333333;-68.1833333
935665;"estancia la sara";;-53.4333333;-68.1833333
935697;"estancia sara"   ;;-53.4333333;-68.1833333
937204;"la sara"         ;;-53.4333333;-68.1833333
940350;"seccion gap"     ;;-52.1666667;-68.5666667
941448;"zanja pique"     ;;-52.1666667;-68.5666667
935941;"gap"             ;;-52.1666667;-68.5666667
935648;"estancia gap"    ;;-52.1666667;-68.5666667
939635;"ritchie"         ;;-51.9833333;-70.4
934948;"d.e. ritchie"    ;;-51.9833333;-70.4
934992;"diego richtie"   ;;-51.9833333;-70.4
934993;"diego ritchie"   ;;-51.9833333;-70.4
934990;"diego e. ritchie";;-51.9833333;-70.4

Я хотел бы удалить все дубликаты, сохранив строки, где:

  • население не равно нулю; а также
  • имя является самым длинным из дубликатов (max(tc.name_lowercase)); а также
  • если ни одно из этих условий не выполняется, сохраните max(tc.id).

Из заданного набора данных оставшиеся строки будут такими:

935665;"estancia la sara";;-53.4333333;-68.1833333
935648;"estancia gap"    ;;-52.1666667;-68.5666667
934990;"diego e. ritchie";;-51.9833333;-70.4

Вопрос

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

Спасибо!


postgresql sql duplicate-removal
person Dave Jarvis    schedule 30.04.2011    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Я думаю, вы ищете что-то вроде этого:

SELECT t.id, t.name_lowercase, t.latitude_decimal, t.longitude_decimal
FROM (SELECT MAX(LENGTH(name_lowercase)) AS len, latitude_decimal, longitude_decimal FROM temp_city GROUP BY latitude_decimal, lng) AS max_length,
     temp_city t
WHERE max_length.latitude_decimal  = t.latitude_decimal
  AND max_length.longitude_decimal = t.longitude_decimal
  AND max_length.len = LENGTH(t.name_lowercase);

Где temp_city — это таблица, содержащая результаты вашего примера.

Вышеупомянутое столкнется с проблемами, если ваш temp_city также содержит эту строку:

1 | xxxancia la sara | -53.4333333 | -68.1833333

Вы не предложили способ выбрать, какая строка из тех, чья name имеет максимальную длину, поэтому обе они будут возвращены:

      1 | xxxancia la sara | -53.4333333 | -68.1833333
 935665 | estancia la sara | -53.4333333 | -68.1833333

ОБНОВЛЕНИЕ: если max(tc.id) является дополнительным критерием от обрезки вышеуказанных дубликатов, вы можете обернуть другой слой:

SELECT t.id, t.name_lowercase, t.latitude_decimal, t.longitude_decimal
FROM  
  (
    SELECT MAX(t.id) AS id
    FROM
      (
        SELECT MAX(LENGTH(name_lowercase)) AS len, latitude_decimal, longitude_decimal
        FROM temp_city
        GROUP BY latitude_decimal, longitude_decimal
      ) AS max_length,
      temp_city t
    WHERE max_length.latitude_decimal  = t.latitude_decimal
      AND max_length.longitude_decimal = t.longitude_decimal
      AND max_length.len               = LENGTH(t.name_lowercase)
    GROUP BY t.latitude_decimal, t.longitude_decimal, LENGTH(t.name_lowercase)
  ) AS tt, 
  temp_city t
WHERE t.id = tt.id
person mu is too short    schedule 30.04.2011
comment
Спасибо. max(tc.id) является критерием разрешения ничьей. - person Dave Jarvis; 30.04.2011
comment
@Dave: я добавил обновление, которое должно иметь дело с этим решающим фактором. Запрос становится довольно чудовищным, но не показывайте его детям или легко напуганным взрослым. - person mu is too short; 30.04.2011
comment
Спасибо; повторяющиеся координаты были удалены. - person Dave Jarvis; 30.04.2011