Hive查询自定义排序需求：先按字母字符排序，再按数字字符排序

问题场景 #

执行如下Hive查询时：

SELECT *
FROM EPAY
ORDER BY EUNQ

得到的当前结果为字典序排序：

但需要实现先排含字母的字符串，再排纯数字字符串的规则，让带字母的5PM35795排在纯数字的50118664之前，期望结果如下：

解决方案 #

通过自定义排序键实现需求：在ORDER BY中先区分字符串是否含非数字字符，将含字母的组设为更高优先级，再按原字段排序。

方法1：正则判断纯数字 #

SELECT *
FROM EPAY
ORDER BY 
  -- 含非数字的行标记为1，纯数字标记为0，倒序后字母行优先
  CASE WHEN NOT regexp_like(EUNQ, '^[0-9]+$') THEN 1 ELSE 0 END DESC,
  EUNQ;

• regexp_like(EUNQ, '^[0-9]+$')匹配全数字字符串，反向判断即可筛选出含字母的行
• 先按标记倒序排列，确保字母行在前，再按原字段字典序保证组内顺序

方法2：字符替换判断非数字 #

若Hive正则支持有限，可使用translate函数：

SELECT *
FROM EPAY
ORDER BY 
  -- 移除所有数字后结果非空，说明含字母/其他非数字字符
  CASE WHEN translate(EUNQ, '0123456789', '') != '' THEN 1 ELSE 0 END DESC,
  EUNQ;

• translate(EUNQ, '0123456789', '')会删除字符串中所有数字，剩余内容非空则表示存在非数字字符
• 同样通过标记分组倒序，实现字母行优先排序

效果验证 #

执行上述任一查询后，会得到符合预期的排序结果：纯数字的14579536因首字符为1排在最前，随后是含字母的5PM35795，最后是纯数字的50118664。

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