行转列与列转行

1、行转列

create table t_0512(prd_code text,xh text);
insert into t_0512 values('GS0009','98|100|95');
insert into t_0512 values('GS0010','92|95');
insert into t_0512 values('GS0011','');
-- 行转列函数
-- 行转列函数会把NULL丢掉,注意提前处理
select prd_code,regexp_split_to_table(xh,E'\\|')  from t_0512;

2、列转行

create table t_0512(prd_code text,xh text);
insert into t_0512 values('1','a');
insert into t_0512 values('1','a');
insert into t_0512 values('1','b');
insert into t_0512 values('1','c');
insert into t_0512 values('2','d');
insert into t_0512 values('2','e');
insert into t_0512 values('3','');
--列转行 -> 等价hive的concat_ws(',',collect_list(xh)) 
select prd_code,string_agg(xh,'|') from t_0512 group by 1;
--列转行排序
select prd_code,string_agg(xh,'|' order by xh) from t_0512 group by 1;
--列转行去重 -> 等价hive的concat_ws(',',collect_set(xh)) -> 等价string_agg(distinct xh,'|')
select prd_code,array_to_string(array_agg(distinct xh),'|') from t_0512 group by 1;
-- 偶数string_agg()函数不支持distinct,仅支持order by 
-- 偶数array_agg()函数不支持同时使用distinct和order by.同时只能排序或去重

-- 同时支持去重和排序需要改写子查询
-- 原文
select T1.CUST_ID,
       T1.REGI_MOBILE_NO,
       T1.REAL_USED_MOBILE_NO,
       T1.IMEI,
       STRING_AGG(DISTINCT T1.SRC_SYS,',' ORDER BY T1.SRC_SYS ASC) as SRC_SYS,
       MIN(T1.FRST_USED_DATE) AS FIRST_USED_DATE,
       MAX(T1.LAST_USED_DATE) AS LAST_USED_DATE
FROM TEP MOBILE_EQ_INFO_DTL T1
GROUP BY T1.CUST_ID,T1.REGI_MOBILE_NO,T1.REAL_USED_MOBILE_NO,T1.IMEI;
-- 偶数语法
select T1.CUST_ID,
       T1.REGI_MOBILE_NO,
       T1.REAL_USED_MOBILE_NO,
       T1.IMEI,
       STRING_AGG(T1.SRC_SYS,',' ORDER BY T1.SRC_SYS ASC) as SRC_SYS,
       MIN(T1.FRST_USED_DATE) AS FIRST_USED_DATE,
       MAX(T1.LAST_USED_DATE) AS LAST_USED_DATE
FROM (
    select T1.CUST_ID,
           T1.REGI_MOBILE_NO,
           T1.REAL_USED_MOBILE_NO,
           T1.IMEI,
           T1.SRC_SYS,
           MIN(T1.FRST_USED_DATE) AS FIRST_USED_DATE,
           MAX(T1.LAST_USED_DATE) AS LAST_USED_DATE
     FROM TEP MOBILE_EQ_INFO_DTL T1
     GROUP BY T1.CUST_ID,T1.REGI_MOBILE_NO,T1.REAL_USED_MOBILE_NO,T1.IMEI,T1.SYS_SRC) T1
GROUP BY T1.CUST_ID,T1.REGI_MOBILE_NO,T1.REAL_USED_MOBILE_NO,T1.IMEI;