如何基于声明变量修改SQL查询实现按指定天数聚合数据？

我的基础查询如下：

SELECT 
  eventtime::date AS p_date, 
  SUM(CASE WHEN x = 1 THEN 1 ELSE 0 END) AS ABC
FROM 
  310189
GROUP BY 
 p_date
ORDER BY 
  p_date;

我希望修改这个查询，让它能根据声明的变量值对ABC进行聚合。比如变量设为7时，就按每7天一组来聚合。

我已经知道怎么声明变量：

DECLARE @aggregation_days INT;
SET @aggregation_days = 1

但不确定怎么修改查询适配这个变量。试了CASE语句没成功，而且觉得不是最优方案；也试过date_trunc函数，同样没效果。想问能不能用generate_series函数实现这个需求？

可以用generate_series结合日期分组实现这个需求，核心是先按指定天数生成连续的时间区间，再将原表数据映射到对应区间后聚合计算。

实现代码 #

DECLARE @aggregation_days INT;
SET @aggregation_days = 7; -- 可修改为需要的聚合天数

WITH date_ranges AS (
    SELECT 
        start_date,
        start_date + INTERVAL '@aggregation_days days' - INTERVAL '1 day' AS end_date
    FROM (
        SELECT generate_series(
            (SELECT MIN(eventtime::date) FROM 310189),
            (SELECT MAX(eventtime::date) FROM 310189),
            INTERVAL '@aggregation_days days'
        ) AS start_date
    ) AS ranges
)
SELECT 
    dr.start_date AS period_start,
    dr.end_date AS period_end,
    SUM(CASE WHEN t.x = 1 THEN 1 ELSE 0 END) AS ABC
FROM date_ranges dr
LEFT JOIN 310189 t 
    ON t.eventtime::date BETWEEN dr.start_date AND dr.end_date
GROUP BY dr.start_date, dr.end_date
ORDER BY dr.start_date;

代码说明 #

1. 生成日期区间：利用generate_series从表中最早日期开始，按@aggregation_days间隔生成每个分组的起始日期，再计算对应结束日期（起始日期+指定天数-1天）。
2. 关联原表：通过左连接将原表数据映射到对应区间，保证无数据的区间也会显示（ABC值为0）。
3. 聚合计算：按生成的日期区间分组，统计每个区间内x=1的记录数得到ABC值。

注意事项 #

如果使用的是PostgreSQL，上述语法完全适用；若为其他数据库（如SQL Server），generate_series的语法需调整，但核心逻辑保持一致。

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