Re: Crossing/Rotating table rows to rows and columns - Mailing list pgsql-sql
| From | Iuri Sampaio |
|---|---|
| Subject | Re: Crossing/Rotating table rows to rows and columns |
| Date | |
| Msg-id | 68F46D33-6E97-41F6-BF95-D71341D442D7@gmail.com Whole thread Raw |
| In response to | Re: Crossing/Rotating table rows to rows and columns (Christophe Pettus <xof@thebuild.com>) |
| List | pgsql-sql |
You can do that particular type of operation with aggregates:
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