每3天SQL累积总和（）

发布于 2025-02-01 16:18:58 字数 523 浏览 4 评论 0原文

我有这样的表

date       amount
2020-02-01    5 
2020-02-02    2 
2020-02-03    10 
2020-02-04    2  
2020-02-06    3 
2020-02-07    1

，我需要每3天每3天进行sum（）：

date       amount   sum
2020-02-01    5      5
2020-02-02    2      7
2020-02-03    10     17
2020-02-04    2      2
2020-02-06    3      5
2020-02-07    1      1
...

因此，当几天之间的差异为3时，总和应该重新开始。有些日子可能不在桌子上。

我尝试使用窗口函数进行此操作，例如sum（nument）（按日期订购），但我不知道如何设置固定的天数并获得这样的累积总和的日期差异。在任何SQL中都可以吗？

原文

I have a table like this

date       amount
2020-02-01    5 
2020-02-02    2 
2020-02-03    10 
2020-02-04    2  
2020-02-06    3 
2020-02-07    1

And I need sum() every 3 days as below:

date       amount   sum
2020-02-01    5      5
2020-02-02    2      7
2020-02-03    10     17
2020-02-04    2      2
2020-02-06    3      5
2020-02-07    1      1
...

So when a difference between days is 3, the summation should start over. Some days may not be in the table.

I tried to do this with window function like sum(amount) over (order by date) but I have no idea how to set a fixed number of days and get the date difference in cumulative sum like this. Is it possible in any SQL?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

雨轻弹 2025-02-08 16:18:58

在MS SQL Server'2020-02-01

select t.[date], t.Amount, sum(t.Amount) over(partition by datediff(d, '2020-02-01', t.[date])/3 order by t.[date]) cum
from tbl t

'中是您想要的开始日期。

In MS Sql Server

select t.[date], t.Amount, sum(t.Amount) over(partition by datediff(d, '2020-02-01', t.[date])/3 order by t.[date]) cum
from tbl t

'2020-02-01' is a starting date you want.

回复收藏 0 原文

情何以堪。 2025-02-08 16:18:58

免责声明
以下解决方案是根据SQL Server 2022的预览版编写的，因此可能无法反映最终版本。

，如果您可以访问SQL Server 2022（昨天进入预览），则可以进行一些乐趣。可以使用date_bucket将分区中的日期到3天，以最低日期作为开始日期。

DECLARE @StartDate date,
        @EndDate date;

SELECT @StartDate = MIN(date),
       @EndDate = MAX(date)
FROM dbo.YourTable;

SELECT date,
       SUM(amount) OVER (PARTITION BY DATE_BUCKET(DAY,3,date,@StartDate) ORDER BY date) AS Amount
FROM dbo.YourTable
WHERE date >= @StartDate
  AND date <= @EndDate; --Incase this would be parametrised

结果图像如预期的，因为2022年的提琴是不存在的：

Disclaimer
The following solution was written based on a Preview version of SQL Server 2022, and thus may not reflect the final release.

For a bit of fun, if you had access to SQL Server 2022 (which went into preview yesterday) you could use DATE_BUCKET to "round" the date in the PARTITION BY to 3 days, using the minimum date as the starting date.

DECLARE @StartDate date,
        @EndDate date;

SELECT @StartDate = MIN(date),
       @EndDate = MAX(date)
FROM dbo.YourTable;

SELECT date,
       SUM(amount) OVER (PARTITION BY DATE_BUCKET(DAY,3,date,@StartDate) ORDER BY date) AS Amount
FROM dbo.YourTable
WHERE date >= @StartDate
  AND date <= @EndDate; --Incase this would be parametrised

Image of results as expected, as Fiddles of 2022 don't exist: