6

Consider the following table:

ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL 
---+----------+-----------+-----------+-----
1  | 1        | 1         | (null)    | 3   
2  | 1        | 2         | (null)    | 2   
3  | 1        | 3         | (null)    | 1   
4  | 1        | 4         | 4         | 2   
5  | 1        | 5         | (null)    | 1   
6  | 2        | 1         | (null)    | 4   
7  | 2        | 2         | 2         | 3   
8  | 2        | 3         | (null)    | 4   
9  | 2        | 4         | (null)    | 2   
10 | 2        | 5         | (null)    | 2   
11 | 2        | 6         | (null)    | 4   
12 | 2        | 7         | 14        | 2   
13 | 2        | 8         | (null)    | 2   

For each row, I need to compute the cumulative sum of VAL for all previous rows (ordered by ORDER_VAL and grouped by GROUP_ID), but each time a non-NULL RESET_VAL is encountered, I need to use that value for the sum. The rows that follow also need to build on top of the RESET_VAL instead of using the actual sum. Note that each group can have multiple reset values.

This is the result I expect for the above table:

ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL | CUMSUM
---+----------+-----------+-----------+-----+-------
1  | 1        | 1         | (null)    | 3   | 0
2  | 1        | 2         | (null)    | 2   | 3
3  | 1        | 3         | (null)    | 1   | 5
4  | 1        | 4         | 4         | 2   | 4
5  | 1        | 5         | (null)    | 1   | 6
6  | 2        | 1         | (null)    | 4   | 0
7  | 2        | 2         | 2         | 3   | 2
8  | 2        | 3         | (null)    | 4   | 5
9  | 2        | 4         | (null)    | 2   | 9
10 | 2        | 5         | (null)    | 2   | 11
11 | 2        | 6         | (null)    | 4   | 13
12 | 2        | 7         | 14        | 2   | 14
13 | 2        | 8         | (null)    | 2   | 16

If not for the reset values, I could use a window query:

SELECT temp.*,
       COALESCE(SUM(val) OVER (PARTITION BY group_id ORDER BY order_val ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
                0) AS cumsum
FROM temp;

SQLFiddle of the above

I was originally mistakenly thinking that I could just put RESET_VAL at the beginning of the COALESCE, but this won't work since it won't reset the value for following rows.

I also tried this solution, but it only resets back to zero, not to a value in the column. Adapting it to do so is proving non-trivial because that value must propagate to all following rows.

A recursive query seems like a natural fit, but I haven't been able to figure out how to do that as of yet.

I should probably mention that the table I actually have to deal with is much larger (hundreds of thousands to a couple million rows) than the example above, so please mention if there are any performance pitfalls with any answer.

2
  • @ypercube Because I make lots of silly mistakes every day, especially when doing things manually. =) Thanks. Fixed.
    – jpmc26
    Jun 19, 2015 at 18:15
  • @Colin'tHart Sorry, I'm confused by your question. When a non-NULL reset_val is encountered, it should completely replace the cumulative sum. It can be any value, anywhere in the group.
    – jpmc26
    Jun 19, 2015 at 19:32

2 Answers 2

4

The following works but there's probably some more clever version. Explanation of the query's logic:

We first find how many "resets" have been done up to and including the current row by counting the not nulls of the reset_val column, so we can separate the rows into subgroups.

We also use another window function LAST_VALUE() with IGNORE NULLS, so we can find the last reset_value.

Note that both these window functions COUNT() and LAST_VALUE() have an ORDER BY, therefore the default window ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Omitted in the query, to make the code more clear.

Assuming that the val is not nullable, the other window function could also be shortened, from:

       COALESCE(SUM(val) OVER 
         (PARTITION BY group_id, reset_count 
          ORDER BY order_val 
          ROWS BETWEEN UNBOUNDED PRECEDING 
                   AND 1 PRECEDING), 0)   

(avoiding the COALESCE() as well) to:

       SUM(val) OVER 
         (PARTITION BY group_id, reset_count 
          ORDER BY order_val)
       - val

Finally, in the second cte, we use the above found subgroups (using PARTITION BY group_id, reset_count) to find the cumulative sums.

WITH x AS
  ( SELECT temp.*, 
           COUNT(reset_val) OVER 
               (PARTITION BY group_id 
                ORDER BY order_val)
             AS reset_count,
           COALESCE(LAST_VALUE(reset_val IGNORE NULLS) OVER 
               (PARTITION BY group_id 
                ORDER BY order_val), 0)
             AS reset_value
    FROM temp
  ) ,
y AS 
  ( SELECT x.*,
           COALESCE(SUM(val) OVER 
             (PARTITION BY group_id, reset_count 
              ORDER BY order_val 
              ROWS BETWEEN UNBOUNDED PRECEDING 
                       AND 1 PRECEDING), 0)            
           + reset_value AS cumsum      
    FROM x
  )
SELECT *
FROM y ;

Test at SQLfiddle.


Another variation, based on @Chris' recursive answer. (slightly improved, works with non-consecutive order_val, avoids the final GROUP BY).
Also works in case the first row of a group has a reset_val:

WITH row_nums AS
  ( SELECT id, group_id, order_val, reset_val, val, 
           ROW_NUMBER() OVER (PARTITION BY group_id
                              ORDER BY order_val)
             AS rn
    FROM temp
  ) ,
