Home > mailing lists

Thread: Recursive Queries

Recursive Queries

From

Alex Magnum

Date:

16 April 2020, 11:35:49

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.

RE: Recursive Queries

From

Rob Northcott

Date:

16 April 2020, 15:48:56

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.

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

Re: Recursive Queries

From

Julien Rouhaud

Date:

16 April 2020, 16:24:35

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
justneeds 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.

Re: Recursive Queries

From

Michael Lewis

Date:

16 April 2020, 16:25:29

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

Re: Recursive Queries

From

Edward Macnaghten

Date:

16 April 2020, 16:36:38

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

Re: Recursive Queries

From

Olivier Gautherot

Date:

16 April 2020, 16:42:44

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/

Re: Recursive Queries

From

Edward Macnaghten

Date:

16 April 2020, 16:45:53

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

Re: Recursive Queries

From

Alex Magnum

Date:

16 April 2020, 21:06:20

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:

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