只保留一列最小的行

Question

我正在寻找一个SQLite查询，以大大减少数据库中不相关的噪音。相关表格包含成对的点("hexbin"(，以及它们与给出该距离的源之间的距离。对于每一对点，我只想保留在任何源中找到的最小距离的行。

我认为

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和距离，但来源不同。在实践中，这不太可能，也不会阻碍需要使用数据库的下一个过程，因此可以根据哪一个更容易进行保留或删除；我上面的查询保持了重复，但在其他条件相同的情况下，我更愿意从所有距离最小的样本中只保留一个任意样本。

天真地阅读，它在表上迭代了三次，这听起来很不方便。我该如何做得更好？

Answer 1

此查询：

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

返回CCD_ 1的每个组合的所有最小距离
如果没有重复的最小距离，您可以这样使用：

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

查看简化的演示

但是，如果存在重复，那么在FIRST_VALUE()窗口功能的帮助下，平局决胜局可以是列rowid：

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

查看简化的演示

Answer 2

您可以尝试：

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)上的索引。