SQL实现用户各次存款间的小时间隔计算

需要编写SQL查询生成表，展示每个用户每连续两次存款之间的小时差值（例如第1次与第2次、第2次与第3次存款的时间间隔）。

原始数据查询语句 #

WITH q1 AS (
    SELECT 
        [UserId],
        [DepositAttemptDate], 
        [AmountSystem]
    FROM [dbo].[v_DepositAttempts_Marketing]
    WHERE 
        [DepositAttemptDate] >= '2024-01-01' 
        AND [Status] IN ('Completed', 'Complete', 'Confirmed') 
        AND [DepositType] = ''
    ORDER BY [UserId] ASC, [DepositAttemptDate] DESC
)

原始数据示例 #

解决方案SQL #

用窗口函数LAG()可以轻松实现这个需求——它能按用户分组，获取当前存款记录的上一次存款时间，再计算时间差转换为小时：

WITH q1 AS (
    SELECT 
        [UserId],
        [DepositAttemptDate], 
        [AmountSystem],
        -- 按用户分组、存款日期升序，获取上一次存款时间
        LAG([DepositAttemptDate]) OVER (PARTITION BY [UserId] ORDER BY [DepositAttemptDate] ASC) AS PreviousDepositDate
    FROM [dbo].[v_DepositAttempts_Marketing]
    WHERE 
        [DepositAttemptDate] >= '2024-01-01' 
        AND [Status] IN ('Completed', 'Complete', 'Confirmed') 
        AND [DepositType] = ''
)
SELECT 
    [UserId] AS User,
    [DepositAttemptDate] AS CurrentDepositDate,
    PreviousDepositDate,
    -- 计算小时差并保留两位小数
    ROUND(DATEDIFF(SECOND, PreviousDepositDate, [DepositAttemptDate]) / 3600.0, 2) AS HoursBetweenDeposits,
    [AmountSystem] AS CurrentDepositValue
FROM q1
-- 过滤掉无前置存款的记录（用户第一次存款）
WHERE PreviousDepositDate IS NOT NULL
ORDER BY [UserId] ASC, [DepositAttemptDate] ASC;

如果需要和原始查询一样按最新存款在前排序，改用LEAD()函数获取下一次存款时间即可：

WITH q1 AS (
    SELECT 
        [UserId],
        [DepositAttemptDate], 
        [AmountSystem],
        -- 按用户分组、存款日期降序，获取下一次存款时间
        LEAD([DepositAttemptDate]) OVER (PARTITION BY [UserId] ORDER BY [DepositAttemptDate] DESC) AS NextDepositDate
    FROM [dbo].[v_DepositAttempts_Marketing]
    WHERE 
        [DepositAttemptDate] >= '2024-01-01' 
        AND [Status] IN ('Completed', 'Complete', 'Confirmed') 
        AND [DepositType] = ''
)
SELECT 
    [UserId] AS User,
    [DepositAttemptDate] AS CurrentDepositDate,
    NextDepositDate,
    ROUND(DATEDIFF(SECOND, NextDepositDate, [DepositAttemptDate]) / 3600.0, 2) AS HoursBetweenDeposits,
    [AmountSystem] AS CurrentDepositValue
FROM q1
WHERE NextDepositDate IS NOT NULL
ORDER BY [UserId] ASC, [DepositAttemptDate] DESC;

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