如何在PostgreSQL中创建动态适配行列数的数据透视表(Pivot Table)

需求说明 #

基于给定的sf1表，将行式的日期-股票-收入数据转换为以股票为行、日期为列的透视表结构，且要求能自动适配表中新增的股票（ticker）和日期（calendardate）。

原表结构与数据 #

CREATE TABLE sf1
    ("calendardate" varchar(10), "ticker" varchar(4), "revenueusd" int);
INSERT INTO sf1
    ("calendardate", "ticker", "revenueusd")
VALUES
('12/2021', 'DIS', '218190'),
('12/2021', 'ADBE', '41100'),
('12/2021', 'AAPL', '1239450'),
('03/2022', 'AAPL', '972780'),
('03/2022', 'DIS', '192490'),
('03/2022', 'ADBE', '42620'),
('06/2022', 'ADBE', '43860'),
('06/2022', 'AAPL', '829590'),
('06/2022', 'DIS', '215040');

原查询结果 #

期望透视表结构 #

实现方案 #

1. 静态透视（适用于已知日期列的场景） #

如果提前知道所有需要转换的日期列，可以直接使用FILTER子句实现静态透视：

SELECT
  ticker AS "ticker/date",
  MAX(revenueusd) FILTER (WHERE calendardate = '12/2021') AS "12/2021",
  MAX(revenueusd) FILTER (WHERE calendardate = '03/2022') AS "03/2022",
  MAX(revenueusd) FILTER (WHERE calendardate = '06/2022') AS "06/2022"
FROM sf1
GROUP BY ticker
ORDER BY ticker DESC;

局限：无法自动适配新增的日期或股票，需要手动修改SQL语句。

2. 动态透视（自动适配任意日期与股票） #

PostgreSQL没有内置的动态PIVOT函数，需通过动态SQL生成或PL/pgSQL函数实现自动适配：

方法一：生成动态SQL语句

先查询所有唯一日期，拼接出对应的透视SQL，再执行生成的语句：

WITH dates AS (
  SELECT DISTINCT calendardate FROM sf1 ORDER BY calendardate
)
SELECT
  'SELECT ticker AS "ticker/date", ' ||
  string_agg(
    'MAX(revenueusd) FILTER (WHERE calendardate = ''' || calendardate || ''') AS "' || calendardate || '"',
    ', '
  ) ||
  ' FROM sf1 GROUP BY ticker ORDER BY ticker DESC;'
FROM dates;

执行上述查询后，会得到适配当前数据的静态透视SQL，复制该SQL执行即可得到目标透视表。

方法二：使用tablefunc扩展（需提前安装）

PostgreSQL的tablefunc扩展提供了crosstab函数，可用于透视表生成，结合动态SQL实现自动适配：

1. 启用扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 生成动态crosstab SQL：

WITH dates AS (
  SELECT DISTINCT calendardate FROM sf1 ORDER BY calendardate
)
SELECT
  'SELECT * FROM crosstab(
    ''SELECT ticker, calendardate, revenueusd FROM sf1 ORDER BY 1,2'',
    ''SELECT DISTINCT calendardate FROM sf1 ORDER BY 1''
  ) AS ct(ticker text, ' ||
  string_agg('"' || calendardate || '" int', ', ') ||
  ');'
FROM dates;

执行生成的SQL即可得到动态适配的透视表。

方法三：PL/pgSQL函数自动返回结果

创建函数自动生成并执行动态SQL，直接返回透视表结果：

CREATE OR REPLACE FUNCTION get_dynamic_pivot()
RETURNS SETOF record AS $$
DECLARE
  cols text;
BEGIN
  -- 获取所有日期列的拼接字符串
  SELECT string_agg(
    'MAX(revenueusd) FILTER (WHERE calendardate = ''' || calendardate || ''') AS "' || calendardate || '"',
    ', '
  ) INTO cols
  FROM (SELECT DISTINCT calendardate FROM sf1 ORDER BY calendardate) AS dates;

  -- 执行动态SQL
  RETURN QUERY EXECUTE format(
    'SELECT ticker AS "ticker/date", %s FROM sf1 GROUP BY ticker ORDER BY ticker DESC',
    cols
  );
END;
$$ LANGUAGE plpgsql;

调用函数时需指定返回结构：

SELECT * FROM get_dynamic_pivot() AS t("ticker/date" text, "12/2021" int, "03/2022" int, "06/2022" int);

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