SQL需求：计算各客户连续交易时间差的平均值

我来帮你搞定这个SQL需求，咱们一步步理清楚：

首先明确数据源和核心需求：
咱们有一张存储所有客户交易记录的SQL表，包含ClientCode（客户编码）和Date（交易时间）两个核心字段，部分示例数据如下：

ClientCode Date
DL2xxx 2016-04-18 00:00:00.000
DL2xxx 2016-04-18 00:00:00.000
E19xxx 2016-04-18 00:00:00.000
E19xxx 2016-04-18 00:00:00.000
E19xxx 2016-04-18 00:00:00.000
JDZxxx 2016-04-18 00:00:00.000

需求规则 #

需要按客户计算连续交易时间差（按天统计）的平均值，具体规则：

• 若客户有多次交易（注意要先去重同一天的交易，不然同一天的时间差为0会拉低平均值），比如交易日期为第1、3、10、15天，时间差集合是{2,7,5}，平均值为4.66
• 若客户仅1次交易，平均值直接返回0
• 最终输出每个唯一ClientCode对应的AverageDays值，期望格式如下：

ClientCode AverageDays
DL2xxx <float_value>
E19xxx <float_value>

解决方案SQL语句 #

这里用窗口函数LAG()来获取每个客户上一次的交易日期，结合日期计算函数求天数差，再分组计算平均值，同时处理仅一次交易的情况：

WITH UniqueTransactions AS (
    -- 先对每个客户的交易日期去重，避免同一天多次交易干扰计算
    SELECT DISTINCT
        ClientCode,
        CAST(Date AS DATE) AS TransactionDate -- 转换为纯日期类型，忽略时分秒
    FROM YourTransactionTable -- 这里替换成你的实际表名
),
TransactionDifferences AS (
    -- 计算每个客户连续交易的天数差
    SELECT
        ClientCode,
        DATEDIFF(day, LAG(TransactionDate) OVER (PARTITION BY ClientCode ORDER BY TransactionDate), TransactionDate) AS DayDiff
    FROM UniqueTransactions
)
-- 分组计算平均值，仅1次交易的客户返回0
SELECT
    ClientCode,
    COALESCE(AVG(CAST(DayDiff AS FLOAT)), 0) AS AverageDays
FROM TransactionDifferences
GROUP BY ClientCode
ORDER BY ClientCode;

代码逻辑解释 #

1. UniqueTransactions CTE：先对原始表按客户+日期去重，确保每个客户每天只有一条交易记录，避免同一天多次交易产生无效的0差值。
2. TransactionDifferences CTE：用LAG()窗口函数按客户分组、交易日期排序，获取每个客户上一次交易的日期，再用DATEDIFF()计算两次交易的天数差。第一次交易的DayDiff会是NULL（因为没有上一次记录）。
3. 最终查询：用AVG()计算每个客户的天数差平均值，用COALESCE()把NULL（仅1次交易的情况）转换为0，完美匹配需求。

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