如何在Spark SQL中实现列非空值左移，填补空值/空白

问题背景 #

现有数据表结构及数据如下：

需求：将每行的Score列中非空值（排除NULL和空白字符串）向左填充，前序位置为空则左移，右侧空位留空，期望输出如下：

此前尝试用COALESCE嵌套的方式，仅能实现部分左移，但右侧会保留旧值，无法满足需求。

解决方案 #

核心思路是通过数组转换+过滤+拆分实现：将所有Score列转为数组，过滤空值后再拆分回对应列，自动补空。

完整Spark SQL代码

SELECT 
  Team,
  Team_Category,
  -- 提取过滤后数组的第1-8位，索引从0开始，不足则返回NULL
  filtered_scores[0] AS Score1,
  filtered_scores[1] AS Score2,
  filtered_scores[2] AS Score3,
  filtered_scores[3] AS Score4,
  filtered_scores[4] AS Score5,
  filtered_scores[5] AS Score6,
  filtered_scores[6] AS Score7,
  filtered_scores[7] AS Score8
FROM (
  SELECT 
    Team,
    Team_Category,
    -- 把所有Score列转成数组，先将空白字符串转NULL，再过滤掉所有NULL值
    FILTER(
      ARRAY(
        NULLIF(Score1, ""), NULLIF(Score2, ""), NULLIF(Score3, ""), NULLIF(Score4, ""),
        NULLIF(Score5, ""), NULLIF(Score6, ""), NULLIF(Score7, ""), NULLIF(Score8, "")
      ), 
      score -> score IS NOT NULL
    ) AS filtered_scores
  FROM your_table_name -- 替换为你的实际表名
) temp;

代码解释

1. 数组转换：用ARRAY()将8个Score列打包成数组，NULLIF(ScoreN, "")把空白字符串统一转为NULL，避免空字符串和NULL的双重判断。
2. 过滤空值：FILTER()函数移除数组中的所有NULL值，得到按原始顺序排列的非空Score数组。
3. 拆分回列：通过数组索引提取对应位置的元素，数组长度不足时，超出索引的位置自动返回NULL，最终呈现为表格中的空值，完全符合需求。

可选优化：将NULL转为空白字符串

如果需要把结果中的NULL显示为空白字符串，可在外层用COALESCE()转换：

SELECT 
  Team,
  Team_Category,
  COALESCE(filtered_scores[0], "") AS Score1,
  COALESCE(filtered_scores[1], "") AS Score2,
  COALESCE(filtered_scores[2], "") AS Score3,
  COALESCE(filtered_scores[3], "") AS Score4,
  COALESCE(filtered_scores[4], "") AS Score5,
  COALESCE(filtered_scores[5], "") AS Score6,
  COALESCE(filtered_scores[6], "") AS Score7,
  COALESCE(filtered_scores[7], "") AS Score8
FROM (
  -- 内层子查询同前
) temp;

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