How To Query Sum Previous Row Of The Same Column With With Pgsql

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 win

Solution 1:

Use user-defined aggregate

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

DDL

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

INSERTINTO 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

createor replace function negative_accum(_accumulated_b numeric, _current_b numeric)
returnsnumericas
$$
    selectcasewhen _accumulated_b <0then
        _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 (orderby grop, month_year) asresultfrom 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:

selectcasewhen _accumulated_b <0then
    _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

createor replace function negative_accum(_accumulated_b numeric, _current_b numeric)
returnsnumericas
$$
    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:

createor replace function negative_accum(_accumulated_b numeric, _current_b numeric)
returnsnumericas
$$beginreturn _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

INSERTINTO 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 |

Solution 2:

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

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

INSERTINTO 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 (partitionby grop orderby 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.