从 SQL 上不规则日期的集合中滚动 3 个月的总和和行计数

Question

是否可以获得每个id的3个月滚动总和(值(？

数据：

+--------------------+---------------+----------------+---------------+
| tiq_transaction_id | id            | date           | value         |
+--------------------+---------------+----------------+---------------+
|           23980806 | a             | 2018-05-15     | 73614336.7168 |
|           24957628 | a             | 2018-09-28     | 47859499.2648 |
|           25403113 | a             | 2018-11-23     | 40708340.1377 |
|           25771232 | a             | 2019-01-07     | 15180519.2423 |
|           23455679 | b             | 2018-02-27     | 21032280.0296 |
|           24456317 | b             | 2018-07-25     | 8081544.0407  |
|           22963119 | c             | 2017-12-01     | 7570269.5457  |
|           22962927 | c             | 2017-12-04     | 8632491.8602  |
|           22962741 | c             | 2017-12-05     | 7029459.0614  |
|           22982978 | c             | 2017-12-06     | 5147303.2     |
|           22982751 | c             | 2017-12-07     | 7667280.7413  |
|           22982520 | c             | 2017-12-08     | 10090265.727  |
|           23007107 | c             | 2017-12-11     | 4930930.2813  |
+--------------------+---------------+----------------+---------------+

Answer 1

在标准 SQL 中，您可以将窗口函数与间隔定义一起使用：

select id, 
date,
value,
sum(value) over (partition by id 
order by date 
range between '3 month' preceding and current row) as running_sum 
from the_table   
order by id, date;

在线示例(使用Postgres(

Answer 2

这是您的查询。(適用於MS SQL Server(

1. 获取您的 yyyyMMdateadd(month, datediff(month, 0, [date]), 0)
2. 使用基于 yyyyMMsum over3 个月的数据
3. 然后group byID

select id, dateadd(month, datediff(month, 0, [date]), 0) as yyyymm
,sum(sum([value])) over 
(partition by id order by 
dateadd(month, datediff(month, 0, [date]), 0) 
rows between 2 preceding and current row) as Rolling3Months
from test
group by id,  dateadd(month, datediff(month, 0, [date]), 0)
order by id, yyyymm

尝试 SQLFIDDLE