sql - 计算每日使用量

Question

我有如下日常使用数据。



我需要为每种 2000 用法重新填充产品。这是预期的结果。



我尝试使用SUM OVER查询来获取每日的先前总使用量，并尝试除以得到2000的倍数。

SELECT Date, Usage,
       SUM(Usage) OVER(ORDER BY Date) AS DailyTotal,
       CAST(SUM(Usage) OVER(ORDER BY Date) / 2000 AS INT) Div
FROM Transaction

但是，我仍然找不到我必须补充的日期。我该怎么做？

Answer 1

您可以尝试将LAG窗口函数与子查询一起使用。

MS SQL Server 2017架构设置:

create table [Transaction]([Date] date, usage int);

insert into [Transaction] values
('2017-01-01',1373),
('2017-01-02',1387),
('2017-01-03',1509),
('2017-01-04',1523),
('2017-01-05',1537);

查询1 :

SELECT Date, 
       Usage,
       DailyTotal,
       (CASE WHEN LAG(Div,1,Div) OVER(ORDER BY Date) <> Div THEN 1 ELSE 0 END) div
FROM (
    SELECT Date, Usage,
           SUM(Usage) OVER(ORDER BY [Date]) AS DailyTotal,
           CAST(SUM(Usage) OVER(ORDER BY [Date]) / 2000 AS INT) Div
    FROM [Transaction]
)t1

Results :

|       Date | Usage | DailyTotal | div |
|------------|-------|------------|-----|
| 2017-01-01 |  1373 |       1373 |   0 |
| 2017-01-02 |  1387 |       2760 |   1 |
| 2017-01-03 |  1509 |       4269 |   1 |
| 2017-01-04 |  1523 |       5792 |   0 |
| 2017-01-05 |  1537 |       7329 |   1 |