Hive中替换Oracle NULLS LAST函数实现指定规则的记录筛选

问题原因 #

Hive 不支持窗口函数 ORDER BY 后跟随 NULLS LAST 语法，我们可以通过新增空值标识排序项实现完全等价的逻辑。

修改后可直接运行的Hive代码 #

SELECT inn,
       publid,
       clusterid,
       issuedate,
       operdate
FROM   (
  SELECT inn,
         publid,
         clusterid,
         issuedate,
         operdate,
         DENSE_RANK() OVER (
           PARTITION BY clusterid
           ORDER     BY CASE WHEN COALESCE(issuedate, operdate) IS NULL THEN 1 ELSE 0 END ASC,
                        COALESCE( issuedate, operdate ) DESC,
                        cnt DESC
         ) AS rnk
  FROM   (
    SELECT t.*,
           COUNT(inn) OVER (PARTITION BY publid) cnt
    FROM   table_name t
    WHERE  clusterid is not null
  )
)
WHERE  rnk = 1;

调整说明 #

仅修改了DENSE_RANK窗口函数的排序规则：

1. 新增第一排序项CASE WHEN COALESCE(issuedate, operdate) IS NULL THEN 1 ELSE 0 END ASC：非空值会标记为0排在前面，空值标记为1排在后面，和原NULLS LAST效果完全一致
2. 后续的日期降序、关联inn数量cnt降序逻辑和原Oracle代码完全相同，完全适配你给出的四条筛选规则，可跑出预期结果。

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