How to query sum previous row of the same column with with pgSql

Question

I need to put in column RESULT the sum of same row's column SOMETHING and previous row column SOMETHING if it is a negative number, e.g. B3 = A3 + MIN(0, B2).

I've tried using window function but got nowhere, It just show a result that it's not the expected. (month_year is DATE field)

group   month_year  something   result
a         jan/19        -2      -2
a         fev/19        -4      -6
a         mar/19        -6      -12
a         abr/19        60      48
a         mai/19        -2      46
a         jun/19        9       55
a         jul/19        11      66
b         jan/19        100     100
b         fev/19        -200    -100
b         mar/19        300     200
b         abr/19        -50     150
b         mai/19        30      180
b         jun/19        -88     92
b         jul/19        -86     6

Expected Result:

That's the result I'm looking for, if there's another way to achive that I'm all for it. Can you help?

Michael Buen · Accepted Answer · 2019-04-09T01:20:27.200

Use user-defined aggregate

Live test: http://sqlfiddle.com/#!17/03ee7/1

DDL

CREATE TABLE t
    (grop varchar(1), month_year text, something int)
;

INSERT INTO t
    (grop, month_year, something)
VALUES
    ('a', '201901', -2),
    ('a', '201902', -4),
    ('a', '201903', -6),
    ('a', '201904', 60),
    ('a', '201905', -2),
    ('a', '201906', 9),
    ('a', '201907', 11),
    ('b', '201901', 100),
    ('b', '201902', -200),
    ('b', '201903', 300),
    ('b', '201904', -50),
    ('b', '201905', 30),
    ('b', '201906', -88),
    ('b', '201907', -86)
;

User-defined aggregate

create or replace function negative_accum(_accumulated_b numeric, _current_b numeric)
returns numeric as
$$
    select case when _accumulated_b < 0 then
        _accumulated_b + _current_b
    else
        _current_b
    end
$$ language 'sql';

create aggregate negative_summer(numeric)
(
    sfunc = negative_accum,
    stype = numeric,
    initcond = 0
);

select  
    *, 
  negative_summer(something) over (order by grop, month_year) as result
from t

The first parameter (_accumulated_b) holds the accumulated value of the column. The second parameter (_current_b) holds the value of the current row's column.

Output:

As for your pseudo-code B3 = A3 + MIN(0, B2)

I used this typical code:

select case when _accumulated_b < 0 then
    _accumulated_b + _current_b
else
    _current_b
end

That can be written idiomatically in Postgres as:

select _current_b + least(_accumulated_b, 0)

Live test: http://sqlfiddle.com/#!17/70fa8/1

create or replace function negative_accum(_accumulated_b numeric, _current_b numeric)
returns numeric as
$$
    select _current_b + least(_accumulated_b, 0) 
$$ language 'sql';

You can also use other language with accumulator function, e.g., plpgsql. Note that plpgsql (or perhaps the $$ quote) is not supported in http://sqlfiddle.com. So no live test link, this would work on your machine though:

create or replace function negative_accum(_accumulated_b numeric, _current_b numeric)
returns numeric as
$$begin
    return _current_b + least(_accumulated_b, 0);
end$$ language 'plpgsql';

UPDATE

I missed the partition by, here's an example data (changed 11 to -11) where without partition by and with partition by would yield different results:

Live test: http://sqlfiddle.com/#!17/87795/4

INSERT INTO t
    (grop, month_year, something)
VALUES
    ('a', '201901', -2),
    ('a', '201902', -4),
    ('a', '201903', -6),
    ('a', '201904', 60),
    ('a', '201905', -2),
    ('a', '201906', 9),
    ('a', '201907', -11), -- changed this from 11 to -11
    ('b', '201901', 100),
    ('b', '201902', -200),
    ('b', '201903', 300),
    ('b', '201904', -50),
    ('b', '201905', 30),
    ('b', '201906', -88),
    ('b', '201907', -86)
;

Output:

