如何用SQL按时间间隔分组多笔硬币交易（LAG函数疑问）

处理加油区机器（洗车机/充气机等）的硬币交易数据，每枚硬币对应一条交易记录，需要将间隔小于3分钟的多条记录合并为一个交易。已尝试用LAG函数判断上一笔交易是否在3分钟内，但无法对多行进行分组（可能包含1条或多条前置记录）。

当前尝试代码 #

select  t1.*,
        datediff(second, PreviousVendDateTime, VendDateTime) as SecondsDiff,
        case when datediff(second, PreviousVendDateTime, VendDateTime) < 360 then 1 else 0 end as SameTransaction
from 
        (select       mct.TransactionID,
                      mct.MachineID,
                      mct.VendDateTime,
                      mct.Amount,
                      lag(mct.VendDateTime, 1)
                        over(partition by mct.MachineID order by mct.VendDateTime) as PreviousVendDateTime
        from          Machines as m
        join          MCI_CashTrans as mct
        on            mct.MachineID = m.MachineID
        and           mct.MachineSerial = m.MachineSerial
        join          Account as a
        on            m.AccountNo = a.AccountNo
        join          MachineType as mt
        on            m.MachineTypeID = mt.MachineTypeID) as t1
order by MachineID, VendDateTime

结果示例 #

核心思路是通过累计求和生成交易组ID：当SameTransaction为0时代表新交易开始，累计这些0的数量就能得到唯一的分组标识，同组内的记录会共享同一个ID。

完整SQL代码 #

WITH base_data AS (
    SELECT 
        mct.TransactionID,
        mct.MachineID,
        mct.VendDateTime,
        mct.Amount,
        LAG(mct.VendDateTime, 1) OVER (PARTITION BY mct.MachineID ORDER BY mct.VendDateTime) AS PreviousVendDateTime
    FROM Machines AS m
    JOIN MCI_CashTrans AS mct 
        ON mct.MachineID = m.MachineID AND mct.MachineSerial = m.MachineSerial
    JOIN Account AS a 
        ON m.AccountNo = a.AccountNo
    JOIN MachineType AS mt 
        ON m.MachineTypeID = mt.MachineTypeID
),
transaction_groups AS (
    SELECT 
        *,
        DATEDIFF(second, PreviousVendDateTime, VendDateTime) AS SecondsDiff,
        CASE 
            WHEN DATEDIFF(second, PreviousVendDateTime, VendDateTime) < 360 THEN 1 
            ELSE 0 
        END AS SameTransaction,
        -- 累计生成分组ID：每遇到新交易（间隔≥3分钟），分组ID加1
        SUM(CASE 
                WHEN DATEDIFF(second, PreviousVendDateTime, VendDateTime) < 360 THEN 0 
                ELSE 1 
            END) OVER (PARTITION BY MachineID ORDER BY VendDateTime) AS TransactionGroupID
    FROM base_data
)
-- 按交易组聚合，得到合并后的交易记录
SELECT 
    MachineID,
    TransactionGroupID,
    MIN(VendDateTime) AS TransactionStart,
    MAX(VendDateTime) AS TransactionEnd,
    SUM(Amount) AS TotalAmount,
    -- 拼接所有关联的原始交易ID
    STRING_AGG(CAST(TransactionID AS VARCHAR), ', ') AS RelatedTransactionIDs
FROM transaction_groups
GROUP BY MachineID, TransactionGroupID
ORDER BY MachineID, TransactionStart;

代码说明 #

1. base_data CTE：获取基础交易数据，用LAG函数拿到上一笔交易的时间，和你之前的逻辑一致。
2. transaction_groups CTE：
   • 计算时间差和是否属于同一交易的标识；
   • 关键的SUM(...) OVER (...)：当当前记录和上一笔间隔≥3分钟时加1，否则加0，累计后得到的TransactionGroupID就是每个交易组的唯一标识，同一组内的所有记录ID相同。
3. 最终聚合：按MachineID和TransactionGroupID分组，计算交易的起止时间、总金额，还可以拼接所有关联的原始交易ID。

这样就能把间隔小于3分钟的连续交易合并成一个组，解决分组问题。

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