如何在PostgreSQL中获取每个用户-商品类别分组的前两条订单日期记录

要实现这个需求，我们可以借助PostgreSQL的窗口函数给每个用户+商品分类组内的订单按日期排序，再通过条件聚合把前两条日期转成对应列。具体实现如下：

1. 先给分组内的订单标记行号 #

首先用ROW_NUMBER()窗口函数，针对每个user_id和product_class的组合，按order_date从小到大给订单编号：

SELECT 
  user_id,
  product_class,
  order_date,
  -- 按用户+分类分组，组内按订单日期升序生成行号
  ROW_NUMBER() OVER (PARTITION BY user_id, product_class ORDER BY order_date) AS rn
FROM orders

这个子查询会把每个组里最早的订单标记为rn=1，第二早的标记为rn=2，以此类推。

2. 聚合提取前两条订单日期 #

基于上面的编号结果，我们用条件聚合把rn=1和rn=2的日期分别提取为first_order和second_order：

SELECT
  user_id,
  product_class,
  -- 取组内行号为1的订单日期（最早订单）
  MAX(CASE WHEN rn = 1 THEN order_date END) AS first_order,
  -- 取组内行号为2的订单日期，无此记录则返回NULL
  MAX(CASE WHEN rn = 2 THEN order_date END) AS second_order
FROM (
  SELECT
    user_id,
    product_class,
    order_date,
    ROW_NUMBER() OVER (PARTITION BY user_id, product_class ORDER BY order_date) AS rn
  FROM orders
) ranked_orders
-- 按用户+分类分组聚合
GROUP BY user_id, product_class
-- 按用户和分类排序，让结果更规整
ORDER BY user_id, product_class;

结果验证 #

执行这段SQL后，会得到完全符合你期望的结果：

补充说明 #

• 这里用MAX()聚合是因为每个组内rn=1和rn=2最多各有一条记录，MAX()会直接返回对应的值，没有匹配记录时就保留NULL，用MIN()也能达到同样效果。
• 如果你的订单日期存在重复，且需要把相同日期的订单都算作“第一条”，可以把ROW_NUMBER()换成RANK()，不过根据你的需求场景，ROW_NUMBER()已经足够满足要求。

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