SQL Server中计算客户交易间隔平均时长及距上次交易天数

没问题，针对你在SQL Server里的这个需求，咱们可以用窗口函数轻松搞定！我帮你整理了具体的解决方案，完全匹配你的要求：

要计算每个客户的交易间隔，关键是先拿到每条交易的上一次交易时间——这时候LAG()窗口函数就派上用场了，它能按客户分组、交易日期排序，帮我们关联到上一笔交易的时间。之后我们只需要计算时间差，再聚合出平均值，同时算出最后一次交易到指定日期的间隔即可。

WITH trade_intervals AS (
    SELECT
        customer,
        trade_date,
        -- 获取当前客户的上一笔交易日期
        LAG(trade_date) OVER (PARTITION BY customer ORDER BY trade_date) AS prev_trade_date,
        -- 提前算出每个客户的最后一笔交易日期
        MAX(trade_date) OVER (PARTITION BY customer) AS last_trade_date
    FROM trades
)
SELECT
    customer,
    -- 计算平均交易间隔：仅对有上一笔交易的记录取平均，只有1条交易时返回NULL
    AVG(CASE WHEN prev_trade_date IS NOT NULL THEN 
        DATEDIFF(SECOND, prev_trade_date, trade_date) / (24.0 * 3600) 
        ELSE NULL END) AS avg_time_btw_trades,
    -- 计算距最后一笔交易的天数（这里用你指定的当前日期2013-05-10）
    DATEDIFF(SECOND, MAX(last_trade_date), '2013-05-10') / (24.0 * 3600) AS time_since_last_trade
FROM trade_intervals
GROUP BY customer
ORDER BY customer;

1. CTE部分（trade_intervals）：
   • 用LAG(trade_date) OVER (PARTITION BY customer ORDER BY trade_date)给每条交易添加上一笔同客户的交易日期
   • 用MAX(trade_date) OVER (PARTITION BY customer)提前获取每个客户的最后交易日期，避免后续重复计算
2. 主查询部分：
   • AVG()函数里通过CASE过滤掉没有上一笔交易的记录（也就是客户的第一笔交易），这样如果客户只有1条交易，平均值自然就是NULL
   • 用DATEDIFF(SECOND, ...)计算时间差的秒数，再除以24.0*3600转换为天的小数，保证精度（比如客户B的两笔交易间隔5天2小时，计算结果就是5.0833...，和你要的5.08一致）
   • 最后用MAX(last_trade_date)确保取到每个客户的最后交易时间，再和指定的当前日期计算间隔

针对你提供的测试数据，运行上述代码后会得到：

完全符合你的期望（B的平均间隔可以根据需求用ROUND()函数四舍五入到两位小数）

如果需要动态使用当前系统日期，把代码里的'2013-05-10'换成GETDATE()即可，注意确保日期类型和trade_date匹配。

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