Aggregate values by interval range given by parameter

Question

I have a table containing an amount column and an index column. I wish to build a single query, which sums up the amount for the given interval ranges for every resulting interval from given start index to end index - i.e. the 'interval range' slices the data according index column. For these groups, sum over amount is built.
Is this possible and how?

SELECT sum(amount) from mytable WHERE index between <startindex> and <endindex> GROUP BY ...

Example:

amount | index 55 1 88 5 45 6 86 7 87 10 88 11 57 16 58 17 59 20 60 21

A query with interval range 5, start index 5 and endindex 14 should result in two return values:

 88+45+86 => 219 87+88 => 175

A query with interval range 4, start index 0 and endindex 15 should result in four return values:

 55 => 55 88+45+86 => 219 87+88 => 175 -- => 0

Thanks for any help!

McNets · Accepted Answer · 2018-01-18 16:53:40Z

CREATE TABLE T(amount INT, index INT); INSERT INTO T VALUES (55, 1),(88, 5),(45, 6),(86, 7),(87, 10), (88, 11),(57, 16),(58, 17),(59, 20),(60, 21);

You can take advantage of Postgres function generate_series().

generate_series(start, stop, step)

Generate a series of values, from start to stop with a step size of step

The example below uses a series from 0 to 14 with an interval of 4.

SELECT generate_series(0, 14, 4) Serie; | serie | | ----: | | 0 | | 4 | | 8 | | 12 | WITH CTS AS ( SELECT generate_series(0, 14, 4) Serie ) SELECT COALESCE(SUM(T.amount),0) AS amount, CTS.Serie FROM CTS LEFT JOIN T ON T.index >= CTS.Serie AND T.index < CTS.Serie + 4 GROUP BY CTS.Serie ORDER BY CTS.Serie; amount | serie -----: | ----: 55 | 0 219 | 4 175 | 8 0 | 12

You can use a user defined function that allows you to use parameters

CREATE OR REPLACE FUNCTION GroupIntervals(low int, high int, step int) RETURNS TABLE (amount int, interv int) AS $$ BEGIN RETURN QUERY WITH CTS AS ( SELECT generate_series(low, high, step) Serie ) SELECT COALESCE(SUM(T.amount),0)::int AS amount, CTS.Serie as interv FROM CTS LEFT JOIN T ON T.index >= CTS.Serie AND T.index < CTS.Serie + step GROUP BY CTS.Serie ORDER BY CTS.Serie; END; $$ LANGUAGE plpgsql;

✓

SELECT * FROM GroupIntervals(0, 14, 4);

 amount | interv -----: | -----: 55 | 0 219 | 4 175 | 8 0 | 12

SELECT * FROM GroupIntervals(5, 14, 5);

 amount | interv -----: | -----: 219 | 5 175 | 10

dbfiddle here

Update

As Evan Carrol has pointed out on him comments you can avoid use CTE and change function language from plpgsql to plain SQL.

CREATE OR REPLACE FUNCTION GroupIntervals(low int, high int, step int) RETURNS TABLE (amount int, interv int) AS $$ SELECT COALESCE(SUM(T.amount),0)::int AS amount, CTS.Serie as interv FROM generate_series(low, high, step) AS CTS(Serie) LEFT JOIN T ON index >= CTS.Serie AND index < CTS.Serie + step GROUP BY CTS.Serie ORDER BY CTS.Serie; $$ LANGUAGE sql;

SELECT * FROM GroupIntervals(0, 14, 4);

 amount | interv -----: | -----: 55 | 0 219 | 4 175 | 8 0 | 12

SELECT * FROM GroupIntervals(5, 14, 5);

 amount | interv -----: | -----: 219 | 5 175 | 10

dbfiddle here

@McNets Good answer, but a couple minor points: I wouldn't wrap the GENERATE_SERIES in a CTE, and I wouldn't use plpgsql for this. This is just regular SQL. — Evan Carroll
– Evan Carroll, Commented Jan 18, 2018 at 16:44
@McNets dbfiddle.uk/… (feel free to incorporate that into the answer) — Evan Carroll
– Evan Carroll, Commented Jan 18, 2018 at 16:48
In the updated answer/fiddle: Where comes CTS from? It would be great, if you could explain that. Thank you!! — badera
– badera, Commented Jan 23, 2018 at 7:33

Stack Exchange Network

Aggregate values by interval range given by parameter

1 Answer 1

Hot Network Questions

Aggregate values by interval range given by parameter

1 Answer 1

Related

Hot Network Questions