如何实现按Product分组后统计真实总客户数(3)与账户数(8)？

问题背景 #

现有TRANSACTION表结构及测试数据如下：

CREATE TABLE TRANSACTION(
user_id numeric,
account_id varchar,
product varchar,
colour varchar,
price numeric);
insert into transaction (user_id, account_id, product, colour, price)
values
(1, 'a1', 'biycle', 'black', 500),
(1, 'a2', 'motorbike', 'red', 1000),
(1, 'a2', 'motorbike', 'blue', 1200),
(2, 'b3', 'car', 'grey', 10000),
(2, 'b2', 'motorbike', 'black', 1250),
(3, 'c1', 'biycle', 'black', 500),
(3, 'c2', 'biycle', 'black', 525),
(3, 'c4', 'skateboard', 'white', 250),
(3, 'c5', 'scooter', 'blue', 260)

已知真实总客户数为3（user_id：1、2、3），真实总账户数为8（account_id：a1、a2、b3、b2、c1、c2、c4、c5）。

当前使用以下SQL按product、colour分组统计：

SELECT
    product,
    colour,
    sum(price)total_price,
    count(DISTINCT user_id)customer_total,
    count(DISTINCT account_id)account_total
 from transaction
 group by 
    product, colour

得到的分组结果中，customer_total列求和为8，account_total列求和为9，与真实总数不符——原因是同一个用户/账户可能出现在多个product-colour分组中，count(DISTINCT)会在每个分组里独立计数，求和时重复计算了跨分组的用户/账户。

解决方案 #

要让分组后customer_total的总和等于真实总客户数、account_total总和等于真实总账户数，核心是每个用户/账户仅在其所属的任意一个product-colour分组中被计数一次。可以通过窗口函数标记用户/账户首次出现的分组，再统计该标记的记录：

WITH transaction_with_seq AS (
    SELECT
        *,
        -- 给每个用户的不同product-colour分组生成唯一序号，首次出现的分组序号为1
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY product, colour) AS user_seq,
        -- 给每个账户的不同product-colour分组生成唯一序号，首次出现的分组序号为1
        ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY product, colour) AS account_seq
    FROM TRANSACTION
)
SELECT
    product,
    colour,
    SUM(price) AS total_price,
    -- 仅统计该用户首次出现的分组
    COUNT(DISTINCT CASE WHEN user_seq = 1 THEN user_id END) AS customer_total,
    -- 仅统计该账户首次出现的分组
    COUNT(DISTINCT CASE WHEN account_seq = 1 THEN account_id END) AS account_total
FROM transaction_with_seq
GROUP BY product, colour

结果说明 #

• total_price仍正常统计每个product-colour分组的总价，不受影响。
• customer_total仅在用户首次出现的分组中计数，所有分组的customer_total求和后等于真实总客户数3。
• account_total仅在账户首次出现的分组中计数，所有分组的account_total求和后等于真实总账户数8。

内容的提问来源于stack exchange，提问作者Napier