PostgreSQL中使用Pivot/Crosstab实现数据转置需求

原始数据 #

Desc.    Value
-----    ------
Date     01/01/2022
Date     30/04/2021
Date     29/03/2022
Weeksgs  14w 3d
Weeksgs  15w 0d

期望结果 #

Date        Weeksgs
------      --------
01/01/2022  14w 3d
30/04/2021  15w 0d
29/03/2022

实现步骤 #

1. 启用tablefunc扩展 #

PostgreSQL的crosstab函数属于tablefunc扩展，需要先启用：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 编写Crosstab查询 #

核心思路是给同类型的记录分配行号，确保Date和Weeksgs按顺序一一对应，再通过crosstab转置：

针对已有表的查询

假设数据存储在your_table表中：

SELECT "Date", "Weeksgs"
FROM crosstab(
    -- 给每个Desc分组内的记录添加行号，保证对应关系
    $$
    SELECT 
        row_number() OVER (PARTITION BY "Desc." ORDER BY "Value") AS rn,
        "Desc.",
        "Value"
    FROM your_table
    $$,
    -- 指定要转置为列的类别
    $$VALUES ('Date'), ('Weeksgs')$$
) AS transposed_data(rn INT, "Date" TEXT, "Weeksgs" TEXT);

针对临时数据的查询

如果是临时数据，可用CTE构造数据源：

CREATE EXTENSION IF NOT EXISTS tablefunc;

WITH temp_data AS (
    VALUES
        ('Date', '01/01/2022'),
        ('Date', '30/04/2021'),
        ('Date', '29/03/2022'),
        ('Weeksgs', '14w 3d'),
        ('Weeksgs', '15w 0d')
)
SELECT "Date", "Weeksgs"
FROM crosstab(
    $$
    SELECT 
        row_number() OVER (PARTITION BY column1 ORDER BY column2) AS rn,
        column1 AS "Desc.",
        column2 AS "Value"
    FROM temp_data
    $$,
    $$VALUES ('Date'), ('Weeksgs')$$
) AS transposed_data(rn INT, "Date" TEXT, "Weeksgs" TEXT);

逻辑说明 #

• row_number() OVER (PARTITION BY "Desc." ORDER BY "Value")：给Date和Weeksgs各自的记录按顺序编号，让第N条Date对应第N条Weeksgs，无对应项的自动显示为空。
• crosstab函数第一个参数是带行号的源数据，第二个参数定义转置为列的类别名称，最终输出目标结构。

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