Sum a record with its previous not-null ordered by timestamp

Question

I have a table like this one below:

+----+----+-----+--------------+---------+
| C1 | C2 | C3  |   TIMESTMP   | MEASURE |
+----+----+-----+--------------+---------+
| A  | AA | AAA | 201804200000 | 20      |
| A  | AA | AAA | 201804200015 | 2       |
| A  | AA | AAA | 201804200030 | 5       |
| A  | AA | AAA | 201804200045 | null    |
| A  | AA | AAA | 201804200100 | null    |
| A  | AA | AAA | 201804200115 | 12      |
| …  | …  | …   | …            | …       |
| A  | AA | AAA | 201804202345 | 20      |
| B  | BB | BBB | 201804200000 | 8       |
| B  | BB | BBB | 201804200015 | null    |
| B  | BB | BBB | 201804200030 | 1       |
| …  | …  | …   | …            | …       |
+----+----+-----+--------------+---------+

I have to sum each value in field MEASURE with its previous value (obtained by taking the previous quarter of an hour in field TIMESTMP) according to these 3 cases:

1. If current value in MEASURE is null, then RESULT is null
2. If current value in MEASURE is not-null, and its previous value in MEASURE is not-null, then sum the two values
3. If current value in MEASURE is not-null, and its previous value in MEASURE is null, then sum the current value of MEASURE with its previous not-null value.

The fields C1, C2, C3 and TIMESTMP are the key, and the sum must be between records having the same values ​​in the fields c1, c2, c3.

The result should be a table like this one below:

+----+----+-----+--------------+---------+--------------------------------+
| C1 | C2 | C3  |   TIMESTMP   | MEASURE |             RESULT             |
+----+----+-----+--------------+---------+--------------------------------+
| A  | AA | AAA | 201804200000 | 20      | 20                             |
| A  | AA | AAA | 201804200015 | 2       | 22 (2+20)                      |
| A  | AA | AAA | 201804200030 | 5       | 7 (5+2)                        |
| A  | AA | AAA | 201804200045 | null    | null                           |
| A  | AA | AAA | 201804200100 | null    | null                           |
| A  | AA | AAA | 201804200115 | 12      | 17 (12+5)                      |
| …  | …  | …   | …            | …       | …                              |
| A  | AA | AAA | 201804202345 | 20      | X (20+previous value not null) |
| B  | BB | BBB | 201804200000 | 8       | 8                              |
| B  | BB | BBB | 201804200015 | null    | null                           |
| B  | BB | BBB | 201804200030 | 1       | 9 (1+8)                        |
| …  | …  | …   | …            | …       | …                              |
+----+----+-----+--------------+---------+--------------------------------+

Answer 1

I think the best option would be to create a CTE and then join that to your initial table:

SQL DEMO

WITH res (c1, c2, c3, timestamp, measure, result) AS (
    SELECT c1, c2, c3, timestamp, measure
         , measure + COALESCE(LAG(measure) OVER ( PARTITION BY c1, c2, c3 ORDER BY timestamp ), 0)
      FROM mytable
     WHERE measure IS NOT NULL
)
SELECT m.c1, m.c2, m.c3, m.timestamp, m.measure, res.result
  FROM mytable m LEFT OUTER JOIN res
    ON m.c1 = res.c1
   AND m.c2 = res.c2
   AND m.c3 = res.c3
   AND m.timestamp = res.timestamp;

Output

| c1 | c2 |  c3 |     timestmp | measure | result |
|----|----|-----|--------------|---------|--------|
|  A | AA | AAA | 201804200000 |      20 |     20 |
|  A | AA | AAA | 201804200015 |       2 |     22 |
|  A | AA | AAA | 201804200030 |       5 |      7 |
|  A | AA | AAA | 201804200045 |  (null) | (null) |
|  A | AA | AAA | 201804200100 |  (null) | (null) |
|  A | AA | AAA | 201804200115 |      12 |     17 |
|  A | AA | AAA | 201804202345 |      20 |     32 |
|  B | BB | BBB | 201804200000 |       8 |      8 |
|  B | BB | BBB | 201804200015 |  (null) | (null) |
|  B | BB | BBB | 201804200030 |       1 |      9 |