如何用SQL实现连续4天上传0字节文件的上传者列表查询
实现连续4天0字节上传用户校验的正确方案
指出现有SQL的核心问题
你当前写的SQL无法得到正确结果,主要有两个缺陷:
- 子查询只返回了每个用户的最新0字节上传日期,没有把
uploader_id作为关联字段带出,JOIN时没有任何匹配条件,会直接生成笛卡尔积,把主表所有记录和所有用户的最新日期盲目配对,是写关联查询时的经典错误 - 完全没有实现连续4天的校验逻辑,仅拿到了每个用户的最后一次0字节上传时间,没有对时间窗口内的记录覆盖度做检查
关于实现方式的答疑
你不需要强行在WHERE子句中硬塞校验逻辑,也不需要依赖IF这类流程控制语句:
- WHERE子句仅适用于分组前的行级过滤,连续日期校验、聚合计数这类逻辑,放在分组后的HAVING子句中实现是最合理的
- 采用窗口函数或集合匹配的常规SQL写法,可读性和执行效率都远高于流程控制语句
两种适配不同需求的实现方案
方案1:严格匹配你的原始校验逻辑(以最新0字节上传为终点回溯4天)
这个方案完全贴合你最初的设计思路:对每个上传过0字节文件的用户,先拿到他最后一次传0字节的日期,再校验从这天往前数3天(加上当天共4个连续自然日),是否每天都存在0字节上传记录。
WITH user_latest_zero_date AS ( -- 先拿到每个传过0字节文件的用户,以及对应的最新0字节上传日期 SELECT uploader_id, MAX(DATE(date_added)) AS latest_zero_date FROM file_uploads WHERE size = 0 GROUP BY uploader_id ), user_all_zero_dates AS ( -- 按日去重,避免同一天多次上传0字节的记录干扰日期计数 SELECT DISTINCT uploader_id, DATE(date_added) AS upload_date FROM file_uploads WHERE size = 0 ) -- 最终匹配符合条件的用户 SELECT ulz.uploader_id FROM user_latest_zero_date ulz INNER JOIN user_all_zero_dates uaz ON ulz.uploader_id = uaz.uploader_id -- 划定4天的校验窗口:最新日期往前3天,到最新日期当天 AND uaz.upload_date BETWEEN DATE_SUB(ulz.latest_zero_date, INTERVAL 3 DAY) AND ulz.latest_zero_date GROUP BY ulz.uploader_id, ulz.latest_zero_date -- 窗口内凑够4个不同的上传日期,说明4天连续无断档 HAVING COUNT(DISTINCT uaz.upload_date) = 4
方案2:通用连续区间校验(捕捉历史上任意一段连续4天的0字节上传)
如果你需要筛选的是所有曾经出现过连续4天传0字节文件的用户,不要求这段连续记录必须以最新一次0字节上传为终点,可以用经典的连续日期差分逻辑实现,这种写法对长周期的连续段识别效率更高:
WITH distinct_user_dates AS ( -- 同样先按日去重拿到每个用户的0字节上传日期 SELECT DISTINCT uploader_id, DATE(date_added) AS upload_date FROM file_uploads WHERE size = 0 ), date_continuity_group AS ( SELECT uploader_id, upload_date, -- 核心逻辑:连续的日期减去按日排序的行号,会得到同一个固定值,以此划分连续区间 DATE_SUB(upload_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY uploader_id ORDER BY upload_date) DAY) AS continuity_group FROM distinct_user_dates ) -- 筛选连续区间长度≥4天的用户 SELECT DISTINCT uploader_id FROM date_continuity_group GROUP BY uploader_id, continuity_group HAVING COUNT(upload_date) >= 4
两个方案的选择建议
- 如果你的需求是“最近一次0字节上传当天,往前推4天全是0字节上传”,选方案1,不会把那些历史上有过连续4天传0字节、但最近已经中断的用户误筛进来
- 如果你的需求是“只要用户历史上出现过哪怕一次连续4天传0字节的行为,就纳入列表”,选方案2,不需要绑定最新上传日期的约束
内容的提问来源于stack exchange,提问作者user10141648
相关产品推荐
相关产品推荐