| grop | month_year | something | result_wrong | result |
|------|------------|-----------|--------------|--------|
|    a |     201901 |        -2 |           -2 |     -2 |
|    a |     201902 |        -4 |           -6 |     -6 |
|    a |     201903 |        -6 |          -12 |    -12 |
|    a |     201904 |        60 |           48 |     48 |
|    a |     201905 |        -2 |           -2 |     -2 |
|    a |     201906 |         9 |            7 |      7 |
|    a |     201907 |       -11 |          -11 |    -11 |
|    b |     201901 |       100 |           89 |    100 |
|    b |     201902 |      -200 |         -200 |   -200 |
|    b |     201903 |       300 |          100 |    100 |
|    b |     201904 |       -50 |          -50 |    -50 |
|    b |     201905 |        30 |          -20 |    -20 |
|    b |     201906 |       -88 |         -108 |   -108 |
|    b |     201907 |       -86 |         -194 |   -194 |

Man that looks awesome. How does it work? How is negative_summer calling negative_accum without parameters? How is this called so I can look it up and learn more about it. Thank you very much — APJ, Apr 07 '19 at 14:10
It's called user-defined aggregate. negative_summer is calling negative_accum with two parameters. the first parameter (_accumulated_b) is the accumulated value, the second parameter (_current_b) is the value of the current row's column. so on first row, _accumulated_b receives initial value from initcond's value (0), _current_b receives the value from current row's column, -2. so on first row, negative_summer is calling `negative_accum(0, -2)`. then on second row, negative_summer will call `negative_accum(-2, -4)`, then on third row, `negative_accum(-6, -6)`, and so on — Michael Buen, Apr 08 '19 at 00:42
here's another example: https://www.cybertec-postgresql.com/en/writing-your-own-aggregation-functions/ — Michael Buen, Apr 08 '19 at 00:43
Thank you very much. Just so the answer is correct, you missed the partition by group. — APJ, Apr 09 '19 at 00:20

score 0 · Answer 2 · answered Apr 06 '19 at 03:20

You probably had problems with the window function because you need to have something to order by, and your month_year column isn't going to naturally sort. See this SQL fiddle where the column is replaced by something more like a date that will order correctly.

http://sqlfiddle.com/#!18/7a304/1/0

CREATE TABLE t
    ([grop] varchar(1), [month_year] varchar(6), [something] int, [result] int)

INSERT INTO t
    ([grop], [month_year], [something], [result])
VALUES
    ('a', '201901', -2, -2),
    ('a', '201902', -4, -6),
    ('a', '201903', -6, -12),
    ('a', '201904', 60, 48),
    ('a', '201905', -2, -2),
    ('a', '201906', 9, 7),
    ('a', '201907', 11, 11),
    ('b', '201901', 100, 100),
    ('b', '201902', -200, -200),
    ('b', '201903', 300, 100),
    ('b', '201904', -50, -50),
    ('b', '201905', 30, -20),
    ('b', '201906', -88, -108),
    ('b', '201907', -86, -194)

select
  grop, month_year, something, result,
  sum(something) over (partition by grop order by grop, month_year) as rtot
from
  t

| grop | month_year | something | result | rtot |
|------|------------|-----------|--------|------|
|    a |     201901 |        -2 |     -2 |   -2 |
|    a |     201902 |        -4 |     -6 |   -6 |
|    a |     201903 |        -6 |    -12 |  -12 |
|    a |     201904 |        60 |     48 |   48 |
|    a |     201905 |        -2 |     -2 |   46 |
|    a |     201906 |         9 |      7 |   55 |
|    a |     201907 |        11 |     11 |   66 |
|    b |     201901 |       100 |    100 |  100 |
|    b |     201902 |      -200 |   -200 | -100 |
|    b |     201903 |       300 |    100 |  200 |
|    b |     201904 |       -50 |    -50 |  150 |
|    b |     201905 |        30 |    -20 |  180 |
|    b |     201906 |       -88 |   -108 |   92 |
|    b |     201907 |       -86 |   -194 |    6 |

The other part of the problem is resetting the running total when you get to a positive number. I'm not sure if you can do that in SQL, without dropping into a stored proc, but perhaps with a clearer example someone knowledgeable will chime in.

Well actually that sum with window function I'd gotten, but as you said it needs do be resetted when it gets to zero. How would I do that using a stored proc? If it works then it's fine by me ! — APJ, Apr 07 '19 at 01:08

How to query sum previous row of the same column with with pgSql

2 Answers2

DDL

User-defined aggregate

Linked