tencent cloud

Stream Compute Service

DokumentasiStream Compute ServiceSQL Developer GuideOperators and Built-in FunctionsTable-Valued Functions

Table-Valued Functions

Download
Mode fokus
Ukuran font
Terakhir diperbarui: 2026-08-11 14:58:16
Diterjemahkan oleh AI

Function

Function Name
Capability Description
GENERATE_SERIES
GENERATE_SERIES is used to generate a continuous integer series and expand the series into multiple rows. This function is commonly used to construct test data, expand continuous numbering based on start and end values, fill in missing sequences, or expand interval fields into multiple rows.
JSON_TUPLE
JSON_TUPLE is used to extract multiple fields from a JSON string at once and return them as multiple columns. Compared to repeatedly calling single-field JSON extraction functions, JSON_TUPLE is more suitable for scenarios where multiple fields need to be extracted from the same JSON string. It is commonly used to parse JSON fields in logs, extract event attributes from tracking points, or convert semi-structured JSON content into relational columns.
MULTI_KEYVALUE
MULTI_KEYVALUE is used to extract the values corresponding to multiple keys from a Key-Value format string at once and return them as multiple columns. It can be considered a multi-field version of KEYVALUE. When multiple keys need to be extracted from the same string, using MULTI_KEYVALUE avoids repeatedly parsing the same field. It is commonly used in scenarios such as parsing extended log fields, parsing URL query parameters, and parsing event attributes from tracking points.
STRING_SPLIT
STRING_SPLIT is used to split a string by a specified delimiter and expand the resulting substrings into multiple rows. This function is commonly used to split comma-separated tags, expand multi-value fields into multiple rows, or convert a string list into row-level data, facilitating subsequent filtering, joining, or aggregation.

GENERATE_SERIES

Supported versions: Version 1.16 and above.
Feature description: GENERATE_SERIES generates a continuous integer sequence and expands the sequence into multiple rows. This function is commonly used to construct test data, expand continuous numbering based on start and end values, fill in missing sequences, or expand interval fields into multiple rows.
Syntax: GENERATE_SERIES(start, stop)

SELECT value
FROM LATERAL TABLE(GENERATE_SERIES(start, stop)) AS T(value);
Parameter description:
Parameter
Type
Required
Description
start
BIGINT / INT
Yes
The starting value of the sequence, inclusive.
stop
BIGINT / INT
Yes
The ending value of the sequence, inclusive.
Return value:
A table-valued function that returns a column of integer sequences.
Returned Column
Type
Description
User-specified column name
BIGINT
The generated sequence value. The column name is specified by the alias in LATERAL TABLE ... AS T(value).
Example:
Generate an integer sequence from 1 to 5:
SELECT value
FROM LATERAL TABLE(GENERATE_SERIES(1, 5)) AS T(value);
Returned results:
value
1
2
3
4
5
Must-Knows
If the generation range is large, a large number of rows will be expanded, which may cause a significant increase in data volume.
When using it, control the interval range to prevent a single piece of input data from being expanded into too many output rows.

JSON_TUPLE

Supported versions: Version 1.16 and above.
Feature description: JSON_TUPLE extracts multiple fields from a JSON string at once and returns them as multiple columns. Compared to calling a single-field JSON extraction function multiple times, JSON_TUPLE is more suitable for scenarios where multiple fields need to be extracted from the same JSON string. It is commonly used to parse JSON fields in logs, extract event attributes from tracking points, or convert semi-structured JSON content into relational columns.
Syntax: JSON_TUPLE(json_str, key1[, key2, key3, ...])
Use it with LATERAL TABLE:
SELECT value
FROM LATERAL TABLE(GENERATE_SERIES(start, stop)) AS T(value);
Parameter description:
Parameter
Type
Required
Description
start
BIGINT / INT
Yes
The starting value of the sequence, inclusive.
stop
BIGINT / INT
Yes
The ending value of the sequence, inclusive.
Return value:
A table-valued function that returns a column of integer sequences.
Returned Column
Type
Description
User-specified column name
BIGINT
The generated sequence value. The column name is specified by the alias in LATERAL TABLE ... AS T(value).
Example:
Generate an integer sequence from 1 to 5.
SELECT value
FROM LATERAL TABLE(GENERATE_SERIES(1, 5)) AS T(value);
Returned results:
value
1
2
3
4
5
Note:
If the generation range is large, a large number of rows will be expanded, which may cause a significant increase in data volume.
When using it, control the interval range to prevent a single piece of input data from being expanded into too many output rows.

