如何编写SQL查询展示日期及7天前的聚合数据？

假设你的原始数据表名为your_table，结构与数据如下：

你已经知道用GROUP BY可以得到每日的item统计数：

SELECT date, COUNT(*) AS count
FROM your_table
GROUP BY date;

要同时展示每个日期的统计数及其7天前的日期和统计数，可以通过自连接预计算的每日统计结果来实现，具体步骤如下：

方法：使用CTE（公共表表达式）+ 自连接 #

先通过CTE计算出每日的基础统计，再将这个统计结果自连接，关联条件为当前日期的7天前等于另一条记录的日期：

WITH daily_counts AS (
    -- 先计算每日的item数量
    SELECT date, COUNT(*) AS count
    FROM your_table
    GROUP BY date
)
SELECT 
    dc.date,
    dc.count,
    -- 计算当前日期的7天前日期（注意：不同数据库语法有差异，见下方说明）
    DATE_SUB(dc.date, INTERVAL 7 DAY) AS `date-7`,
    -- 用COALESCE处理7天前无数据的情况，返回0而非NULL
    COALESCE(dc_prev.count, 0) AS `count(date-7)`
FROM daily_counts dc
-- 左连接确保即使7天前无数据，当前日期的记录仍会保留
LEFT JOIN daily_counts dc_prev 
    ON dc_prev.date = DATE_SUB(dc.date, INTERVAL 7 DAY);

不同数据库的日期加减语法适配 #

上述代码中的DATE_SUB是MySQL/MariaDB的语法，其他数据库需要替换为对应函数：

• PostgreSQL：将DATE_SUB(dc.date, INTERVAL 7 DAY)替换为dc.date - INTERVAL '7 days'
• SQL Server：替换为DATEADD(day, -7, dc.date)
• Oracle：替换为dc.date - 7（Oracle日期类型直接加减数字即为天数）

示例结果 #

假设date1-7、date3-7、date4-7在原始表中无对应数据，执行后的结果如下：

如果某日期的7天前存在统计数据（比如假设date4的7天前是date1），则count(date-7)会显示对应数值（此处为2）。

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