一、基础数据
现有骑手id,订单id列表，订单配送距离列表，配送费列表，其中订单id、配送距离、配送费一一对应。
代码语言：javascript复制 ----------- --------------------------- ---------------------------- -----------------------------
| 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 |
----------- --------------------------- ---------------------------- -----------------------------
二、函数介绍
- split
- posexplode_outer
- nvl
- coalesce
- equal_null
三、列转行
原始数据中order_list中的数据，与distance_list、payment_list内的数据，一一对应，请将数据拆解出rider_id、order_id,distance，payment，其中distance和payment为对应订单id的距离和配送费。需要把骑手r004数据进行展示
期望结果
代码语言：javascript复制 ----------- ----------- ----------- ----------
| rider_id | order_id | distance | payment |
----------- ----------- ----------- ----------
| r001 | 0001 | 8.05 | 7.50 |
| r001 | 0005 | 2.32 | 5.00 |
| r001 | 0008 | 4.35 | 15.00 |
| r002 | 0002 | 3.01 | 13.00 |
| r002 | 0004 | 10.98 | 15.00 |
| r002 | 0006 | 0.78 | 5.00 |
| r002 | 0009 | 5.05 | 9.50 |
| r002 | 0010 | 6.05 | 7.00 |
| r003 | 0003 | 4.12 | 3.50 |
| r003 | 0007 | 8.11 | 8.00 |
| r004 | NULL | NULL | NULL |
----------- ----------- ----------- ----------
1、使用posexplode_outer处理
我们先看下posexplode_outer 处理order_list的结果
执行SQL
代码语言：javascript复制select rider_id, t2.pos, t2.order_id
from t2_delivery_orders t1
lateral view posexplode_outer(split(order_list, ',')) t2 as pos, order_id
SQL结果
代码语言：javascript复制 ----------- ------- -----------
| rider_id | pos | order_id |
----------- ------- -----------
| r001 | 0 | 0001 |
| r001 | 1 | 0005 |
| r001 | 2 | 0008 |
| r002 | 0 | 0002 |
| r002 | 1 | 0004 |
| r002 | 2 | 0006 |
| r002 | 3 | 0009 |
| r002 | 4 | 0010 |
| r003 | 0 | 0003 |
| r003 | 1 | 0007 |
| r004 | NULL | NULL |
----------- ------- -----------
上面可以看到，pos列及order_id 列均为null。
2、处理空值得到结果
该题目与列转行posexplode多列对应转行 思路并无不同，只需要在where条件判断pos是否相等时增加对null的处理。
2.1 nvl或者coalesce对空值处理
因为pos是数组的脚标，所以如果是空值，我们处理成一个负数即可。
执行SQL
代码语言：javascript复制select rider_id, order_id, t3.distance, t4.payment
from t2_delivery_orders t1
lateral view posexplode_outer(split(order_list, ',')) t2 as pos, order_id
lateral view posexplode_outer(split(distance_list, ',')) t3 as pos, distance
lateral view posexplode_outer(split(payment_list, ',')) t4 as pos, payment
where nvl(t2.pos, -1) = nvl(t3.pos, -1)
and nvl(t2.pos, -1) = nvl(t4.pos, -1)
SQL结果
代码语言：javascript复制 ----------- ----------- ----------- ----------
| rider_id | order_id | distance | payment |
----------- ----------- ----------- ----------
| r001 | 0001 | 8.05 | 7.50 |
| r001 | 0005 | 2.32 | 5.00 |
| r001 | 0008 | 4.35 | 15.00 |
| r002 | 0002 | 3.01 | 13.00 |
| r002 | 0004 | 10.98 | 15.00 |
| r002 | 0006 | 0.78 | 5.00 |
| r002 | 0009 | 5.05 | 9.50 |
| r002 | 0010 | 6.05 | 7.00 |
| r003 | 0003 | 4.12 | 3.50 |
| r003 | 0007 | 8.11 | 8.00 |
| r004 | NULL | NULL | NULL |
----------- ----------- ----------- ----------
2.2 使用equal_null判断空值
2.1解法是对空值进行处理后判断，假如没有合适的默认值给空值赋值，我们也可以用equal_null直接对空值进行判断是否全为空值，进行匹配
执行SQL
代码语言：javascript复制select rider_id, order_id, t3.distance, t4.payment
from t2_delivery_orders t1
lateral view posexplode_outer(split(order_list, ',')) t2 as pos, order_id
lateral view posexplode_outer(split(distance_list, ',')) t3 as pos, distance
lateral view posexplode_outer(split(payment_list, ',')) t4 as pos, payment
where equal_null(t2.pos,t3.pos)
and equal_null(t2.pos,t4.pos)
SQL结果
代码语言：javascript复制 ----------- ----------- ----------- ----------
| rider_id | order_id | distance | payment |
----------- ----------- ----------- ----------
| r001 | 0001 | 8.05 | 7.50 |
| r001 | 0005 | 2.32 | 5.00 |
| r001 | 0008 | 4.35 | 15.00 |
| r002 | 0002 | 3.01 | 13.00 |
| r002 | 0004 | 10.98 | 15.00 |
| r002 | 0006 | 0.78 | 5.00 |
| r002 | 0009 | 5.05 | 9.50 |
| r002 | 0010 | 6.05 | 7.00 |
| r003 | 0003 | 4.12 | 3.50 |
| r003 | 0007 | 8.11 | 8.00 |
| r004 | NULL | NULL | NULL |
----------- ----------- ----------- ----------
注意，equal_null是spark从版本3.4.0开始支持
3、lateral view outer posexplode处理方式
除了使用posexplode_outer
执行SQL
代码语言：javascript复制select rider_id, order_id, t3.distance, t4.payment
from t2_delivery_orders t1
lateral view outer posexplode(split(order_list, ',')) t2 as pos, order_id
lateral view outer posexplode(split(distance_list, ',')) t3 as pos, distance
lateral view outer posexplode(split(payment_list, ',')) t4 as pos, payment
where equal_null(t2.pos,t3.pos)
and equal_null(t2.pos,t4.pos)
SQL结果
代码语言：javascript复制 ----------- ----------- ----------- ----------
| rider_id | order_id | distance | payment |
----------- ----------- ----------- ----------
| r001 | 0001 | 8.05 | 7.50 |
| r001 | 0005 | 2.32 | 5.00 |
| r001 | 0008 | 4.35 | 15.00 |
| r002 | 0002 | 3.01 | 13.00 |
| r002 | 0004 | 10.98 | 15.00 |
| r002 | 0006 | 0.78 | 5.00 |
| r002 | 0009 | 5.05 | 9.50 |
| r002 | 0010 | 6.05 | 7.00 |
| r003 | 0003 | 4.12 | 3.50 |
| r003 | 0007 | 8.11 | 8.00 |
| r004 | NULL | NULL | NULL |
----------- ----------- ----------- ----------
四、数据准备
代码语言：javascript复制--建表语句
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);
