PostgreSQL Crosstab – How to Add Extra Columns

pivotpostgresql

I am using PostgreSQL 9.6 and have following schema

CREATE TABLE activitylog (
    sessionid text NOT NULL,
    studentid integer NOT NULL,
    activity text NOT NULL,
    created timestamp without time zone DEFAULT now() NOT NULL
);

The schema is used to log visitors activity. Each visit is assigned a session id which is unique for the visit. Sessions ends when user logs out. Every user can log in and log out multiple times.

INSERT INTO activitylog VALUES
  ( 'abc', 1, 'logged in'   , '2017-08-12T02:39:05.753088Z' ),
  ( 'abc', 1, 'page visited', '2017-08-12T02:49:05.753088Z' ),
  ( 'abc', 1, 'logged out'  , '2017-08-12T02:59:05.753088Z' ),
  ( 'def', 1, 'logged in'   , '2017-08-12T03:09:05.753088Z' ),
  ( 'def', 1, 'page visited', '2017-08-12T03:19:05.753088Z' ),
  ( 'def', 1, 'logged out'  , '2017-08-12T03:29:05.753088Z' ),
  ( 'ghi', 2, 'logged in'   , '2017-08-12T03:39:05.753088Z' ),
  ( 'ghi', 2, 'page visited', '2017-08-12T03:59:05.753088Z' ),
  ( 'ghi', 2, 'logged out'  , '2017-08-12T03:59:05.753088Z' );

Required Result

I want to select data from the table such that I get result in following format

|  sessionid  |  studentid  |          loggedin           |          loggedout          |      interval      |
| ----------- | ----------- |       --------------        |         -----------         | ------------------ |
|     abc     |      1      | 2017-08-12T02:39:05.753088Z | 2017-08-12T02:59:05.753088Z |       20min        |
|     def     |      1      | 2017-08-12T03:09:05.753088Z | 2017-08-12T03:29:05.753088Z |       20min        |
|     ghi     |      2      | 2017-08-12T03:39:05.753088Z | 2017-08-12T03:59:05.753088Z |       20min        |

Basically,

Pivot table
Add studentid column
Calculate interval

What I have tried so far

I have successfuly piovoted the table

SELECT * FROM crosstab(
$$
select sessionid, activity, created from activitylog
where activity = 'logged in' OR activity = 'logged out'
ORDER  BY 1, 2
$$
)
AS sessioninfo (sessionid text, loggedin timestamp, loggedout   timestamp);

Sample Result

I tried to add studentid extra column using this query but it didn't give me desired result

SELECT * FROM crosstab(
$$
select sessionid, studentid, activity, created from
activitylog
where activity = 'logged in' OR activity = 'logged out'
ORDER  BY 1, 2
$$,
$$
VALUES (1), (0)
$$
)
AS sessioninfo (sessionid text, studentid INTEGER, 
loggedin timestamp, loggedout   timestamp);

But this places null in logged in and logged out timestamps.

Sample Result

To sum it up

What is the purpose of VALUES with crosstab()? To be honest I am not completely sure what purpose second parameter of crosstab() serves and why in almost all examples it is VALUES.
How do I add studentid to result?
How do I calculate interval between two columns that are result of crosstab()?

Best Answer

What you want is something like this,

SELECT *
FROM crosstab(
        $$
                SELECT sessionid, studentid, activity, created
                FROM activitylog
                WHERE activity IN ('logged in', 'logged out')
                ORDER BY 1, 2
        $$,
        $$
                VALUES ('logged in'), ('logged out')
        $$
) AS sessioninfo (
        sessionid text,
        studentid int,
        loggedin timestamp,
        loggedout timestamp
)
CROSS JOIN LATERAL age(loggedout, loggedin);

Output:

 sessionid | studentid |          loggedin          |         loggedout          |   age    
-----------+-----------+----------------------------+----------------------------+----------
 abc       |         1 | 2017-08-12 02:39:05.753088 | 2017-08-12 02:59:05.753088 | 00:20:00
 def       |         1 | 2017-08-12 03:09:05.753088 | 2017-08-12 03:29:05.753088 | 00:20:00
 ghi       |         2 | 2017-08-12 03:39:05.753088 | 2017-08-12 03:59:05.753088 | 00:20:00
(3 rows)

What is the purpose of VALUES with crosstab()? To be honest I am not completely sure what purpose second parameter of crosstab() serves and why in almost all examples it is VALUES.