updated_temp (id, group_id, order_val, reset_val, val, rn, cumsum) AS
  ( SELECT id, group_id, order_val, reset_val, val, rn, 
           COALESCE(reset_val, 0)
    FROM row_nums
    WHERE rn = 1
  UNION ALL
    SELECT curr.id, curr.group_id, curr.order_val, curr.reset_val, curr.val, curr.rn, 
           COALESCE(curr.reset_val, prev.val + prev.cumsum) 
    FROM row_nums  curr 
      JOIN updated_temp  prev 
        ON  curr.rn-1 = prev.rn 
        AND curr.group_id = prev.group_id
  )
SELECT id, group_id, order_val, reset_val, val, cumsum
FROM updated_temp
ORDER BY group_id, order_val ;

Test at SQLfiddle-2.


One more variation, using the older (proprietary) CONNECT BY syntax for recursive queries. More compact but I find it more difficult to write and read than the CTE version:

WITH row_nums AS
  ( SELECT id, group_id, order_val, reset_val, val, 
           ROW_NUMBER() OVER (PARTITION BY group_id
                              ORDER BY order_val)
             AS rn,
           COALESCE(reset_val, 0) AS cumsum
    FROM temp
  ) 
SELECT id, group_id, order_val, reset_val, val, rn,  
       COALESCE(reset_val, PRIOR val + PRIOR cumsum, 0) AS cumsum
FROM row_nums
START WITH rn = 1 OR reset_val IS NOT NULL
CONNECT BY  rn-1 = PRIOR rn 
        AND group_id = PRIOR group_id
        AND reset_val IS NULL 
ORDER BY group_id, order_val ; 

Tested at SQLfiddle-3.

6
  • Firstly, thank you! Secondly, I must apologize. I neglected a detail. Each group can have multiple resets. I'll revise my question shortly.
    – jpmc26
    Jun 19, 2015 at 19:18
  • Did you try it? Query works with multiple resets, as it is: sqlfiddle.com/#!4/fe988/1 Oops, no it doesn't... Jun 19, 2015 at 19:23
  • I did: sqlfiddle.com/#!4/3558b9/1/0. I've double checked, but I can't see anything silly I've done to the data. All I did was add 3 rows to the end of group 2, with correct sequence numbers.
    – jpmc26
    Jun 19, 2015 at 19:25
  • Corrected. Please check as I might have other errors now ;) Jun 19, 2015 at 19:43
  • Excellent! Here is a SQLFiddle your query with the updated data: sqlfiddle.com/#!4/3558b9/4/0. It had just hit me that determining the last reset value was the way to go, but I had never seen the IGNORE NULLS syntax. Thank you!
    – jpmc26
    Jun 19, 2015 at 19:51
4

As an academic exercise, I've implemented a solution in Postgres. Now, I know this was a question about Oracle, but it was also framed as a SQL question! :) If someone is more familiar with Oracle's recursive query syntax, maybe they can find the necessary changes to make this run in Oracle.

Recursive Querying (in Postgres)

In this solution, I make use of recursive querying, applying the Postgres syntax WITH RECURSIVE in my CTE. The recursive query is as follows:

WITH RECURSIVE updated_temp AS (
  SELECT temp.*, 0::numeric AS CUMSUM FROM temp
  UNION
  SELECT curr.id, curr.group_id, curr.order_val, curr.reset_val, curr.val, 
  COALESCE(curr.RESET_VAL,prev.VAL + prev.CUMSUM) AS CUMSUM
  FROM temp AS curr JOIN updated_temp AS prev 
  ON curr.ORDER_VAL-1 = prev.ORDER_VAL AND curr.group_id = prev.group_id)
SELECT id, group_id, order_val, reset_val, val, max(cumsum) FROM updated_temp 
GROUP BY id, group_id, order_val, reset_val, val ORDER BY id ASC;

Here is the corresponding SQL Fiddle.

Of course, the key to this query's proper behavior is first using the recursive portion of the query to build your cumulative sum, while making sure to use COALESCE to substitute in any RESET_VAL which dictates the CUMSUM for the row. The last SELECT simply has you filtering for the maximum value of the CUMSUM because if the query was built right, the max is identical to the cumulative sum.

Edit: Oracle version of recursive query

This translation to Oracle was provided by ypercube:

WITH updated_temp (id, group_id, order_val, reset_val, val, cumsum) 
AS (
  SELECT id, group_id, order_val, reset_val, val, 0 
  FROM temp
  UNION ALL
  SELECT curr.id, curr.group_id, curr.order_val, curr.reset_val, curr.val, 
     COALESCE(curr.reset_val, prev.val + prev.cumsum) 
  FROM temp  curr 
    JOIN updated_temp  prev 
      ON  curr.order_val-1 = prev.order_val 
      AND curr.group_id = prev.group_id
)
  SELECT id, group_id, order_val, reset_val, val, max(cumsum) 
  FROM updated_temp 
  GROUP BY id, group_id, order_val, reset_val, val 
  ORDER BY id ASC;

Oracle SQL Fiddle

4
  • Your query, translated into "oracle": sqlfiddle.com/#!4/3558b9/15 Jun 19, 2015 at 20:41
  • Ha, OK, if I had known those were the only syntax differences, I would've put in the effort to do it in Oracle. Thanks for the "translation" ypercube!
    – Chris
    Jun 19, 2015 at 20:45
  • Yeah, only minor quirks (no RECURSIVE, no AS for table aliases, etc.) Jun 19, 2015 at 20:47
  • Well, and you don't even need to use AS for table aliases, I just do it by compulsion because I like placing a big "AS" flag so that when I read my code later I can more quickly parse it.
    – Chris
    Jun 19, 2015 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.