sqlite - 在 SQLite 中从接近重复的行(由 GROUP BY、HAVING、COUNT 确定)中选择 "first"(由 ORDER BY 确定)行

Question

我有一个问题有点超出我的范围(我真的很高兴我是 Beta)涉及重复项(所以 GROUP BY, HAVING, COUNT)，通过将解决方案保留在 SQLite 附带的标准函数中而变得更加复杂。我正在使用来自 Python 的 sqlite3 模块。

示例表工作人员，列:

* ID: integer, auto-incrementing
* ColA: integer
* ColB: varchar(20)
* UserType: varchar(20)
* LoadMe: Boolean   

(是的，SQLite 的数据类型是名义上的)

我的数据表 Workers，一开始看起来像:

ID  ColA  ColB  UserType  LoadMe
1   1     a     Alpha     0
2   1     b     Beta      0
3   2     a     Alpha     0
4   2     a     Beta      0
5   2     b     Delta     0
6   2     b     Alpha     0
7   1     a     Delta     0
8   1     b     Epsilon   0 
9   1     c     Gamma     0
10  4     b     Delta     0
11  5     a     Alpha     0
12  5     a     Beta      0
13  5     b     Gamma     0
14  5     a     Alpha     0

我想启用所有在 ColA 和 ColB 之间具有独特组合的 worker ，以便在新工厂装载到卡车上。对于那些 ColA 和 ColB 的独特组合有多个 worker 的重复项(双胞胎、三胞胎等，可能通过 Bokanovsky 的过程)，我想从每组重复项中只选择一个。为了使问题更难解决，我还希望能够以某种形式的 ORDER BY 根据 UserType 从每组重复项中选择一个。我可能希望选择用户类型为“Alpha”的第一个“副本”来解决一个非常聪明的问题，或者ORDER BY UserType DESC，这样我就可以为最低的 worker 。

您可以看到 ID 9、10 和 13 具有独特的 ColA 和 ColB 组合，最容易识别。然而，1-a、1-b、2-a、2-b 和 5-a 组合中有重复项。

我目前的流程，目前为止:

0) 每个人都有一个唯一的 ID 号。这是在出生时完成的。

1) SET 所有 Worker 为 LoadMe = 1。

UPDATE Workers
SET LoadMe = 1

2) 根据两列中的相似性查找我的重复项 (GROUP BY ColA, ColB):

SELECT Wk1.*
FROM Workers AS Wk1
INNER JOIN (
    SELECT ColA, ColB
    FROM Workers
    GROUP BY ColA, ColB
    HAVING COUNT(*) > 1
) AS Wk2
ON Wk1.ColA = Wk2.ColA
AND Wk1.ColB = Wk2.ColB
ORDER BY ColA, ColB

3) 将我所有的重复设置为 LoadMe = 0。

UPDATE Workers
SET LoadMe = 0
WHERE ID IN (
    SELECT Wk1.ID
    FROM Workers AS Wk1
    INNER JOIN (
        SELECT ColA, ColB
        FROM Workers
        GROUP BY ColA, ColB
        HAVING COUNT(*) > 1
    ) AS Wk2
    ON Wk1.ColA = Wk2.ColA
    AND Wk1.ColB = Wk2.ColB
)

4) 对于我的 GROUP BY 中的每组重复项，ORDERed BY UserType，仅 SELECT一个，列表中的第一个，将 LoadMe SET 设置为 1。

这张表看起来像:

ID  ColA  ColB  UserType  LoadMe
1   1     a     Alpha     1
2   1     b     Beta      1
3   2     a     Alpha     1
4   2     a     Beta      0
5   2     b     Delta     0
6   2     b     Alpha     1
7   1     a     Delta     0
8   1     b     Epsilon   0
9   1     c     Gamma     1
10  4     b     Delta     1
11  5     a     Alpha     1
12  5     a     Beta      0
13  5     b     Gamma     1
14  5     a     Alpha     0

ORDERed BY ColA、ColB、UserType，然后是 ID，然后按 GROUP BY 列分解，(最后间隔为清晰度)相同的数据可能看起来像:

ID  ColA  ColB  UserType  LoadMe
1   1     a     Alpha     1
7   1     a     Delta     0

2   1     b     Beta      1
8   1     b     Epsilon   0

9   1     c     Gamma     1

3   2     a     Alpha     1
4   2     a     Beta      0

6   2     b     Alpha     1
5   2     b     Delta     0

10  4     b     Delta     1

11  5     a     Alpha     1
14  5     a     Alpha     0
12  5     a     Beta      0

13  5     b     Gamma     1

我对最后一步感到困惑，觉得自己像个 Epsilon-minus 半白痴。我以前一直将重复项从数据库中提取到程序空间并在 Python 中工作，但这种情况并不少见，我想更永久地解决这个问题。

Answer 1

我喜欢把这样的问题分解一下。第一步是识别唯一的 ColA、ColB 对:

SELECT ColA,ColB FROM Workers GROUP BY ColA,ColB

现在，对于这些对中的每一对，您要找到最高优先级的记录。连接不起作用，因为您最终会为每个唯一对得到多条记录，但子查询会起作用:

SELECT ColA,ColB,
    (SELECT id FROM Workers w1 
    WHERE w1.ColA=w2.ColA AND w1.ColB=w2.ColB 
    ORDER BY UserType LIMIT 1) AS id
FROM Workers w2 GROUP BY ColA,ColB;

您可以更改子查询中的ORDER BY 子句来控制优先级。 LIMIT 1 确保每个子查询只有一条记录(否则 sqlite 将返回匹配 WHERE 子句的最后一条记录，尽管我不确定是否能保证) .

此查询的结果是要加载的记录列表，其中包含 ColA, ColB, id。我可能会直接从那里工作并摆脱 LoadMe 但如果你想保留它你可以这样做:

BEGIN TRANSACTION;
UPDATE Workers SET LoadMe=0;
UPDATE Workers SET LoadMe=1
WHERE id IN (SELECT 
    (SELECT id FROM Workers w1 
    WHERE w1.ColA=w2.ColA AND w1.ColB=w2.ColB 
    ORDER BY UserType LIMIT 1) AS id
    FROM Workers w2 GROUP BY ColA,ColB);
COMMIT;

这会清除 LoadMe 标志，然后为我们上次查询返回的每条记录将其设置为 1。交易保证这一切的发生或失败都作为一个步骤，并且永远不会让您的 LoadMe 字段处于不一致的状态。