如何在PySpark中获取当前状态之后的所有唯一order_status值

非去重的后续order_status列表 #

可以通过关联子查询结合字符串聚合函数，获取当前记录之后同order_id下的所有order_status值（保留重复）。以MySQL为例：

SELECT 
    o1.order_id,
    o1.order_status,
    o1.modify_time,
    (SELECT GROUP_CONCAT(o2.order_status ORDER BY o2.modify_time SEPARATOR ',')
     FROM orders o2
     WHERE o2.order_id = o1.order_id
       AND o2.modify_time > o1.modify_time) AS subsequent_statuses_non_distinct
FROM orders o1
ORDER BY o1.order_id, o1.modify_time;

核心逻辑是用关联子查询筛选出同订单ID且修改时间晚于当前记录的行，再用GROUP_CONCAT按时间顺序拼接所有order_status为列表。

去重的后续order_status列表 #

如果需要去除重复的状态值，只需在聚合函数中加入DISTINCT关键字：

SELECT 
    o1.order_id,
    o1.order_status,
    o1.modify_time,
    (SELECT GROUP_CONCAT(DISTINCT o2.order_status ORDER BY o2.modify_time SEPARATOR ',')
     FROM orders o2
     WHERE o2.order_id = o1.order_id
       AND o2.modify_time > o1.modify_time) AS subsequent_statuses_distinct
FROM orders o1
ORDER BY o1.order_id, o1.modify_time;

不同数据库适配调整 #

• PostgreSQL：替换GROUP_CONCAT为STRING_AGG，语法为STRING_AGG(o2.order_status, ',' ORDER BY o2.modify_time)，去重版本为STRING_AGG(DISTINCT o2.order_status, ',' ORDER BY o2.modify_time)
• SQL Server：同样使用STRING_AGG函数（需2017及以上版本），语法与PostgreSQL一致
• Oracle：用LISTAGG函数，去重需先通过子查询去重再聚合：

  SELECT 
      o1.order_id,
      o1.order_status,
      o1.modify_time,
      (SELECT LISTAGG(DISTINCT o2.order_status, ',' WITHIN GROUP (ORDER BY o2.modify_time))
       FROM (SELECT DISTINCT order_status, modify_time FROM orders WHERE order_id = o1.order_id AND modify_time > o1.modify_time) o2) AS subsequent_statuses_distinct
  FROM orders o1
  ORDER BY o1.order_id, o1.modify_time;
  

注意事项 #

• 若后续状态数量较多，需注意数据库对聚合字符串长度的限制，比如MySQL需调整group_concat_max_len参数
• 为order_id和modify_time建立联合索引，可大幅提升关联查询的性能

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