Блог

Я ищу запрос SQLite, чтобы значительно уменьшить неуместный шум в моей базе данных. Соответствующая таблица содержит пары точек (‘hexbin’s) вместе с расстоянием между ними и источником, который задал это расстояние. Я хотел бы сохранить только строки для минимального расстояния, найденного в любом источнике для каждой пары точек.

Я думаю

 DELETE FROM dist
  WHERE hexbin1, hexbin2, source NOT IN (
    SELECT hexbin1, hexbin2, source FROM dist INNER JOIN (
      SELECT hexbin1 as h1, hexbin2 as h2, min(distance) as m
      FROM dist GROUP BY hexbin1, hexbin2)
    ON hexbin1==h1 AND hexbin2==h2 AND distance==m);
  

должно поступать примерно правильно.
Теоретически, у меня могут быть разные строки с одинаковыми hexbin1, hexbin2 и расстоянием, но разным источником. На практике это маловероятно и не является препятствием для следующих процессов, которым необходимо использовать базу данных, поэтому их можно сохранить или удалить в зависимости от того, что проще выполнить; мой запрос выше сохраняет дубликаты, но при прочих равных условиях я бы предпочел сохранить только один произвольный образец из всех с минимальным расстоянием.

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

Этот запрос:

 SELECT hexbin1, hexbin2, MIN(distance)
FROM dist d
GROUP BY hexbin1, hexbin2
  

возвращает все минимальные расстояния для каждой комбинации hexbin1, hexbin2 .
Если нет случая повторяющихся минимальных расстояний, вы можете использовать его следующим образом:

 DELETE FROM dist
WHERE (hexbin1, hexbin2, distance) NOT IN (
  SELECT hexbin1, hexbin2, MIN(distance)
  FROM dist 
  GROUP BY hexbin1, hexbin2
)
  

Смотрите упрощенную демонстрацию.

Но если есть дубликаты, то прерывателем связи может быть столбец rowid с помощью FIRST_VALUE() оконной функции:

 DELETE FROM dist
WHERE rowid NOT IN (
  SELECT FIRST_VALUE(rowid) OVER (PARTITION BY hexbin1, hexbin2 ORDER BY distance, rowid)
  FROM dist
)
  

Смотрите упрощенную демонстрацию.

Вы можете попробовать:

 delete from dist
    where exists (select 1
                  from dest d2
                  where d2.hexbin1 = d.hexbin1 and d2.hexbin2 = d.hexbin2 and d2.distance > d.distance
                 );
  

В частности, это может использовать преимущество индекса на (hexbin1, hexbin2, distance) .