Spark SQL中ATLEASTNNONNULLS表达式的等价SQL及用法问询

定义 #

ATLEASTNNONNULLS是Spark SQL中专门处理空值的表达式之一，核心逻辑为：当传入的一组表达式中，至少存在n个非NULL且非NaN的有效值时，返回true；否则返回false。

注意：

• 空字符串（如''）、数值0这类非NULL/非NaN的值会被视为有效值
• NaN（非数值）无论类型是否为数值，都会被判定为无效值

用法 #

语法格式 #

ATLEASTNNONNULLS(n, expr1, expr2, ..., exprk)

参数说明 #

• n: 正整数，指定需要满足的最小非空非NaN值数量
• expr1, expr2, ..., exprk: 任意数量的表达式，可以是列名、常量或计算式

等价SQL实现 #

通过对每个表达式逐一判断是否为非NULL且非NaN，将符合条件的计数求和，最后判断总和是否大于等于n，即可实现与ATLEASTNNONNULLS完全等价的逻辑：

通用等价模板：

(
  CASE WHEN expr1 IS NOT NULL AND NOT (expr1 IS NaN) THEN 1 ELSE 0 END
  + CASE WHEN expr2 IS NOT NULL AND NOT (expr2 IS NaN) THEN 1 ELSE 0 END
  + ...
  + CASE WHEN exprk IS NOT NULL AND NOT (exprk IS NaN) THEN 1 ELSE 0 END
) >= n

针对示例中的ATLEASTNNONNULLS(2, col1, col2, col3, col4, col5)，等价SQL为：

(
  CASE WHEN col1 IS NOT NULL AND NOT (col1 IS NaN) THEN 1 ELSE 0 END
  + CASE WHEN col2 IS NOT NULL AND NOT (col2 IS NaN) THEN 1 ELSE 0 END
  + CASE WHEN col3 IS NOT NULL AND NOT (col3 IS NaN) THEN 1 ELSE 0 END
  + CASE WHEN col4 IS NOT NULL AND NOT (col4 IS NaN) THEN 1 ELSE 0 END
  + CASE WHEN col5 IS NOT NULL AND NOT (col5 IS NaN) THEN 1 ELSE 0 END
) >= 2

示例验证 #

1. 创建示例表 #

CREATE OR REPLACE TABLE tab AS 
SELECT 1 AS id, 'a' AS col1, CURRENT_DATE() AS col2, 42 AS col3, 3.14::FLOAT AS col4, 'a'::VARIANT AS col5 
UNION SELECT 2, 'b', NULL, NULL, 'NaN'::FLOAT, NULL 
UNION SELECT 3, NULL, NULL, NULL, NULL, NULL 
UNION SELECT 4, '', NULL, 0, NULL, NULL;

2. 使用ATLEASTNNONNULLS查询 #

SELECT id, ATLEASTNNONNULLS(2, col1, col2, col3, col4, col5) AS result
FROM tab;

查询结果：

3. 使用等价SQL查询 #

执行等价SQL语句后，得到的结果与上述完全一致。

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