为什么我用GROUP BY得出的COUNT DISTINCT计算不正确？ [英] Why I got incorrect calculation of COUNT DISTINCT with GROUP BY?

问题描述

我有一个表INTERACTIONS

I have a table INTERACTIONS

CustomerID | Channel | Response
-----------+---------+----------
 245       | SMS     | Accept   
 245       | PUSH    | Ignore   
 247       | SMS     | Accept   
 249       | PUSH    | Ignore   

当我发出请求

SELECT COUNT(DISTINCT CUSTOMERID) AS Customers 
FROM INTERACTIONS;

我得到结果 7440

当我按频道对组进行查询，然后计算所有组的总和时：

When I make query with group by Channel, and then calculate sum for all groups:

    SELECT SUM(CUSTOMERS) 
    FROM 
        (SELECT 
             CHANNEL,
             COUNT(DISTINCT CUSTOMERID) AS Customers 
         FROM 
             INTERACTIONS
         GROUP BY 
             CHANNEL);

我得到结果 9993

为什么？怎么了？我希望所有客户的数量都相同。

Why? What's wrong? I expect that number of all customers is the same.

推荐答案

示例数据就在其中。不同的客户是：

It is right there in your sample data. The distinct customers are:

245, 247, 249

当您按渠道分组时， 245 客户分别显示PUSH和SMS：

When you group by channel the 245 customer appears separately for PUSH and SMS:

SMS  | 245, 247
PUSH | 245, 249

因此 COUNT（DISTINCT x）GROUP BY y 可能大于 COUNT（DISTINCT x）-没有分组依据。

这篇关于为什么我用GROUP BY得出的COUNT DISTINCT计算不正确？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！