如何用SQL计算用户购齐指定产品所需的订单数量？

示例数据集 #

order_num    order_date    email             product_id
    101        2023-06-01   xyz@gmail.com            1
    101        2023-06-01   xyz@gmail.com            2
    222        2023-06-02   xyz@gmail.com            1
    333        2023-06-03   xyz@gmail.com            3
    434        2023-06-05   xyz@gmail.com            3
    444        2023-06-01   abc@gmail.com            1
    444        2023-06-01   abc@gmail.com            2
    677        2023-06-02   abc@gmail.com            3

需求说明 #

计算每个用户购齐product_id为1、2、3的产品所需的订单数：

• 用户xyz@gmail.com需3个订单（101、222、333）
• 用户abc@gmail.com需2个订单

期望结果：

email               orders_to_cover_all_products
xyz@gmail.com                   3
abc@gmail.com                   2

我的尝试语句 #

select email max(_row_number) from (
select *, row_number() over (partition by email, product_id order by order_num) _row_number from orders) where _row_number <=3 group by email;

正确SQL实现思路及代码 #

你的尝试思路偏离了核心需求——我们需要追踪用户按订单顺序集齐产品的进度，而非对每个产品的购买次数排序。以下是可行的实现方案：

核心思路 #

1. 去重同一订单内的重复产品记录，避免干扰统计
2. 给每个用户的订单按时间/编号排序，生成订单序号
3. 累积计算每个用户到当前订单为止已集齐的产品种类数
4. 找到用户首次集齐3种产品时对应的订单序号，即为所需订单数

实现代码 #

WITH user_order_products AS (
    -- 去重同一订单内的重复产品，同时给用户的订单按顺序编号
    SELECT DISTINCT
        email,
        order_num,
        order_date,
        product_id,
        DENSE_RANK() OVER (PARTITION BY email ORDER BY order_date, order_num) AS order_sequence
    FROM orders
),
collection_progress AS (
    -- 累积统计到每个订单时，用户已集齐的产品种类
    SELECT
        email,
        order_sequence,
        COUNT(DISTINCT product_id) OVER (
            PARTITION BY email 
            ORDER BY order_sequence 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS collected_product_count
    FROM user_order_products
)
-- 取每个用户首次集齐3种产品的订单序号
SELECT
    email,
    MIN(order_sequence) AS orders_to_cover_all_products
FROM collection_progress
WHERE collected_product_count = 3
GROUP BY email;

代码说明 #

• user_order_products CTE：用DISTINCT去除同一订单内的重复产品，同时用DENSE_RANK给每个用户的订单按时间/编号生成连续序号（同一订单的多个产品会共享同一个序号）。
• collection_progress CTE：通过累积窗口函数，计算到当前订单为止用户已集齐的产品总数。
• 最后筛选出产品总数为3的记录，取最小的订单序号，就是用户购齐所有产品所需的订单数。

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