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 1kDzQS-00035Y-EV for pgsql-sql@arkaria.postgresql.org; Fri, 04 Sep 2020 00:17:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kDzQR-0000fH-CF for pgsql-sql@arkaria.postgresql.org; Fri, 04 Sep 2020 00:17:55 +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 1kDzQR-0000fA-1R for pgsql-sql@lists.postgresql.org; Fri, 04 Sep 2020 00:17:55 +0000 Received: from p3plsmtpa07-10.prod.phx3.secureserver.net ([173.201.192.239]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kDzQO-0005DN-B0 for pgsql-sql@lists.postgresql.org; Fri, 04 Sep 2020 00:17:54 +0000 Received: from [192.168.1.2] ([187.127.207.60]) by :SMTPAUTH: with ESMTPA id DzQJktIZLIY3yDzQLkTO7s; Thu, 03 Sep 2020 17:17:50 -0700 X-CMAE-Analysis: v=2.3 cv=Q+asHL+a c=1 sm=1 tr=0 a=FUaYfqyeFLbpuLVDzyHfUw==:117 a=FUaYfqyeFLbpuLVDzyHfUw==:17 a=x7bEGLp0ZPQA:10 a=PkefZFhya44A:10 a=wf_yTYXYAAAA:8 a=pGLkceISAAAA:8 a=PRJpQKg9itAUiZUvViYA:9 a=CjuIK1q_8ugA:10 a=EIyeUQJXt26HNKKZLisA:9 a=8x9gFKsg31EqjJcn:21 a=_W_S_7VecoQA:10 a=hSGMlWDd2ZYrfeZIb8_S:22 X-SECURESERVER-ACCT: iuri@iurix.com From: Iuri Sampaio Content-Type: multipart/alternative; boundary="Apple-Mail=_CEE465BD-A566-4FE9-9BE0-E600FA12C981" 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: Thu, 3 Sep 2020 21:20:51 -0300 References: <2B732C2C-5DC9-4D66-A54C-90EFF8780541@gmail.com> <82BA6332-81EC-40E4-92AF-5A5254DB32BF@thebuild.com> To: Christophe Pettus , pgsql-sql In-Reply-To: <82BA6332-81EC-40E4-92AF-5A5254DB32BF@thebuild.com> Message-Id: <6E237A69-DCCC-484B-A97E-42DB54D98A1C@gmail.com> X-Mailer: Apple Mail (2.3608.80.23.2.2) X-CMAE-Envelope: MS4wfH66ANND28rB05q5lsHlg9DoOYAFjsnQAJ31+Mx6H1BmYWA8q5aDopGp93WESHubxbn31M2iNY7X5IIKLomUSjlGNxFkU1CMDH3n8DPvRQps++lGuKCQ kn/jihwyOdO8e6aT21uK3SZQ95JW3Dr0DGXW5OPiKKOxHrsnoABbA/MqSN2jvJ0G0EHnq3txJ5Ipzs09+M0528RSCp3wczb1BbUd0ri2ickKuMP9F6KTzoCc List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_CEE465BD-A566-4FE9-9BE0-E600FA12C981 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Indeed, tablefunc -> crosstab would be a solution to it. However, the = number and label of columns dynamically change depending on the records = returned in the creation_date range, declared in the WHERE clasue. 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 ORDER BY datetime ASC' Furthermore, how would datetime column (o.creation_date) be split into = rows and columns as in:=20 =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 So far, I tried to simplify the query to actually get an idea of the = target pivot table, removing datetime interval conditionals. = Unfortunately it returns an error. ERROR: return and sql tuple descriptions are incompatible=20 SELECT * FROM CROSSTAB('select EXTRACT(hour FROM o.creation_date)::text = AS hour, o.creation_date::date::text AS day, COUNT(1) 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'' GROUP BY o.creation_date, 1 ORDER BY = hour ASC') AS t ("hour" TEXT, "day" NUMERIC); ERROR: return and sql tuple descriptions are incompatible > On Muh. 14, 1442 AH, at 23:58, Christophe Pettus = wrote: >=20 >=20 >=20 >> On Sep 2, 2020, at 19:58, Iuri Sampaio = wrote: >> I've tried to use crosstabN(text sql), to solve the problem directly = in the datasource layer, but apparently tablefunc is not supported in = the datamodel Squema=20 >=20 > "tablefunc" is an extension, so you will need to create it in your = database before using it: >=20 > CREATE EXTENSION tablefunc; >=20 > -- > -- Christophe Pettus > xof@thebuild.com >=20 --Apple-Mail=_CEE465BD-A566-4FE9-9BE0-E600FA12C981 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii Indeed, tablefunc -> crosstab would be a solution to it. = However, the number and label of columns dynamically change depending on = the records returned in the creation_date range, declared in the WHERE = clasue.

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 ORDER BY datetime ASC'



Furthermore, how would datetime column = (o.creation_date) be split into rows and columns as in: 

=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


So far, I tried to simplify the query to actually get an idea = of the target pivot table, removing datetime interval conditionals. = Unfortunately it returns an error.
ERROR:  return and sql tuple descriptions are = incompatible 


SELECT * FROM CROSSTAB('select = EXTRACT(hour FROM o.creation_date)::text AS hour, = o.creation_date::date::text AS day, COUNT(1) 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'' GROUP BY o.creation_date, 1 ORDER BY = hour ASC') AS t ("hour" TEXT, "day" NUMERIC);
ERROR:  return and sql tuple descriptions are = incompatible




On Muh. = 14, 1442 AH, at 23:58, Christophe Pettus <xof@thebuild.com> = wrote:



On Sep 2, 2020, at 19:58, Iuri Sampaio <iuri.sampaio@gmail.com> wrote:
I've = tried to use crosstabN(text sql), to solve the problem directly in the = datasource layer, but apparently tablefunc is not supported in the = datamodel Squema

"tablefunc" = is an extension, so you will need to create it in your database before = using it:

CREATE EXTENSION tablefunc;

--
-- Christophe Pettus
  xof@thebuild.com


= --Apple-Mail=_CEE465BD-A566-4FE9-9BE0-E600FA12C981--