Database only Running total with with conditions

Question

There are a ton of questions about calculating running totals with Postgres but I am struggling to do something slightly different.

I have a table that looks like this

txn_id	amount
String	Integer

amounts can either be positive or negative.

I am trying to return a table that looks like this

txn_id	amount	running_total	overage_total
String	Integer	Integer	Integer

Where running total is running sum of the amount column as long as the amount is greater than zero and overage_total is the running sum of amounts that were lower than zero.

An example of would be

txn_id	amount
a	1
b	2
c	-4
d	2
e	-1

I have been using a window function for the running sum but it's not quite what we need.

The correct table would return

txn_id	amount	running_total	overage_total
a	1	1	0
b	2	3	0
c	-4	0	1
d	2	2	1
e	-1	1	1

Currently I have am doing this in code but it would be really incredible to do it in the database if it's possible.

nachospiu · Answer 1 · 2021-11-09T11:06:23.337

2

An option is to use a function to step through the rows and do calculations:

CREATE FUNCTION runningTotalWithCondition() RETURNS TABLE(txn_id char(1), amount int, running_total integer, overage_total integer) AS
$$

DECLARE 

running_total integer := 0;
overage_total integer := 0;
c CURSOR FOR SELECT * FROM t ORDER BY txn_id ASC;

BEGIN
    
    FOR recordvar IN c LOOP
      IF (running_total + recordvar.amount) > 0 THEN
        running_total = running_total + recordvar.amount;
        overage_total = overage_total;
      ELSE 
        overage_total = overage_total + abs(running_total + recordvar.amount);
        running_total = 0;
      END IF;
    
      RETURN QUERY SELECT recordvar.txn_id, recordvar.amount, running_total, overage_total;
    END LOOP;

END;

$$ LANGUAGE plpgsql;

Calling the function:

SELECT * FROM runningTotalWithCondition();

edited Nov 09 '21 at 11:06

answered Oct 05 '21 at 12:27

nachospiu

2,009
2
8
12

Look up window functions. They exist to solve cumulative sum problems and similar running total calculations - I give this as a SQL interview question. – John Zabroski Oct 05 '21 at 12:49
Yes I know, but this is a special running total calculation (with a condition), I didn't find a better way to solve it. – nachospiu Oct 05 '21 at 13:07
https://www.postgresql.org/docs/9.2/functions-conditional.html and window functions https://www.postgresql.org/docs/9.1/tutorial-window.html - update your answer and I will give it an upvote – John Zabroski Oct 05 '21 at 14:37
The issue with window functions is they do not group. I have been using a window function but it's not quite right – austinbv Oct 06 '21 at 13:13
@austinbv I don't understand what you mean when you say, "window functions [...] do not group." Window functions are literally aggregate functions. They operate on groups by their very definition. The PARTITION BY clause dynamically creates a streaming group. This is a direct result of the fact grouping == synchronization and synchronization == counting in set theory/logic programming languages. – John Zabroski Oct 06 '21 at 19:08

score 2 · Answer 2 · answered Oct 06 '21 at 19:21

The pattern here is running total with a cap. It could be achieved with recursive cte:

WITH RECURSIVE cte_r AS (
  SELECT t.*, ROW_NUMBER() OVER(ORDER BY t.txn_id) AS rn FROM tab t
), cte AS (
  SELECT rn,
         txn_id,
         amount, 
         CASE WHEN amount <= 0 THEN 0 ELSE amount END AS total,
         CASE WHEN amount <= 0 THEN 1 ELSE 0 END AS overage_total
  FROM cte_r
  WHERE rn = 1
  UNION ALL
  SELECT cte_r.rn,
         cte_r.txn_id,
         cte_r.amount,
         CASE WHEN cte.total + cte_r.amount <= 0 THEN 0 
                        ELSE cte.total + cte_r.amount 
                   END AS total,
         cte.overage_total + CASE WHEN cte.total + cte_r.amount <= 0 
                                  THEN 1 ELSE 0 END AS overage_total
  FROM cte
  JOIN cte_r
    ON cte.rn = cte_r.rn-1
)
SELECT txn_id, amount, total,overage_total
FROM cte
ORDER BY rn;

Output:

+---------+---------+--------+---------------+
| txn_id  | amount  | total  | overage_total |
+---------+---------+--------+---------------+
| a       |      1  |     1  |             0 |
| b       |      2  |     3  |             0 |
| c       |     -4  |     0  |             1 |
| d       |      2  |     2  |             1 |
| e       |     -1  |     1  |             1 |
| f       |      2  |     3  |             1 |
| h       |     -4  |     0  |             2 |
+---------+---------+--------+---------------+

db<>fiddle demo

Related: Conditional SUM on Oracle and 7. Capping a running total

Database only Running total with with conditions

2 Answers2