pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom: Iuri Sampaio <iuri.sampaio@gmail.com>
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Crossing/Rotating table rows to rows and columns
Date: Wed, 2 Sep 2020 23:58:46 -0300
Message-ID: <2B732C2C-5DC9-4D66-A54C-90EFF8780541@gmail.com> (raw)
Hi there,
How would I convert/rotate (i.e. cross table) the following datasource, which is originally returned by rows (i.e. datetime and total), to rows as hours and columns as dates, where the columns (dates) will be assigned with "total" as their value.
Here it is the chunk of code to convert from base64url to binary PNG
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 ORDER BY datetime ASC''
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)
It would result in the table, as in:
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
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 https://www.postgresql.org/docs/9.2/tablefunc.html <https://www.postgresql.org/docs/9.2/tablefunc.html;
ERROR: function crosstab(unknown, unknown) does not exist
LINE 1: select * from crosstab('select o.creation_date::date AS day ...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Thus, I was wondering if there is a better approach to write a beautiful code out of it.
Does anyone have an idea on how to write this crosstable display?
Best wishes,
I
view thread (5+ messages) latest in thread
Message-ID: <2B732C2C-5DC9-4D66-A54C-90EFF8780541@gmail.com>
Permalink: ../2B732C2C-5DC9-4D66-A54C-90EFF8780541@gmail.com/
Also on: postgresql.org/message-id/2B732C2C-5DC9-4D66-A54C-90EFF8780541@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, pgsql-sql@lists.postgresql.org
Subject: Re: Crossing/Rotating table rows to rows and columns
In-Reply-To: <2B732C2C-5DC9-4D66-A54C-90EFF8780541@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox