一、基础数据

有配送订单表记录骑手配送的物品类型、送达时间、顾客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
• concat_ws
• transform
• split
• sort_array

三、有序行转列

根据配送订单记录表，查询出骑手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);

相关推荐

1. 行转列-collect_list,collect_set进行简单行转列
2. 行转列-使用transform进行有序行转列
3. 行转列-使用transform进行有序行转列-多列一一对应
4. 行转列-多行转多列（竖表转横表）
5. 列转行-多列转多行（横表变竖表）
6. 列转行-lateral view explode列转行
7. 列转行-explode_outer和lateral view outer
8. 列转行-posexplode多列对应转行
9. 列转行-lateral view outer posexplode及posexplode_outer多列对应转行