主页 / dba / 问题 / 158181
Accepted
dw8547
Asked: 2016-12-15 11:51:46 +0800 CST

使用 2+ 列旋转（使用 CROSSTAB？）

  • 772

我有一个deflator定义为的表：

               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 |

此表的示例输出如下所示：

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)

如果我排除列source，我使用以下查询来透视表：

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
);

上面的查询给了我：

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   |

但是由于每个国家的平减指数的来源每年都不同，我想source在枢轴中包含该列，以使我想要的输出看起来像：

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

如何修改此查询以提供所需的输出？（每年的来源都列在年份旁边）。这甚至可能吗？

postgresql postgresql-9.4

2 个回答

  1. 萨达姆有一个聪明的解决方案，但它也有一些弱点。想象一个名为“Fresno, CA”的源（字符串中有逗号）。split_part()会被字符串中的分隔符愚弄...

    为避免此类极端情况问题并保留原始数据类型，请改用（定义明确的！）行类型。您可以使用以下方式永久创建复合类型CREATE TYPE或注册临时类型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
   );

    我还删除了不必要的 CTE 并简化了一点。

    虽然只处理一整年的手，但您可以不crosstab()使用并使用自联接：

    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;

    使用FULL [OUTER] JOIN，因为我们不能假设 . 的每个组合都有一行(country_code, year)。这样我们得到与上面的交叉表查询相同的结果。

    在连接条件中包含country_name似乎是多余的，但如果我们不这样做，我们必须使用它COALESCE(d14.country_name, d15.country_name, d16.country_name) AS country_name来防御丢失的行。这个功能相关的值不应该在表中开始。应该在country正确规范化模式的表中。

    • 5
  2. Best Answer

    是的，这是可能的，这是解决方案：

    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

相关问题