HQL使用lateral view 处理字段（工作实战）

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_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'
)
GROUP BY CUST_ID

最终结果：