Re: Recursive Queries

Hi Alex,

On Thu, Apr 16, 2020 at 10:36 AM Alex Magnum <magnum11200(at)gmail(dot)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/