如何编写跨库SQL查询识别同一发送者的1小时内交易时段

现有一张包含timestamp（时间戳）和sender（发送者）的表Transactions，规则是：同一发送者的连续交易若时间间隔在60分钟内，则归为同一交易分支。需要编写SQL查询输出各交易分支的起止时间、所属发送者，以及分支内的交易数量。

已基于SQLite实现了一段CTE+窗口函数的查询（如下），但希望得到更简洁优雅的实现，要求除时间差计算部分外，代码需兼容MySQL、SQLite、Oracle、SQL Server和DB2。

--- get the next row as a column
with nxttab as (
    select 
    timestamp,LEAD(timestamp,1) over(PARTITION by sender order by timestamp) as nxttimestamp,sender FROM Transactions
), 
--- get all Transactions within 60 minutes
difftab as (
    select timestamp, nxttimestamp,sender FROM  nxttab where (julianday(nxttimestamp) - julianday(timestamp))*24*60 < 60
), 
---  get the difference of current transaction - previous next transaction
endpoints as (
    select  julianday(timestamp) - julianday(LAG(nxttimestamp,1) OVER ( PARTITION BY sender order by timestamp )) as diffnextday,* from difftab
),
--- Mark start of new transaction with 1 and all consecutive transactions with 0
 intervals as (
    select CASE WHEN diffnextday!=0 THEN 1 ELSE 0 END as startnew ,* from endpoints
),
--- Get the partition number of each transaction by cummulative sum from top to current row
 partitions as (
select SUM(startnew) OVER (PARTITION BY Sender ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as partno,* from intervals
)
-- group by sender,partition number find min,max and count(*)+1
select sender,partno,min(timestamp) as start,max(nxttimestamp) as end ,count(*)+1 as count from partitions
group by sender,partno order by sender;

核心思路是用LAG()窗口函数标记新分支的起点，再通过累计求和生成分支编号，最后分组聚合得到结果，能大幅减少CTE层级：

步骤说明 #

• 对每个发送者的交易按时间排序，用LAG()获取上一笔交易的时间戳
• 判断当前交易与上一笔的时间间隔是否超过60分钟，超过则标记为新分支起点（is_new_branch = 1）
• 对每个发送者的is_new_branch做累计求和，得到唯一的分支编号branch_id
• 按发送者和分支编号分组，聚合出分支的起止时间、交易数量

兼容SQL代码 #

WITH transaction_groups AS (
    SELECT
        sender,
        timestamp,
        -- 标记新分支：当前交易与上一笔间隔>60分钟，或为该发送者第一笔交易
        CASE
            WHEN LAG(timestamp) OVER (PARTITION BY sender ORDER BY timestamp) IS NULL 
                 OR -- 替换下方的时间差计算为对应数据库的语法
                    -- SQLite: (julianday(timestamp) - julianday(LAG(timestamp) OVER (...))) * 24 * 60 > 60
                    -- MySQL: TIMESTAMPDIFF(MINUTE, LAG(timestamp) OVER (...), timestamp) > 60
                    -- Oracle: (timestamp - LAG(timestamp) OVER (...)) * 24 * 60 > 60
                    -- SQL Server: DATEDIFF(MINUTE, LAG(timestamp) OVER (...), timestamp) > 60
                    -- DB2: TIMESTAMPDIFF(4, CHAR(timestamp - LAG(timestamp) OVER (...))) > 60
                    (julianday(timestamp) - julianday(LAG(timestamp) OVER (PARTITION BY sender ORDER BY timestamp))) * 24 * 60 > 60
            THEN 1
            ELSE 0
        END AS is_new_branch
    FROM Transactions
),
branch_ids AS (
    SELECT
        sender,
        timestamp,
        -- 累计求和生成分支编号
        SUM(is_new_branch) OVER (PARTITION BY sender ORDER BY timestamp ROWS UNBOUNDED PRECEDING) AS branch_id
    FROM transaction_groups
)
-- 分组聚合得到最终结果
SELECT
    sender,
    branch_id,
    MIN(timestamp) AS branch_start,
    MAX(timestamp) AS branch_end,
    COUNT(*) AS transaction_count
FROM branch_ids
GROUP BY sender, branch_id
ORDER BY sender, branch_start;

关键优化点 #

• 简化CTE层级：从5层压缩到2层，逻辑更直观易懂
• 直接基于原始交易数据标记分支起点，无需依赖LEAD()获取下一笔交易
• 分支编号生成逻辑更简洁，避免复杂的时间差二次计算
• 交易数量直接用COUNT(*)统计，无需额外加1，逻辑更清晰

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