如何优化Spark SQL查询？弃用子查询与UNION改用CASE WHEN

针对你的需求，这里提供两种基于CASE WHEN的优化方案，既不需要嵌套子查询，也不用UNION，分别实现返回两行或一行结果的需求：

方案1：返回两行结果（对应flag_1、flag_2各一行） #

利用窗口函数先全局计算两个flag对应的最大日期，再通过CASE WHEN标记数据所属的flag类型，最后分组聚合得到结果：

spark.sql("""
WITH date_metrics AS (
  SELECT
    -- 全局计算flag_1=1的最大日期
    MAX(CASE WHEN flag_1 = 1 THEN date END) OVER () AS max_date_flag1,
    -- 全局计算flag_2=1的最大日期
    MAX(CASE WHEN flag_2 = 1 THEN date END) OVER () AS max_date_flag2,
    id,
    date
  FROM table.a
)
SELECT
  flag_type,
  COUNT(id) AS cnt_id,
  MAX(date) AS date
FROM (
  SELECT
    id,
    date,
    -- 标记当前数据属于哪个flag的统计范围
    CASE
      WHEN date = max_date_flag1 THEN 'flag_1'
      WHEN date = max_date_flag2 THEN 'flag_2'
      ELSE NULL
    END AS flag_type
  FROM date_metrics
) t
WHERE flag_type IS NOT NULL
GROUP BY flag_type
""").show(10, 0)

方案2：返回一行结果（两个flag的统计数据在同一行） #

通过条件聚合直接在一行中展示两个flag的统计结果，同样基于窗口函数获取全局最大日期：

spark.sql("""
WITH date_metrics AS (
  SELECT
    MAX(CASE WHEN flag_1 = 1 THEN date END) OVER () AS max_date_flag1,
    MAX(CASE WHEN flag_2 = 1 THEN date END) OVER () AS max_date_flag2,
    id,
    date
  FROM table.a
)
SELECT
  -- 统计flag_1最大日期对应的id数量
  COUNT(CASE WHEN date = max_date_flag1 THEN id END) AS cnt_id_flag1,
  max_date_flag1 AS date_flag1,
  -- 统计flag_2最大日期对应的id数量
  COUNT(CASE WHEN date = max_date_flag2 THEN id END) AS cnt_id_flag2,
  max_date_flag2 AS date_flag2
FROM date_metrics
""").show(10, 0)

说明 #

1. 原脚本中第二个查询使用table.b疑似笔误，这里统一按单表table.a处理；若确实涉及多表，只需调整date_metricsCTE中的表名和关联逻辑即可。
2. 窗口函数OVER ()的作用是在全表范围内计算最大日期，避免了嵌套子查询的使用。
3. 两种方案均通过CASE WHEN实现条件筛选和标记，完全替代了原脚本的UNION和嵌套子查询逻辑。

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