如何用KQL高效计算当前行前2小时内的记录数（避免Join）

问题描述 #

需要为现有数据表新增HistoricalCount列，统计时间戳早于当前行且落在当前行时间戳前2小时范围内的记录行数。现有25000条记录，若使用普通Join会导致数据量暴增至28万，需更高效的实现方式。

输入数据 #

Site    Name    Timestamp
1   User 1  2024-03-30T18:30:00Z
1   User 1  2024-03-30T18:45:00Z
1   User 1  2024-03-30T19:00:00Z
1   User 1  2024-03-30T19:30:00Z
1   User 1  2024-03-30T20:00:00Z
1   User 1  2024-03-30T20:15:00Z
1   User 1  2024-03-30T21:15:00Z
1   User 1  2024-03-30T23:15:00Z
1   User 1  2024-03-30T23:30:00Z
1   User 1  2024-03-31T00:00:00Z

期望输出 #

Site    Name    Timestamp   HistoricalCount
1   User 1  2024-03-30T18:30:00Z    0
1   User 1  2024-03-30T18:45:00Z    1
1   User 1  2024-03-30T19:00:00Z    2
1   User 1  2024-03-30T19:30:00Z    3
1   User 1  2024-03-30T20:00:00Z    4
1   User 1  2024-03-30T20:15:00Z    5
1   User 1  2024-03-30T21:15:00Z    3
1   User 1  2024-03-30T23:15:00Z    1
1   User 1  2024-03-30T23:30:00Z    1
1   User 1  2024-03-31T00:00:00Z    2

高效解决方案：滑动窗口函数 #

使用时间范围滑动窗口替代Join，窗口函数会在O(n log n)的时间复杂度内完成计算，完全避免数据膨胀问题。核心逻辑是：按用户维度（Site+Name）分组，在指定时间窗口内统计行数，再减去当前行本身的计数（因为窗口默认包含当前行）。

不同SQL引擎实现代码 #

1. PostgreSQL / BigQuery

SELECT
    Site,
    Name,
    Timestamp,
    COUNT(*) OVER (
        PARTITION BY Site, Name
        ORDER BY Timestamp
        RANGE BETWEEN INTERVAL '2 hours' PRECEDING AND CURRENT ROW
    ) - 1 AS HistoricalCount
FROM your_table
ORDER BY Timestamp;

2. Spark SQL

SELECT
    Site,
    Name,
    Timestamp,
    COUNT(*) OVER (
        PARTITION BY Site, Name
        ORDER BY Timestamp
        RANGE BETWEEN INTERVAL 2 HOURS PRECEDING AND CURRENT ROW
    ) - 1 AS HistoricalCount
FROM your_table
ORDER BY Timestamp;

3. MySQL 8.0+

MySQL对时间范围窗口的支持需将时间戳转为数值（如UNIX时间戳，单位秒）：

SELECT
    Site,
    Name,
    Timestamp,
    COUNT(*) OVER (
        PARTITION BY Site, Name
        ORDER BY UNIX_TIMESTAMP(Timestamp)
        RANGE BETWEEN 7200 PRECEDING AND CURRENT ROW -- 2小时=7200秒
    ) - 1 AS HistoricalCount
FROM your_table
ORDER BY Timestamp;

结果验证 #

以2024-03-30T21:15:00Z行为例：

• 当前时间戳前2小时为2024-03-30T19:15:00Z
• 窗口内包含19:30、20:00、20:15、21:15共4条记录
• 减去当前行后得到3，与期望输出完全匹配

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