HIVE/Spark SQL中Teradata SQL的QUALIFY搭配COUNT的等效替代方法

Hive和Spark SQL原生不支持Teradata的QUALIFY语法，你可以通过子查询或者公用表表达式（CTE）嵌套窗口函数的方式实现完全等价的效果。

原SQL逻辑说明 #

你提供的Teradata SQL作用是从db.source_table中筛选出所属PARENT_ID分组内无任何非空CHILD_ID的所有行：COUNT(CHILD_ID)只会统计非空的CHILD_ID，等于0即代表对应PARENT_ID下所有CHILD_ID都是NULL。

等效实现代码 #

写法1：子查询实现 #

SELECT PARENT_ID, CHILD_ID
FROM (
    SELECT 
        PARENT_ID,
        CHILD_ID,
        COUNT(CHILD_ID) OVER (PARTITION BY PARENT_ID) AS child_nonnull_cnt
    FROM db.source_table
) t
WHERE child_nonnull_cnt = 0

写法2：CTE实现（可读性更高） #

WITH source_with_cnt AS (
    SELECT 
        PARENT_ID,
        CHILD_ID,
        COUNT(CHILD_ID) OVER (PARTITION BY PARENT_ID) AS child_nonnull_cnt
    FROM db.source_table
)
SELECT PARENT_ID, CHILD_ID
FROM source_with_cnt
WHERE child_nonnull_cnt = 0

补充说明 #

• 两种实现的执行效率和原Teradata写法完全一致，窗口函数的计算逻辑没有任何变化，仅把过滤逻辑从QUALIFY移到了外层WHERE子句
• 该方案也通用适用于所有QUALIFY搭配窗口函数的场景：只需将QUALIFY后面对应的窗口函数先在子查询/CTE中计算生成别名，再在外层用WHERE过滤对应条件即可。

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