- StarRocks介绍
- 快速开始
- 表设计
- 导入数据
- 导出数据
- 使用StarRocks
- 参考手册
- SQL参考
- 用户账户管理
- 集群管理
- ADMIN CANCEL REPAIR
- ADMIN CHECK TABLET
- ADMIN REPAIR
- ADMIN SET CONFIG
- ADMIN SET REPLICA STATUS
- ADMIN SHOW CONFIG
- ADMIN SHOW REPLICA DISTRIBUTION
- ADMIN SHOW REPLICA STATUS
- ALTER SYSTEM
- CANCEL DECOMMISSION
- CREATE FILE
- DROP FILE
- INSTALL PLUGIN
- SHOW BACKENDS
- SHOW BROKER
- SHOW FRONTENDS
- SHOW FULL COLUMNS
- SHOW INDEX
- SHOW PLUGINS
- SHOW TABLE STATUS
- SHOW FILE
- UNINSTALL PLUGIN
- DDL
- ALTER DATABASE
- ALTER TABLE
- ALTER VIEW
- BACKUP
- CANCEL BACKUP
- CANCEL RESTORE
- CREATE DATABASE
- CREATE INDEX
- CREATE MATERIALIZED VIEW
- CREATE REPOSITORY
- CREATE RESOURCE
- CREATE TABLE AS SELECT
- CREATE TABLE LIKE
- CREATE TABLE
- CREATE VIEW
- CREATE FUNCTION
- DROP DATABASE
- DROP INDEX
- DROP MATERIALIZED VIEW
- DROP REPOSITORY
- DROP RESOURCE
- DROP TABLE
- DROP VIEW
- DROP FUNCTION
- HLL
- RECOVER
- RESTORE
- SHOW RESOURCES
- SHOW FUNCTION
- TRUNCATE TABLE
- DML
- ALTER ROUTINE LOAD
- BROKER LOAD
- CANCEL LOAD
- CANCEL EXPORT
- DELETE
- EXPORT
- GROUP BY
- INSERT
- PAUSE ROUTINE LOAD
- RESUME ROUTINE LOAD
- CREATE ROUTINE LOAD
- SELECT
- SHOW ALTER
- SHOW BACKUP
- SHOW CREATE TABLE
- SHOW CRAETE VIEW
- SHOW DATA
- SHOW DATABASES
- SHOW DELETE
- SHOW DYNAMIC PARTITION TABLES
- SHOW EXPORT
- SHOW LOAD
- SHOW PARTITIONS
- SHOW PROPERTY
- SHOW REPOSITORIES
- SHOW RESTORE
- SHOW ROUTINE LOAD
- SHOW ROUTINE LOAD TASK
- SHOW SNAPSHOT
- SHOW TABLES
- SHOW TABLET
- SHOW TRANSACTION
- SPARK LOAD
- STOP ROUTINE LOAD
- STREAM LOAD
- 数据类型
- 辅助命令
- 函数参考
- 系统变量
- 错误码
- 系统限制
- SQL参考
- 管理手册
- 常见问题解答
- 性能测试
- 开发指南
- 外部系统集成
编辑
Lateral Join
背景介绍
「行列转化」是ETL处理过程中常见的操作,Lateral 一个特殊的Join关键字,能够按照每行和内部的子查询或者table function关联,通过Lateral 与unnest配合,我们可以实现一行转多行的功能。
使用说明
使用lateral join 需要打开新版优化器:
set global enable_cbo = true;
Lateral 关键字语法说明:
from table_reference join [lateral] table_reference
Unnest关键字,是一种 table function,可以把数组类型转化成table的多行,配合 Lateral Join 就能实现我们常见的各种行展开逻辑。
SELECT student, score , t.unnest
FROM tests
CROSS JOIN LATERAL UNNEST(scores) AS t ;
SELECT student, score, t.unnest
FROM tests, UNNEST(scores) AS t ;
这里第二种写法是第一种的简写,可以使用Unnest 关键字省略 Lateral Join。
使用举例
当前版本 StarRocks 支持 Bitmap、String、Array、Column 之间的转化关系如下:
配合Unnest,我们可以实现以下功能:
- String 展开成多行
CREATE TABLE lateral_test2 (
`v1` bigint(20) NULL COMMENT "",
`v2` string NULL COMMENT "",
`v3` 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","1,2"), (2, "1,3","1,3");
select * from lateral_test2;
+------+-------+------+
| v1 | v2 | v3 |
+------+-------+------+
| 1 | 1,2,3 | 1,2 |
| 2 | 1,3 | 1,3 |
+------+-------+------+
select v1,unnest from lateral_test2 , unnest(split(v2, ",")) ;
+------+--------+
| v1 | unnest |
+------+--------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 3 |
+------+--------+
---多列unnest时需要指定别名
select v1,t1.unnest as v2,t2.unnest as v3 from lateral_test2 , unnest(split(v2, ",")) t1,unnest(split(v3, ",")) t2 ;
+------+------+------+
| v1 | v2 | v3 |
+------+------+------+
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 1 |
| 1 | 2 | 2 |
| 1 | 3 | 1 |
| 1 | 3 | 2 |
| 2 | 1 | 1 |
| 2 | 1 | 3 |
| 2 | 3 | 1 |
| 2 | 3 | 3 |
+------+------+------+
- Array类型展开成多行
CREATE TABLE lateral_test (
`v1` bigint(20) NULL COMMENT "",
`v2` ARRAY<int> 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 |
+------+------------+--------+
- Bitmap类型输出
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 |
+------+--------+
注意事项
- 当前版本 Lateral join 仅用于和Unnest函数配合使用,实现行转列的功能,后续会支持其他table function / UDTF。
- 当前 Lateral join 还不支持子查询。
- 多列unnest操作需要指定别名。示例如下:
select v1,t1.unnest as v2,t2.unnest as v3
from lateral_test , unnest(v2) t1 ,unnest(v3) t2;