Doris：使用表函数explode实现array字段列转行

最新推荐文章于 2024-03-15 13:20:52 发布

坚持是一种态度

最新推荐文章于 2024-03-15 13:20:52 发布

阅读量1.7k

点赞数 29

分类专栏：大数据开发数据库及存储技术文章标签： doris

本文链接：https://blog.csdn.net/u010882234/article/details/136707928

版权

数据库及存储技术同时被 2 个专栏收录

50 篇文章 3 订阅

订阅专栏

大数据开发

24 篇文章 2 订阅

订阅专栏

文章目录

使用场景

我们的大数据数据库，由clickhouse换成了doris
我们有一张路口指标表，安全指标表，记录整个路口周期级别的安全指标，包括路口级的指标和各个进口的部分指标，都在一条记录里
clickhouse原有的array字段，可以使用arrayJoin函数实现列转行，有兴趣的可以看下我的这篇博文：ClickHouse嵌套类型使用案例：使用Array和Tuple（或Nested）表示交通干线指标场景
doris没有arrayJoin函数，但也有实现列转行的方法

相关知识点介绍

一句话总结：Doris宽表怎么转成高表，就是借助Lateral view

explode

官方文档地址：主页 > SQL 手册 > SQL 函数 > 表函数 > EXPLODE
表函数，需配合 Lateral View 使用。将 array 列展开成多行。当 array 为NULL或者为空时，explode_outer 返回NULL。 explode 和 explode_outer 均会返回 array 内部的NULL元素。
如果不支持向量化引擎，先设置一下set enable_vectorized_engine = true

split_by_string

官方文档地址：主页 > SQL 手册 > SQL 函数 > 字符串函数 > SPLIT_BY_STRING
此函数将字符串分隔，转为数组
如果存储的是数组array格式，可以直接使用
如果存储的是字符串格式（符号分隔），可以使用函数split_by_string将字符串分隔为数组

SELECT split_by_string('1,2,3,4',',')

在这里插入图片描述

lateral view

官方文档地址：主页 > 进阶使用 > 行转列
与生成器函数（例如 EXPLODE）结合使用，将生成包含一个或多个行的虚拟表。 LATERAL VIEW 将行应用于每个原始输出行
使用lateral view explode分隔数组

SELECT e1 FROM (SELECT * FROM dwd_signal_securityindex_ri  order by time_stamp desc limit 1) as t lateral view explode(approach_index) tmp1 as e1;

在这里插入图片描述

具体实现和SQL

原始数据

lateral view explode列转行


SELECT time_stamp, intersection_number, safety_factor,sub FROM dwd_signal_securityindex_ri as t lateral view explode ( approach_index ) tbl1 AS sub;

在这里插入图片描述

SPLIT_BY_STRING拆分字符串为数组


SELECT
	time_stamp,
	intersection_number,
	safety_factor,
	SPLIT_BY_STRING ( sub, '-' ) arr 
FROM
	( SELECT time_stamp, intersection_number, safety_factor, sub FROM dwd_signal_securityindex_ri AS t lateral VIEW explode ( approach_index ) tbl1 AS sub ) aaa;

在这里插入图片描述

element_at获取数据

SELECT
	time_stamp,
	intersection_number,
	element_at ( arr, 1 ) AS approach,
	element_at ( arr, 2 ) AS pedestrianTimeGuaranteeRate ,
	element_at ( arr, 3 ) AS pedestrianIllegalRate ,
	element_at ( arr, 4 ) AS trafficConflict 
FROM
	(
	SELECT
		time_stamp,
		intersection_number,
		SPLIT_BY_STRING ( sub, '-' ) arr 
	FROM
		( SELECT time_stamp, intersection_number, safety_factor, sub FROM dwd_signal_securityindex_ri AS t lateral VIEW explode ( approach_index ) tbl1 AS sub ) aaa 
	) bbb

在这里插入图片描述

创建视图

查询SQL写好后，可以创建视图，后续直接查询该视图即可

-- 路口进口级别，安全指标
DROP VIEW IF EXISTS `signal`.dwd_signal_securityindex_ri_view;
CREATE VIEW `signal`.dwd_signal_securityindex_ri_view AS
SELECT
	time_stamp,
	intersection_number,
	CAST(element_at ( arr, 1 ) AS DECIMAL(2,0)) AS approach,
	CAST(element_at ( arr, 2 ) AS DECIMAL(10,2)) AS pedestrian_time_guarantee_rate ,
	CAST(element_at ( arr, 3 ) AS DECIMAL(10,2)) AS pedestrian_illegal_rate ,
	CAST(element_at ( arr, 4 ) AS DECIMAL(5,0)) AS traffic_conflict 
FROM
	(
	SELECT
		time_stamp,
		intersection_number,
		SPLIT_BY_STRING ( sub, '-' ) arr 
	FROM
		( SELECT time_stamp, intersection_number, safety_factor, sub FROM dwd_signal_securityindex_ri AS t lateral VIEW explode ( approach_index ) tbl1 AS sub ) aaa 
	) bbb