如何在PostgreSQL中实现pandas pivot_table的同等数据透视功能

针对三列数据转透视表的需求，PostgreSQL有两种常用实现方案，以下是具体实现步骤：

我们先准备测试用的源表（表名user_daily_value）和示例数据：

-- 建表
CREATE TABLE IF NOT EXISTS user_daily_value (
    Name VARCHAR(10),
    Day VARCHAR(10),
    Value INT
);

-- 插入示例数据
INSERT INTO user_daily_value VALUES
('John', 'Sunday', 6),
('John', 'Monday', 3),
('John', 'Tuesday', 2),
('Mary', 'Sunday', 6),
('Mary', 'Monday', 4),
('Mary', 'Tuesday', 7),
('Alex', 'Tuesday', 1);

方案1：CASE WHEN + 聚合函数实现（无需额外扩展，兼容性最好） #

该方案不需要开启任何扩展，适配所有SQL场景，逻辑可控：

SELECT
    Name AS names,
    MAX(CASE WHEN Day = 'Monday' THEN Value END) AS Monday,
    MAX(CASE WHEN Day = 'Sunday' THEN Value END) AS Sunday,
    MAX(CASE WHEN Day = 'Tuesday' THEN Value END) AS Tuesday
FROM user_daily_value
GROUP BY Name
ORDER BY Name;

说明：此处用MAX聚合仅为适配GROUP BY语法，由于每个Name+Day的组合唯一，不会出现计算偏差，无匹配值的场景会自动返回NULL，完全符合预期输出。

方案2：使用tablefunc扩展的crosstab函数实现（适合列数较多的透视场景） #

crosstab是PostgreSQL专门为行转列透视场景提供的内置函数，需要先开启tablefunc扩展：

-- 开启扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 执行透视查询
SELECT * FROM crosstab(
    'SELECT Name, Day, Value FROM user_daily_value ORDER BY 1',
    'SELECT DISTINCT Day FROM user_daily_value ORDER BY 1'
) AS pivot_result (
    names VARCHAR(10),
    Monday INT,
    Sunday INT,
    Tuesday INT
);

两种方案的输出结果完全一致：

names | monday | sunday | tuesday
-------+--------+--------+---------
 Alex  |  null  |  null  |    1
 John  |   3    |   6    |    2
 Mary  |   4    |   6    |    7

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