SQL中如何实现每日过去一周的数据列聚合求和？

给定如下数据表：

| date | value |
| -------- | -------------- |
| 1/1    | 1            |
| 1/1   | 2            |
| 1/2   | 2            |
| 1/3   | 2            |
| 1/3   | 3            |
| 1/4   | 5           |
| 1/4   | 2            |
| 1/5   | 2            |
| 1/5   | 3            |
| 1/5   | 4            |
| 1/6   | 2            |
| 1/7   | 4            |
| 1/7   | 5            |
| 1/7   | 2            |
| 1/7   | 1            |
| 1/7   | 3            |
| 1/8   | 4            |
| 1/9   | 2            |

需要编写SQL查询，计算每一天**过去一周内（含当天）**的所有value值，以+连接成字符串（按日期从近到远、同日期内value倒序排列），期望输出如下：

| date | value |
| -------- | -------------- |
| 1/1    | 2+1            |
| 1/2   | 2+2+1            |
| 1/3   | 3+2+2+2+1            |
| 1/4   | 2+5+3+2+2+2+1           |
| 1/5   | 4+3+2+2+5+3+2+2+2+1             |
| 1/6   | 2+4+3+2+2+5+3+2+2+2+1            |
| 1/7   | 3+1+2+5+4+2+4+3+2+2+5+3+2+2+2+1           |
| 1/8   | 4+3+1+2+5+4+2+4+3+2+2+5+3+2+2            |
| 1/9   | 2+4+3+1+2+5+4+2+4+3+2+2+5+3+2            |

单一GROUP BY只能得到每日总和，无法满足需求：

SELECT date,SUM(value) AS total FROM table GROUP BY date

要实现该需求，需通过自连接关联目标日期过去一周内的数据，再用字符串聚合函数拼接value。以下是几种常见数据库的实现方式：

MySQL/MariaDB #

使用GROUP_CONCAT函数，搭配自连接筛选时间范围：

SELECT 
    t1.date,
    GROUP_CONCAT(t2.value ORDER BY t2.date DESC, t2.value DESC SEPARATOR '+') AS value
FROM 
    your_table t1
JOIN 
    your_table t2 ON t2.date BETWEEN DATE_SUB(t1.date, INTERVAL 6 DAY) AND t1.date
GROUP BY 
    t1.date
ORDER BY 
    t1.date;

• DATE_SUB(t1.date, INTERVAL 6 DAY)取当前日期前6天到当天的范围，覆盖一周7天
• 排序规则保证近日期的value在前，同日期内value倒序排列，与期望输出一致

PostgreSQL #

使用STRING_AGG函数，需将数值型value转为文本：

SELECT 
    t1.date,
    STRING_AGG(t2.value::TEXT, '+' ORDER BY t2.date DESC, t2.value DESC) AS value
FROM 
    your_table t1
JOIN 
    your_table t2 ON t2.date >= t1.date - INTERVAL '6 days' AND t2.date <= t1.date
GROUP BY 
    t1.date
ORDER BY 
    t1.date;

SQL Server（2017及以上版本） #

使用STRING_AGG函数，通过WITHIN GROUP指定排序规则：

SELECT 
    t1.date,
    STRING_AGG(CAST(t2.value AS VARCHAR(10)), '+' ) WITHIN GROUP (ORDER BY t2.date DESC, t2.value DESC) AS value
FROM 
    your_table t1
JOIN 
    your_table t2 ON t2.date >= DATEADD(DAY, -6, t1.date) AND t2.date <= t1.date
GROUP BY 
    t1.date
ORDER BY 
    t1.date;

注意事项 #

• 确保date字段为日期类型，若为字符串需先转换为日期再做范围判断，避免字符串排序导致的逻辑错误
• 若数据库不支持上述字符串聚合函数，可通过自定义函数或变量拼接的方式实现，核心逻辑仍为自连接筛选时间范围后按顺序拼接

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