如何根据商品购买次数统计唯一user_id的数量？

我使用MySQL Workbench 8.0，想要统计每个商品被用户购买1次、2次（及更多）的唯一user_id数量。

样本数据 #

期望结果 #

说明：椅子有4个唯一用户购买过1次，2个唯一用户购买过2次；桌子有5个唯一用户购买过1次，2个唯一用户购买过2次。

已尝试的SQL #

我已经写出按user_id和Product分区的行号查询：

SELECT 
user_id, Product, ROW_NUMBER() OVER(PARTITION BY `user_id`, Product) rn
FROM myData;

执行结果如下：

解决方案 #

思路一：基于现有行号查询继续处理 #

你的行号查询中，rn的最大值就是该用户对对应商品的购买次数。先分组获取每个用户-商品的最大行号，再按商品和购买次数统计用户数，最后用条件聚合转成列：

WITH user_purchase_counts AS (
    SELECT 
        user_id, 
        Product, 
        MAX(rn) AS purchase_times
    FROM (
        SELECT 
            user_id, 
            Product, 
            ROW_NUMBER() OVER(PARTITION BY `user_id`, Product) rn
        FROM myData
    ) AS ranked_data
    GROUP BY user_id, Product
)
SELECT 
    Product,
    SUM(CASE WHEN purchase_times = 1 THEN 1 ELSE 0 END) AS BoughtOnce,
    SUM(CASE WHEN purchase_times = 2 THEN 1 ELSE 0 END) AS BoughtTwice
    -- 如需统计购买3次及以上的用户，可添加：SUM(CASE WHEN purchase_times >=3 THEN 1 ELSE 0 END) AS BoughtMultiple
FROM user_purchase_counts
GROUP BY Product;

思路二：直接统计用户-商品的购买次数（更简洁） #

无需生成行号，直接用COUNT()分组统计每个用户对每个商品的购买次数，再进行条件聚合：

WITH user_purchase_counts AS (
    SELECT 
        user_id, 
        Product, 
        COUNT(*) AS purchase_times
    FROM myData
    GROUP BY user_id, Product
)
SELECT 
    Product,
    SUM(CASE WHEN purchase_times = 1 THEN 1 ELSE 0 END) AS BoughtOnce,
    SUM(CASE WHEN purchase_times = 2 THEN 1 ELSE 0 END) AS BoughtTwice
    -- 如需统计购买3次及以上的用户，可添加：SUM(CASE WHEN purchase_times >=3 THEN 1 ELSE 0 END) AS BoughtMultiple
FROM user_purchase_counts
GROUP BY Product;

两种方法都能得到目标结果，第二种思路更直接高效，省略了行号生成的步骤。

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