3.行转列-使用transform进行有序行转列-多列一一对应
一、基础数据
有配送订单表记录骑手配送的物品类型、送达时间、顾客id、配送举例及配送费。
+-----------+-----------+-------------+----------------------+--------------+-----------+----------+
| rider_id | order_id | goods_type | delivery_time | customer_id | distance | payment |
+-----------+-----------+-------------+----------------------+--------------+-----------+----------+
| r001 | 0001 | 食品 | 2024-01-01 08:30:00 | c001 | 8.05 | 7.50 |
| r002 | 0002 | 酒水 | 2024-01-01 08:43:00 | c003 | 3.01 | 13.00 |
| r003 | 0003 | 食品 | 2024-01-01 09:15:00 | c004 | 4.12 | 3.50 |
| r002 | 0004 | 文件 | 2024-01-01 09:21:00 | c005 | 10.98 | 15.00 |
| r001 | 0005 | 食品 | 2024-01-01 09:30:00 | c007 | 2.32 | 5.00 |
| r002 | 0006 | 食品 | 2024-01-01 09:47:00 | c002 | 0.78 | 5.00 |
| r003 | 0007 | 文件 | 2024-01-01 10:21:00 | c010 | 8.11 | 8.00 |
| r001 | 0008 | 酒水 | 2024-01-01 11:56:00 | c023 | 4.35 | 15.00 |
| r002 | 0009 | 电子产品 | 2024-01-01 12:30:00 | c031 | 5.05 | 9.50 |
| r002 | 0010 | 文件 | 2024-01-01 13:30:00 | c001 | 6.05 | 7.00 |
+-----------+-----------+-------------+----------------------+--------------+-----------+----------+二、函数介绍
- collect_list (opens in a new tab)
- concat_ws (opens in a new tab)
- transform (opens in a new tab)
- split (opens in a new tab)
- sort_array (opens in a new tab)
三、有序行转列
根据配送订单记录表，查询出骑手id，配送订单id列表、距离列表、配送费列表，要求三列中的数据按照送达时间顺序，且一一对应；
期望结果
+-----------+---------------------------+----------------------------+-----------------------------+
| rider_id | order_id | distance | payment |
+-----------+---------------------------+----------------------------+-----------------------------+
| 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 |
+-----------+---------------------------+----------------------------+-----------------------------+分析
首先要求按照送达时间排序，且要求多列一一对应，所以我们把所有的数据拼接到一起，按照时间排序后再进行拆分。
1.将所需要的字段进行拼接
使用concat_ws将时间字段与其他需要字段进行拼接，因为需要按照时间排序，所以时间排在最前面。
执行SQL
select rider_id,
delivery_time,
order_id,
concat_ws(',', delivery_time, order_id, distance, payment) as col_list
from t_delivery_orders执行结果
+-----------+----------------------+-----------+---------------------------------------+
| rider_id | delivery_time | order_id | col_list |
+-----------+----------------------+-----------+---------------------------------------+
| r001 | 2024-01-01 08:30:00 | 0001 | 2024-01-01 08:30:00,0001,8.05,7.50 |
| r002 | 2024-01-01 08:43:00 | 0002 | 2024-01-01 08:43:00,0002,3.01,13.00 |
| r003 | 2024-01-01 09:15:00 | 0003 | 2024-01-01 09:15:00,0003,4.12,3.50 |
| r002 | 2024-01-01 09:21:00 | 0004 | 2024-01-01 09:21:00,0004,10.98,15.00 |
| r001 | 2024-01-01 09:30:00 | 0005 | 2024-01-01 09:30:00,0005,2.32,5.00 |
| r002 | 2024-01-01 09:47:00 | 0006 | 2024-01-01 09:47:00,0006,0.78,5.00 |
| r003 | 2024-01-01 10:21:00 | 0007 | 2024-01-01 10:21:00,0007,8.11,8.00 |
| r001 | 2024-01-01 11:56:00 | 0008 | 2024-01-01 11:56:00,0008,4.35,15.00 |
| r002 | 2024-01-01 12:30:00 | 0009 | 2024-01-01 12:30:00,0009,5.05,9.50 |
| r002 | 2024-01-01 13:30:00 | 0010 | 2024-01-01 13:30:00,0010,6.05,7.00 |
+-----------+----------------------+-----------+---------------------------------------+2.行转列、排序
使用collect_list进行行转列，使用sort_array函数进行排序，得到一个排序后的数组。
执行SQL
select rider_id,
sort_array(collect_list(col_list)) as sorted_col_list
from (select rider_id,
delivery_time,
order_id,
concat_ws(',', delivery_time, order_id, distance, payment) as col_list
from t_delivery_orders) t
group by rider_id执行结果
+-----------+----------------------------------------------------+
| rider_id | sorted_col_list |
+-----------+----------------------------------------------------+
| r001 | ["2024-01-01 08:30:00,0001,8.05,7.50","2024-01-01 09:30:00,0005,2.32,5.00","2024-01-01 11:56:00,0008,4.35,15.00"] |
| r002 | ["2024-01-01 08:43:00,0002,3.01,13.00","2024-01-01 09:21:00,0004,10.98,15.00","2024-01-01 09:47:00,0006,0.78,5.00","2024-01-01 12:30:00,0009,5.05,9.50","2024-01-01 13:30:00,0010,6.05,7.00"] |
| r003 | ["2024-01-01 09:15:00,0003,4.12,3.50","2024-01-01 10:21:00,0007,8.11,8.00"] |
+-----------+----------------------------------------------------+3.将数字内的字符串转换为数组。
我们使用transform函数和split函数，将数字内的字符串转换成数组，原来的一维数组变为二维数组。
执行SQL
select rider_id,
transform(sort_array(collect_list(col_list)), x->split(x, ',')) as sorted_col_list
from (select rider_id,
delivery_time,
order_id,
concat_ws(',', delivery_time, order_id, distance, payment) as col_list
from t_delivery_orders) t
group by rider_id执行结果
+-----------+----------------------------------------------------+
| rider_id | sorted_col_list |
+-----------+----------------------------------------------------+
| r001 | [["2024-01-01 08:30:00","0001","8.05","7.50"],["2024-01-01 09:30:00","0005","2.32","5.00"],["2024-01-01 11:56:00","0008","4.35","15.00"]] |
| r002 | [["2024-01-01 08:43:00","0002","3.01","13.00"],["2024-01-01 09:21:00","0004","10.98","15.00"],["2024-01-01 09:47:00","0006","0.78","5.00"],["2024-01-01 12:30:00","0009","5.05","9.50"],["2024-01-01 13:30:00","0010","6.05","7.00"]] |
| r003 | [["2024-01-01 09:15:00","0003","4.12","3.50"],["2024-01-01 10:21:00","0007","8.11","8.00"]] |
+-----------+----------------------------------------------------+4.拆分出对应的列，并转换成字符串
使用transform函数，从数组中拆分对应的列，然后转换成字符串得到最终结果
执行sql
select rider_id,
concat_ws(',', transform(sorted_col_list, x-> x[1])) as order_id,
concat_ws(',', transform(sorted_col_list, x-> x[2])) as distance,
concat_ws(',', transform(sorted_col_list, x-> x[3])) as payment
from (select rider_id,
transform(sort_array(collect_list(col_list)), x->split(x, ',')) as sorted_col_list
from (select rider_id,
delivery_time,
order_id,
concat_ws(',', delivery_time, order_id, distance, payment) as col_list
from t_delivery_orders) t
group by rider_id) tt执行结果
+-----------+---------------------------+----------------------------+-----------------------------+
| rider_id | order_id | distance | payment |
+-----------+---------------------------+----------------------------+-----------------------------+
| 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 |
+-----------+---------------------------+----------------------------+-----------------------------+四、数据准备
--建表语句
CREATE TABLE IF NOT EXISTS t_delivery_orders
(
rider_id string, -- 骑手ID
order_id string, -- 订单ID
goods_type STRING, -- 物品类型
delivery_time STRING, -- 送达时间
customer_id STRING, -- 客户id
distance decimal(10, 2), -- 配送距离
payment decimal(10, 2) -- 支付金额，骑手的配送费用
)
COMMENT '骑手配送订单表';
--插入数据
INSERT INTO t_delivery_orders VALUES
('r001', '0001', '食品', '2024-01-01 08:30:00','c001',8.05,7.50),
('r002', '0002', '酒水', '2024-01-01 08:43:00','c003',3.01,13.00),
('r003', '0003', '食品', '2024-01-01 09:15:00','c004',4.12,3.50),
('r002', '0004', '文件', '2024-01-01 09:21:00','c005',10.98,15.00),
('r001', '0005', '食品', '2024-01-01 09:30:00','c007',2.32,5.00),
('r002', '0006', '食品', '2024-01-01 09:47:00','c002',0.78,5.00),
('r003', '0007', '文件', '2024-01-01 10:21:00','c010',8.11,8.00),
('r001', '0008', '酒水', '2024-01-01 11:56:00','c023',4.35,15.00),
('r002', '0009', '电子产品', '2024-01-01 12:30:00','c031',5.05,9.50),
('r002', '0010', '文件', '2024-01-01 13:30:00','c001',6.05,7.00);相关推荐
- 行转列-collect_list,collect_set进行简单行转列 (opens in a new tab)
- 行转列-使用transform进行有序行转列 (opens in a new tab)
- 行转列-使用transform进行有序行转列-多列一一对应 (opens in a new tab)
- 行转列-多行转多列（竖表转横表） (opens in a new tab)
- 列转行-多列转多行（横表变竖表） (opens in a new tab)
- 列转行-lateral view explode列转行 (opens in a new tab)
- 列转行-explode_outer和lateral view outer (opens in a new tab)
- 列转行-posexplode多列对应转行 (opens in a new tab)
- 列转行-lateral view outer posexplode及posexplode_outer多列对应转行 (opens in a new tab)
本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术 (opens in a new tab)“发表