Spark SQL Lateral View Outer 与 explode_outer：保留NULL行的列转行

一、基础数据

现有骑手id,订单id列表，订单配送距离列表，配送费列表，其中订单id、配送距离、配送费一一对应。

+-----------+---------------------------+----------------------------+-----------------------------+
| rider_id  |        order_list         |       distance_list        |        payment_list         |
+-----------+---------------------------+----------------------------+-----------------------------+
| r001      | 0001,0005,0008            | 8.05,2.32,4.35             | 7.50,5.00,15.00             |
| r002      | 0002,0004,0006,0009,0010  | 3.01,10.98,0.78,5.05,6.05  | 13.00,15.00,5.00,9.50,7.00  |
| r003      | 0003,0007                 | 4.12,8.11                  | 3.50,8.00                   |
| r004      | NULL                      | NULL                       | NULL                        |
+-----------+---------------------------+----------------------------+-----------------------------+

二、列转行

将骑手及其订单转换成订单粒度，每单一行记录。需要把骑手r004数据进行展示

期望结果

+-----------+-----------+
| rider_id  | order_id  |
+-----------+-----------+
| r001      | 0001      |
| r001      | 0005      |
| r001      | 0008      |
| r002      | 0002      |
| r002      | 0004      |
| r002      | 0006      |
| r002      | 0009      |
| r002      | 0010      |
| r003      | 0003      |
| r003      | 0007      |
| r004      | NULL      |
+-----------+-----------+

1、lateral view outer explode方案

先将字符串通过split函数转换成array，然后使用explode炸开，即可得到最终结果。这里我们不在使用lateral view 而是使用lateral view outer 完成

select rider_id, order_id
from t2_delivery_orders
         lateral view outer explode(split(order_list, ',')) t2 as order_id

执行结果

+-----------+-----------+
| rider_id  | order_id  |
+-----------+-----------+
| r001      | 0001      |
| r001      | 0005      |
| r001      | 0008      |
| r002      | 0002      |
| r002      | 0004      |
| r002      | 0006      |
| r002      | 0009      |
| r002      | 0010      |
| r003      | 0003      |
| r003      | 0007      |
| r004      | NULL      |
+-----------+-----------+

2、lateral view explode_outer解决方法

2.1 explode和explode_outer函数比较

我们先看下在order_list列使用explode函数和explode_outer函数的结果

explode

select explode(split(order_list,',')) as order_id
from t2_delivery_orders

执行结果

+-----------+
| order_id  |
+-----------+
| 0001      |
| 0005      |
| 0008      |
| 0002      |
| 0004      |
| 0006      |
| 0009      |
| 0010      |
| 0003      |
| 0007      |
+-----------+

explode_outer

select explode_outer(split(order_list,',')) as order_id
from t2_delivery_orders

执行结果

+-----------+
| order_id  |
+-----------+
| 0001      |
| 0005      |
| 0008      |
| 0002      |
| 0004      |
| 0006      |
| 0009      |
| 0010      |
| 0003      |
| 0007      |
| NULL      |
+-----------+

r004行对应的order_id使用explode_outer后，会有对应一个空值行。

2.2 解决sql

执行SQL

select rider_id, order_id
from t2_delivery_orders
         lateral view explode_outer(split(order_list, ',')) t2 as order_id

SQL结果

+-----------+-----------+
| rider_id  | order_id  |
+-----------+-----------+
| r001      | 0001      |
| r001      | 0005      |
| r001      | 0008      |
| r002      | 0002      |
| r002      | 0004      |
| r002      | 0006      |
| r002      | 0009      |
| r002      | 0010      |
| r003      | 0003      |
| r003      | 0007      |
| r004      | NULL      |
+-----------+-----------+

三、数据准备

--建表语句
CREATE TABLE IF NOT EXISTS t2_delivery_orders
(
    rider_id      string, -- 骑手ID
    order_list    string, -- 订单id列表
    distance_list STRING, --订单距离列表
    payment_list  STRING  --配送费列表
)
    COMMENT '骑手配送订单表';
--插入数据
INSERT INTO t2_delivery_orders VALUES
('r001', '0001,0005,0008', '8.05,2.32,4.35', '7.50,5.00,15.00'),
('r002', '0002,0004,0006,0009,0010', '3.01,10.98,0.78,5.05,6.05', '13.00,15.00,5.00,9.50,7.00'),
('r003', '0003,0007', '4.12,8.11', '3.50,8.00'),
('r004', null, null, null);

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题