内容纲要
概要描述
本章主要简单介绍 inceptor 中的行转列(一行变多行)的实现方式,以供需要时参考。
详细说明
问题:
假设有数据如下所示:
id price
1 1,2,3
2 1,2,3
3 1,2,3
--
假如对于 id=1 的行,如果想要得到如下所示结果
id new_c
1 1
1 2
1 3
实现:采用 LATERAL VIEW
结合 EXPLODE
,SPLIT
函数
SET transaction.type=inceptor;
-- 构造测试数据
CREATE TABLE IF NOT EXISTS test_explode(id INT, price STRING) STORED AS ORC;
INSERT INTO test_explode SELECT 1,'1,2,3' FROM system.dual;
INSERT INTO test_explode SELECT 2,'1,2,3' FROM system.dual;
INSERT INTO test_explode SELECT 3,'1,2,3' FROM system.dual;
-- SQL 语句实现
SELECT t1.id, new_c FROM test_explode t1
LATERAL VIEW EXPLODE(SPLIT(t1.price,',')) t2 AS new_c;
拓展:
-
把上述整张 test_explode 表 price 列的数据相加
SELECT SUM(col) Price_Sum FROM (SELECT EXPLODE(SPLIT(t1.price,',')) FROM test_explode t1);
-
把上述 test_explode 表 price 列的数据按照 id 列分别汇总相加
SELECT t1.id, SUM(new_c) Sum_Price FROM test_explode t1 LATERAL VIEW EXPLODE(SPLIT(t1.price,',')) t2 AS new_c GROUP BY t1.id ORDER BY t1.id;
-
统计 test_explode 表 price 列里每个数据在表中出现的次数
SELECT price_c, COUNT(1) FROM test_explode t1 LATERAL VIEW EXPLODE(SPLIT(t1.price,',')) t2 AS price_c GROUP BY price_c ORDER BY price_c;
补充:
- LATERAL VIEW
用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释:用于和SPLIT
、EXPLODE
等 UDTF 函数一起使用,它能够将一列数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。LATERAL VIEW
首先为原始表的每行调用 UDTF,UTDF 会把一行拆分成一或者多行,LATERAL VIEW
再把结果组合,产生一个支持别名表的虚拟表。