Postgres Pro
Downloads
Home   >   mailing lists

RE: Recursive Queries - Mailing list pgsql-general

From Rob Northcott
Subject RE: Recursive Queries
Date
Msg-id AM0PR08MB53139956CB15AC50D563C17D9BD80@AM0PR08MB5313.eurprd08.prod.outlook.com
Whole thread Raw
In response to Recursive Queries  (Alex Magnum <magnum11200@gmail.com>)
Responses Re: Recursive Queries
Re: Recursive Queries
List pgsql-general
Tree view

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

pgsql-general by date:

Previous
From: Kyotaro Horiguchi
Date:
Subject: Re: pg_restore: could not close data file: Success
Next
From: Julien Rouhaud
Date:
Subject: Re: Recursive Queries