LATERAL VIEW EXPLODE与LATERAL VIEW OUTER EXPLODE的区别

最新推荐文章于 2024-11-11 08:41:40 发布

伴生伴熟

最新推荐文章于 2024-11-11 08:41:40 发布

阅读量617

点赞数

分类专栏： hive 文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/chinacmt/article/details/128676990

版权

hive 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

LATERAL VIEW EXPLODE与LATERAL VIEW OUTER EXPLODE的区别

LATERAL VIEW EXPLODE 当需要拆分的字段为null时，null的那行数据不会出现在结果
LATERAL VIEW OUTER EXPLODE 所有行都会显示在结果集中（包含要拆分的字段为null时）

LATERAL VIEW EXPLODE ：

SELECT  a.c1,
        a.c2,
        b.c22
FROM    (
            SELECT  '1' AS c1,
                    'a,b,c' AS c2
            UNION ALL
            SELECT  '2' AS c1,
                    NULL AS c2
            UNION ALL
            SELECT  '3' AS c1,
                    '' AS c2
        ) a
LATERAL VIEW
        EXPLODE(split(a.c2, ',')) b AS c22;
结果：
c1	c2	c22
1	a,b,c	a
1	a,b,c	b
1	a,b,c	c
3

LATERAL VIEW OUTER EXPLODE :

SELECT  a.c1,
        a.c2,
        b.c22
FROM    (
            SELECT  '1' AS c1,
                    'a,b,c' AS c2
            UNION ALL
            SELECT  '2' AS c1,
                    NULL AS c2
            UNION ALL
            SELECT  '3' AS c1,
                    '' AS c2
        ) a
LATERAL VIEW OUTER
        EXPLODE(split(a.c2, ',')) b AS c22;
结果：
c1	c2	c22
1	a,b,c	a
1	a,b,c	b
1	a,b,c	c
3		
2	NULL	NULL