Обсуждение: Recursive Queries

Поиск
Список
Период
Сортировка

Recursive Queries

От
Alex Magnum
Дата:
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

RE: Recursive Queries

От
Rob Northcott
Дата:

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

Re: Recursive Queries

От
Julien Rouhaud
Дата:
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

От
Michael Lewis
Дата:
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.

Re: Recursive Queries

От
Edward Macnaghten
Дата:
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

От
Olivier Gautherot
Дата:
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 

Re: Recursive Queries

От
Edward Macnaghten
Дата:
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

От
Alex Magnum
Дата:
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