一、基础数据
有配送订单表记录骑手配送的物品类型、送达时间、顾客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.collect_list函数介绍
collect_list(expr) - 收集并返回一个非唯一元素的列表
Examples:
>
SELECT collect_list(
col)
FROM
VALUES (
1), (
2), (
1)
AS tab(
col);
[1,2,1]
注意
该函数是非确定性的，因为收集结果的顺序取决于行的顺序，这在经过shuffle之后可能是不确定的。
Since: 2.0.0
2.collect_set函数介绍
collect_set(expr) - 收集并返回一个唯一元素的集合。
Examples:
>
SELECT collect_set(
col)
FROM
VALUES (
1), (
2), (
1)
AS tab(
col);
[1,2]
注意
该函数是非确定性的，因为收集结果的顺序取决于行的顺序，这在经过shuffle之后可能是不确定的。
Since: 2.0.0
3.sort_array介绍
sort_array(array[, ascendingOrder]) - 根据数组元素的自然顺序，将输入数组排序为升序或降序。对于double/float类型，NaN值大于任何非NaN元素。在升序排序中，空元素将被放置在返回数组的开头；在降序排序中，空元素将被放置在返回数组的末尾。
Examples:
>
SELECT sort_array(
array(
'b',
'd',
null,
'c',
'a'),
true);
[null,"a","b","c","d"]
Since: 1.5.0
4.concat_ws函数介绍
concat_ws(sep[, str | array(str)]+) - 使用 sep 作为分隔符返回字符串的连接，跳过空值。
Examples:
>
SELECT
concat_ws(
' ',
'Spark',
'SQL');
Spark SQL
>
SELECT
concat_ws(
's');
>
SELECT
concat_ws(
'/',
'foo',
null,
'bar');
foo/bar
>
SELECT
concat_ws(
null,
'Spark',
'SQL');
NULL
Since: 1.5.0
三、行转列
1.直接行转列
根据配送订单记录表，查询出骑手id，配送品类数据 goods_type_list
执行SQL
select rider_id,
concat_ws(
',', collect_list(goods_type))
as goods_type_list
from t_delivery_orders
group
by rider_id
执行结果
+
-----------+-------------------+
| rider_id | goods_type_list |
+
-----------+-------------------+
| r001 | 食品,食品,酒水 |
| r002 | 酒水,文件,食品,电子产品,文件 |
| r003 | 食品,文件 |
+
-----------+-------------------+
2.行转列去重
根据配送订单记录表，查询出骑手id，配送品类数据 goods_type_list，要求goods_type_list中不重复。
执行SQL
select rider_id,
concat_ws(
',', collect_set(goods_type))
as goods_type_list
from t_delivery_orders
group
by rider_id
执行结果
+
-----------+------------------+
| rider_id | goods_type_list |
+
-----------+------------------+
| r001 | 酒水,食品 |
| r002 | 酒水,食品,文件,电子产品 |
| r003 | 食品,文件 |
+
-----------+------------------+
3.行转列后排序
根据配送订单记录表，查询出骑手id，配送品类数据 goods_type_list，要求goods_type_list中不重复且数据按照倒叙排列
执行SQL
select rider_id,
concat_ws(
',',sort_array(collect_set(goods_type),
false))
as goods_type_list
from db_topic_rowcolumn.t_delivery_orders
group
by rider_id
执行结果
+
-----------+------------------+
| rider_id | goods_type_list |
+
-----------+------------------+
| r001 | 食品,酒水 |
| r002 | 食品,酒水,电子产品,文件 |
| r003 | 食品,文件 |
+
-----------+------------------+
四、数据准备
--建表语句
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进行简单行转列 -
行转列-使用transform进行有序行转列 -
行转列-使用transform进行有序行转列-多列一一对应 -
行转列-多行转多列（竖表转横表） -
列转行-多列转多行（横表变竖表） -
列转行-lateral view explode列转行 -
列转行-explode_outer和lateral view outer -
列转行-posexplode多列对应转行 -
列转行-lateral view outer posexplode及posexplode_outer多列对应转行
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70040536/viewspace-3026748/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
spark3.5.1中内置函数大全
下一篇：
有序行转列
请登录后发表评论
登录
全部评论
