How to sum a record with its previous not-null ordered by timestamp using ORACLE?

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:

If current value in MEASURE is null, then RESULT is null
If current value in MEASURE is not-null, and its previous value in MEASURE is not-null, then sum the two values
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)                        |
| …  | …  | …   | …            | …       | …                              |
+----+----+-----+--------------+---------+--------------------------------+

David Faber · Accepted Answer · 2018-04-20T14:14:57.280

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 |

Hope this helps.

edited Apr 20 '18 at 14:14

answered Apr 20 '18 at 14:06

David Faber

12,277
2
29
40

Lag only bring the previous one. OP want the previous not null value. check row `17 (12+5)` – Juan Carlos Oropeza Apr 20 '18 at 14:07
I'm excluding `NULL` values in the CTE – David Faber Apr 20 '18 at 14:08
There is a mistake, the first row should be 20, not null? – Juan Carlos Oropeza Apr 20 '18 at 14:14
@JuanCarlosOropeza, maybe you missed my edit to add `COALESCE()`? – David Faber Apr 20 '18 at 14:15
Thanks for putting the demo together by the way – David Faber Apr 20 '18 at 14:17
No problem, I was going in a different direction when saw you already solve it – Juan Carlos Oropeza Apr 20 '18 at 14:19

How to sum a record with its previous not-null ordered by timestamp using ORACLE?

1 Answers1