基于参考日期和设定时间段按自定义周期范围分组数据

这就帮你搞定这个分组查询的需求！先把你的数据结构整理成清晰的表格：

核心需求是从指定起始日期开始，遍历连续N天，按日期分组统计数据，这里我会针对几种主流数据库给出实现方案，还会包含参数化写法方便你在应用中调用：

MySQL（8.0+ 支持递归CTE） #

如果你的MySQL版本是8.0及以上，用递归CTE生成连续日期序列，再左连数据表确保无数据的日期也能显示（用COALESCE把空值转成0）：

-- 示例：从2018-01-25开始，统计10天的数据
WITH RECURSIVE date_range AS (
    SELECT DATE('2018-01-25') AS group_date
    UNION ALL
    SELECT DATE_ADD(group_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE group_date < DATE_ADD('2018-01-25', INTERVAL 9 DAY) -- 天数-1，因为起始日算第1天
)
SELECT 
    dr.group_date,
    COALESCE(SUM(t.volume), 0) AS total_volume
FROM date_range dr
LEFT JOIN your_table t ON DATE(t.createdAt) = dr.group_date
GROUP BY dr.group_date
ORDER BY dr.group_date;

如果要做成参数化查询（比如在Java/Python里传参），可以把固定日期换成占位符：

WITH RECURSIVE date_range AS (
    SELECT DATE(?) AS group_date
    UNION ALL
    SELECT DATE_ADD(group_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE group_date < DATE_ADD(?, INTERVAL ? - 1 DAY)
)
SELECT 
    dr.group_date,
    COALESCE(SUM(t.volume), 0) AS total_volume
FROM date_range dr
LEFT JOIN your_table t ON DATE(t.createdAt) = dr.group_date
GROUP BY dr.group_date
ORDER BY dr.group_date;

三个参数分别是：起始日期、起始日期、要遍历的天数。

PostgreSQL #

PostgreSQL有原生的generate_series函数，生成日期序列更简洁：

-- 示例：从2018-01-25开始，统计10天的数据
SELECT 
    dr.group_date::DATE,
    COALESCE(SUM(t.volume), 0) AS total_volume
FROM generate_series(
    '2018-01-25'::DATE,
    '2018-01-25'::DATE + INTERVAL '9 days',
    INTERVAL '1 day'
) dr(group_date)
LEFT JOIN your_table t ON DATE(t.createdAt) = dr.group_date::DATE
GROUP BY dr.group_date::DATE
ORDER BY dr.group_date::DATE;

参数化版本可以用$1、$2占位：

SELECT 
    dr.group_date::DATE,
    COALESCE(SUM(t.volume), 0) AS total_volume
FROM generate_series(
    $1::DATE,
    $1::DATE + INTERVAL ($2 - 1) || ' days',
    INTERVAL '1 day'
) dr(group_date)
LEFT JOIN your_table t ON DATE(t.createdAt) = dr.group_date::DATE
GROUP BY dr.group_date::DATE
ORDER BY dr.group_date::DATE;

$1是起始日期，$2是要遍历的天数。

SQL Server #

SQL Server同样用递归CTE，注意如果遍历天数超过100，需要加OPTION (MAXRECURSION 0)解除递归深度限制：

-- 示例：从2018-01-25开始，统计10天的数据
WITH date_range AS (
    SELECT CAST('2018-01-25' AS DATE) AS group_date
    UNION ALL
    SELECT DATEADD(DAY, 1, group_date)
    FROM date_range
    WHERE group_date < CAST('2018-01-25' AS DATE) + 9
)
SELECT 
    dr.group_date,
    COALESCE(SUM(t.volume), 0) AS total_volume
FROM date_range dr
LEFT JOIN your_table t ON CAST(t.createdAt AS DATE) = dr.group_date
GROUP BY dr.group_date
ORDER BY dr.group_date
OPTION (MAXRECURSION 0);

参数化版本用变量：

DECLARE @StartDate DATE = '2018-01-25';
DECLARE @DaysCount INT = 10;

WITH date_range AS (
    SELECT @StartDate AS group_date
    UNION ALL
    SELECT DATEADD(DAY, 1, group_date)
    FROM date_range
    WHERE group_date < DATEADD(DAY, @DaysCount - 1, @StartDate)
)
SELECT 
    dr.group_date,
    COALESCE(SUM(t.volume), 0) AS total_volume
FROM date_range dr
LEFT JOIN your_table t ON CAST(t.createdAt AS DATE) = dr.group_date
GROUP BY dr.group_date
ORDER BY dr.group_date
OPTION (MAXRECURSION 0);

这样不管某天有没有数据，都会返回该日期的统计结果（无数据时显示0），完全符合你的需求~

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