如何编写SQL获取客户当前所处的最高业务步骤及对应状态

现有两张业务表结构如下：

• Customer表：存储客户基础信息，包含customer_id、customer_name字段
• Action表：存储客户步骤操作记录，包含action_id、customer_id、type、status字段

客户步骤流转遵循固定顺序：step 1 pending → step 1 email sent → step 1 completed → step 2 pending → 后续步骤以此类推，但Action表的action_id生成顺序与实际步骤流转顺序无关，原有通过取max(action_id)的查询无法得到正确结果。

原有错误SQL #

select customer_id, customer_name, Action.type, Action.status
from Customer 
left join Action on Customer.customer_id = Action.customer_id 
where Action.action_id in 
(Select max(action_id) 
from Action 
where type in ('step 1','step 2', 'step 3', 'step 4') 
and status in ('pending','email_sent','completed'))

需求 #

编写正确的SQL，返回每个客户当前所处的最高阶type及对应status，预期输出示例：

• customer_id为123的客户Alex对应step 4、completed
• customer_id为456的客户Baldwin对应step 2、email sent

以下SQL兼容MySQL 8.0+/PostgreSQL/Oracle等所有支持窗口函数的主流数据库：

WITH action_ranked AS (
    SELECT 
        a.customer_id,
        a.type,
        a.status,
        -- 按步骤优先级、同步骤下状态优先级排序
        ROW_NUMBER() OVER (
            PARTITION BY a.customer_id 
            ORDER BY 
                -- 提取步骤数字倒序，步骤越高权重越大
                CAST(SUBSTRING_INDEX(a.type, ' ', -1) AS UNSIGNED) DESC,
                -- 同步骤下状态越靠后权重越大
                CASE a.status 
                    WHEN 'completed' THEN 3
                    WHEN 'email_sent' THEN 2
                    WHEN 'pending' THEN 1
                END DESC
        ) AS rn
    FROM Action a
    WHERE a.type IN ('step 1','step 2', 'step 3', 'step 4')
      AND a.status IN ('pending','email_sent','completed')
)
SELECT 
    c.customer_id,
    c.customer_name,
    ar.type,
    ar.status
FROM Customer c
LEFT JOIN action_ranked ar 
    ON c.customer_id = ar.customer_id 
    AND ar.rn = 1
-- 如需过滤无任何操作记录的客户，可取消下方注释
-- WHERE ar.customer_id IS NOT NULL

逻辑说明 #

1. 用ROW_NUMBER()窗口函数按客户分组，对每个客户的所有有效操作记录排序：优先按步骤数字倒序，同步骤下按状态流转顺序倒序，确保最新最高阶的记录排在第一位
2. 取每个客户排序后的第一条记录（rn=1），即为该客户当前所处的最高阶步骤和对应状态
3. 左关联Customer表保证无操作记录的客户也能正常返回，对应步骤、状态字段为NULL，可按需加过滤条件调整

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