SQL - YearMonth整数的延迟/延迟

Question

我有一张桌子：

| YearMonth | Client_ID |
| 202001    | A         |
| 201903    | A         |
| 201802    | A         |
| 202001    | B         |
| 202001    | B         |

我正在寻找下面的输出，其中YearMonth_1是(YearMonth - 1)，YearMonth_2是(YearMonth - 2)，依此类推：

| YearMonth | Client_ID | YearMonth_1 | YearMonth_2 | YearMonth_3 |
| 202001    | A         | 201912      | 201911      | 201910      |
| 201903    | A         | 201902      | 201901      | 201812      |
| 201802    | A         | 201801      | 201712      | 201711      |
| 202001    | B         | 201912      | 201911      | 201910      |
| 201912    | B         | 201911      | 201910      | 201909      |

在我的真实数据集中，我有直到YearMonth_8，我的解决方案是：

SELECT YearMonth, Client_ID, (YearMonth - 1) AS YearMonth_1, 
(YearMonth - 2) AS YearMonth_2, (YearMonth - e) AS YearMonth_3
FROM TABLE1

当为YearMonth = 202001时，YearMonth_1将为202000。我用以下方法来解决这个问题：

UPDATE TABLE1
SET YearMonth_1 = 201912
WHERE YearMonth_1 = 202000

这是一个简单而快速的解决方案。但是，当你在我的真实数据集中达到YearMonth_3或YearMonth_8时，我必须在YearMonth = 20XX01时为YearMonth_3设置3个值，或者为YearMonth_8设置8个值，这非常耗时。有没有更有效的方法？

Answer 1

我会将这些值转换为日期：

select t.*,
       year(dateadd(month, -1, yyyymm)) * 100 + month(dateadd(month, -1, yyyymm)),
       year(dateadd(month, -2, yyyymm)) * 100 + month(dateadd(month, -2, yyyymm)),
       year(dateadd(month, -3, yyyymm)) * 100 + month(dateadd(month, -3, yyyymm))
from t cross apply
     (values (convert(date, concat(yearmonth, '01')))) v(yyyymm)

Answer 2

您真的应该将EOMONTH(date)存储为date，而不是奇怪的int。

但是你不需要存储之前的8个值，你可以动态查询它：

SELECT
    ClientID,
    YearMonth,
    YearMonth_1 = LAG(YearMonth, 1) OVER (PARTITION BY ClientID ORDER BY YearMonth,
    YearMonth_2 = LAG(YearMonth, 2) OVER (PARTITION BY ClientID ORDER BY YearMonth,
    YearMonth_3 = LAG(YearMonth, 3) OVER (PARTITION BY ClientID ORDER BY YearMonth,
    YearMonth_4 = LAG(YearMonth, 4) OVER (PARTITION BY ClientID ORDER BY YearMonth,
    YearMonth_5 = LAG(YearMonth, 5) OVER (PARTITION BY ClientID ORDER BY YearMonth,
    YearMonth_6 = LAG(YearMonth, 6) OVER (PARTITION BY ClientID ORDER BY YearMonth,
    YearMonth_7 = LAG(YearMonth, 7) OVER (PARTITION BY ClientID ORDER BY YearMonth,
    YearMonth_8 = LAG(YearMonth, 8) OVER (PARTITION BY ClientID ORDER BY YearMonth,
FROM t

最好在(ClientID, YearMonth)上建立索引