基于日期范围的滚动总和在SQL中的实现

Question

寻找一种性能有效的SQL代码，用于基于日期求和计算滚动总和。我的数据如下：

+----+-----------+----+
| ID |   Date    | Qt |
+----+-----------+----+
|  1 | 1/12/2009 |  2 |
|  2 | 1/13/2009 |  3 |
|  3 | 1/14/2009 | 10 |
|  4 | 1/15/2009 |  2 |
|  5 | 1/16/2009 |  3 |
|  6 | 1/17/2009 |  7 |
|  7 | 1/18/2009 |  5 |
|  8 | 1/19/2009 |  4 |
|  9 | 1/20/2009 |  2 |
| 10 | 1/21/2009 |  3 |
| 11 | 1/22/2009 | 10 |
| 12 | 1/23/2009 |  2 |
| 13 | 1/24/2009 |  3 |
| 14 | 1/25/2009 |  7 |
| 15 | 1/26/2009 |  5 |
| 16 | 1/27/2009 |  4 |
| 17 | 1/28/2009 |  1 |
| 18 | 1/29/2009 |  0 |
| 19 | 1/30/2009 |  8 |
| 20 | 1/31/2009 |  9 |
+----+-----------+----+

输出应该像这样：

+----+-----------+----+-----------+
| ID |   Date    | Qt | Roll10Day |
+----+-----------+----+-----------+
|  1 | 1/12/2009 |  2 |         2 |
|  2 | 1/13/2009 |  3 |         5 |
|  3 | 1/14/2009 | 10 |        15 |
|  4 | 1/15/2009 |  2 |        17 |
|  5 | 1/16/2009 |  3 |        20 |
|  6 | 1/17/2009 |  7 |        27 |
|  7 | 1/18/2009 |  5 |        32 |
|  8 | 1/19/2009 |  4 |        36 |
|  9 | 1/20/2009 |  2 |        38 |
| 10 | 1/21/2009 |  3 |        41 |
| 11 | 1/22/2009 | 10 |        49 |
| 12 | 1/23/2009 |  2 |        48 |
| 13 | 1/24/2009 |  3 |        41 |
| 14 | 1/25/2009 |  7 |        46 |
| 15 | 1/26/2009 |  5 |        48 |
| 16 | 1/27/2009 |  4 |        45 |
| 17 | 1/28/2009 |  1 |        41 |
| 18 | 1/29/2009 |  0 |        37 |
| 19 | 1/30/2009 |  8 |        43 |
| 20 | 1/31/2009 |  9 |        49 |
+----+-----------+----+-----------+

我们能用窗口函数实现吗？我知道可以在SQL中使用内部查询来获取它。

- Jay

---

可能是重复的问题：SQL Server：如何在同一表中为不同客户获取3天滚动总和。 - Lars Br.

为什么你需要在MS Sql Server和HANA中都使用它？ - András

Answer 1

这通常可以使用带有sum()的窗口子句来处理：

select t.*,
       sum(qt) over (order by date rows between 9 preceding and current row)
from t;

我不知道SAP Hana是否支持这种语法，但SQL Server是支持的。

- Gordon Linoff

---

我只是在想，是否有一种方法可以用“BETWEEN [DATE - 9] and [DATE]”来替换“BETWEEN 9 PRECEDING AND CURRENT ROW”。 - Jay

@Jay...我不相信SQL Server支持那个。 - Gordon Linoff

Answer 2

你可以这样做来处理行与行之间不存在连续日期的情况。

SELECT 
t1.Id
,t1.Date
,t1.Qt
,SUM(t2.Qt) as Rolling10DayQt
FROM [your-table] t1

    CROSS APPLY (
        SELECT *
        FROM [your-same-table] a
        WHERE a.Id= t1.Id 
              AND (a.Date > DATEADD(DAY,-10,t1.Date) AND a.Date <= t1.Date) ) t2

GROUP BY t1.Id ,t1.Date ,t1.Qt

希望这可以帮到您。

- user6235970

---

顺便提一下，这在 Oracle 中似乎是默认支持的 -- 请参见 https://blog.jooq.org/2016/10/31/a-little-known-sql-feature-use-logical-windowing-to-aggregate-sliding-ranges/ 的结尾。 - kolistivra

Answer 3

尝试一下这个...

select
 ID
,Date
,Qt
,SUM(Qt) OVER (ORDER BY  ID  ROWS BETWEEN UNBOUNDED PRECEDING  AND  0  PRECEDING) AS 'Roll10Day'
from [TABLE NAME]

- M Danish

---

我在想是否有办法将 "BETWEEN 9 PRECEDING AND CURRENT ROW" 替换成 "BETWEEN [DATE - 9] and [DATE]"。 - Jay