使用PostgreSQL的CROSSTAB创建透视表报错求助

错误原因 #

你遇到的语法错误是因为输出列名2020、2021以数字开头，PostgreSQL中这类标识符必须用双引号包裹，否则会被解析为语法错误。另外你的原始查询还存在两个问题：

• 子查询的列顺序不符合CROSSTAB要求：CROSSTAB需要输入格式为(row_name, category, value)，你写的rank, country, year顺序颠倒，应该将作为列类别的year放在第二位，作为值的country放在第三位
• 未过滤不需要的2022年数据，会导致结果不符合预期

修正后的代码 #

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM CROSSTAB (
  'SELECT rank, year, country 
   FROM country_yearly 
   WHERE year IN (2020, 2021)
   ORDER BY rank, year'
) AS ct(rank int, "2020" varchar, "2021" varchar);

关键说明 #

• 双引号包裹列名：用"2020"、"2021"这种带双引号的形式定义以数字开头的输出列，符合PostgreSQL标识符规则
• 调整列顺序：子查询改为rank, year, country，匹配CROSSTAB的(行标识, 列类别, 对应值)结构，确保年份能正确转换为列
• 过滤与排序：通过WHERE子句筛选目标年份，同时ORDER BY rank, year保证输入数据有序——CROSSTAB依赖有序输入来正确填充各列的值

执行上述代码后，就能得到你需要的宽表结构。

内容的提问来源于stack exchange，提问作者RoboData