在多列上进行去重计数

Question

有没有更好的方法来执行这样的查询：

SELECT COUNT(*) 
FROM (SELECT DISTINCT DocumentId, DocumentSessionId
      FROM DocumentOutputItems) AS internalQuery

我需要计算这个表中两列之间不同项的数量，我的查询已经正常工作但是我想知道是否可以只使用一个查询语句得到最终结果（不使用子查询）。

- Novitzky

---

FYI：https://community.oracle.com/ideas/18664 - quetzalcoatl

检查我的答案 https://dev59.com/cY3da4cB1Zd3GeqPvSDu#75068192 - Palash Mondal

Answer 1

如果您想提高性能，可以尝试在两个列的哈希值或连接值上创建一个持久化计算列。

一旦它被持久化，只要该列是确定性的且您正在使用“合理”的数据库设置，就可以在其上创建索引和/或统计信息。

我相信计算列的不同计数将等同于您的查询。

- Jason Horner

---

请提供一个示例或代码样本，以便更清楚地展示这意味着什么以及如何实现？ - jayqui

它与在这些列上创建多列索引有何不同？如果我说得不清楚，请见谅，我是SQL新手。 - Sreram

这个技巧如何处理哈希冲突？我认为由于冲突，哈希值上的不同计数会比实际情况小。 - Kota Mori

Answer 2

编辑：从不太可靠的仅校验和查询中进行修改

我发现了一种方法（在SQL Server 2005中），可以很好地运作，并且我可以使用尽可能多的列（通过将它们添加到CHECKSUM()函数中）。REVERSE()函数将int类型转换成varchar类型，以使得去重更加可靠。

SELECT COUNT(DISTINCT (CHECKSUM(DocumentId,DocumentSessionId)) + CHECKSUM(REVERSE(DocumentId),REVERSE(DocumentSessionId)) )
FROM DocumentOutPutItems

- JayTee

---

使用像Checksum()这样的哈希函数，不同的输入返回相同的哈希值的概率较小，因此计数可能会略微偏差。HashBytes()的发生这种情况的概率更小，但仍然不为零。如果这两个ID是int类型（32位），则可以使用“无损哈希”将它们组合成bigint（64位），例如Id1 << 32 + Id2。 - crokusek

机会并不是很小，特别是当你开始组合列时（这就是它原来的意图）。我对这种方法很感兴趣，尤其是在某些情况下，校验和的计数结果比预期少了10%。如果你再仔细考虑一下，校验和只返回一个整数，因此，如果你对完整的bigint范围进行校验和，你得到的不同计数会比实际上要小20亿倍。-1 - pvolders

更新了查询，使用“REVERSE”来消除重复的可能性。 - JayTee

我们能避免使用校验和吗？我们能不能只是将两个值连接起来？我认为这样可能会冒险将它们视作相同的事物：（'he'，'art'）== 'hear','t'）。但是，像@APC提出的那样，我认为可以通过使用一个分隔符（某个不出现在任何一列中的值）来解决这个问题，因此 'he | art'！='hear | t'。对于简单的“连接”方法还有其他问题吗？ - Nate Anderson

我认为连接可以起作用 - 数据库仍然必须确定唯一性。 - JayTee

Answer 3

要作为单个查询运行，请连接列，然后获取连接字符串实例的不同计数。

SELECT count(DISTINCT concat(DocumentId, DocumentSessionId)) FROM DocumentOutputItems;

在MySQL中，您可以不需要连接步骤直接执行以下操作：

SELECT count(DISTINCT DocumentId, DocumentSessionId) FROM DocumentOutputItems;

这个功能在MySQL文档中有提到：

http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_count-distinct

- spelunk1

---

这是一个关于SQL Server的问题，你发布的两个选项在以下回答中已经提到：https://dev59.com/-nM_5IYBdhLWcg3wPAVF#1471444 和 https://dev59.com/-nM_5IYBdhLWcg3wPAVF#1471713。 - sstan

就翻译而言，这段话的意思是：在PostgreSQL中，这个语句几乎可以正常运行；只需要加上额外的括号即可：SELECT COUNT(DISTINCT (DocumentId, DocumentSessionId)) FROM DocumentOutputItems;我会尽力使翻译通俗易懂，但不会对原文进行解释或添加额外的内容。 - ijoseph

请非常小心使用此方法，因为它可能导致计数不正确。以下示例将返回计数为1。DocumentID | DocumentSessionID "A" | "AB" "AA" | "B" - Bort

正如@Bort所指出的，第一种选项可能会导致不正确的结果，并且最好使用CONCAT_WS进行编写。如果任何列可为空，则第二种方法也不能保证产生与原始查询相同的结果。 - Tomty

Answer 4

你现有的查询有什么不满意的地方吗？如果你担心在两个列上使用DISTINCT不能返回唯一的排列，为什么不试一下呢？在Oracle中，它确实像你期望的那样工作。

SQL> select distinct deptno, job from emp
  2  order by deptno, job
  3  /

    DEPTNO JOB
---------- ---------
        10 CLERK
        10 MANAGER
        10 PRESIDENT
        20 ANALYST
        20 CLERK
        20 MANAGER
        30 CLERK
        30 MANAGER
        30 SALESMAN

9 rows selected.


SQL> select count(*) from (
  2  select distinct deptno, job from emp
  3  )
  4  /

  COUNT(*)
----------
         9

SQL>

编辑

我在分析数据时走了一条死胡同，但答案却令人沮丧地显而易见...

