代码之家 › 专栏 › 技术社区 › Anaphory

只保留一列最少的行

sqlite

0

Anaphory · 技术社区 · 5 年前

我正在寻找一个SQLite查询来大大减少数据库中不相关的噪音。

我想

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和距离,但来源不同。实际上,这不太可能,也不妨碍下一个需要使用数据库的进程,因此可以保留或删除它们,这取决于哪个更容易做到;我上面的查询保留了重复的部分,但其他所有内容都是相同的,我更愿意从所有具有最小距离的样本中只保留一个任意样本。

0 回复 | 直到 5 年前

1

2

forpas 5 年前

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
)

demo .

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
)

请参见简化的 demo .

2

1

Gordon Linoff 5 年前

您可以尝试:

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)