列转行-lateral view explode列转行

一、基础数据

现有骑手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                        |
+-----------+---------------------------+----------------------------+-----------------------------+

二、列转行

将骑手及其订单转换成订单粒度，每单一行记录。

期望结果

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

1. Lateral View explode 解决方案

先将字符串通过split函数转换成array，然后使用explode炸开，即可得到最终结果。

select rider_id, order_id
from t2_delivery_orders
         lateral view 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      |
+-----------+-----------+

2. Lateral View Outer 保留 NULL 行

由于我们使用了lateral view横向视图与explode结合炸裂，我们会发现 骑手r004因为其order_list为空没有出现在最终结果中。原因为在explode处理order_list时因为r004对应行记录为null而直接剔除了，lateral view 关联方式为inner join 方式，导致最终结果中无对应数据。

三、数据准备

--建表语句
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);

常见坑点

坑1：Lateral View 与 WHERE 的执行顺序

Lateral View 在 WHERE 之前执行。如果 WHERE 条件过滤了某些行，Lateral View 的结果也会相应减少。注意不要在 WHERE 中引用 Lateral View 生成的列——应该用子查询。

坑2：Lateral View Outer vs Lateral View

Lateral View Outer 保留 NULL 和空数组行（生成一行 NULL），普通 Lateral View 直接丢弃。数据完整性要求高的场景（如报表）必须用 Outer。

坑3：多次 Lateral View 的列名冲突

多次使用 Lateral View explode 时，如果不同数组的 explode 结果列名相同，后面的会覆盖前面的。

面试怎么考

• "Lateral View 是做什么的？" → 将数组/MAP 列展开为多行
• "Lateral View 和 Lateral View Outer 的区别？" → 是否保留 NULL 行
• "explode 函数可以单独使用吗？" → 需要配合 Lateral View

📱关注公众号

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

💬加群交流

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