当前位置：首页 > news >正文

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

news 2025/9/14 21:29:23

文章目录

- 使用场景
- 相关知识点介绍
- - explode
  - split_by_string
  - lateral view
- 具体实现和SQL
- - lateral view explode列转行
  - SPLIT_BY_STRING拆分字符串为数组
  - element_at获取数据
  - 创建视图

使用场景

我们的大数据数据库，由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拆分字符串为数组


SELECTtime_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获取数据

SELECTtime_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(SELECTtime_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
SELECTtime_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(SELECTtime_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