Re: Crossing/Rotating table rows to rows and columns

> On Sep 3, 2020, at 17:20, Iuri Sampaio <iuri.sampaio@gmail.com> wrote:
>
> From the table structure, such as:
>
> hour | total
> ------------------------+-------
> 2020-07-26 02:00:00+00 | 1
> 2020-07-26 04:00:00+00 | 7
> 2020-07-26 05:00:00+00 | 6
> 2020-07-26 06:00:00+00 | 6
> 2020-07-26 07:00:00+00 | 17
> 2020-07-26 08:00:00+00 | 17
> 2020-07-26 09:00:00+00 | 6
> 2020-07-26 10:00:00+00 | 8
> 2020-07-26 11:00:00+00 | 14
> 2020-07-26 12:00:00+00 | 16
> 2020-07-26 13:00:00+00 | 10
> 2020-07-26 14:00:00+00 | 17
> 2020-07-26 15:00:00+00 | 15
> 2020-07-26 16:00:00+00 | 2
> 2020-07-27 00:00:00+00 | 1
> 2020-07-27 06:00:00+00 | 1
> ..
> 2020-08-01 07:00:00+00 | 7
> 2020-08-01 08:00:00+00 | 4
> 2020-08-01 09:00:00+00 | 7
> 2020-08-01 10:00:00+00 | 10
> 2020-08-01 11:00:00+00 | 20
> 2020-08-01 12:00:00+00 | 25
> 2020-08-01 13:00:00+00 | 18
> 2020-08-01 14:00:00+00 | 14
> 2020-08-01 15:00:00+00 | 12
> 2020-08-01 16:00:00+00 | 4
> (91 rows)
>
> to the target pivot table:
> hour 2020-7-26 2020-7-27 ... 2020-7-31 2020-8-01
> 0:00:00
> 1:00:00
> 2:00:00
> 3:00:00
> 4:00:00
> 5:00:00 1
> 6:00:00 2 2 4 22 7 4
> 7:00:00 8 2 3 8 1
> 8:00:00 3 8 4 1 9 4
> 9:00:00 4 6 2 35 8
> 10:00:00 9 19 14 2 10 2
> 11:00:00 11 8 7 13 10 13 10
> 12:00:00 12 7 18 12 8 12 5
> 13:00:00 6 14 8 24 10 6 6
> 14:00:00 8 10 9 7 14 11 4
> 15:00:00 21 10 4 2 13 15
> 16:00:00 12 15 11 10 22 22
> 17:00:00 30 14 11 28 10 29
> 18:00:00 1
> 19:00:00
> 20:00:00
> 21:00:00
> 22:00:00
> 23:00:00

You can do that particular type of operation with aggregates:

    SELECT hour, array_agg(total) as totals FROM (select date_trunc('hour', o.creation_date) AS datetime,
            COUNT(1) AS total
            FROM cr_items ci, acs_objects o, cr_revisions cr
            WHERE ci.item_id = o.object_id
            AND ci.item_id = cr.item_id
            AND ci.latest_revision = cr.revision_id
            AND ci.content_type = :content_type
            AND o.creation_date BETWEEN :creation_date::date - INTERVAL '6 day' AND :creation_date::date + INTERVAL '1
day'
            GROUP BY 1) t;

This will not include any of the "hour"s that do not have a "total", but you can achieve that with a left outer join
againsta subquery that uses generate_series to create a single-row table with all of the desired hours in it. 

--
-- Christophe Pettus
   xof@thebuild.com