1、group by 后连接字符串
1)使用Strcat函数(vertica版本9.1以上)
select promo_id,public.strcat(deptname) over(partition by promo_id) deptname
from $mpp_schema_dev$.tmp_p_app_cond_bill_discount_day_02 a
2)使用 coalesce或nvl 后接 max
select ccust_row_id ,
max(decode(rk,1,a.target_group_4up5))||
coalesce(max(decode(rk,2,','||a.target_group_4up5)),'')||
coalesce(max(decode(rk,3,','||a.target_group_4up5)),'')||
coalesce(max(decode(rk,4,','||a.target_group_4up5)),'')||
coalesce(max(decode(rk,5,','||a.target_group_4up5)),'') target_group_4up5_con
from
(select ccust_row_id,target_group_4up5,row_number() over(partition by ccust_row_id order by target_group_4up5 ) rk
from test.test_name_field where target_name_4up5 = '1'
) a
group by ccust_row_id
2、字符串分隔函数
1)使用StringTokenizerDelim 函数(vertica版本9.1以上)
SELECT ORDER_NO,MODEL_NAME,MODEL,v_txtindex.StringTokenizerDelim(TAC,',')
over(partition by ORDER_NO,MODEL_NAME,MODEL order by ORDER_NO desc) FROM TEST.DIM_USER_DATA_10_MON_TERM
2)使用 SPLIT_PART 函数
r6_net_flow:格式
201911&0TT||201912&0TT||202001&0TT||202002&0TT||202003&0TT||202004&0TT
select prom_integ_id,
r6_net_flow,
SPLIT_PART(r6_net_flow,'||',2) --2代表要取的第几个
from test.tem_temp_field
取完后: 201912&0TT