SQL如何查询过去1年内任意2天窗口期内发言超过6次的人员

数据老陈

2026-9-30

需求实现SQL方案

前提说明

假设原始表名为user_comment，字段对应关系如下：

comment_date：发言日期，日期类型
speaker：发言人
comment_id：评论唯一ID

实现思路

先过滤过去1年的有效发言数据
按发言人、发言日期聚合，统计每个用户每天的发言次数
对聚合后的每日统计结果做自关联，匹配同一用户相邻两天的发言数据
计算两天累计发言次数，筛选出累计超过6次的记录，按要求格式化输出

具体SQL代码

WITH daily_speak_cnt AS (
    -- 统计每个用户每天的发言次数，过滤过去1年数据
    SELECT 
        speaker,
        comment_date,
        COUNT(comment_id) AS daily_cnt
    FROM user_comment
    WHERE comment_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) 
    -- 不同数据库时间函数可对应替换，比如PostgreSQL可替换为CURRENT_DATE - INTERVAL '1 year'
    GROUP BY speaker, comment_date
)
-- 匹配相邻两天数据，计算累计次数后输出结果
SELECT 
    CONCAT(
        t1.speaker, '：',
        t1.comment_date, ' & ', t2.comment_date,
        '，共', (t1.daily_cnt + t2.daily_cnt), '次'
    ) AS result
FROM daily_speak_cnt t1
INNER JOIN daily_speak_cnt t2
    ON t1.speaker = t2.speaker
    AND DATEDIFF(t1.comment_date, t2.comment_date) = 1 -- 保证t1是较晚日期，t2是前一天
WHERE t1.daily_cnt + t2.daily_cnt > 6
ORDER BY t1.speaker, t1.comment_date DESC;