Início / dba / Perguntas / 158181
Accepted
dw8547
Asked: 2016-12-15 11:51:46 +0800 CST

Pivot com 2+ colunas (usando CROSSTAB?)

  • 772

Eu tenho uma tabela deflatorque é definida como:

               Table "deflator"
    Column   |       Type        | Modifiers
-------------+-------------------+-----------
country_code | smallint          | not null
country_name | character varying | not null
year         | smallint          | not null
deflator     | numeric           |
source       | character varying |

A saída de exemplo desta tabela se parece com:

country_code | country_name  | year | deflator | source
-------------+---------------+------+----------+----------
           1 | country_1     | 2016 |       12 | source_1
           1 | country_1     | 2015 |       11 | source_2
           1 | country_1     | 2014 |       10 | source_2
           2 | country_2     | 2016 |       15 | source_1
           2 | country_2     | 2015 |       14 | source_1
           2 | country_2     | 2014 |       13 | source_2
           3 | country_3     | 2016 |       18 | source_1
           3 | country_3     | 2015 |       17 | source_2
           3 | country_3     | 2014 |       16 | source_3
(9 rows)

Eu uso a seguinte consulta para dinamizar a tabela se eu excluir a colunasource :

SELECT
    *
FROM CROSSTAB (
    'SELECT
        country_code
        , country_name
        , year
        , deflator
     FROM dimension.master_oecd_deflator
     ORDER BY 1;'
     , $$ VALUES ('2014'::TEXT), ('2015'::TEXT), ('2016'::TEXT) $$
) AS "ct" (
    "country_code" SMALLINT
    , "country_name" TEXT
    , "2014" NUMERIC
    , "2015" NUMERIC
    , "2016" NUMERIC
);

A consulta acima me dá:

country_code |   country_name    | 2016 | 2015 | 2014 |
-------------+-------------------+------+--- --+------+
           1 | country_1         | 12   | 11   | 10   |
           2 | country_2         | 15   | 14   | 13   |
           3 | country_3         | 18   | 17   | 16   |

Mas como a fonte do deflator varia de ano para ano para cada país, quero incluir a sourcecoluna no pivô para que meu resultado desejado fique assim:

country_code |   country_name    | 2016 | 2016_source | 2015 | 2015_source | 2014 | 2014_source
-------------+-------------------+------+-------------+------+-------------+------+------------
           1 | country_1         | 12   | source_1    | 11   | source_2    | 10   | source_2
           2 | country_2         | 15   | source_1    | 14   | source_1    | 13   | source_2
           3 | country_3         | 18   | source_1    | 17   | source_2    | 16   | source_3

Como faço para modificar esta consulta para me dar a saída desejada? (Com a fonte de cada ano listada ao lado do ano). Isso é mesmo possível?

postgresql postgresql-9.4

2 respostas

  1. Saddam tem uma solução inteligente, mas carrega alguns pontos fracos. Imagine uma fonte chamada 'Fresno, CA' (com vírgula na string). split_part()seria enganado pelo caractere separador na string...

    Para evitar esses problemas de caso de canto e preservar os tipos de dados originais, use um tipo de linha (bem definido!) em vez disso. Você pode criar um tipo composto permanentemente com CREATE TYPEou registrar um temporário com CREATE TEMP TABLE:

    CREATE TEMP TABLE defso (def numeric, so varchar);  -- once per session!

SELECT country_code
     , country_name
     , (d14).def AS deflator_2014  -- note the parentheses!
     , (d14).so  AS source_2014
     , (d15).def AS deflator_2015
     , (d15).so  AS source_2015
     , (d16).def AS deflator_2016
     , (d16).so  AS source_2016
FROM   crosstab (
    'SELECT country_code, country_name, year, (deflator, source)::defso
     FROM   deflator
     ORDER  BY 1'
  , 'SELECT generate_series(2014, 2016)::int2'
   ) AS ct (country_code int2
          , country_name text
          , d14 defso
          , d15 defso
          , d16 defso
   );

    Também removi o CTE desnecessário e simplifiquei um pouco.

    Ao lidar com apenas alguns anos, você pode dispensar crosstab()e usar autojunções:

    SELECT country_code, country_name
     , d14.deflator AS deflator_2014
     , d14.source   AS source_2014
     , d15.deflator AS deflator_2015
     , d15.source   AS source_2015
     , d16.deflator AS deflator_2016
     , d16.source   AS source_2016
FROM        (SELECT * FROM deflator WHERE year = int2 '2014') d14
FULL   JOIN (SELECT * FROM deflator WHERE year = int2 '2015') d15 USING (country_code, country_name)
FULL   JOIN (SELECT * FROM deflator WHERE year = int2 '2016') d16 USING (country_code, country_name)
ORDER  BY country_code;

    Usando FULL [OUTER] JOIN, pois não podemos assumir uma linha para cada combinação de (country_code, year). Dessa forma, obtemos o mesmo resultado da consulta cruzada acima.

    Incluir country_namena condição de junção parece redundante, mas se não o fizermos, teremos que usar COALESCE(d14.country_name, d15.country_name, d16.country_name) AS country_namepara nos defender contra linhas ausentes. Esse valor funcionalmente dependente não deveria estar na tabela para começar. Deve estar em uma countrytabela em um esquema devidamente normalizado.

    • 5
  2. Best Answer

    Sim é possível, aqui está a solução:

    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 ;
    • 3

relate perguntas