It specifies the categories. It can also be a query but here there is no need for that and being explicit was even more terse. You're interested in two categories those are loggedin, and loggedout. Age being a function of those two categories is calculated with CROSS JOIN LATERAL.

VALUES itself is a SQL expression. We could have written SELECT * FROM VALUES () AS t(category) but that's just more work.

How do I add studentid to result?

Just like in my demonstration.

How do I calculate interval between two columns that are result of crosstab()?

We used age. However one caveat about this we should hit right now..

Your schema is specified as timestamp without time zone. That's certainly a mistake. When that crosses DST, if ever, you're going to be in a world of pain. It'll show the time elapsed in the rollover.

Proper crosstab query

First of all, the presented query would not work while you still have char(n) in your table definition - like we discussed under your previous question. You would need to convert to text or varchar first.

This works with your current table definition:

SELECT * FROM crosstab(
   'SELECT postcode_nospace_::text, ageband::text, count(ageband) AS ct
    FROM   adult_social_care.activities_in_localities_asc
    GROUP  BY 1, 2
    ORDER  BY 1'

   ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64')
           , ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER')$$
   )
AS t("postcode" text
   , "18-24" bigint, "25-34" bigint,"35-44" bigint, "45-54" bigint, "55-64" bigint
   , "65-74" bigint, "75-84" bigint, "85-94" bigint, "95 AND OVER" bigint);

I am also using bigint instead of numeric to save another unnecessary type conversion (count() returns bigint).

Detailed explanation:

PostgreSQL Crosstab Query

Better table definition

But better ALTER the data type in your table to text like @dezso suggested. Or, if you want to keep the length limit, add a CHECK constraint or use varchar(n). Never use char(n).

ALTER TABLE activities_in_localities_asc
   ALTER COLUMN ageband TYPE varchar(12)
 , ALTER COLUMN postcode_nospace_ TYPE varchar(8);

 ALTER TABLE gazetteers_and_addresses.unit_postcode
   ALTER COLUMN pc_area TYPE varchar(10);

Then you don't need to cast original column values any more:

SELECT * FROM crosstab(
   'SELECT postcode_nospace_, ageband, count(ageband)
    ...

Ideally, though, ageband should be an enum or (my preference) an ID referencing a lookup table, not plain, error-prone text ... key word: normalization.

Add total count

Building on your table, but with text or varchar() columns.

Wrap your original query into a CTE and call the the result twice in a UNION query - the 2nd call adds totals per postcode:

SELECT * FROM crosstab(
  $$WITH cte AS (
      SELECT postcode_nospace_, ageband, count(ageband) AS ct
      FROM   adult_social_care.activities_in_localities_asc  
      GROUP  BY 1, 2
      )
   TABLE  cte  -- original results
   UNION ALL   -- add total per postcode
   SELECT postcode_nospace_, 'total' AS ageband, sum(ct) AS ct
   FROM   cte
   GROUP  BY 1
   ORDER  BY 1$$  -- dollar-quotes to include single quotes easily

   ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64')
           , ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER'), ('total')$$
   )
AS t("postcode" text
   , "18-24" bigint, "25-34" bigint,"35-44" bigint, "45-54" bigint, "55-64" bigint
   , "65-74" bigint, "75-84" bigint, "85-94" bigint, "95 AND OVER" bigint, "total" bigint);

About the TABLE command:

Is there a shortcut for SELECT * FROM in psql?

Related CTE examples:

PostgreSQL Pivot with Multiple Columns – Using CROSSTAB

Yes it is possible, here is the solution:

WITH cte AS 
    ( SELECT  * 
      FROM CROSSTAB 
          ( 'SELECT  country_code, country_name, year, 
                     deflator || '',''|| source
             FROM deflator 
             ORDER BY 1;', 
             $$ VALUES ('2014'::TEXT), ('2015'::TEXT), ('2016'::TEXT) $$
          ) AS "ct" ( "country_code" SMALLINT, 
                      "country_name" TEXT , 
                      "2014" text, "2015" text, "2016" text
                    )
    )
SELECT country_code, country_name, 
       split_part("2014",',',1) AS "2014", 
       split_part("2014",',',2) AS "2014_source", 
       split_part("2015",',',1) AS "2015", 
       split_part("2015",',',2) AS "2015_source", 
       split_part("2016",',',1) AS "2016", 
       split_part("2016",',',2) AS "2016_source" 
FROM cte ;