Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kF6S5-0006N2-9S for pgsql-sql@arkaria.postgresql.org; Mon, 07 Sep 2020 02:00:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kF6S4-0002Wa-7K for pgsql-sql@arkaria.postgresql.org; Mon, 07 Sep 2020 02:00:12 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kF6S3-0002WH-MT for pgsql-sql@lists.postgresql.org; Mon, 07 Sep 2020 02:00:11 +0000 Received: from p3plsmtpa06-03.prod.phx3.secureserver.net ([173.201.192.104]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kF6S0-0000WL-GR for pgsql-sql@lists.postgresql.org; Mon, 07 Sep 2020 02:00:10 +0000 Received: from [192.168.1.3] ([152.234.153.24]) by :SMTPAUTH: with ESMTPA id F6RtkHgdVY1HwF6Rwkqmym; Sun, 06 Sep 2020 19:00:06 -0700 X-CMAE-Analysis: v=2.3 cv=QdQYQfTv c=1 sm=1 tr=0 a=+hyGCyc6Xm5D08NSvo7kUA==:117 a=+hyGCyc6Xm5D08NSvo7kUA==:17 a=x7bEGLp0ZPQA:10 a=PkefZFhya44A:10 a=wf_yTYXYAAAA:8 a=pGLkceISAAAA:8 a=BtxOJPrh5aIkfRUmD6UA:9 a=QEXdDO2ut3YA:10 a=OZLvo1xC5ysUBlx4WlUA:9 a=rEozLAWO7L--cpeZ:21 a=_W_S_7VecoQA:10 a=hSGMlWDd2ZYrfeZIb8_S:22 X-SECURESERVER-ACCT: iuri@iurix.com From: Iuri Sampaio Message-Id: <68F46D33-6E97-41F6-BF95-D71341D442D7@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_E70E0202-4658-40A6-8059-B76B2B7310BC" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.80.23.2.2\)) Subject: Re: Crossing/Rotating table rows to rows and columns Date: Sun, 6 Sep 2020 23:03:17 -0300 In-Reply-To: <613EDE8D-78F1-440C-8338-93FE29268E06@thebuild.com> Cc: pgsql-sql To: Christophe Pettus References: <2B732C2C-5DC9-4D66-A54C-90EFF8780541@gmail.com> <82BA6332-81EC-40E4-92AF-5A5254DB32BF@thebuild.com> <6E237A69-DCCC-484B-A97E-42DB54D98A1C@gmail.com> <613EDE8D-78F1-440C-8338-93FE29268E06@thebuild.com> X-Mailer: Apple Mail (2.3608.80.23.2.2) X-CMAE-Envelope: MS4wfPW8pGu5HAbobZMvT5lKkKN02bVr4ccirIlPEyCurML1p+AX/bhUYTHTUw7M4ifqGOTTCOlIfdiB6kM4IxMsAq0fKDx8bBY/AD7CUTVJEfj7yFjW1P/P enLibqEE0kDVLo6UxrA9ExtOjBWWxAnOoBES+GUyaoHXQfCTs3trwtADtTPFQJ/MeIkTiBlZzzgZFT4hKPWNgzETA9VG22M4ct9jtmO4dvkY+sL4dCERYDjx List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_E70E0202-4658-40A6-8059-B76B2B7310BC Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > You can do that particular type of operation with aggregates: not quite. Your query misses GROUP BY in the end. ERROR: column "t.datetime" must appear in the GROUP BY clause or be = used in an aggregate function Furthermore, aggregate function misses concatenation of datetime and = total. So, I fixed it and its result is not what I need. Please, see = bellow. SELECT datetime, array_agg(datetime || ', ' || 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 'qt_face' AND o.creation_date = BETWEEN '2020-09-02'::date - INTERVAL '6 day' AND '2020-09-02'::date + = INTERVAL '1 day' GROUP BY datetime, 1) t GROUP BY datetime; datetime | totals =20 ------------------------+-------------------------------- 2020-08-27 06:00:00+00 | {"2020-08-27 06:00:00+00, 4"} 2020-08-27 07:00:00+00 | {"2020-08-27 07:00:00+00, 1"} 2020-08-27 08:00:00+00 | {"2020-08-27 08:00:00+00, 4"} 2020-08-27 09:00:00+00 | {"2020-08-27 09:00:00+00, 8"} 2020-08-27 10:00:00+00 | {"2020-08-27 10:00:00+00, 2"} 2020-08-27 11:00:00+00 | {"2020-08-27 11:00:00+00, 10"} 2020-08-27 12:00:00+00 | {"2020-08-27 12:00:00+00, 5"} 2020-08-27 13:00:00+00 | {"2020-08-27 13:00:00+00, 6"} 2020-08-27 14:00:00+00 | {"2020-08-27 14:00:00+00, 4"} 2020-08-27 15:00:00+00 | {"2020-08-27 15:00:00+00, 15"} 2020-08-27 16:00:00+00 | {"2020-08-27 16:00:00+00, 22"} 2020-08-27 17:00:00+00 | {"2020-08-27 17:00:00+00, 37"} 2020-08-27 18:00:00+00 | {"2020-08-27 18:00:00+00, 14"} 2020-08-28 06:00:00+00 | {"2020-08-28 06:00:00+00, 3"} 2020-08-28 07:00:00+00 | {"2020-08-28 07:00:00+00, 1"} 2020-08-28 08:00:00+00 | {"2020-08-28 08:00:00+00, 7"} 2020-08-28 09:00:00+00 | {"2020-08-28 09:00:00+00, 5"} =E2=80=A6 The target structure must have dates as columns, and that turns out to = be complex because the number of columns is not static, plus the labels = of the columns change based in the day the query is executed, thus = CROSSTAB doesn=E2=80=99t seem to be applicable. =20 > hour 2020-7-26 2020-7-27 ... 2020-7-31 2020-8-01 > 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 > hour 2020-7-30 2020-7-31 ... 2020-8-01 2020-8-02 > 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 > hour 2020-8-10 2020-8-11 ... 2020-8-12 2020-8-16 > 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 Is there anyway to rotate lines to columns, without loosing the lines = per hour, as in the target pivot table bellow: > 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 > On Muh. 15, 1442 AH, at 21:22, Christophe Pettus = wrote: >=20 >=20 >=20 >> On Sep 3, 2020, at 17:20, Iuri Sampaio = 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 >=20 > You can do that particular type of operation with aggregates: >=20 > 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; >=20 > 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. >=20 > -- > -- Christophe Pettus > xof@thebuild.com >=20 --Apple-Mail=_E70E0202-4658-40A6-8059-B76B2B7310BC Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
You can do that = particular type of operation with aggregates:

not quite.

Your query misses GROUP BY in the = end.

ERROR:  column "t.datetime" must appear in the GROUP BY = clause or be used in an aggregate function


Furthermore, aggregate function misses concatenation of = datetime and total. So, I fixed it and its result is not what I need. = Please, see bellow.

SELECT datetime, array_agg(datetime || ', ' || 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 'qt_face' = AND o.creation_date BETWEEN '2020-09-02'::date - INTERVAL '6 day' AND = '2020-09-02'::date + INTERVAL '1 day' GROUP BY datetime, 1) t GROUP BY = datetime;
        = datetime        |           =   totals            =  
------------------------+--------------------------------
 2020-08-27 06:00:00+00 | = {"2020-08-27 06:00:00+00, 4"}
 2020-08-27 07:00:00+00 | {"2020-08-27 07:00:00+00, = 1"}
 2020-08-27 08:00:00+00 | = {"2020-08-27 08:00:00+00, 4"}
 2020-08-27 09:00:00+00 | {"2020-08-27 09:00:00+00, = 8"}
 2020-08-27 10:00:00+00 | = {"2020-08-27 10:00:00+00, 2"}
 2020-08-27 11:00:00+00 | {"2020-08-27 11:00:00+00, = 10"}
 2020-08-27 12:00:00+00 | = {"2020-08-27 12:00:00+00, 5"}
 2020-08-27 13:00:00+00 | {"2020-08-27 13:00:00+00, = 6"}
 2020-08-27 14:00:00+00 | = {"2020-08-27 14:00:00+00, 4"}
 2020-08-27 15:00:00+00 | {"2020-08-27 15:00:00+00, = 15"}
 2020-08-27 16:00:00+00 | = {"2020-08-27 16:00:00+00, 22"}
 2020-08-27 17:00:00+00 | {"2020-08-27 17:00:00+00, = 37"}
 2020-08-27 18:00:00+00 | = {"2020-08-27 18:00:00+00, 14"}
 2020-08-28 06:00:00+00 | {"2020-08-28 06:00:00+00, = 3"}
 2020-08-28 07:00:00+00 | = {"2020-08-28 07:00:00+00, 1"}
 2020-08-28 08:00:00+00 | {"2020-08-28 08:00:00+00, = 7"}
 2020-08-28 09:00:00+00 | = {"2020-08-28 09:00:00+00, 5"}


=E2=80=A6


The = target structure must have dates as columns, and that turns out to be = complex because the number of columns is not static, plus the labels of = the columns change based in the day the query is executed, thus CROSSTAB = doesn=E2=80=99t seem to be applicable.  




hour 2020-7-26 2020-7-27 = ... 2020-7-31 2020-8-01
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


hour 2020-7-30 2020-7-31 = ... 2020-8-01 2020-8-02
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


hour 2020-8-10 2020-8-11 ... 2020-8-12 2020-8-16
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




Is there anyway to = rotate lines to columns, without loosing the lines per hour, as in the = target pivot table bellow:


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



On Muh. 15, 1442 AH, at 21:22, Christophe = Pettus <xof@thebuild.com> wrote:



On Sep 3, = 2020, at 17:20, Iuri Sampaio <iuri.sampaio@gmail.com> wrote:

=46rom 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 =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


= --Apple-Mail=_E70E0202-4658-40A6-8059-B76B2B7310BC--