MULTI_KEYVALUE

Supported versions: Version 1.16 and above.
Feature description: MULTI_KEYVALUE extracts the values corresponding to multiple keys from a Key-Value format string at once and returns them as multiple columns. It can be considered a multi-field version of KEYVALUE. When multiple keys need to be extracted from the same string, using MULTI_KEYVALUE avoids repeatedly parsing the same field. It is commonly used in scenarios such as log extended field parsing, URL query parameter parsing, and tracking point attribute parsing.
Syntax: MULTI_KEYVALUE(str, split1, split2, key_name1[, key_name2, key_name3, ...])
Use it with LATERAL TABLE:
SELECT
  kv.value1,
  kv.value2
FROM source_table AS s,
LATERAL TABLE(
  MULTI_KEYVALUE(s.ext, '&', '=', 'key1', 'key2')
) AS kv(value1, value2);
Parameter description:
Parameter
Type
Required
Description
str
STRING
Yes
The Key-Value string to be parsed.
split1
STRING
Yes
The primary delimiter, used to separate multiple Key-Value segments.
split2
STRING
Yes
The secondary delimiter, used to separate Key and Value.
key_name1
STRING
Yes
The name of the first Key to be extracted.
key_name2, key_name3, ...
STRING
No
The names of other Keys to be extracted subsequently.
Return value:
A table-valued function that returns multiple columns of strings.
Returned Column
Type
Description
User-specified column name
STRING
Each Key corresponds to a returned column. The column name is specified by LATERAL TABLE ... AS alias(...).
Example:
Example 1: Extract multiple keys at once.
SELECT
  uid,
  os,
  version
FROM LATERAL TABLE(
  MULTI_KEYVALUE('uid=10001;os=ios;version=1.2.0', ';', '=', 'uid', 'os', 'version')
) AS T(uid, os, version);

Returned results:
uid
os
version
10001
ios
1.2.0
Use limits
MULTI_KEYVALUE is a table-valued function and must be used with LATERAL TABLE.
The number of returned columns must match the number of specified keys.
If a Key does not exist, the corresponding column returns NULL.
If you only need to extract a single Key, you can use KEYVALUE.

STRING_SPLIT

Supported versions: Version 1.16 and above.
Feature description: STRING_SPLIT is used to split a string by a specified delimiter and expand the resulting substrings into multiple rows.
This function is commonly used to split comma-separated tags, expand multi-value fields into multiple rows, or convert string lists into row-level data, facilitating subsequent filtering, joining, or aggregation.
Syntax: STRING_SPLIT(str, delimiter)
Use it with LATERAL TABLE:
SELECT
  t.value
FROM LATERAL TABLE(STRING_SPLIT(str, delimiter)) AS t(value);
Parameter description:
Parameter
Type
Required
Description
str
STRING
Yes
The string to be split.
delimiter
STRING
Yes
Delimiter.
Return value:
A table-valued function that returns a column of strings.
Returned Column
Type
Description
User-specified column name
STRING
The substring after splitting. The column name is specified by LATERAL TABLE ... AS alias(...).
Example:
1. Example 1: Split a comma-separated string.
SELECT value
FROM LATERAL TABLE(
  STRING_SPLIT('apple,banana,orange', ',')
) AS T(value);

Returned results:
value
apple
banana
orange
2. Example 2: The delimiter does not exist.
SELECT item
FROM LATERAL TABLE(
  STRING_SPLIT('abc', ',')
) AS T(item);
Returned results:
item
abc
Note:
Splitting expands a single row of input into multiple rows of output. Note that this can cause a significant increase in data volume.
If the delimiter does not exist, the original string is typically returned as a single row.
Topik sebelumnya: Other FunctionsTopik selanjutnya: Naming Rules

Bantuan dan Dukungan

Apakah halaman ini membantu?

masukan