Recursive Queries

From: Alex Magnum <magnum11200@gmail.com>
Sent: 16 April 2020 09:36
To: Postgres General <pgsql-general@postgresql.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

On Thu, Apr 16, 2020 at 2:49 PM Rob Northcott
<Rob.Northcott@compilator.com> wrote:

From: Alex Magnum <magnum11200@gmail.com>

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

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.

Note that you wouldn't need subqueries for that, the FILTER clause can
be used and is supported since version 9.4.

You don't want recursion, you want pivot table (Excel) behavior to reformat
rows into columns. The easiest way to get this data in its raw form would
be to group by date and hour of day and compute the count.

If you have the option to add extensions in your environment, then you
should be able to pivot your data pretty simply.

https://www.postgresql.org/docs/12/tablefunc.html

On 16/04/2020 09:35, Alex Magnum wrote:

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.

Various ways, but for me...

SELECT hour, SUM(CASE(WHEN date = date THEN 1 ELSE 0)), ...., ....
WHERE whatever
GROUP BY hour

could be your friend

Hi Alex,

On Thu, Apr 16, 2020 at 10:36 AM Alex Magnum <magnum11200@gmail.com> wrote:

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

You don't need subqueries. The WHEN statement can help you in this case (a
bit tedious to write but fast to run):

WITH q AS (select date_part('hour', ts) AS hr, ts::date AS mydate FROM
your_table_or_query)
SELECT hr,
sum(CASE WHEN mydate = '2020-04-01'::date THEN 1 ELSE 0 END),
sum(CASE WHEN mydate = '2020-04-02'::date THEN 1 ELSE 0 END),
...
FROM q ORDER BY hr;

Hope it helps
--
Olivier Gautherot
Tel: +33 6 02 71 92 23
https://www.linkedin.com/in/ogautherot/

On 16/04/2020 14:36, Edward Macnaghten wrote:

On 16/04/2020 09:35, Alex Magnum wrote:

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.

SELECT hour, SUM(CASE(WHEN date = date THEN 1 ELSE 0 END)), ...., ...
WHERE whatever
GROUP BY hour

Could be your friend

thanks for the suggestion. tablefunc extension might be the easiest one

On Thu, Apr 16, 2020 at 9:46 PM Edward Macnaghten <eddy@edlsystems.com>
wrote: