From: Alex Magnum <[email protected]>
Sent: 16 April 2020 09:36
To: Postgres General <[email protected]>
Subject: Recursive Queries
Hi,
I have a simple table with singup timestamps
What I would like to do is to create a table as shown below that displays the
counts per our for the past n dates.
I can do this with a function but is there an easy way to use recursive queries?
Counts per hour for given date
HR 2020-04-01 2020-04-02 ... 2020-04-10
00 38 33 36
01 33 26 18
02 26 36 17
03 36 18 10
04 18 17 3
05 17 10 3
06 10 3 6
07 3 3 10
. 3 6 13
. 6 10 22
. 10 13 12
22 13 22 9
23 22 11 8
Thanks for any suggestions.
A
Wouldn’t it be easier to do it the other way round, with a column per hour and
a row per date? That way the query just needs one sub-query per hour to select
just the count for that hour, and group by date.
Rob
