Postgres基于列值行转列：同年度季度数据合并为单行并补全Null

实现方案 #

方案1：条件聚合（通用标准SQL，无需额外扩展）

该方案兼容性最好，无需安装Postgres扩展，自动处理缺失季度为NULL：

-- 直接查询结果
SELECT
    stock,
    year,
    MAX(CASE WHEN statement = 'Q1 Earnings' THEN amount END) AS "Q1 Earnings",
    MAX(CASE WHEN statement = 'Q2 Earnings' THEN amount END) AS "Q2 Earnings",
    MAX(CASE WHEN statement = 'Q3 Earnings' THEN amount END) AS "Q3 Earnings",
    MAX(CASE WHEN statement = 'Q4 Earnings' THEN amount END) AS "Q4 Earnings"
FROM statement
GROUP BY stock, year
ORDER BY stock, year;

如果需要直接生成新表，在查询前加CREATE TABLE 新表名 AS即可：

-- 生成新表 quarterly_earnings
CREATE TABLE quarterly_earnings AS
SELECT
    stock,
    year,
    MAX(CASE WHEN statement = 'Q1 Earnings' THEN amount END) AS "Q1 Earnings",
    MAX(CASE WHEN statement = 'Q2 Earnings' THEN amount END) AS "Q2 Earnings",
    MAX(CASE WHEN statement = 'Q3 Earnings' THEN amount END) AS "Q3 Earnings",
    MAX(CASE WHEN statement = 'Q4 Earnings' THEN amount END) AS "Q4 Earnings"
FROM statement
GROUP BY stock, year
ORDER BY stock, year;

逻辑说明：CASE语句匹配到对应季度时返回对应金额，未匹配默认返回NULL，使用MAX聚合（同一stock+同一年+同一季度仅一条数据时，MAX/MIN/AVG结果无差异），按stock和year分组即可得到每行对应一只股票同一年度的四个季度数据。

方案2：crosstab交叉表（Postgres原生专用方案）

如果偏好Postgres专门的行转列语法，可以使用tablefunc模块提供的crosstab函数：

-- 先启用tablefunc扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 交叉表查询
SELECT * FROM crosstab(
    'SELECT stock, year, statement, amount FROM statement ORDER BY 1,2',
    $$VALUES ('Q1 Earnings'::text), ('Q2 Earnings'), ('Q3 Earnings'), ('Q4 Earnings')$$
) AS ct(stock text, year int, "Q1 Earnings" numeric, "Q2 Earnings" numeric, "Q3 Earnings" numeric, "Q4 Earnings" numeric);

逻辑说明：第二个参数枚举所有需要生成的季度列，源数据中不存在的季度会自动填充NULL，结果和条件聚合完全一致，同样可以加CREATE TABLE ... AS直接生成新表。

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