时间戳

首页 > 解决方案 > 只保留一列最少的行

sql - 只保留一列最少的行

问题描述

我正在寻找一个 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 和距离，但来源不同。在实践中，这不太可能也不会妨碍下一个需要使用数据库的流程，因此可以保留或删除它们，这取决于哪个更容易做；我上面的查询保留了重复项，但在其他一切都相同的情况下，我宁愿只保留所有距离最小的样本中的一个任意样本。

天真地阅读，它在表格上迭代了 3 次，这听起来很不方便。我怎样才能做得更好？

标签： sqlsqlitejoinsql-delete

解决方案

这个查询：

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
)

查看简化的演示。

推荐阅读