一、基础数据

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

+
-----------+-----------+-------------+----------------------+--------------+-----------+----------+

二、函数介绍

1.transform介绍

transform(expr, func) - 使用函数对数组中的元素进行转换。

Examples:

> 
SELECT transform(
array(
1, 
2, 
3), x -> x + 
1);

 [2,3,4]

> 
SELECT transform(
array(
1, 
2, 
3), (x, i) -> x + i);

 [1,3,5]

Since: 2.4.0

2.其他函数介绍

• collect_list
• collect_set
• concat_ws

三、有序行转列

根据配送订单记录表，查询出骑手id，顾客id列表，要求顾客id列表中的顺序按照送达时间早晚排序。

分析

这里要求按照订单送达时间，对顾客id进行排序。直接考虑是使用开窗函数，根据时间进行排序（这种方法不可行，原因在于collect_list和collect_set那句提示“该函数是非确定性的，因为收集结果的顺序取决于行的顺序，这在经过shuffle之后可能是不确定的”。collect_list函数很难测试，但是collect_set因为有去重操作，所以比较好测试。另外一种解法是将时间和用户id拼接在一起，时间在前，用户id在后，这样对整个字符串拼接为数组，进行排序后再拆分，如此得到的数据能确定保证按照顺序完成。

1.不确定解法（不建议使用）

collect_list执行SQL

select rider_id,

       delivery_time,

       customer_id,

       collect_list(customer_id)
over(
partition 
by rider_id 
order 
by delivery_time) 
as customer_id_list


from t_delivery_orders

collect_list执行结果

+
-----------+----------------------+--------------+---------------------------------------+

| rider_id  |    delivery_time     | customer_id  |           customer_id_list            |

+
-----------+----------------------+--------------+---------------------------------------+

| r001      | 2024-01-01 08:30:00  | c001         | ["c001"]                              |

| r001      | 2024-01-01 09:30:00  | c007         | ["c001","c007"]                       |

| r001      | 2024-01-01 11:56:00  | c023         | ["c001","c007","c023"]                |

| r002      | 2024-01-01 08:43:00  | c003         | ["c003"]                              |

| r002      | 2024-01-01 09:21:00  | c005         | ["c003","c005"]                       |

| r002      | 2024-01-01 09:47:00  | c002         | ["c003","c005","c002"]                |

| r002      | 2024-01-01 12:30:00  | c031         | ["c003","c005","c002","c031"]         |

| r002      | 2024-01-01 13:30:00  | c001         | ["c003","c005","c002","c031","c001"]  |

| r003      | 2024-01-01 09:15:00  | c004         | ["c004"]                              |

| r003      | 2024-01-01 10:21:00  | c010         | ["c004","c010"]                       |

+
-----------+----------------------+--------------+---------------------------------------+

collect_set执行SQL

select rider_id,

       delivery_time,

       customer_id,

       collect_set(customer_id)
over(
partition 
by rider_id 
order 
by delivery_time) 
as customer_id_list


from t_delivery_orders

collect_set执行结果

+
-----------+----------------------+--------------+---------------------------------------+

| rider_id  |    delivery_time     | customer_id  |           customer_id_list            |

+
-----------+----------------------+--------------+---------------------------------------+

| r001      | 2024-01-01 08:30:00  | c001         | ["c001"]                              |

| r001      | 2024-01-01 09:30:00  | c007         | ["c001","c007"]                       |

| r001      | 2024-01-01 11:56:00  | c023         | ["c001","c007","c023"]                |

| r002      | 2024-01-01 08:43:00  | c003         | ["c003"]                              |

| r002      | 2024-01-01 09:21:00  | c005         | ["c005","c003"]                       |

| r002      | 2024-01-01 09:47:00  | c002         | ["c005","c003","c002"]                |

| r002      | 2024-01-01 12:30:00  | c031         | ["c005","c003","c002","c031"]         |

| r002      | 2024-01-01 13:30:00  | c001         | ["c001","c005","c003","c002","c031"]  |

| r003      | 2024-01-01 09:15:00  | c004         | ["c004"]                              |

| r003      | 2024-01-01 10:21:00  | c010         | ["c010","c004"]                       |

+
-----------+----------------------+--------------+---------------------------------------+

