hivesql 炸裂函数

1.LATERAL VIEW EXPLODE 炸裂

--LATERAL VIEW  EXPLODE 炸裂
SELECT  pageid
        ,adids
FROM    (
            SELECT  2222 AS pageid
                    ,SPLIT('12,313,1,32,55,,44',',') AS adid
        )  t1
LATERAL VIEW  EXPLODE(adid) t2 AS adids

结果：
pageid	adids
2222	12
2222	313
2222	1
2222	32
2222	55
2222	
2222	44

2.要炸裂的数组为空的情况

SELECT  pageid
        ,adids
FROM    (
            SELECT  2222 AS pageid
                    ,SPLIT('',',') AS adid
        )  t1
LATERAL VIEW  EXPLODE(adid) t2 AS adids
结果：
pageid	adids

3.保留要炸裂的数组为空的行数据

SELECT  pageid
        ,adids
FROM    (
            SELECT  2222 AS pageid
                    ,SPLIT('',',') AS adid
        )  t1
LATERAL VIEW OUTER EXPLODE(adid) t2 AS adids
结果：
pageid	adids
2222	\N

4.多列炸裂

SELECT  id
        ,activity_id
        ,floorid
        ,user_id
        ,order_no
        ,product_id
        ,gift_product_id
        ,gift_order_no
        ,address_status
        ,address_id
        ,createtime
        ,updatetime
FROM    (
            SELECT  id
                    ,activity_id
                    ,floorid
                    ,user_id
                    ,order_no
                    ,product_id
                    ,split(giftproductidlist,',') AS giftproductidlist
                    ,split(giftordernolist,',') AS giftordernolist
                    ,address_status
                    ,address_id
                    ,createtime
                    ,updatetime
            FROM    dwd_activity_buy_gift_order_ed
            --WHERE   id = 4
        ) t1
LATERAL VIEW OUTER EXPLODE(giftproductidlist) t2 AS gift_product_id
LATERAL VIEW OUTER EXPLODE(giftordernolist) t2 AS gift_order_no
;

5.多列炸裂的问题

如果一个需要炸裂的字段是商品id集合，另一个是商品类型集合,如:

用户id               商品id集合       商品类型集合
131116974          [1123,1143]       [4,7]
两个字段同时炸裂后产生的结果为：
131116974  1123  4
131116974  1123  7
131116974  1143  4
131116974  1143  7

而实际的业务关系是

商品1123 对应的商品类型是4
商品1143 对应的商品类型是7

可以在两个字段集合同时炸裂后再关联商品维表取正确的数据。
实际情况根据自己业务场景考虑