如何获取每日总购买人数（避免GROUP BY拆分）并按商品分组展示

问题背景 #

我有一张buyers表，结构如下：

day         Item    Buyer_id
19/10/2022  Shoes   58423401
19/10/2022  Shoes   58423402
19/10/2022  Bikes   58423403
19/10/2022  Shoes   58423404
20/10/2022  Bikes   58423405
20/10/2022  Shoes   58423406 

需要实现的需求是：按商品分组展示数据，同时右侧列统计当日所有商品的总购买人数，期望结果如下：

Day         Item     number_of_buyers    total_number_of_buyers_per_day
19/10/2022  Shoes    5,000               55,000
19/10/2022  Bikes    50,000              55,000
20/10/2022  Shoes    45,000              95,000
20/10/2022  Bikes    50,000              95,000 

但当前执行SQL后，每日总购买人数被GROUP BY拆分，和商品购买人数一致，错误结果如下：

Day         Item     number_of_buyers   total_number_of_buyers_per_day
19/10/2022  Shoes    5,000              5,000 
19/10/2022  Bikes    50,000             50,000 
20/10/2022  Shoes    45,000             45,000 
20/10/2022  Bikes    50,000             50,000 

我尝试的关联查询SQL如下：

SELECT 
        a.day
        , a.item
        , COUNT (DISTINCT a.buyer_id) AS number_of_buyers
        , COUNT(b.number_of_total_users_on_site) AS total_number_of_buyers_per_day

FROM 
        buyers

LEFT JOIN
       (
        SELECT day, COUNT (DISTINCT buyer_id) AS number_of_total_buyers
        FROM buyers
        GROUP BY 1, 2
        ORDER BY 1, 2
        ) AS b 
        ON a.buyer_id = b.buyer_id 
        AND a.day = b.day 
 
GROUP BY 
        1, 2 
 
ORDER BY 
        1, 2 

问题分析 #

你的SQL存在两个核心错误：

• 子查询里的GROUP BY 1,2是按day和item分组，返回的是每日每个商品的购买人数，而非当日所有商品的总购买人数；
• 关联条件用了a.buyer_id = b.buyer_id，导致关联后只能匹配同用户同日期的记录，最终统计的总人数变成了当前商品的购买人数，而非当日全局总数。

正确解决方案 #

方法一：使用窗口函数（推荐） #

窗口函数可以直接在分组统计的同时计算当日总购买人数，无需关联子查询，效率更高：

SELECT
    day,
    item,
    COUNT(DISTINCT buyer_id) AS number_of_buyers,
    COUNT(DISTINCT buyer_id) OVER (PARTITION BY day) AS total_number_of_buyers_per_day
FROM buyers
GROUP BY day, item
ORDER BY day, item;

方法二：关联正确的子查询 #

如果不支持窗口函数，可以先单独统计每日总购买人数，再按日期关联主查询：

SELECT
    a.day,
    a.item,
    COUNT(DISTINCT a.buyer_id) AS number_of_buyers,
    b.total_number_of_buyers_per_day
FROM buyers a
LEFT JOIN (
    SELECT day, COUNT(DISTINCT buyer_id) AS total_number_of_buyers_per_day
    FROM buyers
    GROUP BY day  -- 仅按日期分组，得到当日总购买人数
) b ON a.day = b.day  -- 仅按日期关联
GROUP BY a.day, a.item, b.total_number_of_buyers_per_day
ORDER BY a.day, a.item;

说明 #

• 方法一中，窗口函数COUNT(DISTINCT buyer_id) OVER (PARTITION BY day)会在每个日期分组内计算所有用户的总数，并将这个值填充到该日期的每一行中；
• 方法二中，子查询仅按day分组得到当日总人数，主查询按day和item分组后通过日期关联，就能让每个商品行都匹配到当日的全局总数，不会被商品拆分。

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