行转列-collect_list,collect_set进行简单行转列
一、基础数据
有配送订单表记录骑手配送的物品类型、送达时间、顾客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 直接行转列
根据配送订单记录表，查询出骑手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. collect_set 行转列去重
根据配送订单记录表，查询出骑手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. sort_array 行转列后排序
根据配送订单记录表，查询出骑手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_list 聚合时不保证元素顺序。如果要求拼接结果有序（如按时间先后），必须先 ORDER BY 在子查询中排好序，或使用 sort_array 后再 concat_ws。
坑2：collect_set 会去重
collect_set 会丢弃重复值。如果业务需要保留所有记录（包括重复），必须用 collect_list。
坑3：concat_ws 的 NULL 处理
concat_ws 遇到 NULL 时会跳过该元素，不会报错也不会拼接出"NULL"字符串。如果需要保留 NULL 占位，用 concat 函数。
面试怎么考
- "怎么把多行合并成一行？" → collect_list + concat_ws
- "collect_list 和 collect_set 有什么区别？" → 去重与否
- "怎么保证行转列后的顺序？" → sort_array 或子查询 ORDER BY
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
