我有以下数据:

ID 类别值1 NHB 700905.72431 HBW 164216.13111 HBO 700905.72432 NHB 146023.37922 HBW 89543.29722 HBO 82152.0723 NHB 1409818.3283 HBW 220430.79223 HBO 323512.93914 NHB 48711.38144 HBW 163385.15754 HBO 363352.3441

我想重组为:

ID HBO HBW NHB1 700905.7243 164216.1311 700905.72432 82152.072 89543.2972 146023.37923 323512.9391 220430.7922 1409818.3284 363352.3441 163385.1575 48711.3814

请注意，HBW、HBO 和 NHB 列中的值是总计(总和).

这是我用来创建输出的脚本:

-- CREATE EXTENSION tablefunc;选择 *来自 CROSSTAB('选择_tlfd.id，_tlfd."类",_tlfd."值"FROM public._tlfdWHERE _tlfd."class" = ''HBW'' 或 _tlfd."class" = ''HBO'' 或 _tlfd."class" = ''NHB''按 1,2' 订购)作为(类" int，HBW"文本，HBO"文本，NHB"文本，--目的" varchar,值"双精度);

当我运行脚本时出现此错误:

错误:return 和 sql 元组描述不兼容.

我不确定这意味着什么以及如何更正错误.谁能告诉我:

我在脚本中做错了什么?我的脚本会产生所需的输出吗?

解决方案

这适用于 Postgres 9.3:

选择 *从交叉表($$SELECT id，类，值"来自 _tlfdWHERE 类 = ANY ('{HBW, HBO, NHB}')按 1,2$$ 订购) 作为 t (class int, -- 需要一个表别名！"HBW" float8, -- 结果列是双精度的！HBO"浮动8，NHB"浮动8-- "value" 双精度 -- 结果中不存在列！);

产生所需的输出.

基本变化

表格别名(粗体t)删除的剩余列value"数据列的正确数据类型(双精度又名float8)

剩下的就是品味和风格的问题了.我不会使用 value 作为列名，因为它是一个 SQL 中的保留字.

crosstab() 查询的基础知识在这里:

PostgreSQL 交叉表查询

I have the following data:

ID  CLASS   VALUE
1   NHB    700905.7243
1   HBW    164216.1311
1   HBO    700905.7243
2   NHB    146023.3792
2   HBW    89543.2972
2   HBO    82152.072
3   NHB    1409818.328
3   HBW    220430.7922
3   HBO    323512.9391
4   NHB    48711.3814
4   HBW    163385.1575
4   HBO    363352.3441

That I want to reorganize as:

ID     HBO             HBW              NHB
1   700905.7243    164216.1311      700905.7243
2   82152.072      89543.2972       146023.3792
3   323512.9391    220430.7922      1409818.328
4   363352.3441    163385.1575      48711.3814

Please note that the values in columns HBW, HBO and NHB are totals (sum).

Here is the script I am using to create the output:

-- CREATE EXTENSION tablefunc;

SELECT *
FROM  CROSSTAB
(
    'SELECT _tlfd.id,   
    _tlfd."class",
    _tlfd."value"
    FROM public._tlfd
    WHERE _tlfd."class" = ''HBW'' or _tlfd."class" = ''HBO'' or _tlfd."class" = ''NHB'' 
    ORDER BY 1,2'
) 
    AS
(
    "class" int, 
    "HBW" text,
    "HBO" text,
    "NHB" text,
    --"Purpose" varchar, 
    "value" double precision
);

When I run the script I get this error:

ERROR:  return and sql tuple descriptions are incompatible. 

I am not sure what this means and how to correct the error. Can someone please let me know:

What am I doing wrong in the script? Will my script produce the desired output?

解决方案

This works for me on Postgres 9.3:

SELECT *
FROM   crosstab (
 $$SELECT id, class, "value"
   FROM   _tlfd
   WHERE  class = ANY ('{HBW, HBO, NHB}')
   ORDER  BY 1,2$$
   ) AS t (
        class int,                   -- needs a table alias!
        "HBW" float8,                -- resulting columns are double precision!
        "HBO" float8,
        "NHB" float8
        -- "value" double precision  -- column does not exist in result!
    );

Produces the desired output.

Essential changes

the table alias (bold t) the removed surplus column "value" the correct data type for your data columns (double precision a.k.a. float8)

The rest is a matter of taste and style. I wouldn't use value as column name though, since it is a reserved word in SQL.

Basics for crosstab() queries here:

PostgreSQL Crosstab Query