- StarRocks
- 产品简介
- 快速开始
- 表设计
- 导入数据
- 导出数据
- 查询数据源
- 查询加速
- 管理手册
- 部署集群
- 运维集群
- 数据恢复
- 管理用户权限及认证
- 性能调优
- 参考手册
- SQL参考
- 关键字
- 用户账户管理
- 集群管理
- ADMIN CANCEL REPAIR TABLE
- ADMIN CHECK TABLET
- ADMIN REPAIR TABLE
- ADMIN SET CONFIG
- ADMIN SET REPLICA STATUS
- ADMIN SHOW CONFIG
- ADMIN SHOW REPLICA DISTRIBUTION
- ADMIN SHOW REPLICA STATUS
- ALTER RESOURCE GROUP
- ALTER SYSTEM
- CANCEL DECOMMISSION
- CREATE RESOURCE GROUP
- CREATE FILE
- DROP FILE
- DROP RESOURCE GROUP
- INSTALL PLUGIN
- SHOW BACKENDS
- SHOW BROKER
- SHOW COMPUTE NODES
- SHOW FILE
- SHOW FRONTENDS
- SHOW FULL COLUMNS
- SHOW INDEX
- SHOW PLUGINS
- SHOW PROC
- SHOW PROCESSLIST
- SHOW RESOURCE GROUP
- SHOW TABLE STATUS
- UNINSTALL PLUGIN
- DDL
- ALTER DATABASE
- ALTER MATERIALIZED VIEW
- ALTER TABLE
- ALTER VIEW
- ALTER RESOURCE
- ANALYZE TABLE
- BACKUP
- CANCEL ALTER TABLE
- CANCEL BACKUP
- CANCEL RESTORE
- CREATE ANALYZE
- CREATE DATABASE
- CREATE EXTERNAL CATALOG
- CREATE INDEX
- CREATE MATERIALIZED VIEW
- CREATE REPOSITORY
- CREATE RESOURCE
- CREATE TABLE AS SELECT
- CREATE TABLE LIKE
- CREATE TABLE
- CREATE VIEW
- CREATE FUNCTION
- DROP ANALYZE
- DROP STATS
- DROP CATALOG
- DROP DATABASE
- DROP INDEX
- DROP MATERIALIZED VIEW
- DROP REPOSITORY
- DROP RESOURCE
- DROP TABLE
- DROP VIEW
- DROP FUNCTION
- HLL
- KILL ANALYZE
- RECOVER
- REFRESH EXTERNAL TABLE
- RESTORE
- SHOW ANALYZE JOB
- SHOW ANALYZE STATUS
- SHOW META
- SHOW RESOURCES
- SHOW FUNCTION
- TRUNCATE TABLE
- USE
- DML
- ALTER LOAD
- ALTER ROUTINE LOAD
- BROKER LOAD
- CANCEL LOAD
- CANCEL REFRESH MATERIALIZED VIEW
- CANCEL EXPORT
- CREATE ROUTINE LOAD
- DELETE
- EXPORT
- GROUP BY
- INSERT
- PAUSE ROUTINE LOAD
- REFRESH MATERIALIZED VIEW
- RESUME ROUTINE LOAD
- SELECT
- SHOW ALTER TABLE
- SHOW ALTER MATERIALIZED VIEW
- SHOW BACKUP
- SHOW CATALOGS
- SHOW CREATE MATERIALIZED VIEW
- SHOW CREATE TABLE
- SHOW CREATE VIEW
- SHOW DATA
- SHOW DATABASES
- SHOW DELETE
- SHOW DYNAMIC PARTITION TABLES
- SHOW EXPORT
- SHOW LOAD
- SHOW MATERIALIZED VIEW
- 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
- 辅助命令
- 数据类型
- 函数参考
- Java UDF
- 窗口函数
- Lambda 表达式
- 日期函数
- add_months
- adddate
- convert_tz
- current_date
- current_time
- current_timestamp
- date
- date_add
- date_format
- date_slice
- date_sub, subdate
- date_trunc
- datediff
- day
- dayname
- dayofmonth
- dayofweek
- dayofyear
- days_add
- days_diff
- days_sub
- from_days
- from_unixtime
- hour
- hours_add
- hours_diff
- hours_sub
- microseconds_add
- microseconds_sub
- minute
- minutes_add
- minutes_diff
- minutes_sub
- month
- monthname
- months_add
- months_diff
- months_sub
- now
- quarter
- second
- seconds_add
- seconds_diff
- seconds_sub
- str_to_date
- str2date
- time_slice
- time_to_sec
- timediff
- timestamp
- timestampadd
- timestampdiff
- to_days
- to_date
- unix_timestamp
- utc_timestamp
- week
- weekofyear
- weeks_add
- weeks_diff
- weeks_sub
- year
- years_add
- years_diff
- years_sub
- 加密函数
- 地理位置函数
- 字符串函数
- JSON 函数
- Map 函数
- 模糊/正则匹配函数
- 工具函数
- 聚合函数
- Bitmap 函数
- bitmap_and
- bitmap_andnot
- bitmap_contains
- bitmap_count
- bitmap_empty
- bitmap_from_string
- bitmap_hash
- bitmap_has_any
- bitmap_intersect
- bitmap_max
- bitmap_min
- bitmap_or
- bitmap_remove
- bitmap_to_array
- bitmap_to_base64
- base64_to_bitmap
- bitmap_to_string
- bitmap_union
- bitmap_union_count
- bitmap_union_int
- bitmap_xor
- intersect_count
- sub_bitmap
- to_bitmap
- 数组函数
- array_agg
- array_append
- array_avg
- array_concat
- array_contains
- array_contains_all
- array_cum_sum
- array_difference
- array_distinct
- array_filter
- array_intersect
- array_join
- array_length
- array_map
- array_max
- array_min
- arrays_overlap
- array_position
- array_remove
- array_slice
- array_sort
- array_sortby
- array_sum
- array_to_bitmap
- reverse
- unnest
- bit函数
- cast函数
- hash函数
- 条件函数
- 百分位函数
- 标量函数
- 数学函数
- 系统变量
- 用户自定义变量
- 错误码
- 系统限制
- SQL参考
- 常见问题解答
- 性能测试
使用 Lateral Join 实现行转列
本文介绍如何使用 Lateral Join 功能。
「行列转化」是 ETL 处理过程中常见的操作。Lateral Join 功能能够将每行数据和内部的子查询或者 Table Function 关联。通过 Lateral Join 与 Unnest 功能配合,您可以实现一行转多行的功能。Unnest 是一种 Table Function,可以把数组类型转化成 Table 的多行。更多信息,参见 unnest。
注意
- 当前版本中,Lateral Join 仅用于和 Unnest 函数配合使用,实现行转列的功能。后续会支持配合其他 Table Function 或 UDTF。
- 当前版本中,Lateral Join 暂不支持子查询。
开启 CBO 优化器
StarRocks 2.4 版本已集成并启用 CBO 优化器,您无需进行额外操作。
使用 Lateral Join
Lateral Join 功能的语法如下:
from table_reference join [lateral] table_reference;
您可以结合 Lateral Join 功能与 Unnest 功能实现常见的行展开逻辑。
-- 完整 SQL 语句。
SELECT student, score, t.unnest
FROM tests
CROSS JOIN LATERAL UNNEST(scores) AS t;
-- 简化 SQL 语句。您可以使用 Unnest 关键字省略 Lateral Join 关键字。
SELECT student, score, t.unnest
FROM tests, UNNEST(scores) AS t;
注意
多列 Unnest 操作需要指定别名。例如:
select v1, t1.unnest as v2, t2.unnest as v3 from lateral_test, unnest(v2) t1 ,unnest(v3) t2;
。
当前版本 StarRocks 支持 Bitmap、String、Array、Column 之间的转化关系如下:
展开 STRING 类型数据
您可以使用 Lateral Join 功能配合 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");
检查展开前的数据。
mysql> select * from lateral_test2; +------+-------+------+ | v1 | v2 | v3 | +------+-------+------+ | 1 | 1,2,3 | 1,2 | | 2 | 1,3 | 1,3 | +------+-------+------+
展开 STRING 数据。
-- 对单行数据进行 Unnest 操作。 mysql> select v1, unnest from lateral_test2, unnest(split(v2, ",")); +------+--------+ | v1 | unnest | +------+--------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 3 | +------+--------+ -- 对多行数据进行 Unnest 操作,需要指定别名。 mysql> 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 类型数据
您可以使用 Lateral Join 功能配合 Unnest 功能将 ARRAY 类型数据展开为多行数据。从 2.5 版本开始,Unnest 支持传入多个 array,并且多个 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);
检查展开前的数据。
mysql> select * from lateral_test; +------+------------+ | v1 | v2 | +------+------------+ | 1 | [1,2] | | 2 | [1,null,3] | | 3 | NULL | +------+------------+
展开 ARRAY 数据。
mysql> 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 类型数据
您可以使用 Lateral Join 功能配合 Unnest 功能展开 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));
检查当前数据中
v1
以及bitmap_to_string(v2)
。mysql> select v1, bitmap_to_string(v2) from lateral_test3; +------+------------------------+ | v1 | bitmap_to_string(`v2`) | +------+------------------------+ | 1 | 1,2 | | 2 | 3 | +------+------------------------+
插入一行新数据。
mysql> insert into lateral_test3 values (1, to_bitmap(3));
检查新数据中
v1
以及bitmap_to_string(v2)
。mysql> select v1, bitmap_to_string(v2) from lateral_test3; +------+------------------------+ | v1 | bitmap_to_string(`v2`) | +------+------------------------+ | 1 | 1,2,3 | | 2 | 3 | +------+------------------------+
展开 Bitmap 类型数据。
mysql> select v1, unnest from lateral_test3, unnest(bitmap_to_array(v2)); +------+--------+ | v1 | unnest | +------+--------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 3 | +------+--------+