MySQL：计算用户多笔交易间隔时长及平均间隔时长的技术实现问询

嗨，我看到你已经尝试用CTE加自连接来计算订单时间差了，确实可以用更简洁高效的窗口函数来优化，同时还能一并搞定第二个任务。咱们一步步来拆解：

任务1：计算每个客户当前订单与上一笔订单的时间差（单位：天） #

你之前的代码用了DENSE_RANK再加自连接，逻辑没问题但有点绕。其实用LAG()窗口函数就能直接拿到上一笔订单的日期，完全不需要自连接，代码更清爽：

-- 任务1：计算每个客户当前订单与上一笔订单的时间差
SELECT
    USER_ID,
    TRANSACTION_ID,
    TRANSACTION_DATE,
    -- 获取同一用户的上一笔交易日期
    LAG(TRANSACTION_DATE) OVER (PARTITION BY USER_ID ORDER BY TRANSACTION_DATE) AS prev_transaction_date,
    -- 计算时间差（第一笔订单无前置订单，结果为NULL）
    DATEDIFF(TRANSACTION_DATE, LAG(TRANSACTION_DATE) OVER (PARTITION BY USER_ID ORDER BY TRANSACTION_DATE)) AS days_since_last_transaction
FROM CDM_Bill_Details
-- 过滤掉只有单笔交易的用户
WHERE USER_ID IN (
    SELECT USER_ID
    FROM CDM_Bill_Details
    GROUP BY USER_ID
    HAVING COUNT(*) > 1
)
ORDER BY USER_ID, TRANSACTION_DATE;

代码说明： #

• LAG(TRANSACTION_DATE) OVER (PARTITION BY USER_ID ORDER BY TRANSACTION_DATE)：按用户分组、交易日期排序，为每一行返回上一行的交易日期，完美匹配"上一笔订单"的需求。
• 外层的DATEDIFF直接计算当前订单和上一笔的天数差，因为已经过滤了只有单笔交易的用户，所以每个用户至少有一行非NULL的时间差结果。

任务2：计算每个客户的两笔订单之间的平均时间差 #

基于任务1的思路，我们可以先把每个用户的所有订单时间差算出来，再分组求平均：

-- 任务2：计算每个客户的平均订单时间差
WITH user_transaction_gaps AS (
    SELECT
        USER_ID,
        DATEDIFF(TRANSACTION_DATE, LAG(TRANSACTION_DATE) OVER (PARTITION BY USER_ID ORDER BY TRANSACTION_DATE)) AS days_since_last_transaction
    FROM CDM_Bill_Details
    WHERE USER_ID IN (
        SELECT USER_ID
        FROM CDM_Bill_Details
        GROUP BY USER_ID
        HAVING COUNT(*) > 1
    )
)
SELECT
    USER_ID,
    -- 自动忽略第一笔订单的NULL值，计算有效时间差的平均值
    AVG(days_since_last_transaction) AS avg_days_between_transactions
FROM user_transaction_gaps
GROUP BY USER_ID
ORDER BY USER_ID;

代码说明： #

• 先在CTE里计算每个用户每笔订单与上一笔的时间差，过滤掉单交易用户。
• 用AVG()函数分组求平均，它会自动忽略第一笔订单的NULL值，直接计算有效时间差的平均值。

对比你原来的写法，用LAG()窗口函数避免了自连接，代码更简洁，执行效率也更高——尤其是数据量较大的时候，窗口函数的性能优势会很明显。另外注意：不同数据库的DATEDIFF语法可能略有差异（比如PostgreSQL直接用日期相减就能得到天数），但核心逻辑是通用的。

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