Vertcia高阶函数

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