PostgreSQL 透视表查询实现方法详解

透视表(Pivot Table)是一种强大的数据分析工具，能够将行数据转换为列进行交叉分析。PostgreSQL 提供了多种实现透视表功能的方法，下面我将详细介绍这些技术。

1. 使用 crosstab 函数

PostgreSQL 的 tablefunc 扩展提供了 crosstab 函数，这是最直接的透视表实现方式。

安装扩展

CREATE EXTENSION IF NOT EXISTS tablefunc;

基本语法

SELECT * FROM crosstab(
  '源查询SQL',
  '分类值查询SQL'
) AS ct (行标识 数据类型, 列1 数据类型, 列2 数据类型, ...);

示例

假设有销售数据表 sales:

date       | product | amount
-----------+---------+-------
2023-01-01 | A       | 100
2023-01-01 | B       | 150
2023-01-02 | A       | 200
2023-01-02 | B       | 250

创建按日期透视产品销售额的报表：

SELECT * FROM crosstab(
  'SELECT date, product, amount FROM sales ORDER BY 1,2',
  'SELECT DISTINCT product FROM sales ORDER BY 1'
) AS ct (date date, product_a int, product_b int);

2. 使用 FILTER 子句

PostgreSQL 9.4+ 支持 FILTER 子句，可以更灵活地实现透视功能。

示例

SELECT 
  date,
  SUM(amount) FILTER (WHERE product = 'A') AS product_a,
  SUM(amount) FILTER (WHERE product = 'B') AS product_b
FROM sales
GROUP BY date
ORDER BY date;

3. 使用 CASE WHEN 表达式

这是最兼容的方法，适用于所有PostgreSQL版本。

示例

SELECT 
  date,
  SUM(CASE WHEN product = 'A' THEN amount ELSE 0 END) AS product_a,
  SUM(CASE WHEN product = 'B' THEN amount ELSE 0 END) AS product_b
FROM sales
GROUP BY date
ORDER BY date;

4. 动态透视表实现

当列值不确定或很多时，可以使用动态SQL生成透视查询。

示例存储过程

CREATE OR REPLACE FUNCTION dynamic_pivot(central_query text, headers_query text)
RETURNS SETOF record AS $$
DECLARE
  headers text;
  column_list text;
  final_query text;
  rec record;
BEGIN
  -- 获取列名
  EXECUTE headers_query INTO headers;

  -- 构建列定义
  SELECT string_agg(format('%s numeric', header), ', ')
  INTO column_list
  FROM (SELECT header FROM unnest(string_to_array(headers, ',')) AS h(header);

  -- 构建最终查询
  final_query := format('
    SELECT * FROM crosstab(
      %L,
      %L
    ) AS ct(row_name text, %s)', central_query, headers_query, column_list);

  -- 执行动态查询
  FOR rec IN EXECUTE final_query LOOP
    RETURN NEXT rec;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

5. 使用 jsonb 聚合

PostgreSQL 9.4+ 支持 JSON 功能，可以灵活地处理动态列。

示例

SELECT 
  date,
  jsonb_object_agg(product, amount) AS product_amounts
FROM sales
GROUP BY date
ORDER BY date;

性能考虑

1. 对于已知列的情况，crosstab 通常性能最佳
2. 对于动态列，FILTER 或 CASE WHEN 更灵活
3. 大数据集上，确保透视列上有适当的索引

实际应用建议

1. 对于报表系统，考虑预先物化透视结果
2. 复杂透视可以使用物化视图定期刷新
3. 结合PostgreSQL的窗口函数可以实现更复杂的分析

通过以上方法，您可以在PostgreSQL中实现各种复杂的透视表分析功能，满足业务数据分析需求。