如何实现 PostgreSQL 行转列？

Question

在使用 PostgreSQL 进行数据处理时，常常会遇到需要将**行数据转换为列**（即“行转列”）的场景，例如对分组后的不同值进行横向展示。那么，请问：**如何使用 PostgreSQL 实现动态或静态的行转列操作？请结合实际案例说明 pivot 的实现方式及其适用场景。** 这是一个常见的报表生成、数据分析需求，考察了对聚合函数、条件表达式（如 `SUM(CASE WHEN...)` 或 `FILTER (WHERE...)`）以及 `crosstab()` 函数等技术点的理解与应用能力。

Answer 1

PostgreSQL 行转列（Pivot）技术详解与实践

在数据分析和报表生成的场景中，经常需要将原本按行存储的数据转换为以某些字段为列的形式进行展示。这种“行转列”操作在 PostgreSQL 中可以通过多种方式实现，主要包括使用条件聚合、FILTER 子句以及 crosstab 函数等。

• 静态 Pivot： 列名已知且固定时使用。
• 动态 Pivot： 列名不确定或变化频繁时使用。

1. 使用条件聚合实现静态行转列

这是最基础也是最常用的实现方式，适用于列名固定的场景。通过 CASE WHEN 或 FILTER (WHERE ...) 实现。

-- 示例数据结构
CREATE TABLE sales (
    product_id INT,
    region TEXT,
    amount NUMERIC
);

INSERT INTO sales VALUES
(1, 'North', 100),
(1, 'South', 200),
(2, 'North', 150),
(2, 'East', 300);

-- 静态行转列：每个 region 转换为一列
SELECT product_id,
       SUM(CASE WHEN region = 'North' THEN amount ELSE 0 END) AS north,
       SUM(CASE WHEN region = 'South' THEN amount ELSE 0 END) AS south,
       SUM(CASE WHEN region = 'East'  THEN amount ELSE 0 END) AS east
FROM sales
GROUP BY product_id;

product_id	north	south	east
1	100	200	0
2	150	0	300

2. 使用 FILTER 子句简化条件聚合

PostgreSQL 支持 FILTER (WHERE condition) 语法，可以更清晰地表达聚合逻辑。

-- 使用 FILTER 实现行转列
SELECT product_id,
       SUM(amount) FILTER (WHERE region = 'North') AS north,
       SUM(amount) FILTER (WHERE region = 'South') AS south,
       SUM(amount) FILTER (WHERE region = 'East') AS east
FROM sales
GROUP BY product_id;

结果与上表相同，但语义更清晰，推荐用于可读性要求较高的项目。

3. 使用 crosstab 函数实现动态行转列

当列的数量不固定或经常变化时，应使用扩展模块 tablefunc 提供的 crosstab() 函数。

-- 安装 tablefunc 扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 示例数据结构扩展
CREATE TABLE survey (
    rowid TEXT,
    attribute TEXT,
    value TEXT
);

INSERT INTO survey VALUES
('A', 'age', '30'),
('A', 'gender', 'M'),
('B', 'age', '25'),
('B', 'gender', 'F');

-- 动态行转列示例
SELECT * FROM crosstab(
  'SELECT rowid, attribute, value FROM survey ORDER BY 1,2'
) AS ct(rowid TEXT, age TEXT, gender TEXT);

rowid	age	gender
A	30	M
B	25	F

4. 动态 SQL 构建实现灵活的行转列

对于复杂业务场景，如列名未知或频繁变动，可通过编写函数动态构建 SQL 查询语句。

DO $$
DECLARE
    cols TEXT;
BEGIN
    SELECT string_agg(DISTINCT quote_ident(region), ', ')
    INTO cols
    FROM sales;

    EXECUTE format('
        SELECT product_id, %s
        FROM (
            SELECT product_id, region, amount
            FROM sales
        ) s
        PIVOT (
            SUM(amount)
            FOR region IN (%s)
        ) AS pvt
    ', cols, cols);
END $$;

注意：PostgreSQL 原生不支持 PIVOT 语法，此代码为伪代码，实际需结合字符串拼接与 EXECUTE 实现。

5. 不同方法对比与适用场景总结

graph TD A[行转列需求] --> B{列是否固定?} B -- 是 --> C[使用 CASE WHEN / FILTER] B -- 否 --> D[使用 crosstab / 动态SQL] C --> E[适合报表、统计分析] D --> F[适合元数据驱动、多维分析]

• 静态行转列： 推荐用于报表展示、维度固定、查询性能要求高的场景。
• 动态行转列： 适用于列数量不固定、用户自定义字段、OLAP 类型分析。