如何编写SQL查询：按日期分组并聚合自身及前后一周搜索量总和

要实现按日期分组，计算每个日期恰好前一周、当周、后一周的搜索量总和，可参考以下两种主流SQL写法：

方法一：自连接（适配所有主流数据库） #

通过将表与自身关联，匹配目标日期的前7天、当天、后7天的记录，再分组求和：

MySQL 版本 #

SELECT 
    main.Date,
    SUM(related.Searches) AS Sum
FROM 
    search_data main
JOIN 
    search_data related 
    ON related.Date IN (DATE_SUB(main.Date, INTERVAL 7 DAY), main.Date, DATE_ADD(main.Date, INTERVAL 7 DAY))
GROUP BY 
    main.Date
ORDER BY 
    main.Date;

PostgreSQL 版本 #

SELECT 
    main.Date,
    SUM(related.Searches) AS Sum
FROM 
    search_data main
JOIN 
    search_data related 
    ON related.Date IN (main.Date - INTERVAL '7 days', main.Date, main.Date + INTERVAL '7 days')
GROUP BY 
    main.Date
ORDER BY 
    main.Date;

SQL Server 版本 #

SELECT 
    main.Date,
    SUM(related.Searches) AS Sum
FROM 
    search_data main
JOIN 
    search_data related 
    ON related.Date IN (DATEADD(day, -7, main.Date), main.Date, DATEADD(day, 7, main.Date))
GROUP BY 
    main.Date
ORDER BY 
    main.Date;

方法二：窗口函数（适配支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、SQL Server 2012+） #

利用范围窗口函数直接计算前后7天内的搜索量总和，语法更简洁：

MySQL 8.0+ 版本 #

SELECT 
    Date,
    SUM(Searches) OVER (
        ORDER BY Date
        RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND INTERVAL 7 DAY FOLLOWING
    ) AS Sum
FROM 
    search_data;

PostgreSQL 版本 #

SELECT 
    Date,
    SUM(Searches) OVER (
        ORDER BY Date
        RANGE BETWEEN '7 days' PRECEDING AND '7 days' FOLLOWING
    ) AS Sum
FROM 
    search_data;

SQL Server 版本 #

SELECT 
    Date,
    SUM(Searches) OVER (
        ORDER BY Date
        RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND INTERVAL 7 DAY FOLLOWING
    ) AS Sum
FROM 
    search_data;

结果验证 #

针对你提供的示例数据，两种方法都会输出如下结果：

完全符合你期望的2/10分组Sum为7、2/17分组Sum为10的要求。

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