SQL> select count(distinct concat(deptno,job)) from emp
  2  /

COUNT(DISTINCTCONCAT(DEPTNO,JOB))
---------------------------------
                                9

SQL>

编辑2

给定以下数据，上面提供的连接解决方案将会计算错误：

col1  col2
----  ----
A     AA
AA    A

所以我们需要添加一个分隔符...

select col1 + '*' + col2 from t23
/

显然，所选的分隔符必须是一个字符或一组字符，它们在任一列中都永远不会出现。

- APC

Answer 5

一些SQL数据库可以使用元组表达式，因此您可以这样做：

SELECT COUNT(DISTINCT (DocumentId, DocumentSessionId))
      FROM DocumentOutputItems;

如果您的数据库不支持此功能，可以按照 @oncel-umut-turer 的建议使用 CHECKSUM 或其他标量函数来模拟实现良好唯一性，例如 COUNT(DISTINCT CONCAT(DocumentId, ':', DocumentSessionId))。 MySQL 特别支持非 SQL 标准语法的 COUNT(DISTINCT expr, expr, ...)。它还指出，在标准 SQL 中，您必须对 COUNT(DISTINCT ...) 中的所有表达式进行连接。 元组的一个相关用途是执行诸如 IN 查询之类的操作：

SELECT * FROM DocumentOutputItems
WHERE (DocumentId, DocumentSessionId) in (('a', '1'), ('b', '2'));

- karmakaze

---

在ORACLE中无法工作，例如。 - Pere

例如，在ORACLE中无法工作 - undefined

Answer 6

可以尝试使用以下语句：

select count(*)
from
  (select count(*) cnt
   from DocumentOutputItems
   group by DocumentId, DocumentSessionId) t1

这个语句与您已经使用的语句可能是一样的，但它避免了使用 DISTINCT 关键字。

- Trevor Tippins

---

在我的测试中（使用 SET SHOWPLAN_ALL ON），它具有相同的执行计划和完全相同的 TotalSubtreeCost。 - KM.

根据原始查询的复杂程度，使用“GROUP BY”解决这个问题可能会为查询转换引入一些额外的挑战，以实现所需的输出（例如，当原始查询已经有“GROUP BY”或“HAVING”子句时...）。 - Lukas Eder

Answer 7

下面是不带子查询的简短版本：

SELECT COUNT(DISTINCT DocumentId, DocumentSessionId) FROM DocumentOutputItems

在MySQL中它能够很好地工作，我认为优化器更容易理解这个。

编辑：显然，我误读了MSSQL和MySQL - 对此感到抱歉，但也许这有所帮助。

- Alexander Kjäll

---

在SQL Server中，您会看到以下信息：Msg 102，Level 15，State 1，Line 1 Incorrect syntax near ','. 它的意思是出现错误语法附近逗号。 - KM.

@Kamil Nowicki，在SQL Server中，你只能在COUNT()函数中使用一个字段。在我的答案中，我展示了如何将两个字段合并成一个来尝试这种方法。然而，我建议仍然坚持原始的方法，因为查询计划最终会相同。 - KM.

请查看@JayTee的答案。它运行得非常好。COUNT(DISTINCT CHECKSUM([Field1], [Field2]) - Custodio

即使在MySQL中，这也不完全等同于原始查询，因为具有NULL值的行不会被计算。 - Tomty

Answer 8

我已经使用过这种方法，并且它对我有效。

SELECT COUNT(DISTINCT DocumentID || DocumentSessionId) 
FROM  DocumentOutputItems

对于我的情况，它提供了正确的结果。

- Jaanis Veinberg

---

它不会给你两列联合的不同值的计数。至少在MySQL 5.8中不会。 - Anwar Shaikh

这个问题被标记为SQL Server，但这不是SQL Server的语法。 - Tab Alleman

@AnwarShaikh 我不明白你的评论。你的意思是说它没有给你"DocumentID"和"DocumentSessionID"这两列中不同行的数量吗？ - AdamO

警告！这可能会导致不正确的结果。请考虑使用DocumentID=123和DocumentSessionId=21，以及DocumentID=12和DocumentSessionId=321。 - rouen

Answer 9

如果你正在使用固定长度的数据类型，你可以将其转换为二进制，这样就可以非常简单和快速地完成此操作。假设DocumentId和DocumentSessionId都是int，因此长度为4个字节...

SELECT COUNT(DISTINCT CAST(DocumentId as binary(4)) + CAST(DocumentSessionId as binary(4)))
FROM DocumentOutputItems

我的具体问题要求我将一个 SUM除以各种外键和日期字段的不同组合的COUNT，同时按照另一个外键进行分组并偶尔通过一些值或键进行过滤。该表非常大，在使用子查询时，查询时间急剧增加。由于复杂性，统计数据根本不是可行的选择。使用CHECKSUM方案在转换速度上也太慢了，特别是由于各种数据类型，我不能冒险使用其不可靠性。

然而，使用以上解决方案对查询时间几乎没有任何增加（与仅使用SUM相比），且应该是完全可靠的！它应该能够帮助其他遇到类似情况的人，所以我在此发布。

- IphStich

Answer 10

这个怎么样？（注：该句为中文表达，无需翻译）

Select DocumentId, DocumentSessionId, count(*) as c 
from DocumentOutputItems 
group by DocumentId, DocumentSessionId;

这将得出DocumentId和DocumentSessionId所有可能组合的数量。

- Nikhil Singh