我们可以观察最后一行结果，发现顺序是不一样。 可以看到collect_list结果是正确的（这里属于巧合，但是大多数情况都是对的），而collect_set的结果是错误的。两个函数中均有提示，不保证结果顺序，从严谨出发，这两个函数均不可因为开窗函数来保证有序。  如果忽略该问题，使用collect_list进行行转列开窗，大部分情况得到的结果是正确的。我们只需要取出每个骑手最后一行数据即可。

执行SQL

select

    rider_id,

    
concat_ws(
',',customer_id_list) 
as customer_id_list


from

(
select rider_id,

       delivery_time,

       customer_id,

       collect_list(customer_id)
over(
partition 
by rider_id 
order 
by delivery_time) 
as customer_id_list,

       row_number() 
over (
partition 
by rider_id 
order 
by delivery_time 
desc) 
as rn


from t_delivery_orders

    ) t


where rn = 
1

执行结果

+
-----------+---------------------------+

| rider_id  |     customer_id_list      |

+
-----------+---------------------------+

| r001      | c001,c007,c023            |

| r002      | c003,c005,c002,c031,c001  |

| r003      | c004,c010                 |

+
-----------+---------------------------+

2.拼接排序后拆分

2.1先把时间和顾客id进行拼接，拼接后进行行转列

执行SQL

select rider_id,

       delivery_time,

       customer_id,

       
concat(delivery_time, customer_id) 
as time_customer


from t_delivery_orders

执行结果

+
-----------+----------------------+--------------+--------------------------+

| rider_id  |    delivery_time     | customer_id  |      time_customer       |

+
-----------+----------------------+--------------+--------------------------+

| r001      | 2024-01-01 08:30:00  | c001         | 2024-01-01 08:30:00c001  |

| r002      | 2024-01-01 08:43:00  | c003         | 2024-01-01 08:43:00c003  |

| r003      | 2024-01-01 09:15:00  | c004         | 2024-01-01 09:15:00c004  |

| r002      | 2024-01-01 09:21:00  | c005         | 2024-01-01 09:21:00c005  |

| r001      | 2024-01-01 09:30:00  | c007         | 2024-01-01 09:30:00c007  |

| r002      | 2024-01-01 09:47:00  | c002         | 2024-01-01 09:47:00c002  |

| r003      | 2024-01-01 10:21:00  | c010         | 2024-01-01 10:21:00c010  |

| r001      | 2024-01-01 11:56:00  | c023         | 2024-01-01 11:56:00c023  |

| r002      | 2024-01-01 12:30:00  | c031         | 2024-01-01 12:30:00c031  |

| r002      | 2024-01-01 13:30:00  | c001         | 2024-01-01 13:30:00c001  |

+
-----------+----------------------+--------------+--------------------------+

2.2 对time_customer 按照骑手id分组行转列,并使用sort_array排序

执行SQL

select rider_id,

       sort_array(collect_list(time_customer)) 
as sorted_time_customer


from (
select rider_id,

             delivery_time,

             customer_id,

             
concat(delivery_time, customer_id) 
as time_customer

      
from t_delivery_orders) t


group 
by rider_id

执行结果

+
-----------+----------------------------------------------------+

| rider_id  |                sorted_time_customer                |

+
-----------+----------------------------------------------------+

| r001      | ["2024-01-01 08:30:00c001","2024-01-01 09:30:00c007","2024-01-01 11:56:00c023"] |

| r002      | ["2024-01-01 08:43:00c003","2024-01-01 09:21:00c005","2024-01-01 09:47:00c002","2024-01-01 12:30:00c031","2024-01-01 13:30:00c001"] |

| r003      | ["2024-01-01 09:15:00c004","2024-01-01 10:21:00c010"] |

+
-----------+----------------------------------------------------+

2.3 去掉时间部分后，转换成字符串

执行SQL

select rider_id,

       
concat_ws(
',', transform(sort_array(collect_list(time_customer)), x->
substr(x, 
20))) 
as customer_list


from (
select rider_id,

             delivery_time,

             customer_id,

             
concat(delivery_time, customer_id) 
as time_customer

      
from t_delivery_orders) t


group 
by rider_id

执行结果

+
-----------+---------------------------+

| rider_id  |       customer_list       |

+
-----------+---------------------------+

| r001      | c001,c007,c023            |

| r002      | c003,c005,c002,c031,c001  |

| r003      | c004,c010                 |

+
-----------+---------------------------+

四、数据准备

--建表语句


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多列对应转行