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