Hive使用UNION子句查询时如何实现id列自动递增

问题原因 #

你当前写法的问题是，row_number() 窗口函数是在每个union的独立子查询中分别计算的，每个子查询仅返回1条数据，所以每个分支计算出的id都是1，合并后自然出现重复。

解决方案 #

先把所有状态数据合并，再对合并后的整体结果集计算自增id即可，正确的SQL如下：

select 
  row_number() over (order by StatusCode) as id,
  StatusCode,
  StatusLibelle
from (
  select "-1" AS StatusCode,"Not informed" AS StatusLibelle
  union all
  select "0" AS StatusCode,"Off" AS StatusLibelle
  union all
  select "1" AS StatusCode,"On" AS StatusLibelle
) t

补充说明：这里用union all代替union性能更好，因为三个分支数据本身没有重复，不需要union的去重逻辑。窗口函数的order by可以根据你需要的排序规则调整，示例中按StatusCode排序正好匹配你期望的输出顺序。

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