如何用SQL基于订单频率聚合用户数据并计算平均订单间隔？

现有订单数据集如下：

order_id    email             product_id       order_date
   1        xyz@gmail.com         1            2023-05-23
   1        xyz@gmail.com         2            2023-05-23
   2        abc@gmail.com         1            2023-05-26
   3        xyz@gmail.com         3            2023-06-01
   4        abc@gmail.com         3            2023-06-02
   5        ijk@gmail.com         1            2023-06-10
   6        klm@gmail.com         1            2023-06-11
   7        xyz@gmail.com         4            2023-06-14

期望得到的聚合结果表：

order_frequency     #of users       avg_gap_between_orders
     1                  2                  null
     >=2                2                   9
     >=3                1                   11

规则说明：

• >=2代表至少下单两次的用户，此类用户共2位：
  • xyz@gmail.com的平均订单间隔为11天（2023-05-23至2023-06-01间隔9天，2023-06-01至2023-06-14间隔13天，平均值为11天）
  • abc@gmail.com的平均订单间隔为7天（2023-05-26至2023-06-02间隔7天）
• >=2分组的平均间隔为两位用户的平均值（(11+7)/2=9）；>=3分组仅包含xyz@gmail.com，所以平均间隔为11天。

我尝试了以下SQL语句，但无法得到预期结果，请问如何用SQL实现该需求？

尝试的SQL：

select case when order_count = 1 then '1'
            when order_count >=2 then '>=2'
            when order_count >=3 then '>=3'
       end as order_frequency, 
       count(distinct email) users
from (
    select email, count(distinct order_num) order_count
    from orders
    group by email) s1
group by order_frequency;

你的现有SQL仅统计了各分组的用户数，缺少订单间隔的计算逻辑。以下是完整的实现方案：

WITH user_order_dates AS (
    -- 去重同一订单的重复日期，得到每个用户的独立订单日期
    SELECT 
        email,
        order_id,
        MIN(order_date) AS order_date
    FROM orders
    GROUP BY email, order_id
),
user_order_gaps AS (
    -- 计算每个用户相邻订单的间隔天数
    SELECT
        email,
        order_date,
        LAG(order_date) OVER (PARTITION BY email ORDER BY order_date) AS prev_order_date,
        DATEDIFF(order_date, LAG(order_date) OVER (PARTITION BY email ORDER BY order_date)) AS gap_days
    FROM user_order_dates
),
user_metrics AS (
    -- 计算每个用户的订单次数和自身平均间隔天数
    SELECT
        email,
        COUNT(DISTINCT order_id) AS order_count,
        AVG(gap_days) AS user_avg_gap
    FROM user_order_gaps
    GROUP BY email
)
-- 按三个频率分组分别统计并合并结果
SELECT 
    '1' AS order_frequency,
    COUNT(*) AS "#of users",
    NULL AS avg_gap_between_orders
FROM user_metrics
WHERE order_count = 1

UNION ALL

SELECT 
    '>=2' AS order_frequency,
    COUNT(*) AS "#of users",
    ROUND(AVG(user_avg_gap)) AS avg_gap_between_orders
FROM user_metrics
WHERE order_count >= 2

UNION ALL

SELECT 
    '>=3' AS order_frequency,
    COUNT(*) AS "#of users",
    ROUND(AVG(user_avg_gap)) AS avg_gap_between_orders
FROM user_metrics
WHERE order_count >= 3
ORDER BY order_frequency;

关键逻辑说明 #

1. 订单日期去重：同一order_id对应同一天下单，通过GROUP BY email, order_id确保每个订单只保留一个日期，避免重复计算。
2. 计算相邻订单间隔：使用窗口函数LAG()获取用户上一次下单的日期，再用DATEDIFF()计算间隔天数。
3. 用户维度聚合：统计每个用户的订单次数和自身的平均间隔天数。
4. 分组统计合并：通过UNION ALL分别处理三个分组，确保>=3的用户被单独统计，同时>=2包含所有下单2次及以上的用户；用ROUND()对平均间隔取整，与预期结果一致。

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