如何按天分组计算表中每个用户的各时间戳间隔时长？

要获取每个用户每天每笔交易与上一笔交易的时间间隔，需要使用窗口函数LAG()关联同用户同日期下的前一笔交易记录，具体实现如下：

完整SQL脚本 #

WITH RankedTransactions AS (
    SELECT 
        TRANSACTION_DATE,
        CAST(TRANSACTION_DATE AS DATE) AS TRANSACTION_DAY,
        TRANSACTION_DESCRIPTION,
        TRANSACTION_LOCATION,
        TRANSACTION_ID,
        LAST_NAME,
        FIRST_NAME,
        -- 获取同用户同日期下的上一笔交易时间
        LAG(TRANSACTION_DATE) OVER (
            PARTITION BY LAST_NAME, FIRST_NAME, CAST(TRANSACTION_DATE AS DATE)
            ORDER BY TRANSACTION_DATE
        ) AS PREV_TRANSACTION_DATE
    FROM [DB].[DBO].[TRANSACTION_TABLE]
)
SELECT 
    TRANSACTION_DAY,
    LAST_NAME,
    FIRST_NAME,
    TRANSACTION_ID,
    TRANSACTION_DATE,
    PREV_TRANSACTION_DATE,
    -- 计算当前交易与上一笔的时间间隔（单位：小时，可按需调整为MINUTE/SECOND等）
    DATEDIFF(HOUR, PREV_TRANSACTION_DATE, TRANSACTION_DATE) AS TRANSACTION_INTERVAL,
    -- 同时保留全天的最大最小时间间隔
    MAX(TRANSACTION_DATE) OVER (PARTITION BY LAST_NAME, FIRST_NAME, TRANSACTION_DAY) AS DAILY_MAX_DATE,
    MIN(TRANSACTION_DATE) OVER (PARTITION BY LAST_NAME, FIRST_NAME, TRANSACTION_DAY) AS DAILY_MIN_DATE,
    DATEDIFF(HOUR, 
        MIN(TRANSACTION_DATE) OVER (PARTITION BY LAST_NAME, FIRST_NAME, TRANSACTION_DAY),
        MAX(TRANSACTION_DATE) OVER (PARTITION BY LAST_NAME, FIRST_NAME, TRANSACTION_DAY)
    ) AS DAILY_TOTAL_DURATION
FROM RankedTransactions
ORDER BY LAST_NAME, FIRST_NAME, TRANSACTION_DAY, TRANSACTION_DATE;

关键说明 #

• LAG()窗口函数：通过PARTITION BY指定分组维度（用户+交易日期），ORDER BY按交易时间排序，从而获取当前记录的上一笔交易时间。
• 时间间隔单位：可根据需求修改DATEDIFF()的第一个参数，比如改为MINUTE获取分钟间隔，SECOND获取秒间隔。
• 第一笔交易的PREV_TRANSACTION_DATE和TRANSACTION_INTERVAL会显示为NULL，因为没有前序交易，可根据实际需求用ISNULL()处理。

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