agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Iuri Sampaio <iuri.sampaio@gmail.com>
To: Christophe Pettus <xof@thebuild.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Crossing/Rotating table rows to rows and columns
Date: Sun, 6 Sep 2020 23:03:17 -0300
Message-ID: <68F46D33-6E97-41F6-BF95-D71341D442D7@gmail.com> (raw)
In-Reply-To: <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>
<613EDE8D-78F1-440C-8338-93FE29268E06@thebuild.com>
> 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 = o.object_id AND ci.item_id = cr.item_id AND ci.latest_revision = cr.revision_id AND ci.content_type = '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"}
…
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’t 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:
>>
>> 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 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
>
view thread (5+ messages)
Message-ID: <68F46D33-6E97-41F6-BF95-D71341D442D7@gmail.com>
Permalink: ../68F46D33-6E97-41F6-BF95-D71341D442D7@gmail.com/
Also on: postgresql.org/message-id/68F46D33-6E97-41F6-BF95-D71341D442D7@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: iuri.sampaio@gmail.com, xof@thebuild.com, pgsql-sql@lists.postgresql.org
Subject: Re: Crossing/Rotating table rows to rows and columns
In-Reply-To: <68F46D33-6E97-41F6-BF95-D71341D442D7@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox