sql - 在当前行中,对代表上周(7 天)记录的行的数据求和
问题描述
我有 SQL 表BaseData
(value
列中的值是随机的):
------------------------------------------------------
| Date | Value | Day | Moth | Year |
------------------------------------------------------
| 01-07-2020 | 1 | 1 | 7 | 2020 |
| 02-07-2020 | 2 | 2 | 7 | 2020 |
| 03-07-2020 | 3 | 3 | 7 | 2020 |
| 04-07-2020 | 4 | 4 | 7 | 2020 |
| 05-07-2020 | 5 | 5 | 7 | 2020 |
| 06-07-2020 | 6 | 6 | 7 | 2020 |
| 07-07-2020 | 7 | 7 | 7 | 2020 |
| 08-07-2020 | 8 | 8 | 7 | 2020 |
| 09-07-2020 | 9 | 9 | 7 | 2020 |
| 10-07-2020 | 10 | 10 | 7 | 2020 |
------------------------------------------------------
我需要的是,以BaseData
某种方式查询数据,对于每一行,我将获得过去7 天(不包括当天)的总和。Values
例如,对于 7 月 8 日,我们应该有 7 月 1 日到 7 月 7 日之间的值的总和。
例如,这应该是我上面给定示例的结果表:
----------------------------
| Date | ResultSum |
----------------------------
| 01-07-2020 | 0 | //assuming this is the first record
| 02-07-2020 | 1 | //SUM = 1
| 03-07-2020 | 3 | //SUM = 1 + 2
| 04-07-2020 | 6 | //SUM = 1 + 2 + 3
| 05-07-2020 | 10 | //SUM = 1 + 2 + 3 + 4
| 06-07-2020 | 15 | //SUM = 1 + 2 + 3 + 4 + 5
| 07-07-2020 | 21 | //SUM = 1 + 2 + 3 + 4 + 5 + 6
| 08-07-2020 | 28 | //SUM = 1 + 2 + 3 + 4 + 5 + 6 + 7
| 09-07-2020 | 35 | //SUM = 2 + 3 + 4 + 5 + 6 + 7 + 8
| 10-07-2020 | 42 | //SUM = 3 + 4 + 5 + 6 + 7 + 8 + 9
----------------------------
到目前为止,我所拥有的是:
SELECT Date,
(SELECT SUM(nestedTable.Value)
FROM BaseData AS nestedTable
WHERE DATEADD(dd, DATEDIFF(dd, 0, Date), 0) BETWEEN
DATEADD(day, DATEDIFF(day, 0, DATEADD(d,-1, Date)), 0) AND
DATEADD(day, DATEDIFF(day, 0, DATEADD(d,-8, Date)), 0)
) AS ResultSum
FROM BaseData
但似乎它不起作用。我在这里发现了一些类似的问题,但我无法弄清楚如何在这里使用这些答案。
解决方案
只需使用窗口函数。假设日期在表中是连续的,你会这样做:
select
date,
coalesce(
sum(value) over(order by date rows between 7 preceding and 1 preceding),
0
) resultsum
from basedata
在不支持rows
窗口函数子句的 SQL Server 版本中,您可以改用相关子查询或横向连接:
select
date,
(
select coalesce(sum(b1.value), 0)
from basedata b1
where b1.date between dateadd(day, -7, b.date) and dateadd(day, -1, b.date)
) resultsum
from basedata b
此查询将利用date
列上的索引 - 它具有处理非连续日期的优势。
两个查询都假定日期存储为数据类型date
(或类似的) - 如果不是这种情况,您首先需要cast()
它们(或者更好的是......修复您的数据模型!)。
推荐阅读
- python - Python aioconsole stdout.write() 到变量
- php - 匹配两个数组,如果没有匹配的 ID,则将 post 置于挂起状态
- javascript - 如何在 react-native 中将函数从父级发送到子级并观察变量
- r - ggplot图例在图例上显示错误的形状
- c++ - Visual Studio .CreateInputLayout 的东西
- php - .htacess 隐藏真实网址
- r - 更改轴标签和刻度标签的字体和位置的问题
- django - Ansible playbook 中的主管未运行 Gunicorn
- python - 为什么每个进程的process.cpu_percent()不等于psutil.cpu_percent()
- firebase - Firebase 推送通知未显示