### 6.9.1 背景介绍 行列转化是ETL处理过程中常见的操作,Lateral 一个特殊的Join关键字,能够按照每行和内部的子查询或者table function关联,通过Lateral 与unnest配合,我们可以实现一行转多行的功能。 ### 6.9.2 使用说明 使用lateral join 需要打开新版优化器 ~~~ set global enable_cbo = true; ~~~ lateral 关键字语法说明 ~~~ from table_reference join [lateral] table_reference ~~~ Unnest关键字,unnest是一种table function 可以把数组类型转化成table的多行,配合Lateral Join就能实现我们常见的各种行展开逻辑。有以下两种写法 ~~~ SELECT student, score FROM tests CROSS JOIN LATERAL UNNEST(scores) AS t (score); SELECT student, score FROM tests, UNNEST(scores) AS t (score); ~~~ 这里第二种写法是第一种的简写,可以使用Unnest 关键字省略Lateral Join。 ### 6.9.3 使用举例 当前版本DorisDB支持,Bitmap、String、Array、Column之间的转化关系如下: ![](https://img.kancloud.cn/41/e6/41e6c6a46b1d510c34f1353526510fa5_1044x610.png) 配合Unnest 我们可以实现以下功能: 1. String 展开成多行 ``` plain text CREATE TABLE lateral_test2 ( `v1` bigint(20) NULL COMMENT "", `v2` string NULL COMMENT "" ) duplicate key(v1) DISTRIBUTED BY HASH(`v1`) BUCKETS 1 PROPERTIES ( "replication\_num" \= "1", "in\_memory" \= "false", "storage\_format" \= "DEFAULT" ); insert into lateral_test2 values (1, "1,2,3"), (2, "1,3"); select * from lateral_test2; +------+-------+ | v1 | v2 | +------+-------+ | 1 | 1,2,3 | | 2 | 1,3 | +------+-------+ select v1,unnest from lateral_test2 CROSS JOIN LATERAL unnest(split(v2, ",")) ; +------+--------+ | v1 | unnest | +------+--------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 3 | +------+--------+ --另一种写法 select v1,unnest from lateral_test2 , unnest(split(v2, ",")) ; +------+--------+ | v1 | unnest | +------+--------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 3 | +------+--------+ ``` 2. Array类型展开成多行 ``` text CREATE TABLE lateral_test ( `v1` bigint(20) NULL COMMENT "", `v2` ARRAY NULL COMMENT "" ) duplicate key(v1) DISTRIBUTED BY HASH(`v1`) BUCKETS 1 PROPERTIES ( "replication_num" = "1", "in_memory" = "false", "storage_format" = "DEFAULT" ); insert into lateral_test values (1, [1,2]), (2, [1, null, 3]), (3, null); select * from lateral_test; +------+------------+ | v1 | v2 | +------+------------+ | 1 | [1,2] | | 2 | [1,null,3] | | 3 | NULL | +------+------------+ select v1,v2,unnest from lateral_test , unnest(v2) ; +------+------------+--------+ | v1 | v2 | unnest | +------+------------+--------+ | 1 | [1,2] | 1 | | 1 | [1,2] | 2 | | 2 | [1,null,3] | 1 | | 2 | [1,null,3] | NULL | | 2 | [1,null,3] | 3 | +------+------------+--------+ ``` 3. Bitmap类型输出 ``` plain text CREATE TABLE lateral_test3 ( `v1` bigint(20) NULL COMMENT "", `v2` Bitmap BITMAP_UNION COMMENT "" ) Aggregate key(v1) DISTRIBUTED BY HASH(`v1`) BUCKETS 1; insert into lateral_test3 values (1, bitmap_from_string('1, 2')), (2, to_bitmap(3)); select v1, bitmap_to_string(v2) from lateral_test3; +------+------------------------+ | v1 | bitmap_to_string(`v2`) | +------+------------------------+ | 1 | 1,2 | | 2 | 3 | +------+------------------------+ insert into lateral_test3 values (1, to_bitmap(3)); select v1, bitmap_to_string(v2) from lateral_test3; +------+------------------------+ | v1 | bitmap_to_string(`v2`) | +------+------------------------+ | 1 | 1,2,3 | | 2 | 3 | +------+------------------------+ select v1,unnest from lateral_test3 , unnest(bitmap_to_array(v2)) ; +------+--------+ | v1 | unnest | +------+--------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 3 | +------+--------+ ``` ### 6.9.4 注意事项 * 当前版本Lateral join 仅用于和Unnest函数配合使用,实现行转列的功能,后续会支持其他table function / UDTF。 * 当前Lateral join 还不支持子查询