选择不同的行"modulo null"

Question

假设我有一个表mytable：

a     b     c     d     
------------------------
1     2     3     4
1     1     1     null
1     2     3     4
1     null  null  null
1     2     null  null
1     null  1     null
null  null  null  null

现在，该表的第一行和第三行是精确的重复。但是，我们还可以将第五行视为复制包含第一行的信息，因为1 2 null null只是1 2 3 4的副本，但缺少一些数据。假设1 2 null null 被 1 2 3 4覆盖。

"覆盖"是像 <=这样的关系，而"精确重复"是像 ==这样的关系。在上表中，我们还认为第六行被第二行覆盖，第四行被所有其他行覆盖，除最后一行外，最后一行均由所有其他行覆盖彼此覆盖。

现在，我想使用此覆盖概念来重塑mytable。我说的是不同的，我想要"最少的掩护"。这意味着，每当row1＆lt; = row2时，都应从结果中删除行1。在这种情况下，结果为

a     b     c     d     
------------------------
1     2     3     4
1     1     1     null

这就像SELECT DISTINCT，但具有增强的无效行为。

更正式地，我们可以将deduplicate(table)定义为table行的子集，这样：

• 对于table的每个行 r ，存在deduplicate(table)的行 c ，这样 r ＆lt; = em>和
• 如果 c1 和 c2 是deduplicate(table)中的任何两个单独的行，则 c1 ＆lt; = c2不是保留。

或算法：

def deduplicate(table):
  outcome = set()
  for nextRow in table:
    if any(nextRow <= o for o in outcome):
      continue
    else:
      for possiblyNowADuplicate in outcome:
        if possiblyNowADuplicate <= nextRow:
          # it is now a duplicate
          outcome.remove(possiblyNowADuplicate)
      outcome.add(nextRow)
  return outcome

我该如何在sql？

中执行此操作

