Re: Crossing/Rotating table rows to rows and columns

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

From: Christophe Pettus <xof@thebuild.com>
To: Iuri Sampaio <iuri.sampaio@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Crossing/Rotating table rows to rows and columns
Date: Thu, 3 Sep 2020 17:22:01 -0700
Message-ID: <613EDE8D-78F1-440C-8338-93FE29268E06@thebuild.com> (raw)
In-Reply-To: <6E237A69-DCCC-484B-A97E-42DB54D98A1C@gmail.com>
References: <2B732C2C-5DC9-4D66-A54C-90EFF8780541@gmail.com>
	<82BA6332-81EC-40E4-92AF-5A5254DB32BF@thebuild.com>
	<6E237A69-DCCC-484B-A97E-42DB54D98A1C@gmail.com>



> 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)  latest in thread

Message-ID: <613EDE8D-78F1-440C-8338-93FE29268E06@thebuild.com>
Permalink:  ../613EDE8D-78F1-440C-8338-93FE29268E06@thebuild.com/
Also on:    postgresql.org/message-id/613EDE8D-78F1-440C-8338-93FE29268E06@thebuild.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: xof@thebuild.com, iuri.sampaio@gmail.com, pgsql-sql@lists.postgresql.org
  Subject: Re: Crossing/Rotating table rows to rows and columns
  In-Reply-To: <613EDE8D-78F1-440C-8338-93FE29268E06@thebuild.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