sql - 根据日期滚动总和(当日期缺失时)

Question

您可能知道将聚合结果滚动到特定数量的前面的行上。即:过去 7 天我吃了多少热狗

SELECT HotDogCount, 
       DateKey, 
       SUM(HotDogCount) OVER (ORDER BY DateKey ROWS 6 PRECEDING) AS HotDogsLast7Days
FROM dbo.HotDogConsumption

结果:

+-------------+------------+------------------+
| HotDogCount |  DateKey   | HotDogsLast7Days |
+-------------+------------+------------------+
|           3 | 09/21/2020 |                3 |
|           2 | 9/22/2020  |                5 |
|           1 | 09/23/2020 |                6 |
|           1 | 09/24/2020 |                7 |
|           1 | 09/25/2020 |                8 |
|           4 | 09/26/2020 |               12 |
|           1 | 09/27/2020 |               13 |
|           3 | 09/28/2020 |               13 |
|           2 | 09/29/2020 |               13 |
|           1 | 09/30/2020 |               13 |
+-------------+------------+------------------+

现在，我遇到的问题是日期之间存在间隙。所以，基本上，有一天我的肠道和循环系统会对我尖叫:“你到底在做什么，你要杀了我们所有人!!!”所以，我决定让我的 body 休息一天，现在没有那一天的记录。当我使用“ROWS 6 PRECEDING”方法时，我现在将返回 8 天，而不是 7 天，因为错过了一天。

所以，问题是，你们中有人知道如何使用 OVER 子句来真正使用日期值(类似于“DATEADD(day,-7,DateKey)”)来确定应该有多少前行总结为真正的 7 天滚动总和，无论我是只在一天吃热狗还是 7 天都吃热狗？

旁注，将我没有吃任何热狗的日子记录为 0 是不可行的。我知道我可以使用日期数组并左连接它并执行

CASE WHEN Datekey IS NULL THEN 0 END

交易类型，但我想知道是否有不同的方式可以根据日期动态确定前面的行值。

Answer 1

理论上，窗口函数是正确的方法。但要回顾之前的 7 天(不是行)，我们需要一个范围框架规范 - 不幸的是，SQL Server 不支持该规范。

我将推荐一个子查询，或者横向连接:

select hdc.*, hdc1.*
from dbo.HotDogConsumption hdc
cross apply (
    select coalesce(sum(HotDogCount), 0) HotDogsLast7Days 
    from dbo.HotDogConsumption hdc1
    where hdc1.datekey >= dateadd(day, -7, hdc.datekey)
      and hdc1.datekey < hdc.datekey
) hdc1

您可能需要将子查询的 where 子句中的条件调整为所需的精确框架。上面的代码计算过去 7 天的数据，不包括今天。与您当前的尝试等效的内容如下:

    where hdc1.datekey >= dateadd(day, -6, hdc.datekey)
      and hdc1.datekey <= hdc.datekey