如何用SQL统计指定区间内按7天间隔的数值型日期记录数？

核心思路 #

1. 将数值型的DATE字段转换为数据库可识别的日期类型，方便进行日期计算
2. 基于日期类型计算每条记录对应的周起始日期（按7天间隔分组）
3. 筛选指定时间范围的数据，按周起始日期分组统计记录数

不同数据库的实现代码 #

MySQL

SELECT
    DATE_FORMAT(DATE_SUB(STR_TO_DATE(`DATE`, '%Y%m%d'), INTERVAL WEEKDAY(STR_TO_DATE(`DATE`, '%Y%m%d')) DAY), '%Y%m%d') AS WEEK,
    COUNT(*) AS COUNT
FROM
    your_table_name
WHERE
    `DATE` BETWEEN 20210101 AND 20231130
GROUP BY
    WEEK
ORDER BY
    WEEK;

• 说明：WEEKDAY()返回0（周一）到6（周日），通过DATE_SUB减去对应天数得到周一作为周起始日期；若需以周日为起始，可将WEEKDAY替换为DAYOFWEEK并调整间隔天数（DAYOFWEEK返回1（周日）到7（周六））

PostgreSQL

SELECT
    TO_CHAR(DATE_TRUNC('week', TO_DATE("DATE"::TEXT, 'YYYYMMDD')), 'YYYYMMDD') AS WEEK,
    COUNT(*) AS COUNT
FROM
    your_table_name
WHERE
    "DATE" BETWEEN 20210101 AND 20231130
GROUP BY
    WEEK
ORDER BY
    WEEK;

• 说明：DATE_TRUNC('week')默认以周一为周起始（若需调整可通过SET datestyle = 'ISO, DMY';确保或使用DATE_TRUNC('week', ...) + INTERVAL '1 day'改为周日起始）

SQL Server

-- 先设置周一为一周第一天（可选，若默认周日为起始可跳过）
SET DATEFIRST 1;

SELECT
    CONVERT(VARCHAR(8), DATEADD(DAY, 1 - DATEPART(WEEKDAY, CONVERT(DATE, CAST("DATE" AS VARCHAR(8)), 112)), CONVERT(DATE, CAST("DATE" AS VARCHAR(8)), 112)), 112) AS WEEK,
    COUNT(*) AS COUNT
FROM
    your_table_name
WHERE
    "DATE" BETWEEN 20210101 AND 20231130
GROUP BY
    DATEADD(DAY, 1 - DATEPART(WEEKDAY, CONVERT(DATE, CAST("DATE" AS VARCHAR(8)), 112)), CONVERT(DATE, CAST("DATE" AS VARCHAR(8)), 112))
ORDER BY
    WEEK;

• 说明：SET DATEFIRST 1指定周一为一周起始；通过DATEADD计算出每条记录对应的周起始日期，最终转换为YYYYMMDD格式的字符串

注意事项 #

• 替换代码中的your_table_name为实际表名
• 数值型DATE字段直接用BETWEEN筛选范围是可行的，因为YYYYMMDD格式的数值大小顺序与日期顺序一致
• 若需要包含统计范围内所有周（即使某周无记录），需生成周日期序列并与原表左连接（可根据数据库特性实现）

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