SQLServer：使用LAG()和計算出的先前值

我在 SQL Server 2016 資料庫表中有這樣的資料：

我有一個條件來填寫零值，例如：

CASE 
    WHEN PERIODE_FORECAST > PERIODE 
        THEN [PREVIOUS_MONTH] * 0.1
    ELSE VALUE 
END

我正在嘗試使用該LAG()功能，但它沒有像我希望的那樣出現。

詢問 ：

SELECT
    PERIODE,PERIODE_FORECAST,
    CASE 
        WHEN PERIODE_FORECAST > PERIODE 
            THEN LAG(VALUE, 1, 0) OVER (ORDER BY PERIODE_FORECAST ASC) * 0.1 
        ELSE VALUE 
    END VALUE
FROM 
    MyTable

結果 ：

預期成績：

| PERIODE       | PERIODE_FORECAST      | VALUE     |
| --------------|-----------------------|-----------|
| 2021-08-01    | 2021-01-01            | 51384.673 |
| 2021-08-01    | 2021-02-01            | 44118.129 |
| 2021-08-01    | 2021-03-01            | 43164.446 |
| 2021-08-01    | 2021-04-01            | 38113.745 |
| 2021-08-01    | 2021-05-01            | 37306.956 |
| 2021-08-01    | 2021-06-01            | 38390.359 |
| 2021-08-01    | 2021-07-01            | 42692.390 |
| 2021-08-01    | 2021-08-01            | 39814.047 |
| 2021-08-01    | 2021-09-01            | 3981.4047 |
| 2021-08-01    | 2021-10-01            | 398.14047 |
| 2021-08-01    | 2021-11-01            | 39.814047 |
| 2021-08-01    | 2021-12-01            | 3.9814047 |   
|---------------|-----------------------|-----------|

有沒有不創建存盤程序的解決方案或解決方法？

謝謝

uj5u.com熱心網友回復：

您的問題是，當您在第一個周期后 2 個月時，您的值滯后于 0。我認為你必須在函式 lag() 的第二部分使用一個引數。有點像： lag(VALUE, DATEDIFF(m,convert(date,'2021-08-01'),convert(date,'2021-10-01')) ,0 )

這里 ：

滯后（值，DATEDIFF（m，轉換（日期，PERIODE），轉換（日期，PERIODE_FORECAST）），0）

您可能需要將此 datediff 添加為乘法器 0.1 的指數。

uj5u.com熱心網友回復：

這是一個有趣的問題，我的方法可能不是最高效的，因為我是一名程式員，但它確實產生了您期望的結果。我首先創建一個公共表運算式并使用 ROW_NUMBER 對行進行編號。然后我回到 CTE 以找到具有最高 ROW_NUMBER 的單個 VALUE。您可以使用 ROW_NUMBER 中的差異作為 10.0000 的 POWER 來獲得乘數。

首先我創建了你的資料：

DECLARE @Forecast AS TABLE
(
    [PERIODE]          DATE           NOT NULL,
    [PERIODE_FORECAST] DATE           NOT NULL,
    [VALUE]            DECIMAL(16, 8) NOT NULL
);

INSERT INTO @Forecast
(
    [PERIODE],
    [PERIODE_FORECAST],
    [VALUE]
)
VALUES
('2021-08-01', '2021-01-01', 51384.673),
('2021-08-01', '2021-02-01', 44118.129),
('2021-08-01', '2021-03-01', 43164.446),
('2021-08-01', '2021-04-01', 38113.745),
('2021-08-01', '2021-05-01', 37306.956),
('2021-08-01', '2021-06-01', 38390.359),
('2021-08-01', '2021-07-01', 42692.390),
('2021-08-01', '2021-08-01', 39814.047),
('2021-08-01', '2021-09-01', 0.000),
('2021-08-01', '2021-10-01', 0.000),
('2021-08-01', '2021-11-01', 0.000),
('2021-08-01', '2021-12-01', 0.000);

一旦我有了這個，查詢是：

;WITH [NumberedRows]
AS (SELECT [PERIODE],
           [PERIODE_FORECAST],
           [VALUE],
           ROW_NUMBER() OVER (PARTITION BY [PERIODE]
                              ORDER BY [PERIODE_FORECAST]
                             ) AS [rn]
    FROM   @Forecast)
SELECT [nr1].[PERIODE],
       [nr1].[PERIODE_FORECAST],
       CASE WHEN [sub].[VALUE] IS NOT NULL THEN
                [sub].[VALUE] * POWER(10.0000000, ([sub].[rn] - [nr1].[rn]))
            ELSE
                [nr1].[VALUE]
       END AS [VALUE]
FROM   [NumberedRows] AS [nr1]
       LEFT OUTER JOIN
       (
           SELECT   TOP(1)
                    [nr2].[PERIODE], [nr2].[PERIODE_FORECAST], [nr2].[VALUE], [nr2].[rn]
           FROM     [NumberedRows] [nr2]
           WHERE    [nr2].[VALUE] <> 0
           ORDER BY [nr2].[rn] DESC
       ) AS [sub]
           ON [sub].[PERIODE] = [nr1].[PERIODE]
              AND [sub].[rn] < [nr1].[rn];

結果：

uj5u.com熱心網友回復：

這是間隙和孤島問題，其中每個非零值都標志著新孤島的開始。分組后，您可以first_value用來挑選相應的“先前值”，同時row_number用作計算乘數的偏移量。

with A as (
    select *,
      sum(case when "value" <> 0 then 1 else 0 end)
        over (order by periode_forecast) as grp
    from T
), B as (
    select *,
      first_value("value")
        over (partition by grp order by periode_forecast) as pv,
      power(10e, 1 - row_number()
        over (partition by grp order by periode_forecast)) as mult
    from A
)
select periode_forecast, "value", grp, pv, pv * mult as new_value
from B
order by periode_forecast;

您可能想堅持使用decimal數學并避免float. 如果是這樣，則調整10e內部power()參考。

如果沒有有效的先前非零行，則結果將為零。目前尚不清楚這是否會發生在您的資料中，或者如何以不同的方式對待它。

https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=545d6e84ea43885788e45a2fb0393884

標籤：sql sql-server 日期 约会时间 递归查询

上一篇：{%if%}塊中的Django模板標簽不顯示

下一篇：如何調整此功能以使用夏令時？