Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 11.5 \(3445.9.5\))
Subject: Re: Crossing/Rotating table rows to rows and columns
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: <6E237A69-DCCC-484B-A97E-42DB54D98A1C@gmail.com>
Date: Thu, 3 Sep 2020 17:22:01 -0700
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <613EDE8D-78F1-440C-8338-93FE29268E06@thebuild.com>
References: <2B732C2C-5DC9-4D66-A54C-90EFF8780541@gmail.com>
 <82BA6332-81EC-40E4-92AF-5A5254DB32BF@thebuild.com>
 <6E237A69-DCCC-484B-A97E-42DB54D98A1C@gmail.com>
To: Iuri Sampaio <iuri.sampaio@gmail.com>
Precedence: bulk


> On Sep 3, 2020, at 17:20, Iuri Sampaio <iuri.sampaio@gmail.com> wrote:
>=20
> =46rom the table structure, such as:
>=20
> 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)
>=20
> 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 =3D o.object_id
            AND ci.item_id =3D cr.item_id
            AND ci.latest_revision =3D cr.revision_id
            AND ci.content_type =3D :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 against a subquery that uses =
generate_series to create a single-row table with all of the desired =
hours in it.

--
-- Christophe Pettus
   xof@thebuild.com