基于状态排序获取企业当前与历史状态的SQL实现求助

需求说明 #

需要从源数据表中查询每个企业的最新状态（latest）和上一状态（prev），已完成状态优先级排序的基础SQL，需补充逻辑生成目标列。

当前尝试的SQL #

select 
    company_name,
    status as latest,
    status as status_update,
    case when status = 'todo' then 1
        when status = 'inprogress' then 2
        when status = 'completed' then 3
        else 0 end as no
from TABLE

改进后的SQL方案 #

基于状态优先级排序（无时间字段时） #

利用窗口函数标记最新状态并获取上一状态：

WITH ranked_status AS (
    SELECT 
        company_name,
        status,
        -- 定义状态优先级：completed > inprogress > todo
        CASE 
            WHEN status = 'todo' THEN 1
            WHEN status = 'inprogress' THEN 2
            WHEN status = 'completed' THEN 3
            ELSE 0 
        END AS status_rank,
        -- 按企业分组，优先级倒序排名，1为最新状态
        ROW_NUMBER() OVER (PARTITION BY company_name ORDER BY status_rank DESC) AS rn,
        -- 获取同一企业的上一状态
        LAG(status) OVER (PARTITION BY company_name ORDER BY status_rank DESC) AS prev_status
    FROM TABLE
)
SELECT 
    company_name,
    status AS latest,
    prev_status AS prev
FROM ranked_status
WHERE rn = 1;

基于更新时间排序（推荐，若有时间字段） #

如果表中存在状态更新时间字段（如update_time），按时间倒序排序更贴合“最新”的业务逻辑：

WITH ranked_status AS (
    SELECT 
        company_name,
        status,
        -- 按更新时间倒序排名，1为最新状态
        ROW_NUMBER() OVER (PARTITION BY company_name ORDER BY update_time DESC) AS rn,
        -- 获取同一企业的上一状态
        LAG(status) OVER (PARTITION BY company_name ORDER BY update_time DESC) AS prev_status
    FROM TABLE
)
SELECT 
    company_name,
    status AS latest,
    prev_status AS prev
FROM ranked_status
WHERE rn = 1;

说明 #

• PARTITION BY company_name确保仅在同一企业内进行状态排序和取值
• ROW_NUMBER()用于筛选出每个企业的最新状态记录
• LAG()函数提取同一分组内排序后的前一条记录状态，即上一状态；若企业只有一条状态记录，prev列会返回NULL

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