如何计算产品维度下用户首次和二次复购的平均间隔天数

需求说明 #

计算产品维度下每个用户首次购买和第二次购买的平均间隔时长，现有SQL问题排查及修正如下：

现有SQL存在的问题 #

• 字段拼写错误：内层查询使用CustomerId字段，外层GROUP BY语句写为Customer_Id，字段名不一致会直接执行报错
• 分组逻辑错误：外层GROUP BY包含了OrderSequence和OrderDate字段，会导致每个用户、产品、订单序号单独拆分为一行，无法将首次、第二次购买日期合并到同一行，无法计算两个日期间隔
• 核心逻辑缺失：现有代码没有实现日期间隔计算、平均间隔聚合的逻辑，无法得到最终需求结果

修正后可运行SQL #

WITH user_product_order_rank AS (
    SELECT 
        t.CustomerId,
        t.ProductId,
        t.OrderDate,
        -- 同用户、同产品维度按下单日期排序
        DENSE_RANK() OVER (PARTITION BY t.CustomerId, t.ProductId ORDER BY t.OrderDate ASC) AS order_sequence,
        -- 取当前行的下一次下单日期，即第二次购买日期
        LEAD(t.OrderDate, 1) OVER (PARTITION BY t.CustomerId, t.ProductId ORDER BY t.OrderDate ASC) AS second_order_date
    FROM Transactions t (NOLOCK)
    WHERE t.SiteKey = '01' -- 字符类型参数加引号避免隐式转换报错
    GROUP BY t.CustomerId, t.ProductId, t.OrderDate -- 保留原去重逻辑，过滤同用户同产品同日期多订单的场景
)
-- 计算所有符合条件的用户产品组合的首次、第二次购买平均间隔天数
SELECT AVG(DATEDIFF(day, OrderDate, second_order_date)) * 1.0 AS avg_interval_days
FROM user_product_order_rank
-- 只取首次订单行，second_order_date 即为对应第二次下单日期
WHERE order_sequence = 1 
-- 过滤仅购买次数不足2次的无效数据
AND second_order_date IS NOT NULL;

补充说明 #

如果需要先查看每个用户每个产品的单独间隔时长，可去掉最后的AVG聚合，直接查询CustomerId、ProductId、DATEDIFF(day, OrderDate, second_order_date) AS interval_days即可。

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