英文:
Aggregate rolling totals for last 3 months for each month
问题
| ID | 日期 | 费用 | 过去3个月总计 |
|---|---|---|---|
| 1 | 202201 | 100 | 220 |
| 1 | 202112 | 50 | 240 |
| 1 | 202111 | 70 | 195 |
| 1 | 202110 | 120 | 125 |
| 1 | 202110 | 5 | 5 |
英文:
| ID | Date | Cost |
|---|---|---|
| 1 | 202201 | 100 |
| 1 | 202112 | 50 |
| 1 | 202111 | 70 |
| 1 | 202110 | 120 |
| 1 | 202110 | 5 |
Required output: sum of previous 3 months of totals
| ID | Date | Cost | Rolling3Months |
|---|---|---|---|
| 1 | 202201 | 100 | 220 |
| 1 | 202112 | 50 | 240 |
| 1 | 202111 | 70 | 195 |
| 1 | 202110 | 120 | 125 |
| 1 | 202110 | 5 | 5 |
Required output: sum of previous 3 months of totals
答案1
得分: 3
以下是翻译好的部分：
创建表 your_table (
ID INT,
[Date] date, --<< [Date] is a terrible column name & changed the data to be actual dates
Cost INT
);
向 your_table 插入数据 (ID, Date, Cost) VALUES (1, '20220101', 100);
向 your_table 插入数据 (ID, Date, Cost) VALUES (1, '20211201', 50);
向 your_table 插入数据 (ID, Date, Cost) VALUES (1, '20211101', 70);
向 your_table 插入数据 (ID, Date, Cost) VALUES (1, '20211001', 120);
向 your_table 插入数据 (ID, Date, Cost) VALUES (1, '20210901', 5); --<< changed to "09"
SELECT ID, Date, Cost,
SUM(Cost) OVER (PARTITION BY ID ORDER BY [Date] ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Rolling3Months
FROM your_table
order by [date] DESC
| ID | Date | Cost | Rolling3Months |
|---|---|---|---|
| 1 | 2022-01-01 | 100 | 220 |
| 1 | 2021-12-01 | 50 | 240 |
| 1 | 2021-11-01 | 70 | 195 |
| 1 | 2021-10-01 | 120 | 125 |
| 1 | 2021-09-01 | 5 | 5 |
查询也适用于你所称之为“Date”的YYYYMM字符串。请参考：https://dbfiddle.uk/xbxUsZA6
英文:
Assuming you do actually want a SQL Server solution:
CREATE TABLE your_table (
ID INT,
[Date] date, --<< [Date] is a terrible column name & changed the data to be actual dates
Cost INT
);
INSERT INTO your_table (ID, Date, Cost) VALUES (1, '20220101', 100);
INSERT INTO your_table (ID, Date, Cost) VALUES (1, '20211201', 50);
INSERT INTO your_table (ID, Date, Cost) VALUES (1, '20211101', 70);
INSERT INTO your_table (ID, Date, Cost) VALUES (1, '20211001', 120);
INSERT INTO your_table (ID, Date, Cost) VALUES (1, '20210901', 5); --<< changed to "09"
5 rows affected
SELECT ID, Date, Cost,
SUM(Cost) OVER (PARTITION BY ID ORDER BY [Date] ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Rolling3Months
FROM your_table
order by [date] DESC
| ID | Date | Cost | Rolling3Months |
|---|---|---|---|
| 1 | 2022-01-01 | 100 | 220 |
| 1 | 2021-12-01 | 50 | 240 |
| 1 | 2021-11-01 | 70 | 195 |
| 1 | 2021-10-01 | 120 | 125 |
| 1 | 2021-09-01 | 5 | 5 |
the query will also work on the YYYYMM strings you call "Date". See: https://dbfiddle.uk/xbxUsZA6
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论