Sum Non Null Values Block in SQL-CodePudding

How to add Non Null Values block by block without any grouping criteria :

Example input :

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

Example output :

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

I don't have any other grouping criteria other than device column , but I want sum block wise

CodePudding user response：

You need to define the groups and use windowed SUM():

Table:

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)

Statement:

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

Result:

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