如何利用ROW_NUMBER计算客户最后两笔交易的间隔天数及平均值？

首先要纠正你现有代码的一个关键问题：按Visit_Date DESC排序时，row_num = 1是客户的最后一笔交易，row_num = 2是倒数第二笔交易，你当前筛选row_num = 2 OR row_num = 3是错误的，应该锁定row_num IN (1,2)的记录。

以下是完整的分步解决方案：

步骤1：获取每个客户的最后两笔交易记录 #

先修正子查询的筛选条件，拿到每个客户的最近两次交易数据：

SELECT Centre_Id, Cust_Id, Visit_Date, row_num
FROM (
    SELECT Centre_Id, Cust_Id, Visit_Date
        , ROW_NUMBER() OVER (PARTITION BY Cust_Id ORDER BY Visit_Date DESC) AS row_num
    FROM dbo.Fact_Centre_Txn
) AS n
WHERE n.row_num IN (1, 2)

步骤2：计算每个客户的交易间隔天数 #

这里提供两种简洁的实现方式：

方法一：使用LAG窗口函数（推荐） #

直接在子查询中获取倒数第二笔交易的日期，再计算间隔：

WITH CustomerLastTwoTxn AS (
    SELECT 
        Cust_Id,
        Centre_Id,
        Visit_Date,
        -- 获取同一客户的上一笔（倒数第二笔）交易日期
        LAG(Visit_Date) OVER (PARTITION BY Cust_Id ORDER BY Visit_Date DESC) AS Prev_Visit_Date,
        ROW_NUMBER() OVER (PARTITION BY Cust_Id ORDER BY Visit_Date DESC) AS row_num
    FROM dbo.Fact_Centre_Txn
)
SELECT 
    Cust_Id,
    Centre_Id, -- 取最后一笔交易对应的门店ID
    DATEDIFF(day, Prev_Visit_Date, Visit_Date) AS Interval_Days
FROM CustomerLastTwoTxn
WHERE row_num = 1 -- 只保留最后一笔交易的记录
AND Prev_Visit_Date IS NOT NULL -- 过滤只有1笔交易的客户

方法二：使用自连接 #

通过将同一客户的最后两笔交易自连接，计算日期差：

WITH CustomerLastTwoTxn AS (
    SELECT Centre_Id, Cust_Id, Visit_Date, row_num
    FROM (
        SELECT Centre_Id, Cust_Id, Visit_Date
            , ROW_NUMBER() OVER (PARTITION BY Cust_Id ORDER BY Visit_Date DESC) AS row_num
        FROM dbo.Fact_Centre_Txn
    ) AS n
    WHERE n.row_num IN (1, 2)
)
SELECT 
    t1.Cust_Id,
    t1.Centre_Id, -- 最后一笔交易的门店ID
    DATEDIFF(day, t2.Visit_Date, t1.Visit_Date) AS Interval_Days
FROM CustomerLastTwoTxn t1
JOIN CustomerLastTwoTxn t2 
    ON t1.Cust_Id = t2.Cust_Id 
    AND t1.row_num = 1 
    AND t2.row_num = 2

步骤3：计算所有客户的平均间隔天数 #

基于步骤2的结果，用AVG函数计算平均值（注意转换为浮点型避免整数取整）：

WITH CustomerLastTwoTxn AS (
    SELECT 
        Cust_Id,
        Visit_Date,
        LAG(Visit_Date) OVER (PARTITION BY Cust_Id ORDER BY Visit_Date DESC) AS Prev_Visit_Date
    FROM dbo.Fact_Centre_Txn
),
CustomerInterval AS (
    SELECT 
        DATEDIFF(day, Prev_Visit_Date, Visit_Date) AS Interval_Days
    FROM CustomerLastTwoTxn
    WHERE ROW_NUMBER() OVER (PARTITION BY Cust_Id ORDER BY Visit_Date DESC) = 1
    AND Prev_Visit_Date IS NOT NULL
)
SELECT AVG(CAST(Interval_Days AS FLOAT)) AS Avg_Interval_Days
FROM CustomerInterval

内容的提问来源于stack exchange，提问作者phong nguyễn