在SQL中为时间序列数据创建滚动窗口

Question

我有一个关于在SQL中添加滚动窗口列的问题。表A是24个月时间序列数据的样本。我需要添加上个月和上个月前一个月的每月余额之间的差额栏。例如，对于2020年3月，我需要分别为每个ID的3月和2月以及3月和1月之间的存款和取款差异(表B(。我尝试在sql中使用"window"函数，但我不知道如何使用。

**Table A**
ID      | Date      |A      | B       |
+--------+-----------+-------+---------
| 1      | Jan 20    | $200  | $100    |
| 1      | Feb 20    | $500  | $250    |
| 1      | Mar 20    | $1000 | $550    | 
+--------+-----------+-------+---------+

我想要这样的结果：

**Table B**
ID      | Date      |A      | B       | A(Mar-Feb)| A(Mar-Jan)| B(Mar-Feb)| B(Mar-Jan)|
+--------+-----------+-------+------------------------------------------------------
| 1      | Jan 20    | $200  | $100    |            |           |          |           |
| 1      | Feb 20    | $500  | $250    |            |           |          |           |
| 1      | Mar 20    | $1000 | $550    | $500       |$800       |$300      |$450       |
+--------+-----------+-------+---------+------------+-----------+----------+-----------+

如果有人能帮我，我将不胜感激。

Answer 1

编辑：根据OP 的更多信息，请参阅底部的编辑以获得更正的答案

我"思考；这就是你所要求的，可能不是你想要的，因为它也会填充其他行。。。

IF OBJECT_ID('tempdb..#TableA','U') IS NOT NULL DROP TABLE #TableA; --SELECT * FROM #TableA
CREATE TABLE #TableA (
ID     int  NOT NULL,
[Date] date NOT NULL,
A      int  NOT NULL,
B      int  NOT NULL,
)
INSERT INTO #TableA (ID, Date, A, B)
VALUES (1, '2020-01-01',  200, 100)
,  (1, '2020-02-01',  500, 250)
,  (1, '2020-03-01', 1000, 550)
SELECT ta.ID
, [Date] = FORMAT(ta.[Date],'MMM yy')
, ta.A, ta.B
, A_DiffPrev  = ta.A - LAG(ta.A)         OVER (ORDER BY ta.[Date])
, A_DiffFirst = ta.A - FIRST_VALUE(ta.A) OVER (ORDER BY ta.[Date])
, B_DiffPrev  = ta.B - LAG(ta.B)         OVER (ORDER BY ta.[Date])
, B_DiffFirst = ta.B - FIRST_VALUE(ta.B) OVER (ORDER BY ta.[Date])
FROM #TableA ta

退货：

| ID | Date   | A    | B   | A_DiffPrev | A_DiffFirst | B_DiffPrev | B_DiffFirst | 
|----|--------|------|-----|------------|-------------|------------|-------------| 
| 1  | Jan 20 | 200  | 100 | NULL       | 0           | NULL       | 0           | 
| 1  | Feb 20 | 500  | 250 | 300        | 300         | 150        | 150         | 
| 1  | Mar 20 | 1000 | 550 | 500        | 800         | 300        | 450         | 

---

说明

• LAG(ta.A) OVER (ORDER BY ta.[Date])-这将为您提供按提供的ORDER BY排序的上一个值。因此，在这种情况下，它说，如果您按[Date]升序排序，请给我出现在当前行之前的值
• FIRST_VALUE(ta.A) OVER (ORDER BY ta.[Date])-与LAG()的想法类似，只是说要获得第一个项目，而不是前一个项目

---

编辑

在您提到的评论中，FIRST_VALUE()对您不起作用，因为您不想与第一个月进行比较，您想与前一个月和两个月后进行比较。

在这种情况下，您可以使用以下解决方案：

SELECT ta.ID
, [Date] = FORMAT(ta.[Date],'MMM yy')
, ta.A, ta.B
, A_DiffPrev1 = ta.A - LAG(ta.A,1) OVER (ORDER BY ta.[Date])
, A_DiffPrev2 = ta.A - LAG(ta.A,2) OVER (ORDER BY ta.[Date])
, B_DiffPrev1 = ta.B - LAG(ta.B,1) OVER (ORDER BY ta.[Date])
, B_DiffPrev2 = ta.B - LAG(ta.B,2) OVER (ORDER BY ta.[Date])
FROM #TableA ta

说明：

在这次更改中，我对所有内容都使用LAG()。但是，我告诉LAG()我希望它向后看多少行。

因此，为了获得前一个月，我说LAG(A, 1)，这意味着抓取前一行，这是默认的，我在这里提供它只是为了更明确地说明发生了什么。

然后我说LAG(A, 2)，意思是返回两行并获取该值。

注意：这都是假设你的数据没有缺口。