Hive对分组结果进行排序拼接

背景

需要对分组结果进行排序后然后拼接，例如：品类分类路径树，举例：

WITH users AS (
        SELECT 1 AS UID,
               3 AS LEVEL,
               'C' AS alpha
         UNION SELECT 1 AS UID,
               2 AS LEVEL,
               'B' AS alpha
         UNION SELECT 1 AS UID,
               4 AS LEVEL,
               'D' AS alpha
         UNION SELECT 1 AS UID,
               1 AS LEVEL,
               'A' AS alpha
         UNION SELECT 1 AS UID,
               5 AS LEVEL,
               'E' AS alpha
         UNION SELECT 1 AS UID,
               9 AS LEVEL,
               'J' AS alpha
         UNION SELECT 1 AS UID,
               8 AS LEVEL,
               'I' AS alpha
         UNION SELECT 1 AS UID,
               7 AS LEVEL,
               'H' AS alpha
         UNION SELECT 1 AS UID,
               6 AS LEVEL,
               'G' AS alpha
       ) SELECT UID,
       CONCAT_WS('-', C2),
       REGEXP_REPLACE(CONCAT_WS('-', C2), ':[0-9]{3}:', '')
  FROM (
        SELECT UID,
               SORT_ARRAY(COLLECT_SET(CONCAT(':',LPAD(CAST(LEVEL AS STRING), 3, '0'), ':',alpha))) AS C2
          FROM users
         GROUP BY UID
       )T1

执行结果

语法解析

CONCAT(':',LPAD(CAST(LEVEL AS STRING), 3, '0'), ':',alpha)
-- 1、在所在层级前补充0，这样做的目的是为了方便字符串比较排序
-- 2、对层级进行拼接后与目标字段进行拼接

SORT_ARRAY(COLLECT_SET(CONCAT(':',LPAD(CAST(LEVEL AS STRING), 3, '0'), ':',alpha)))
-- 对拼接结果进行排序
-- 如果需要降序,可预先排序生成一个新的level字段

REGEXP_REPLACE(CONCAT_WS('-', C2), ':[0-9]{3}:', '')
-- 通过短横线进行拼接
-- 最后替代因便于排序插入的标记内容