Oracle含KEEP(DENSE_RANK LAST)语句转Spark SQL求助

原Oracle逻辑解析 #

原查询核心是为每个POSITION_ID获取最新状态，具体规则：

1. 优先按DATE取最新（最大）日期的记录
2. 同一日期下，按CODE优先级排序：FINISHED(3) > IN PROGRESS(2) > READY TO BEGIN(1) > 其他(10)
3. 若有多个符合上述规则的记录，取CODE~DATE~COMMENT拼接字符串的最大值

Spark SQL等价实现 #

WITH base_study_pos AS (
    -- 原查询中TABLE2与TABLE3的内连接，获取有效STUDY_ID和POSITION_ID组合
    SELECT t2.STUDY_ID, t2.POSITION_ID
    FROM TABLE2 t2
    JOIN TABLE3 t3 ON t2.STUDY_ID = t3.STUDY_ID
),
ranked_records AS (
    SELECT 
        b.STUDY_ID,
        b.POSITION_ID,
        -- 拼接STATUS字符串，对应原Oracle的CODE||'~'||DATE||'~'||COMMENT
        CONCAT(t1.CODE, '~', t1.DATE, '~', t1.COMMENT) AS STATUS,
        -- 模拟Oracle的KEEP DENSE_RANK LAST逻辑：按日期降序、CODE优先级降序排名
        DENSE_RANK() OVER (
            PARTITION BY b.POSITION_ID 
            ORDER BY 
                t1.DATE DESC,
                CASE 
                    WHEN t1.CODE IN ('READY TO BEGIN') THEN 1
                    WHEN t1.CODE IN ('IN PROGRESS') THEN 2
                    WHEN t1.CODE IN ('FINISHED') THEN 3
                    ELSE 10
                END DESC
        ) AS status_rank
    FROM base_study_pos b
    JOIN TABLE1 t1 ON b.POSITION_ID = t1.POSITION_ID
    WHERE t1.CODE IS NOT NULL
),
final_status AS (
    -- 对应原Oracle的MAX()逻辑：同rank下取拼接字符串的最大值
    SELECT 
        STUDY_ID,
        POSITION_ID,
        MAX(STATUS) AS STATUS
    FROM ranked_records
    WHERE status_rank = 1
    GROUP BY STUDY_ID, POSITION_ID
)
SELECT STUDY_ID, POSITION_ID, STATUS
FROM final_status;

关键对应说明 #

1. 窗口函数替代KEEP语法：Spark没有KEEP关键字，用DENSE_RANK()窗口函数按原规则排序，取排名为1的组
2. 排序方向调整：原Oracle用DENSE_RANK LAST取排序后的最后一组，等价于Spark中按DATE和CASE值降序排序后取第一组
3. MAX聚合补全逻辑：原Oracle子查询中的MAX()是为了处理同一排序组内的多条记录，Spark中通过对rank=1的组做MAX(STATUS)实现一致效果
4. 关联逻辑对齐：原查询的隐式内连接改为Spark标准的JOIN ... ON语法，逻辑完全一致

示例输出验证 #

执行上述代码后，将得到与原Oracle查询一致的结果：

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