(据称正在实现现代ANSI SQL的PRESTO；此外，我与之合作的桌子比mytable多的列和吨数更多，因此该解决方案必须很好地扩展到代码中，既可以很好地缩放复杂性(理想情况下不需要代码长度o(n^2(在列数中！(，在执行时间方面。(

编辑：基于 @toonice的响应，我有以下改进：

• 在进一步反思中，如果查询代码长度在列数中为o(1((可能排除在Select中要在select中操作的列的单个明确命名，用于可维护性(。在组和订单中对每一列具有复杂的布尔条件有点多。我必须编写一个Python脚本来生成我的SQL查询。但是，这可能是不可避免的。

• 我至少在数百万行上运行。我不能在o(n^2(时间中这样做。所以：

  • 可以更快地执行此操作吗？
  • 如果没有，我应该在我的真实数据集中提到，我有一个非零列" userId"，以便每个用户ID最多都会说与之相关的100行。我们可以利用此细分只能在每个用户ID上进行二次操作，然后重新组合数据吗？(还有6万用户，所以我绝对不能在查询中明确命名。(

Answer 1

请尝试以下...

SELECT DISTINCT leftTable.a,
                leftTable.b,
                leftTable.c,
                leftTable.d
FROM tblTable AS leftTable
JOIN tblTable AS rightTable ON ( ( leftTable.a = rightTable.a OR
                                   rightTable.a IS NULL ) AND
                                 ( leftTable.b = rightTable.b OR
                                   rightTable.b IS NULL ) AND
                                 ( leftTable.c = rightTable.c OR
                                   rightTable.c IS NULL ) AND
                                 ( leftTable.d = rightTable.d OR
                                   rightTable.d IS NULL ) )
GROUP BY rightTable.a,
         rightTable.b,
         rightTable.c,
         rightTable.d
ORDER BY ISNULL( leftTable.a ),
         leftTable.a DESC,
         ISNULL( leftTable.b ),
         leftTable.b DESC,
         ISNULL( leftTable.c ),
         leftTable.c DESC,
         ISNULL( leftTable.d ),
         leftTable.d DESC;

此语句首先在tblTable的两个副本上执行INNER JOIN，我给出了leftTable和rightTable的别名。此加入将将每个记录的副本从rightTable附加到leftTable中的每个记录，其中leftTable 涵盖rightTable 的记录。

然后将结果数据集分组，以消除leftTable中字段中的任何重复条目。

然后将分组的数据集订购为降序顺序，在非NULL值之后放置幸存的NULL值。

扩展

如果您很高兴从leftTable中选择所有字段，则可以在第一行上使用SELECT DISTINCT leftTable.*-我刚刚养成了列出字段的习惯。在这种情况下，两者都可以正常工作。如果您要处理大量字段，则leftTable.*可能会证明更持久。我不确定执行时间是否有差异。

。

我无法通过说leftTable.* = rightTable.*或等效的内容来说明在WHERE子句中所有字段在哪里相等的方法。我们不是在测试等效性而是覆盖的事实更加复杂。虽然如果有一种方法可以测试覆盖ensse，我会很喜欢它，但恐怕您只需要做很多复制，粘贴和精心换句话的字母，以便我的回答中每个字段使用的测试应用于您的每个字段。

另外，我无法找到GROUP BY所有字段的方法，无论是按表中或任何顺序出现的顺序，都没有指定要分组的每个字段。这也很高兴知道，但是现在我认为您必须从rightTable指定每个字段。寻找荣耀并提防复制，粘贴和编辑的危险！

如果您不关心在订购的值是NULL时首先或最后一个行，则可以通过从ORDER BY子句中删除ISNULL()条件来稍微加速该语句。

。

如果您根本不关心订购，则可以通过完全删除ORDER BY条款来进一步加速声明。根据您的语言的怪癖，您将需要用一无所有或用ORDER BY NULL替换它。除非指定了GROUP BY子句，否则某些语言(例如MySQL(会自动按CC_39子句中指定的字段进行排序。ORDER BY NULL实际上是告诉它不要进行任何排序的一种方法。

如果我们仅为每个用户重复数据覆盖的记录(即每个用户的记录与其他用户的记录无关(，则应使用以下语句...

SELECT DISTINCT leftTable.userid,
                leftTable.a,
                leftTable.b,
                leftTable.c,
                leftTable.d
FROM tblTable AS leftTable
JOIN tblTable AS rightTable ON ( leftTable.userid = rightTable.userid AND
                                 ( leftTable.a = rightTable.a OR
                                   rightTable.a IS NULL ) AND
                                 ( leftTable.b = rightTable.b OR
                                   rightTable.b IS NULL ) AND
                                 ( leftTable.c = rightTable.c OR
                                   rightTable.c IS NULL ) AND
                                 ( leftTable.d = rightTable.d OR
                                   rightTable.d IS NULL ) )
GROUP BY rightTable.userid,
         rightTable.a,
         rightTable.b,
         rightTable.c,
         rightTable.d
ORDER BY leftTable.userid,
         ISNULL( leftTable.a ),
         leftTable.a DESC,
         ISNULL( leftTable.b ),
         leftTable.b DESC,
         ISNULL( leftTable.c ),
         leftTable.c DESC,
         ISNULL( leftTable.d ),
         leftTable.d DESC;

通过在数据集中删除将其他用户的记录加入每个用户的需求，您正在删除很多处理开销的处理，输出和通过在加入和时测试另一对字段，通过添加另一层分组和，必须通过ORDER BY另一个字段。

我恐怕我无法想到任何其他方法来提高这一说法。如果有人知道有一种方式，那么我想听听。

如果您有任何疑问或评论，请随时发表评论。

附录

使用以下脚本创建的数据集在MySQL中测试了此代码...

CREATE TABLE tblTable
(
    a   INT,
    b   INT,
    c   INT,
    d   INT
);
INSERT INTO tblTable ( a,
                       b,
                       c,
                       d )
VALUES ( 1,    2,    3,    4    ),
       ( 1,    1,    1,    NULL ),
       ( 1,    2,    3,    4    ),
       ( 1,    NULL, NULL, NULL ),
       ( 1,    2,    NULL, NULL ),
       ( 1,    NULL, NULL, NULL ),
       ( NULL, NULL, NULL, NULL );