如何用单条SQL查询判断用户近48小时内是否有24小时提交超20条记录

• 核心逻辑：任意满足条件的连续24小时窗口，必然对应窗口内最后一条提交记录的时间点，只要统计每个提交记录往前推24小时内同用户的提交总数，只要有一处数值>=20即可判定存在违规。
• 范围裁剪：先过滤出最近48小时内的所有提交记录，避免全表扫描，大幅降低计算量。
• 滑动统计：通过窗口函数按用户分组、按提交时间排序，统计每条记录对应24小时窗口内的提交量。
• 快速判定：用EXISTS函数判断是否存在符合要求的记录，不需要全量返回结果，查询效率更高。

首先假设存储提交记录的表名为user_submissions，以下是不同数据库的实现：

MySQL 8.0+ 版本 #

SELECT EXISTS (
    SELECT 1
    FROM (
        SELECT 
            user,
            COUNT(*) OVER (
                PARTITION BY user 
                ORDER BY UNIX_TIMESTAMP(datetime)
                RANGE BETWEEN 86400 PRECEDING AND CURRENT ROW
            ) AS submit_count_24h
        FROM user_submissions
        WHERE datetime >= NOW() - INTERVAL 48 HOUR
    ) t
    WHERE submit_count_24h >= 20
) AS has_violation;

说明：86400为24小时对应的秒数，返回结果has_violation为1代表存在违规，为0代表无违规。

PostgreSQL 版本 #

SELECT EXISTS (
    SELECT 1
    FROM (
        SELECT 
            "user",
            COUNT(*) OVER (
                PARTITION BY "user"
                ORDER BY datetime
                RANGE BETWEEN INTERVAL '24 hours' PRECEDING AND CURRENT ROW
            ) AS submit_count_24h
        FROM user_submissions
        WHERE datetime >= NOW() - INTERVAL '48 hours'
    ) t
    WHERE submit_count_24h >= 20
) AS has_violation;

说明：PostgreSQL中user为系统关键字，需要用双引号包裹避免语法错误。

• 如果需要定位具体违规的用户，只需去掉外层EXISTS查询，返回内层t表中submit_count_24h >=20的去重user字段即可。
• 给user和datetime字段建联合索引可以大幅提升查询性能，适合高并发的业务校验场景。

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