如何在Null沒有任何分組條件的情況下逐塊添加非值：
示例輸入：
Machine Value DateTime
a null 1 Dec 2021 8:34AM
a 2 1 Dec 2021 8:35AM
a 1 1 Dec 2021 9:34AM
a 3 1 Dec 2021 10:11AM
a null 1 Dec 2021 11:14AM
a null 1 Dec 2021 11:16AM
a 5 1 Dec 2021 11:58AM
a 6 1 Dec 2021 11:59AM
示例輸出：
Machine Value DateTime SumValue
a null 1 Dec 2021 8:34AM
a 2 1 Dec 2021 8:35AM
a 1 1 Dec 2021 9:34AM
a 3 1 Dec 2021 10:11AM 6
a null 1 Dec 2021 11:14AM
a null 1 Dec 2021 11:16AM
a 5 1 Dec 2021 11:58AM
a 6 1 Dec 2021 11:59AM 11
除了設備列之外，我沒有任何其他分組標準，但我想要總和塊明智
uj5u.com熱心網友回復：
您需要定義組并使用 windowed SUM()：
桌子：
SELECT *
INTO Data
FROM (VALUES
('2021-12-12T09:00:01', 'a', null),
('2021-12-12T09:00:02', 'a', 2),
('2021-12-12T09:00:03', 'a', 1),
('2021-12-12T09:00:04', 'a', 3),
('2021-12-12T09:00:05', 'a', null),
('2021-12-12T09:00:06', 'a', null),
('2021-12-12T09:00:07', 'a', 5),
('2021-12-12T09:00:08', 'a', 6)
) v (Date, Machine, Value)
陳述：
SELECT
Date, Machine, Value,
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY Machine, GroupNumber ORDER BY Date DESC) = 1
THEN SUM(Value) OVER (PARTITION BY Machine, GroupNumber ORDER BY (SELECT NULL))
END AS SumValue
FROM (
SELECT
*,
SUM(CASE WHEN Value IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY Machine ORDER BY Date) AS GroupNumber
FROM Data
) t
ORDER BY Machine, Date
結果：
Date Machine Value SumValue
2021-12-12T09:00:01 a
2021-12-12T09:00:02 a 2
2021-12-12T09:00:03 a 1
2021-12-12T09:00:04 a 3 6
2021-12-12T09:00:05 a
2021-12-12T09:00:06 a
2021-12-12T09:00:07 a 5
2021-12-12T09:00:08 a 6 11
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/385366.html
標籤：sql sql-server
上一篇：如何計算每個月的收入？
下一篇：SQL-選擇具有最新日期的值