Skip to content

Transposing datasets

Jump to bottom
Peter edited this page Mar 4, 2018 · 10 revisions

A didactic reference for crosstab operations:

 CREATE TEMP TABLE t4 (
  timeof   text -- instead timestamp to simplify examples
 ,entity    character
 ,status    integer
 ,ct        integer);

 INSERT INTO t4 VALUES 
  ('2012-01-01', 'a', 1, 1)
 ,('2012-01-01', 'a', 0, 2)
 ,('2012-01-02', 'b', 1, 3)
 ,('2012-01-02', 'c', 0, 4);
timeof entity status ct
2012-01-01 a 1 1
2012-01-01 a 0 2
2012-01-02 b 1 3
2012-01-02 c 0 4

Example 0 - As spreadsheet

Transposing at spreadsheet (non-SQL),

trp1 trp2 trp3 trp4 trp5
timeof 2012-01-01 2012-01-01 2012-01-02 2012-01-02
entity a a b c
status 1 0 1 0
ct 1 2 3

Example 1 - Grouping by restriction

Section Attribute 1 0
2012-01-01 a 1 2
2012-01-02 b 3 4 
 SELECT * FROM crosstab(
     'SELECT timeof, entity, status, ct
      FROM   t4
      ORDER  BY 1,2,3'
     ,$$VALUES (1::text), (0::text)$$)
 AS ct ("Section" timestamp, "Attribute" character, "1" int, "0" int);

Example 2 - Classification

Attribute Section status_1 status_0
a 2012-01-01 1 2
b 2012-01-02 3
c 2012-01-02 4 
SELECT * FROM crosstab(
      'SELECT entity, timeof, status, ct
       FROM   t4
       ORDER  BY 1'
      ,'VALUES (1), (0)')
 AS ct (
    "Attribute" character
   ,"Section" timestamp
   ,"status_1" int
   ,"status_0" int)
;

Building a tabular JSON

The aim is to use JSONb array (1 row with all dataset)... But is interesting to see also the single-JSON-array and JSON-objects convertion:

CREATE TABLE jobjs as  -- setof json objects
  SELECT to_jsonb(t4) as robj FROM  t4;
CREATE TABLE jarrs as  -- setof json arrays
  SELECT json_build_array(timeof, entity, status, ct) as r FROM  t4;

We can the aimed obtaim the full array by
SELECT to_jsonb(array_agg(r)) as j_array FROM jarrs, that results in

[
 ["2012-01-01", "a", 1, 1], 
 ["2012-01-01", "a", 0, 2], 
 ["2012-01-02", "b", 1, 3], 
 ["2012-01-02", "c", 0, 4]
]

By other hand, jobjs is the simplest and not need previous knowledge about column names (imagine 100 or more columns), so another strategy is to build a function that build the JSONb array from any table.

CREATE or replace FUNCTION jobjs2arrayvals(jsonb) RETURNS jsonb AS $f$
  SELECT to_jsonb(array_agg(value)) FROM jsonb_each($1) t
$f$ LANGUAGE SQL IMMUTABLE;

SELECT  to_jsonb(array_agg(  jobjs2arrayvals(robj)  )) FROM jobjs;

... Remembering that, to analyse... Only Exploding in key-value pairs

SELECT  key,value
FROM jobjs, LATERAL jsonb_each(robj) t(key,value);

The JSON transpose

See ref1 and ref2 for usual array algorithm for transpose.

Example-0

Example-1

Example-2

Some other links

Clone this wiki locally