2020-04-18

Colocando linhas em colunas no PostgreSQL (Pivot, Crosstab)

Aqui vai um exemplo de como mover linhas para colunas e colunas para linhas (pivotar, pivot, transpor, transpose) tabelas no PostgreSQL. É um código SQL muito útil para fazer análise de dados e estatísticas.

Movendo valores em linhas para colunas

Imagine a seguinte tabela:

CREATE TABLE values(    "time" timestamp with time zone NOT NULL,    type text COLLATE pg_catalog."default" NOT NULL,    value numeric NOT NULL,    CONSTRAINT values_pkey PRIMARY KEY ("time", type));

Com os seguintes valores

INSERT INTO public.values VALUES ('2020-01-01 00:00:00-03', 'a', 1);INSERT INTO public.values VALUES ('2020-01-01 00:00:00-03', 'b', 2);INSERT INTO public.values VALUES ('2020-01-01 00:00:00-03', 'c', 3);INSERT INTO public.values VALUES ('2020-01-01 00:01:00-03', 'a', 2);INSERT INTO public.values VALUES ('2020-01-01 00:01:00-03', 'b', 2);INSERT INTO public.values VALUES ('2020-01-01 00:01:00-03', 'c', 23);INSERT INTO public.values VALUES ('2020-01-01 00:02:00-03', 'a', 21);INSERT INTO public.values VALUES ('2020-01-01 00:02:00-03', 'c', 12);

A tabela fica algo assim:

|                 time | type | value ||----------------------|------|-------|| 2020-01-01T03:00:00Z |    a |     1 || 2020-01-01T03:00:00Z |    b |     2 || 2020-01-01T03:00:00Z |    c |     3 || 2020-01-01T03:01:00Z |    a |     2 || 2020-01-01T03:01:00Z |    b |     2 || 2020-01-01T03:01:00Z |    c |    23 || 2020-01-01T03:02:00Z |    a |    21 || 2020-01-01T03:02:00Z |    c |    12 |

Usando a extensão nativa crosstab

-- Instale a extensão crosstab. Se já estiver instalada o PostgreSQL lançará uma exceptionCREATE EXTENSION crosstab;SELECT * FROM crosstab(  'SELECT time, type, value FROM values',  'SELECT DISTINCT type FROM values ORDER BY type ASC')AS  ct_result(time TIMESTAMPTZ, "a" NUMERIC, "b" NUMERIC, "c" NUMERIC);

Sem usar crosstab, usando CASE WHEN pré PostgreSQL 9.4

SELECT   time,  SUM(CASE WHEN type = 'a' THEN value END) as "a",  SUM(CASE WHEN type = 'b' THEN value END) as "b",  SUM(CASE WHEN type = 'c' THEN value END) as "c"FROM public.valuesGROUP BY timeORDER BY time;

Sem usar crosstab, usando FILTER pós PostgreSQL 9.4

SELECT   time,  SUM(value) FILTER(WHERE type = 'a') AS "a",  SUM(value) FILTER(WHERE type = 'b') AS "b",  SUM(value) FILTER(WHERE type = 'c') AS "c"FROM public.valuesGROUP BY timeORDER BY time;

Sem usar crosstab, usando FULL JOIN.

SELECT   "a".time,  "a".value AS "a",  "b".value AS "b",  "c".value AS "c"FROM public.values AS "a"FULL JOIN public.values "b" ON ("b".time = "a".time) AND "b".type = 'b'FULL JOIN public.values "c" ON ("c".time = "a".time OR "c".time = "b".time) AND "c".type = 'c'WHERE "a".type = 'a'

Movendo colunas para valores em linhas

Neste caso temos o oposto, queremos pegar colunas e colocá-las como valores em uma linha.

Há três formas de se fazer:

UNNEST

SELECT   UNNEST(ARRAY['x', 'y', 'z']) AS "subject",   UNNEST(ARRAY[x_value, y_value, z_value]) AS "value"FROM tableORDER BY "subject"

UNION

WITH desired_values AS (     SELECT       x_value,       y_value,       z_value,     FROM table)SELECT 'x' AS subject, x_value AS valueFROM desired_valuesUNION ALLSELECT 'y' AS subject, y_value AS valueFROM desired_valuesUNION ALLSELECT 'z' AS subject, z_value AS valueFROM desired_values;