求Oracle SQL查询：统计特定日期1小时滑动窗口内超1000条记录的用户

针对你需要找出特定日期内用户在1小时滑动窗口中交易记录≥1000条的需求，以下是正确的Oracle SQL实现方案，核心利用窗口函数的时间范围统计实现滑动窗口计算：

核心SQL代码 #

WITH hourly_transaction_counts AS (
    SELECT
        user_code,
        transaction_timestamp, -- 替换为你的实际timestamp字段名
        COUNT(*) OVER (
            PARTITION BY user_code
            ORDER BY transaction_timestamp
            RANGE BETWEEN INTERVAL '1' HOUR PRECEDING AND CURRENT ROW
        ) AS window_record_count
    FROM your_transaction_table -- 替换为你的实际表名
    WHERE TRUNC(transaction_timestamp) = DATE '2024-05-20' -- 替换为目标日期
)
SELECT DISTINCT
    user_code,
    transaction_timestamp - INTERVAL '1' HOUR AS window_start_time,
    transaction_timestamp AS window_end_time,
    window_record_count
FROM hourly_transaction_counts
WHERE window_record_count >= 1000
ORDER BY user_code, window_start_time;

关键逻辑说明 #

• 窗口范围定义：使用RANGE BETWEEN INTERVAL '1' HOUR PRECEDING AND CURRENT ROW，确保统计当前交易记录时间往前1小时到当前时间的1小时滑动窗口内的记录数，而非固定行数（这是多数错误实现的核心问题）。
• 用户分组：PARTITION BY user_code保证仅统计同一用户的交易数据，避免跨用户的错误统计。
• 去重处理：DISTINCT用于去除重复的窗口结果——同一滑动窗口内的多条交易记录都会触发window_record_count >= 1000，去重后仅保留唯一的窗口时间段。
• 日期过滤：TRUNC(transaction_timestamp)将时间戳截断到日期维度，精准筛选目标日期内的交易数据。

性能优化建议 #

如果交易表数据量较大，建议创建联合索引加速窗口计算：

CREATE INDEX idx_user_transaction_ts ON your_transaction_table(user_code, transaction_timestamp);

扩展说明 #

如果需要检测任意1小时窗口（不以交易记录为终点），上述方案依然有效——只要窗口内存在≥1000条记录，窗口的最后一条交易记录就会触发统计条件，从而捕获到该窗口。

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