如何将PostgreSQL用户订单按时间排序后转置为宽表

我正在使用名为orders的PostgreSQL表，表结构及数据如下：

user_id   product   order_date
1     pants         7/1/2022
2     shirt         6/1/2022
1     socks         3/17/2023
3     pants         2/17/2023
4     shirt         3/13/2023
2     pants         8/15/2022
1     hat           4/15/2022
5     hat           3/14/2023
2     socks         12/3/2022
3     shirt         4/15/2023
4     socks         1/15/2023

我的目标是输出展示用户订单顺序的宽表，期望输出如下：

user_id   first_order   second_order    third_order
1     hat           pants           socks
2     shirt         pants           socks
3     pants         shirt           <null>
4     socks         shirt           <null>
5     hat           <null>          <null>

我已使用RANK() OVER (PARTITION BY user_id ORDER BY order_date) AS rank得到长格式的用户订单排序结果，但无法将其转换为上述目标宽表格式，请问该如何实现？

方法一：条件聚合（通用方案） #

通过CASE语句结合聚合函数，将长格式数据转为宽表，无需依赖PostgreSQL扩展：

SELECT
  user_id,
  MAX(CASE WHEN rank = 1 THEN product END) AS first_order,
  MAX(CASE WHEN rank = 2 THEN product END) AS second_order,
  MAX(CASE WHEN rank = 3 THEN product END) AS third_order
FROM (
  SELECT
    user_id,
    product,
    RANK() OVER (PARTITION BY user_id ORDER BY order_date) AS rank
  FROM orders
) ranked_orders
GROUP BY user_id
ORDER BY user_id;

• 子查询先为每个用户的订单按日期排序，生成对应的rank值；
• 外层查询通过CASE匹配rank值，将对应商品放到指定列，用MAX聚合是因为每个rank对单个用户仅对应一个商品，不会出现数据冲突；
• 若用户订单数量超过3个，可继续添加MAX(CASE WHEN rank = N THEN product END)格式的列来扩展。

方法二：PostgreSQL专属crosstab函数 #

使用PostgreSQL的tablefunc扩展提供的crosstab函数，更简洁地实现行列转换：

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

-- 生成目标宽表
SELECT * FROM crosstab(
  'SELECT user_id, rank, product
   FROM (
     SELECT user_id, product,
            RANK() OVER (PARTITION BY user_id ORDER BY order_date) AS rank
     FROM orders
   ) ranked_orders
   ORDER BY user_id, rank',
  'SELECT generate_series(1,3)' -- 指定要转换的rank范围
) AS ct(user_id INT, first_order TEXT, second_order TEXT, third_order TEXT)
ORDER BY user_id;

• crosstab第一个参数是源数据SQL，需返回分组列（user_id）、类别列（rank）、值列（product）；
• 第二个参数定义要转换的类别值集合（这里是1到3的rank）；
• 最后必须显式定义结果集的列名和数据类型。

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