RE: Recursive Queries

From: Alex Magnum <magnum11200(at)gmail(dot)com>
Sent: 16 April 2020 09:36
To: Postgres General <pgsql-general(at)postgresql(dot)org>
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