Oracle SQL：如何按1秒内时间间隔分组聚合交易数据？

核心思路 #

通过窗口函数计算相邻交易的时间差，结合累计求和生成分组ID，既保留时间精度，又避免复杂逻辑和性能损耗，精准将同一customer_id下间隔≤2秒的交易归为同一组。

具体实现（以Oracle为例） #

WITH ranked_trans AS (
    SELECT 
        customer_id,
        units,
        pkid,
        actdate,
        -- 计算当前交易与同用户上一笔交易的时间差（秒），首笔交易时间差设为0
        EXTRACT(SECOND FROM (actdate - LAG(actdate, 1, actdate) OVER (PARTITION BY customer_id ORDER BY actdate))) AS time_diff
    FROM sampledata
),
grouped_trans AS (
    SELECT 
        *,
        -- 时间差超过2秒则生成新分组，累计求和得到唯一分组ID
        SUM(CASE WHEN time_diff > 2 THEN 1 ELSE 0 END) OVER (PARTITION BY customer_id ORDER BY actdate) AS group_id
    FROM ranked_trans
)
SELECT 
    customer_id,
    TRUNC(actdate) AS actdate_trunc,
    SUM(units) AS total_units,
    MAX(pkid) AS max_pkid
FROM grouped_trans
GROUP BY customer_id, TRUNC(actdate), group_id
ORDER BY customer_id, actdate_trunc, group_id;

方案优势 #

• 性能更优：仅需两次窗口扫描，避免了LEAD函数嵌套或自连接带来的高开销
• 精度无损：完全基于原始时间戳计算间隔，不会因舍入丢失关键时间信息
• 逻辑清晰：分组ID生成逻辑直观，针对customer_id=13710675这类需拆分多笔的场景，能自动按时间间隔拆分分组

其他数据库适配说明 #

如果使用PostgreSQL，时间差计算需调整为：

EXTRACT(EPOCH FROM (actdate - LAG(actdate, 1, actdate) OVER (PARTITION BY customer_id ORDER BY actdate))) AS time_diff

核心分组逻辑保持一致。

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