Postgresql: aggregate data in a set of date ranges
I have a table
CREATE TABLE t1
(
id serial NOT NULL,
in_quantity bigint NULL,
price money NOT NULL,
out_quantity bigint NULL,
stamp timestamp NOT NULL
);
with data like this, for example (date is the same, but not time)
INSERT INTO t1 (in_quantity, price, out_quantity, stamp)
VALUES
( 100, 10.00, NULL, '2014-10-20 00:00:00'), -- id = 1
( 200, 11.00, NULL, '2014-10-20 00:01:00'), -- id = 2
( 300, 12.00, NULL, '2014-10-20 00:02:00'), -- id = 3
(NULL, 13.00, 400, '2014-10-20 00:03:00'), -- id = 4
(NULL, 14.00, 500, '2014-10-20 00:04:00'), -- id = 5
( 600, 15.00, NULL, '2014-10-20 00:15:00'), -- id = 6
( 700, 16.00, NULL, '2014-10-20 00:16:00'), -- id = 7
( 800, 17.00, NULL, '2014-10-20 00:17:00'), -- id = 8
(NULL, 18.00, 900, '2014-10-20 00:18:00'), -- id = 9
(NULL, 19.00, 1000, '2014-10-20 00:19:00'), -- id = 10
(2300, 23.00, NULL, '2014-10-20 00:23:00'), -- id = 11
(2400, 24.00, NULL, '2014-10-20 00:24:00'); -- id = 12
I need to get rows from this table with the maximum counts in and for the count for each date range in a specific set. Install for example:
( "2014-10-20 00:00:00" : "2014-10-20 00:05:00" ] ( "2014-10-20 00:05:00" : "2014-10-20 00:10:00" ] ( "2014-10-20 00:10:00" : "2014-10-20 00:15:00" ] ( "2014-10-20 00:15:00" : "2014-10-20 00:20:00" ] ( "2014-10-20 00:20:00" : "2014-10-20 00:25:00" ]
and my desired output with this example would be
interval begin | interval end | max_in_q | max_in_q_id | max_out_q | max_out_q_id
======================+=======================+==========+=============+===========+=============
"2014-10-20 00:00:00" | "2014-10-20 00:05:00" | 300 | 3 | 400 | 4
"2014-10-20 00:05:00" | "2014-10-20 00:10:00" | NULL | NULL | NULL | NULL
"2014-10-20 00:10:00" | "2014-10-20 00:15:00" | NULL | NULL | NULL | NULL
"2014-10-20 00:15:00" | "2014-10-20 00:20:00" | 800 | 8 | 1000 | 10
"2014-10-20 00:20:00" | "2014-10-20 00:25:00" | 2400 | 12 | NULL | NULL
So. I can generate a set like this with a query like this
SELECT
i::timestamp AS dleft,
i::timestamp + '1 hour' AS dright
FROM
generate_series('2014-10-20 00:00:00'::timestamp, '2014-10-20 23:00:00'::timestamp, '1 hour') AS i
But I cannot figure out how I can make an aggregate function for each of these small ranges and how I can join the results.
source to share
First, you need to understand that you also need id
for every aggregated value, which is not a simple query, in any DBMS.
This problem is mostly solved with the help DISTINCT ON
in PostgreSQL:
SELECT DISTINCT ON (s)
s ts_start, s + '5 minutes' ts_end, in_quantity max_in_q, id max_in_id
FROM
generate_series('2014-10-20 00:00:00'::timestamp, '2014-10-20 00:20:00'::timestamp, '5 minutes') s
LEFT JOIN
t1 ON stamp <@ tsrange(s, s + '5 minutes', '(]')
ORDER BY
s, in_quantity DESC NULLS LAST;
But this only allows you to select one max / min value and the entire row they belong to.
If you really want both max columns, then you need to write self-joins and subqueries that won't be as fast:
SELECT
lower(r) ts_start, upper(r) ts_end, max_in_q, max_in.id max_in_id, max_out_q, max_out.id max_out_id
FROM (
SELECT
r, max(in_quantity) max_in_q, max(out_quantity) max_out_q
FROM
generate_series('2014-10-20 00:00:00'::timestamp, '2014-10-20 00:20:00'::timestamp, '5 minutes') s,
tsrange(s, s + '5 minutes', '(]') r
LEFT JOIN
t1 ON stamp <@ r
GROUP BY
r
ORDER BY
r
) m
LEFT JOIN
t1 max_in ON max_in.in_quantity = max_in_q
LEFT JOIN
t1 max_out ON max_out.out_quantity = max_out_q;
Note : with this second version you have to deal with duplicates of yourself, because in_quantity
, and out_quantity
not unique.
source to share
I think it can be quite simple with a range type :
WITH data(in_quantity,price,out_quantity,stamp) AS (VALUES
( 100::int8, 10.00, NULL::int8, '2014-10-20 00:00:00'::timestamp), -- id = 1
( 200, 11.00, NULL, '2014-10-20 00:01:00'), -- id = 2
( 300, 12.00, NULL, '2014-10-20 00:02:00'), -- id = 3
(NULL, 13.00, 400, '2014-10-20 00:03:00'), -- id = 4
(NULL, 14.00, 500, '2014-10-20 00:04:00'), -- id = 5
( 600, 15.00, NULL, '2014-10-20 00:15:00'), -- id = 6
( 700, 16.00, NULL, '2014-10-20 00:16:00'), -- id = 7
( 800, 17.00, NULL, '2014-10-20 00:17:00'), -- id = 8
(NULL, 18.00, 900, '2014-10-20 00:18:00'), -- id = 9
(NULL, 19.00, 1000, '2014-10-20 00:19:00'), -- id = 10
(2300, 23.00, NULL, '2014-10-20 00:23:00'), -- id = 11
(2400, 24.00, NULL, '2014-10-20 00:24:00')
)
SELECT
tsrange(i,i+INTERVAL '1h','[)') r,
max(in_quantity) max_in_q,
max(out_quantity) max_out_q
FROM generate_series('2014-10-20 00:00:00'::timestamp,
'2014-10-20 23:00:00'::timestamp, '1 hour') AS i
LEFT JOIN data d ON tsrange(i,i+INTERVAL '1h','[)') @> d.stamp
GROUP BY r
ORDER BY r;
I used it LEFT JOIN
here as I thought you would like to see all ranges, adapt to your needs.
source to share