简单行转列

一、基础数据

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

相关推荐

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