Postgresql – Aggregate values by interval range given by parameter

aggregatepostgresql

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!

Best Answer

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

Related Solutions

Postgresql – Optimizing a query on a large table (EXPLAIN ANALYZE result inside)

A few thoughts. Consider range partitioning on "timestamp", it will reduce the amount of work your query have to do. Further optimization might be to calculate and store the agg for "closed" partitions. You will of course have to recalculate this when you modify historical information.

As a bonus it will be much easier to roll-out historical data that is no longer needed.

As mentioned this is just some thoughts and reflections, it may or it may not fit your situation. One pitfall may be the number of different intervals that you use for reporting. If larger intervals is not multiples of the partitioning interval this wont work well.

Edit: How is the plan affected by creating an temp table and an index on "timestamp"?

create index X on market_trades ( timestamp );

create temp table T ( time_range tsrange );

insert into T ( time_range )
WITH vals AS (
    SELECT '2013-08-19 0:00'::timestamp  AS frame_start,
       '2013-08-26 0:00'::timestamp  AS frame_end,
       '17h'::interval               AS interval_length
),   intervals AS (
    SELECT tsrange(start_time,
               lead(start_time, 1, frame_end) OVER (ORDER BY start_time   NULLS FIRST)) AS time_range
    FROM (
        SELECT generate_series(frame_start, frame_end, interval_length) AS  
               start_time, frame_end
        FROM vals
    ) _
    WHERE start_time < frame_end
)
SELECT time_range
FROM intervals i;

create index x on T ( time_range );

analyze t;

SELECT time_range, count(td.id) AS agg
FROM T
LEFT JOIN market_trades td
    ON td.timestamp <@ T.time_range
GROUP  BY T.time_range
ORDER  BY T.time_range;

Postgresql – Cross-product between table column and input values

In other RDBMS (like SQL Server before 2008 - as per Paul's comment) one might cross join to a subquery with UNION ALL SELECT, but there are more convenient and efficient options in Postgres.

And you don't need a CTE for this. You can use it, but it has no performance benefit.

Provide a set with VALUES:

VALUES computes a row value or set of row values specified by value expressions. It is most commonly used to generate a "constant table" within a larger command, but it can be used on its own.
```
SELECT t.i, m.col1
FROM   mTable m
CROSS  JOIN (VALUES (1), (2)) t(i);
```

Provide an array and unnest()

2a. with an array constructor:

SELECT i, m.col1
FROM   mTable m
CROSS  JOIN unnest (ARRAY[1,2]) i;

2b. With an array literal:

SELECT i, m.col1
FROM   mTable m
CROSS  JOIN unnest ('{1,2}'::int[]) i;

Add ORDER BY i, m.col1 if you need the sort order in your result.

About row and array syntax:

Array of strings when updating a field

Best Answer

Related Solutions

Postgresql – Optimizing a query on a large table (EXPLAIN ANALYZE result inside)

Postgresql – Cross-product between table column and input values

Related Question