PostgreSQL中如何获取行内值相对位置，判断商品购买先后

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

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
4         pants         4/19/2023
5         shirt         5/2/2023
5         belt          5/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           pants
5         hat           shirt           belt            

现在需要在行级别添加指示器shirt_before_pants，判断特定客户是否先购买shirt再购买pants，期望输出如下：

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

请问能否获取行内给定值的相对位置？

当然可以获取行内给定值的相对位置，以下是两种实用的解决方法：

方法1：基于已生成的订单序列表处理 #

通过字符串拼接结合POSITION函数判断两个商品的位置先后，同时处理商品不存在的情况：

SELECT 
    user_id,
    first_order,
    second_order,
    third_order,
    CASE 
        -- 仅当两个商品都存在时，判断shirt的位置是否在pants之前
        WHEN 'shirt' IN (first_order, second_order, third_order) 
             AND 'pants' IN (first_order, second_order, third_order)
             AND POSITION('shirt' IN CONCAT_WS(',', first_order, second_order, third_order)) 
                < POSITION('pants' IN CONCAT_WS(',', first_order, second_order, third_order))
        THEN true
        ELSE false
    END AS shirt_before_pants
FROM 你的订单序列表名;

方法2：直接从原orders表计算（更灵活） #

如果后续存在更多订单，预先生成的序列表列数可能不足，直接从原表按用户分组计算订单顺序更可靠：

WITH user_order_ranks AS (
    SELECT 
        user_id,
        product,
        -- 按用户分组，根据订单日期生成每个商品的购买顺序排名
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_rank
    FROM orders
)
SELECT 
    u1.user_id,
    -- 提取各顺序对应的商品
    MAX(CASE WHEN u1.order_rank = 1 THEN u1.product END) AS first_order,
    MAX(CASE WHEN u1.order_rank = 2 THEN u1.product END) AS second_order,
    MAX(CASE WHEN u1.order_rank = 3 THEN u1.product END) AS third_order,
    -- 判断shirt的购买排名是否早于pants
    CASE 
        WHEN MAX(CASE WHEN u1.product = 'shirt' THEN u1.order_rank END) 
             < MAX(CASE WHEN u1.product = 'pants' THEN u1.order_rank END)
        THEN true
        ELSE false
    END AS shirt_before_pants
FROM user_order_ranks u1
GROUP BY u1.user_id
ORDER BY u1.user_id;

这种方法无需依赖预先生成的序列表，能自动适配任意数量的订单，同时精准判断两个商品的购买顺序。

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