sql - 如何聚合SQL中的一列一周？

Question

我有一个这样的表:

| 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            |

我想编写一个查询来对过去一周的每一天的值求和。我想要的输出是:

 | 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            |

我想每周汇总。如果我做单组，我只会得到每一天的总和，而不是过去一周的总和。

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

Answer 1

你可以使用 OVER 子句

SELECT tot.inputDate, SUM(tot.inputVal)OVER(partition by '' ORDER BY inputDate ROWS BETWEEN 6 preceding and current row)  sum_7_days
FROM 
(SELECT inputDate, SUM(inputVal) as inputVal
FROM test
GROUP BY inputDate) as tot

前面和当前行的单词将获得当前行之前的行数。

请检查这个db fiddle