T-SQL-使用前几行的计算值更新行

Question

我需要创建一个数据集，使用前一行的结果计算值，但只有第一行包含实际值。

这是我的数据集：

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp (
    Date DATE
    , Month INT
    , Increment FLOAT
    , Results FLOAT
)
INSERT INTO #tmp(Date, Month, Increment, Results) 
VALUES
('7/1/2022', 0, 0.0027347877960046, 0.00439631056653702)
, ('7/1/2022', 1,   0.0332610867687839, NULL)
, ('7/1/2022', 2,   0.0541567096339919, NULL)
, ('7/1/2022', 3,   0.0534245249728661, NULL)
, ('7/1/2022', 4,   0.0497604938051764, NULL)
, ('7/1/2022', 5,   0.0448266874224477, NULL)
, ('7/1/2022', 6,   0.0637221774467554, NULL)
, ('7/1/2022', 7,   0.0953341922962425, NULL)
, ('7/1/2022', 8,   0.117940928214655,  NULL)
, ('7/1/2022', 9,   0.0955895317176205, NULL)
, ('6/1/2022',  0,  0.0027347877960046, 0.00439631056653702)
, ('6/1/2022',  1,  0.0332610867687839, 0.00752724387918406)
, ('6/1/2022',  2,  0.0541567096339919, NULL)
, ('6/1/2022',  3,  0.0534245249728661, NULL)
, ('6/1/2022',  4,  0.0497604938051764, NULL)
, ('6/1/2022',  5,  0.0448266874224477, NULL)
, ('6/1/2022',  6,  0.0637221774467554, NULL)
, ('6/1/2022',  7,  0.0953341922962425, NULL)
, ('6/1/2022',  8,  0.117940928214655,  NULL)
, ('6/1/2022',  9,  0.0955895317176205, NULL)

我需要结果=以前的结果+增量

Date    Month   Increment   Results
6/1/2022    0   0.002734788 0.004396311
6/1/2022    1   0.033261087 0.007527244
6/1/2022    2   0.05415671  0.061683954
6/1/2022    3   0.053424525 0.115108478
6/1/2022    4   0.049760494 0.164868972
6/1/2022    5   0.044826687 0.20969566
6/1/2022    6   0.063722177 0.273417837
6/1/2022    7   0.095334192 0.368752029
6/1/2022    8   0.117940928 0.486692958
6/1/2022    9   0.095589532 0.582282489
7/1/2022    0   0.002734788 0.004396311
7/1/2022    1   0.033261087 0.037657397
7/1/2022    2   0.05415671  0.091814107
7/1/2022    3   0.053424525 0.145238632
7/1/2022    4   0.049760494 0.194999126
7/1/2022    5   0.044826687 0.239825813
7/1/2022    6   0.063722177 0.303547991
7/1/2022    7   0.095334192 0.398882183
7/1/2022    8   0.117940928 0.516823111
7/1/2022    9   0.095589532 0.612412643

对此最好的方法是什么？

Answer 1

似乎可以在这里使用累积SUM。但是，由于需要使用Month 0中的Results，并且忽略Month 0的Increment的值，因此还需要使用条件聚合：

SELECT *,
       SUM(CASE MONTH WHEN 0 THEN Results END) OVER () +
       SUM(CASE WHEN Month > 0 THEN Increment END) OVER (ORDER BY Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Results2
FROM #tmp;

db<gt；小提琴

Answer 2

我使用@larnu的rowbetween方法找到了一个解决方案。需要将数据拆分到两个表中并将它们合并在一起。表1包含实际结果列，表2包含增量计算的结果。

DROP TABLE IF EXISTS #tmp;
CREATE TABLE #tmp (
    Date DATE
    , Month INT
    , Increment FLOAT
    , Results FLOAT
)
;
INSERT INTO #tmp(Date, Month, Increment, Results) 
VALUES
('7/1/2022', 0, 0.0027347877960046, 0.00439631056653702)
, ('7/1/2022', 1,   0.0332610867687839, NULL)
, ('7/1/2022', 2,   0.0541567096339919, NULL)
, ('7/1/2022', 3,   0.0534245249728661, NULL)
, ('7/1/2022', 4,   0.0497604938051764, NULL)
, ('7/1/2022', 5,   0.0448266874224477, NULL)
, ('7/1/2022', 6,   0.0637221774467554, NULL)
, ('7/1/2022', 7,   0.0953341922962425, NULL)
, ('7/1/2022', 8,   0.117940928214655,  NULL)
, ('7/1/2022', 9,   0.0955895317176205, NULL)
, ('6/1/2022',  0,  0.0027347877960046, 0.00439631056653702)
, ('6/1/2022',  1,  0.0332610867687839, 0.00752724387918406)
, ('6/1/2022',  2,  0.0541567096339919, NULL)
, ('6/1/2022',  3,  0.0534245249728661, NULL)
, ('6/1/2022',  4,  0.0497604938051764, NULL)
, ('6/1/2022',  5,  0.0448266874224477, NULL)
, ('6/1/2022',  6,  0.0637221774467554, NULL)
, ('6/1/2022',  7,  0.0953341922962425, NULL)
, ('6/1/2022',  8,  0.117940928214655,  NULL)
, ('6/1/2022',  9,  0.0955895317176205, NULL)
;
WITH mt AS (
    SELECT
        Date
        , MAX(Month) AS Month
    FROM #tmp
    WHERE Results IS NOT NULL
    GROUP BY Date
),
results AS (
    SELECT 
        t.*
        , SUM(CASE WHEN t.Results IS NOT NULL THEN t.Results END) OVER(PARTITION BY t.Date) +
         SUM(CASE WHEN t.Results IS NULL THEN t.Increment END) OVER (PARTITION BY t.Date ORDER BY t.Date, t.Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Results2
    FROM #tmp t
    INNER JOIN mt mt ON t.Date = mt.Date AND t.Month >= mt.Month
)
SELECT
    t.Date 
    , t.Month 
    , t.Increment 
    , t.Results
FROM #tmp t
LEFT JOIN mt mt ON t.Date = mt.Date 
WHERE t.Month <= mt.Month
UNION ALL
SELECT
    r.Date 
    , r.Month 
    , r.Increment
    , r.Results2 AS Results
FROM results r
WHERE r.results2 IS NOT NULL
ORDER BY Date, Month