Sqlite: Accumulator (Sum) Column in a Select Statement

sqlite filtering by sum

Supposing a table created thus:

CREATE TABLE Files (Id INTEGER PRIMARY KEY, FileName TEXT, CreationDate DATE, Size INTEGER);

To get the running sum, use the following query:

SELECT f1.id AS FileId, sum(f2.size) AS RunningSumSize
FROM file f1 INNER JOIN file f2
ON f1.createdDate<=f2.createdDate
GROUP BY FileId
ORDER BY RunningSumSize DESC;

To delete the file ID's above the threshold:

DELETE FROM File WHERE Id IN
 (SELECT FileId FROM 
  (SELECT f1.id AS FileId, sum(f2.size) AS RunningSumSize
  FROM file f1 INNER JOIN file f2
  ON f1.createdDate<=f2.createdDate
  GROUP by FileId
  ORDER by RunningSumSize DESC)
  WHERE RunningSumSize > :ThresholdSize:);

Note: The order by is optional.

Performing a prefix computation using SQL without defined procedures

try something like this:

select value,
(select sum(t2.value) from table t2 where t2.id <= t1.id ) as accumulated
from table t1

from: SQLite: accumulator (sum) column in a SELECT statement

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

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:

Sample Image

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)
;