Greenplum中string_agg函数转换为Hive SQL支持语法的迁移问题咨询

核心函数替换逻辑 #

原Greenplum的拼接语法：

select string_agg(Display_String, ';' order by data_day )

全Hive版本兼容的等价写法：

select concat_ws(';', sort_array(collect_list(struct(data_day, Display_String))).Display_String)

实现原理：

• 把排序字段data_day和待拼接字段Display_String组装为结构体存入集合
• sort_array默认按结构体第一个字段（也就是排序键data_day）升序排列，如需降序可传入第二个参数false：sort_array(collect_list(struct(data_day, Display_String)), false)
• 提取排序后结构体中的待拼接字段，用concat_ws按指定分隔符拼接，效果和Greenplum的string_agg完全一致

全量改造后SQL #

select concat_ws(';', sort_array(collect_list(struct(data_day, Display_String))).Display_String)
from
(
select data_day, 
sum(revenue)/1000000.00 as revenue,
concat(data_day, ' ', trim(format_number(sum(revenue), 0))) as Display_String 
from(
select case when data_date = current_date then 'D:'
when data_date = date_sub(current_date, 1) then ' D-01:'
when data_date = date_sub(current_date, 2) then ' D-02:'
when data_date = date_sub(current_date, 7) then ' D-07:'
when data_date = date_sub(current_date, 28) then ' D-28:'
end data_day, revenue/1000000.00 revenue
from test.testable
where data_date between date_sub(current_date, 28) and current_date 
and hour <=(Select hour from ( select row_number() over(order by hour desc) iRowsID, hour from test.testable where data_date = current_date and  type = 'UVC')tbl1
where irowsid = 2) 
and type in( 'UVC')
order by 1 desc) a
group by 1)aa;

其他适配注意事项 #

• 数字格式化适配：Greenplum的to_char(sum(revenue),'9,999,999,999')替换为Hive的format_number(sum(revenue), 0)，自动生成带千分位的整数字符串
• 日期运算适配：Greenplum支持current_date - n的直接日期加减写法，Hive需要替换为date_sub(current_date, n)实现日期往前推n天的逻辑
• 若使用Hive 2.3.0及以上版本，可使用更简化的写法：concat_ws(';', collect_list(Display_String) order by data_day)，无需组装结构体
• Hive部分版本中current_date需写成current_date()，运行报错可调整该语法

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