CUST_ID | ST_ENTP_TYPE_CD | |
---|---|---|
101 | 01,02 | |
102 | 01 |
1. 首先把ST_ENTP_TYPE_CD字段炸开
SELECT CUST_ID, ST_ENTP_TYPE
FROM RIDM.XXXXXX
LATERAL VIEW explode (split(ST_ENTP_TYPE_CD, ',')) tmp AS ST_ENTP_TYPE
WHERE DT='20220516'
2. 把ST_ENTP_TYPE_CD字段替换
SELECT CUST_ID
, CASE
WHEN ST_ENTP_TYPE = '01' THEN '科技型企业'
WHEN ST_ENTP_TYPE = '02' THEN '科技型中小企业'
END AS ST_ENTP_TYPE
FROM RIDM.XXXXXX
LATERAL VIEW explode (split(ST_ENTP_TYPE_CD, ',')) tmp AS ST_ENTP_TYPE
WHERE DT='20220516'
3. 聚合数据
SELECT CUST_ID, CONCAT_WS(',', COLLECT_LIST(ST_ENTP_TYPE)) ST_ENTP_TYPE
FROM(
SELECT CUST_