如何将查询COMPUTED_DATA表的两条SQL合并为单条Spark SQL语句

场景说明 #

• 业务表为COMPUTED_DATA，包含三个字段：data（业务数据）、cluster（集群标识）、last_execution（任务执行时间）
• 每两周执行一次定时任务，为指定cluster插入多条对应业务数据及本次执行的last_execution时间
• 需求：查询指定cluster最新一次执行任务生成的所有data数据

原有实现及痛点 #

原有实现分两次查询完成：

1. 查询指定cluster的最新执行时间

SELECT DISTINCT(last_execution) FROM COMPUTED_DATA 
WHERE cluster=1204 ORDER BY last_execution DESC LIMIT 1

2. 用查询得到的最新时间匹配对应业务数据

SELECT data FROM COMPUTED_DATA WHERE cluster=1204 AND last_execution={last_execution}

痛点：Spark集群环境下单次SQL查询开销较高，两次查询会放大性能损耗；且第二次查询返回行数不固定，无法通过LIMIT直接限制结果合并查询。

单查询实现方案 #

方案1：窗口函数实现（推荐） #

利用Spark SQL支持的窗口函数按执行时间倒序排名，直接过滤最新时间的所有数据，仅需一次表扫描：

SELECT data
FROM (
    SELECT
        data,
        RANK() OVER(PARTITION BY cluster ORDER BY last_execution DESC) AS execution_rank
    FROM COMPUTED_DATA
    WHERE cluster = 1204
) tmp
WHERE execution_rank = 1

方案2：子查询匹配最大时间 #

通过子查询获取指定cluster的最大执行时间，外层直接匹配该时间，实现单次查询：

SELECT data
FROM COMPUTED_DATA
WHERE cluster = 1204
AND last_execution = (
    SELECT MAX(last_execution)
    FROM COMPUTED_DATA
    WHERE cluster = 1204
)

方案说明 #

两种方案都可以实现单查询返回所有目标数据，窗口函数方案在数据量较大、分片较多的场景下，Spark的执行优化效率更高，优先推荐使用。

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