列转行-lateral view explode列转行

数据仓库晨曦

发布于 2024-08-19 15:59:41

820

发布于 2024-08-19 15:59:41

文章被收录于专栏：数据仓库技术

一、基础数据

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

二、函数介绍

explode
split

三、列转行

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

期望结果

+-----------+-----------+
| 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、解决方案

先将字符串通过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横向视图与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);

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2024-08-18，如有侵权请联系 cloudcommunity@tencent.com 删除

view