This is an automated email from the ASF dual-hosted git repository. panxiaolei pushed a commit to branch dev_0807_2 in repository https://gitbox.apache.org/repos/asf/doris-website.git
commit c4bd5492b83eba5efd2ced7f439cb683b178129a Author: BiteTheDDDDt <[email protected]> AuthorDate: Thu Aug 7 19:59:54 2025 +0800 update case group-bit/grouping series and histogram --- .../aggregate-functions/group-bit-and.md | 50 +++++++++------ .../aggregate-functions/group-bit-or.md | 46 +++++++++----- .../aggregate-functions/group-bit-xor.md | 52 +++++++++------ .../aggregate-functions/group-concat.md | 50 +++++++++++---- .../sql-functions/aggregate-functions/histogram.md | 74 ++++++++++++++++------ .../other-functions}/grouping-id.md | 0 .../other-functions}/grouping.md | 0 .../sql-functions/window-functions/grouping-id.md | 1 - .../sql-functions/window-functions/grouping.md | 1 - .../aggregate-functions/group-bit-and.md | 48 ++++++++------ .../aggregate-functions/group-bit-or.md | 52 +++++++++------ .../aggregate-functions/group-bit-xor.md | 50 +++++++++------ .../aggregate-functions/group-concat.md | 62 ++++++++++++------ .../sql-functions/aggregate-functions/histogram.md | 66 ++++++++++++++----- .../other-functions}/grouping-id.md | 0 .../other-functions}/grouping.md | 0 .../sql-functions/window-functions/grouping-id.md | 1 - .../sql-functions/window-functions/grouping.md | 1 - sidebars.json | 8 +-- 19 files changed, 370 insertions(+), 192 deletions(-) diff --git a/docs/sql-manual/sql-functions/aggregate-functions/group-bit-and.md b/docs/sql-manual/sql-functions/aggregate-functions/group-bit-and.md index 7ed57868469..0ae3c798d47 100644 --- a/docs/sql-manual/sql-functions/aggregate-functions/group-bit-and.md +++ b/docs/sql-manual/sql-functions/aggregate-functions/group-bit-and.md @@ -1,7 +1,7 @@ --- { -"title": "GROUP_BIT_AND", -"language": "en" + "title": "GROUP_BIT_AND", + "language": "en" } --- @@ -19,37 +19,49 @@ GROUP_BIT_AND(<expr>) | Parameter | Description | | -- | -- | -| `<expr>` | Supports all INT types | +| `<expr>` | Supports types: TinyInt, SmallInt, Integer, BigInt, LargeInt. | ## Return Value -Returns an integer value. +Returns an integer value of the same type as <expr>. If all values are NULL, returns NULL. NULL values are not involved in the bitwise operation. ## Example ```sql -select * from group_bit; +-- setup +create table group_bit( + value int +) distributed by hash(value) buckets 1 +properties ("replication_num"="1"); + +insert into group_bit values + (3), + (1), + (2), + (4), + (NULL); +``` + +```sql +select group_bit_and(value) from group_bit; ``` ```text -+-------+ -| value | -+-------+ -| 3 | -| 1 | -| 2 | -| 4 | -+-------+ ++----------------------+ +| group_bit_and(value) | ++----------------------+ +| 0 | ++----------------------+ ``` ```sql -select group_bit_and(value) from group_bit; +select group_bit_and(value) from group_bit where value is null; ``` ```text -+------------------------+ -| group_bit_and(`value`) | -+------------------------+ -| 0 | -+------------------------+ ++----------------------+ +| group_bit_and(value) | ++----------------------+ +| NULL | ++----------------------+ ``` diff --git a/docs/sql-manual/sql-functions/aggregate-functions/group-bit-or.md b/docs/sql-manual/sql-functions/aggregate-functions/group-bit-or.md index 299b42db0d0..20ffada54b7 100644 --- a/docs/sql-manual/sql-functions/aggregate-functions/group-bit-or.md +++ b/docs/sql-manual/sql-functions/aggregate-functions/group-bit-or.md @@ -19,37 +19,49 @@ GROUP_BIT_OR(<expr>) | Parameter | Description | | -- | -- | -| `<expr>` | Supports all INT types | +| `<expr>` | Supports types: TinyInt, SmallInt, Integer, BigInt, LargeInt. | ## Return Value -Returns an integer value +Returns an integer value of the same type as <expr>. If all values are NULL, returns NULL. NULL values are not involved in the bitwise operation. ## Example ```sql -select * from group_bit; +-- setup +create table group_bit( + value int +) distributed by hash(value) buckets 1 +properties ("replication_num"="1"); + +insert into group_bit values + (3), + (1), + (2), + (4), + (NULL); +``` + +```sql +select group_bit_or(value) from group_bit; ``` ```text -+-------+ -| value | -+-------+ -| 3 | -| 1 | -| 2 | -| 4 | -+-------+ ++---------------------+ +| group_bit_or(value) | ++---------------------+ +| 7 | ++---------------------+ ``` ```sql -mysql> select group_bit_or(value) from group_bit; +select group_bit_or(value) from group_bit where value is null; ``` ```text -+-----------------------+ -| group_bit_or(`value`) | -+-----------------------+ -| 7 | -+-----------------------+ ++---------------------+ +| group_bit_or(value) | ++---------------------+ +| NULL | ++---------------------+ ``` diff --git a/docs/sql-manual/sql-functions/aggregate-functions/group-bit-xor.md b/docs/sql-manual/sql-functions/aggregate-functions/group-bit-xor.md index bfb00ef5df7..988f73af096 100644 --- a/docs/sql-manual/sql-functions/aggregate-functions/group-bit-xor.md +++ b/docs/sql-manual/sql-functions/aggregate-functions/group-bit-xor.md @@ -1,13 +1,13 @@ --- { -"title": "GROUP_BIT_XOR", -"language": "en" + "title": "GROUP_BIT_XOR", + "language": "en" } --- ## Description -Performs a bitwise xor operation on all values in a single integer column or expression. +Performs a bitwise XOR operation on all values in a single integer column or expression. ## Syntax @@ -19,37 +19,49 @@ GROUP_BIT_XOR(<expr>) | Parameter | Description | | -- | -- | -| `<expr>` | Supports all INT types | +| `<expr>` | Supports types: TinyInt, SmallInt, Integer, BigInt, LargeInt. | ## Return Value -Returns an integer value +Returns an integer value of the same type as <expr>. If all values are NULL, returns NULL. NULL values are not involved in the bitwise operation. ## Example ```sql -select * from group_bit; +-- setup +create table group_bit( + value int +) distributed by hash(value) buckets 1 +properties ("replication_num"="1"); + +insert into group_bit values + (3), + (1), + (2), + (4), + (NULL); +``` + +```sql +select group_bit_xor(value) from group_bit; ``` ```text -+-------+ -| value | -+-------+ -| 3 | -| 1 | -| 2 | -| 4 | -+-------+ ++----------------------+ +| group_bit_xor(value) | ++----------------------+ +| 4 | ++----------------------+ ``` ```sql -select group_bit_xor(value) from group_bit; +select group_bit_xor(value) from group_bit where value is null; ``` ```text -+------------------------+ -| group_bit_xor(`value`) | -+------------------------+ -| 4 | -+------------------------+ ++----------------------+ +| group_bit_xor(value) | ++----------------------+ +| NULL | ++----------------------+ ``` diff --git a/docs/sql-manual/sql-functions/aggregate-functions/group-concat.md b/docs/sql-manual/sql-functions/aggregate-functions/group-concat.md index 24bf8fdd9e1..0975a4939be 100644 --- a/docs/sql-manual/sql-functions/aggregate-functions/group-concat.md +++ b/docs/sql-manual/sql-functions/aggregate-functions/group-concat.md @@ -27,22 +27,22 @@ GROUP_CONCAT([DISTINCT] <str>[, <sep>] [ORDER BY { <col_name> | <expr>} [ASC | D ## Return Value Returns a value of type VARCHAR. +If the input data contains NULL, returns NULL. ## Example ```sql -select value from test; -``` - -```text -+-------+ -| value | -+-------+ -| a | -| b | -| c | -| c | -+-------+ +-- setup +create table test( + value varchar(10) +) distributed by hash(value) buckets 1 +properties ("replication_num"="1"); + +insert into test values + ("a"), + ("b"), + ("c"), + ("c"); ``` ```sql @@ -69,7 +69,31 @@ select GROUP_CONCAT(DISTINCT value) from test; +-----------------------+ ``` -```sql +```sql +select GROUP_CONCAT(value ORDER BY value DESC) from test; +``` + +```text ++-----------------------+ +| GROUP_CONCAT(`value`) | ++-----------------------+ +| c, c, b, a | ++-----------------------+ +``` + +```sql +select GROUP_CONCAT(DISTINCT value ORDER BY value DESC) from test; +``` + +```text ++-----------------------+ +| GROUP_CONCAT(`value`) | ++-----------------------+ +| c, b, a | ++-----------------------+ +``` + +```sql select GROUP_CONCAT(value, " ") from test; ``` diff --git a/docs/sql-manual/sql-functions/aggregate-functions/histogram.md b/docs/sql-manual/sql-functions/aggregate-functions/histogram.md index 8a2bab245bf..d5d3fb4a4af 100644 --- a/docs/sql-manual/sql-functions/aggregate-functions/histogram.md +++ b/docs/sql-manual/sql-functions/aggregate-functions/histogram.md @@ -7,7 +7,7 @@ ## Description -The histogram function is used to describe the distribution of the data. It uses an "equal height" bucking strategy, and divides the data into buckets according to the value of the data. It describes each bucket with some simple data, such as the number of values that fall in the bucket. +The histogram function is used to describe the distribution of the data. It uses an "equal height" bucking strategy, and divides the data into buckets according to the value of the data. It describes each bucket with some simple data, such as the number of values that fall in the bucket. Only non-NULL data is counted. ## Alias @@ -17,32 +17,51 @@ HIST ```sql HISTOGRAM(<expr>[, <num_buckets>]) +HIST(<expr>[, <num_buckets>]) ``` ## Parameters | Parameters | Description | | -- | -- | -| `expr` | The expression that needs to be obtained. | -| `num_buckets` | Optional. Limit the number of histogram buckets. The default value is 128.| +| `expr` | The expression to be calculated. Supported types: TinyInt, SmallInt, Integer, BigInt, LargeInt, Float, Double, Decimal, String. | +| `num_buckets` | Optional. Limit the number of histogram buckets. The default value is 128. Supported type: Integer.| ## Return Value -Returns a value of JSON type after histogram estimation. Special cases: -- When the parameter <expr> is NULL, it returns NULL. +Returns a value of JSON type after histogram estimation. If all data in the group is NULL, returns NULL. If there is no valid data, returns a result with num_buckets = 0. ## Example +```sql +-- setup +CREATE TABLE histogram_test ( + c_int INT, + c_float FLOAT, + c_string VARCHAR(20) +) DISTRIBUTED BY HASH(c_int) BUCKETS 1 +PROPERTIES ("replication_num"="1"); + +INSERT INTO histogram_test VALUES + (1, 0.1, 'str1'), + (2, 0.2, 'str2'), + (3, 0.8, 'str3'), + (4, 0.9, 'str4'), + (5, 1.0, 'str5'), + (6, 1.0, 'str6'), + (NULL, NULL, 'str7'); +``` + ```sql SELECT histogram(c_float) FROM histogram_test; ``` ```text +-------------------------------------------------------------------------------------------------------------------------------------+ -| histogram(`c_float`) | +| histogram(c_float) | +-------------------------------------------------------------------------------------------------------------------------------------+ -| {"num_buckets":3,"buckets":[{"lower":"0.1","upper":"0.1","count":1,"pre_sum":0,"ndv":1},...]} | +| {"num_buckets":5,"buckets":[{"lower":"0.1","upper":"0.1","ndv":1,"count":1,"pre_sum":0},{"lower":"0.2","upper":"0.2","ndv":1,"count":1,"pre_sum":1},{"lower":"0.8","upper":"0.8","ndv":1,"count":1,"pre_sum":2},{"lower":"0.9","upper":"0.9","ndv":1,"count":1,"pre_sum":3},{"lower":"1","upper":"1","ndv":1,"count":2,"pre_sum":4}]} | +-------------------------------------------------------------------------------------------------------------------------------------+ ``` @@ -51,11 +70,24 @@ SELECT histogram(c_string, 2) FROM histogram_test; ``` ```text -+-------------------------------------------------------------------------------------------------------------------------------------+ -| histogram(`c_string`) | -+-------------------------------------------------------------------------------------------------------------------------------------+ -| {"num_buckets":2,"buckets":[{"lower":"str1","upper":"str7","count":4,"pre_sum":0,"ndv":3},...]} | -+-------------------------------------------------------------------------------------------------------------------------------------+ ++-----------------------------------------------------------------------------------------------------------------------------------------------------------+ +| histogram(c_string, 2) | ++-----------------------------------------------------------------------------------------------------------------------------------------------------------+ +| {"num_buckets":2,"buckets":[{"lower":"str1","upper":"str4","ndv":4,"count":4,"pre_sum":0},{"lower":"str5","upper":"str7","ndv":3,"count":3,"pre_sum":4}]} | ++-----------------------------------------------------------------------------------------------------------------------------------------------------------+ +``` + +```sql +-- NULL case +SELECT histogram(c_float) FROM histogram_test WHERE c_float IS NULL; +``` + +```text ++--------------------------------+ +| histogram(c_float) | ++--------------------------------+ +| {"num_buckets":0,"buckets":[]} | ++--------------------------------+ ``` Query result description: @@ -91,13 +123,13 @@ Query result description: ```text Field description: -- num_buckets：The number of buckets -- buckets：All buckets - - lower：Upper bound of the bucket - - upper：Lower bound of the bucket - - count：The number of elements contained in the bucket - - pre_sum：The total number of elements in the front bucket - - ndv：The number of different values in the bucket - -> Total number of histogram elements = number of elements in the last bucket(count) + total number of elements in the previous bucket(pre_sum). +- num_buckets: The number of buckets +- buckets: All buckets + - lower: Upper bound of the bucket + - upper: Lower bound of the bucket + - count: The number of elements contained in the bucket + - pre_sum: The total number of elements in the front bucket + - ndv: The number of different values in the bucket + +> Total number of elements in the histogram = count of the last bucket + pre_sum of the last bucket. ``` diff --git a/docs/sql-manual/sql-functions/aggregate-functions/grouping-id.md b/docs/sql-manual/sql-functions/scalar-functions/other-functions/grouping-id.md similarity index 100% rename from docs/sql-manual/sql-functions/aggregate-functions/grouping-id.md rename to docs/sql-manual/sql-functions/scalar-functions/other-functions/grouping-id.md diff --git a/docs/sql-manual/sql-functions/aggregate-functions/grouping.md b/docs/sql-manual/sql-functions/scalar-functions/other-functions/grouping.md similarity index 100% rename from docs/sql-manual/sql-functions/aggregate-functions/grouping.md rename to docs/sql-manual/sql-functions/scalar-functions/other-functions/grouping.md diff --git a/docs/sql-manual/sql-functions/window-functions/grouping-id.md b/docs/sql-manual/sql-functions/window-functions/grouping-id.md deleted file mode 120000 index 8caf06c2b1b..00000000000 --- a/docs/sql-manual/sql-functions/window-functions/grouping-id.md +++ /dev/null @@ -1 +0,0 @@ -../aggregate-functions/grouping-id.md \ No newline at end of file diff --git a/docs/sql-manual/sql-functions/window-functions/grouping.md b/docs/sql-manual/sql-functions/window-functions/grouping.md deleted file mode 120000 index 13f73128503..00000000000 --- a/docs/sql-manual/sql-functions/window-functions/grouping.md +++ /dev/null @@ -1 +0,0 @@ -../aggregate-functions/grouping.md \ No newline at end of file diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-bit-and.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-bit-and.md index e7e6b3696d0..c6848dc5a9f 100644 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-bit-and.md +++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-bit-and.md @@ -7,7 +7,7 @@ ## 描述 -对单个整数列或表达式中的所有值执行按位 and 运算 +对单个整数列或表达式中的所有值执行按位 and 运算。 ## 语法 @@ -19,38 +19,50 @@ GROUP_BIT_AND(<expr>) | 参数 | 说明 | | -- | -- | -| `<expr>` | 支持所有 INT 类型 | +| `<expr>` | 支持类型为 TinyInt，SmallInt，Integer，BigInt，LargeInt。 | ## 返回值 -返回一个整数值 +返回一个整数值，类型与 <expr> 相同。如果所有值均为 NULL，则返回 NULL。NULL 值不参与按位运算。 ## 举例 ```sql -select * from group_bit; +-- setup +create table group_bit( + value int +) distributed by hash(value) buckets 1 +properties ("replication_num"="1"); + +insert into group_bit values + (3), + (1), + (2), + (4), + (NULL); +``` + +```sql +select group_bit_and(value) from group_bit; ``` ```text -+-------+ -| value | -+-------+ -| 3 | -| 1 | -| 2 | -| 4 | -+-------+ ++----------------------+ +| group_bit_and(value) | ++----------------------+ +| 0 | ++----------------------+ ``` ```sql -select group_bit_and(value) from group_bit; +select group_bit_and(value) from group_bit where value is null; ``` ```text -+------------------------+ -| group_bit_and(`value`) | -+------------------------+ -| 0 | -+------------------------+ ++----------------------+ +| group_bit_and(value) | ++----------------------+ +| NULL | ++----------------------+ ``` diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-bit-or.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-bit-or.md index 02adad83b21..25499a38cad 100644 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-bit-or.md +++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-bit-or.md @@ -1,13 +1,13 @@ --- { -"title": "GROUP_BIT_OR", -"language": "zh-CN" + "title": "GROUP_BIT_OR", + "language": "zh-CN" } --- ## 描述 -对单个整数列或表达式中的所有值执行按位 or 运算 +对单个整数列或表达式中的所有值执行按位 or 运算。 ## 语法 @@ -19,37 +19,49 @@ GROUP_BIT_OR(<expr>) | 参数 | 说明 | | -- | -- | -| `<expr>` | 支持所有 INT 类型 | +| `<expr>` | 支持类型为 TinyInt，SmallInt，Integer，BigInt，LargeInt。 | ## 返回值 -返回一个整数值 +返回一个整数值，类型与 <expr> 相同。如果所有值均为 NULL，则返回 NULL。NULL 值不参与按位运算。 ## 举例 ```sql -select * from group_bit; +-- setup +create table group_bit( + value int +) distributed by hash(value) buckets 1 +properties ("replication_num"="1"); + +insert into group_bit values + (3), + (1), + (2), + (4), + (NULL); +``` + +```sql +select group_bit_or(value) from group_bit; ``` ```text -+-------+ -| value | -+-------+ -| 3 | -| 1 | -| 2 | -| 4 | -+-------+ ++---------------------+ +| group_bit_or(value) | ++---------------------+ +| 7 | ++---------------------+ ``` ```sql -mysql> select group_bit_or(value) from group_bit; +select group_bit_or(value) from group_bit where value is null; ``` ```text -+-----------------------+ -| group_bit_or(`value`) | -+-----------------------+ -| 7 | -+-----------------------+ ++---------------------+ +| group_bit_or(value) | ++---------------------+ +| NULL | ++---------------------+ ``` diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-bit-xor.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-bit-xor.md index bb835771f24..1f96df05778 100644 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-bit-xor.md +++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-bit-xor.md @@ -7,7 +7,7 @@ ## 描述 -对单个整数列或表达式中的所有值执行按位 xor 运算 +对单个整数列或表达式中的所有值执行按位 xor 运算。 ## 语法 @@ -19,38 +19,50 @@ GROUP_BIT_XOR(<expr>) | 参数 | 说明 | | -- | -- | -| `<expr>` | 支持所有 INT 类型 | +| `<expr>` | 支持类型为 TinyInt，SmallInt，Integer，BigInt，LargeInt。 | ## 返回值 -返回一个整数值 +返回一个整数值，类型与 <expr> 相同。如果所有值均为 NULL，则返回 NULL。NULL 值不参与按位运算。 ## 举例 +```sql +-- setup +create table group_bit( + value int +) distributed by hash(value) buckets 1 +properties ("replication_num"="1"); + +insert into group_bit values + (3), + (1), + (2), + (4), + (NULL); ``` -select * from group_bit; + +```sql +select group_bit_xor(value) from group_bit; ``` ```text -+-------+ -| value | -+-------+ -| 3 | -| 1 | -| 2 | -| 4 | -+-------+ ++----------------------+ +| group_bit_xor(value) | ++----------------------+ +| 4 | ++----------------------+ ``` -```text -select group_bit_xor(value) from group_bit; +```sql +select group_bit_xor(value) from group_bit where value is null; ``` ```text -+------------------------+ -| group_bit_xor(`value`) | -+------------------------+ -| 4 | -+------------------------+ ++----------------------+ +| group_bit_xor(value) | ++----------------------+ +| NULL | ++----------------------+ ``` diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-concat.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-concat.md index 5b0fa93feda..d0491799bca 100644 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-concat.md +++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/group-concat.md @@ -7,7 +7,7 @@ ## 描述 -GROUP_CONCAT 函数将结果集中的多行结果连接成一个字符串 +GROUP_CONCAT 函数将结果集中的多行结果连接成一个字符串。 ## 语法 @@ -19,30 +19,30 @@ GROUP_CONCAT([DISTINCT] <str>[, <sep>] [ORDER BY { <col_name> | <expr>} [ASC | D | 参数 | 说明 | | -- | -- | -| `<str>` | 必选。需要连接值的表达式 | -| `<sep>` | 可选。字符串之间的连接符号 | -| `<col_name>` | 可选。用于指定排序的列 | -| `<expr>` | 可选。用于指定排序的表达式 | +| `<str>` | 必选，需要连接值的表达式，支持类型为 String 。 | +| `<sep>` | 可选，字符串之间的连接符号。 | +| `<col_name>` | 可选，用于指定排序的列。 | +| `<expr>` | 可选，用于指定排序的表达式。 | ## 返回值 -返回 VARCHAR 类型的数值。 +返回 String 类型的数值。 +如果输入的数据包含 NULL ，返回 NULL 。 ## 举例 ```sql -select value from test; -``` - -```text -+-------+ -| value | -+-------+ -| a | -| b | -| c | -| c | -+-------+ +-- setup +create table test( + value varchar(10) +) distributed by hash(value) buckets 1 +properties ("replication_num"="1"); + +insert into test values + ("a"), + ("b"), + ("c"), + ("c"); ``` ```sql @@ -69,7 +69,31 @@ select GROUP_CONCAT(DISTINCT value) from test; +-----------------------+ ``` -```sql +```sql +select GROUP_CONCAT(value ORDER BY value DESC) from test; +``` + +```text ++-----------------------+ +| GROUP_CONCAT(`value`) | ++-----------------------+ +| c, c, b, a | ++-----------------------+ +``` + +```sql +select GROUP_CONCAT(DISTINCT value ORDER BY value DESC) from test; +``` + +```text ++-----------------------+ +| GROUP_CONCAT(`value`) | ++-----------------------+ +| c, b, a | ++-----------------------+ +``` + +```sql select GROUP_CONCAT(value, " ") from test; ``` diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/histogram.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/histogram.md index 3c42d4cc0fc..5f7f41c55e3 100644 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/histogram.md +++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/histogram.md @@ -7,7 +7,7 @@ ## 描述 -HISTOGRAM（直方图）函数用于描述数据分布情况，它使用“等高”的分桶策略，并按照数据的值大小进行分桶，并用一些简单的数据来描述每个桶，比如落在桶里的值的个数。 +HISTOGRAM（直方图）函数用于描述数据分布情况，它使用“等高”的分桶策略，并按照数据的值大小进行分桶，并用一些简单的数据来描述每个桶，比如落在桶里的值的个数。仅统计非 NULL 的数据。 ## 别名 @@ -17,33 +17,52 @@ HIST ```sql HISTOGRAM(<expr>[, <num_buckets>]) +HIST(<expr>[, <num_buckets>]) ``` ## 参数 | 参数 | 说明 | | -- | -- | -| `expr` | 需要获取第一个值的表达式 | -| `num_buckets` | 可选。用于限制直方图桶（bucket）的数量，默认值 128 | - +| `expr` | 需要获取第一个值的表达式，支持的类型为 TinyInt，SmallInt，Integer，BigInt，LargeInt，Float，Double，Decimal ，String。 | +| `num_buckets` | 可选。用于限制直方图桶（bucket）的数量，默认值 128，支持的类型为 Integer。| ## 返回值 -返回直方图估算后的 JSON 类型的值。特殊情况： -- 当参数<expr>为 NULL 时，返回 NULL。 +返回直方图估算后的 JSON 格式的值，类型为 String 。 +组内没有有效数据时，返回 num_buckets 为0的结果。 ## 举例 +```sql +-- setup +CREATE TABLE histogram_test ( + c_int INT, + c_float FLOAT, + c_string VARCHAR(20) +) DISTRIBUTED BY HASH(c_int) BUCKETS 1 +PROPERTIES ("replication_num"="1"); + +INSERT INTO histogram_test VALUES + (1, 0.1, 'str1'), + (2, 0.2, 'str2'), + (3, 0.8, 'str3'), + (4, 0.9, 'str4'), + (5, 1.0, 'str5'), + (6, 1.0, 'str6'), + (NULL, NULL, 'str7'); +``` + ```sql SELECT histogram(c_float) FROM histogram_test; ``` ```text -+-------------------------------------------------------------------------------------------------------------------------------------+ -| histogram(`c_float`) | -+-------------------------------------------------------------------------------------------------------------------------------------+ -| {"num_buckets":3,"buckets":[{"lower":"0.1","upper":"0.1","count":1,"pre_sum":0,"ndv":1},...]} | -+-------------------------------------------------------------------------------------------------------------------------------------+ ++---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +| histogram(c_float) | ++---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +| {"num_buckets":5,"buckets":[{"lower":"0.1","upper":"0.1","ndv":1,"count":1,"pre_sum":0},{"lower":"0.2","upper":"0.2","ndv":1,"count":1,"pre_sum":1},{"lower":"0.8","upper":"0.8","ndv":1,"count":1,"pre_sum":2},{"lower":"0.9","upper":"0.9","ndv":1,"count":1,"pre_sum":3},{"lower":"1","upper":"1","ndv":1,"count":2,"pre_sum":4}]} | ++---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ ``` ```sql @@ -51,14 +70,27 @@ SELECT histogram(c_string, 2) FROM histogram_test; ``` ```text -+-------------------------------------------------------------------------------------------------------------------------------------+ -| histogram(`c_string`) | -+-------------------------------------------------------------------------------------------------------------------------------------+ -| {"num_buckets":2,"buckets":[{"lower":"str1","upper":"str7","count":4,"pre_sum":0,"ndv":3},...]} | -+-------------------------------------------------------------------------------------------------------------------------------------+ ++-----------------------------------------------------------------------------------------------------------------------------------------------------------+ +| histogram(c_string, 2) | ++-----------------------------------------------------------------------------------------------------------------------------------------------------------+ +| {"num_buckets":2,"buckets":[{"lower":"str1","upper":"str4","ndv":4,"count":4,"pre_sum":0},{"lower":"str5","upper":"str7","ndv":3,"count":3,"pre_sum":4}]} | ++-----------------------------------------------------------------------------------------------------------------------------------------------------------+ +``` + +```sql +-- NULL 处理相关 case +SELECT histogram(c_float) FROM histogram_test WHERE c_float IS NULL; +``` + +```text ++--------------------------------+ +| histogram(c_float) | ++--------------------------------+ +| {"num_buckets":0,"buckets":[]} | ++--------------------------------+ ``` -查询结果说明： +## 查询结果说明： ```json { diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/grouping-id.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/other-functions/grouping-id.md similarity index 100% rename from i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/grouping-id.md rename to i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/other-functions/grouping-id.md diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/grouping.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/other-functions/grouping.md similarity index 100% rename from i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/grouping.md rename to i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/other-functions/grouping.md diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/grouping-id.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/grouping-id.md deleted file mode 120000 index 8caf06c2b1b..00000000000 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/grouping-id.md +++ /dev/null @@ -1 +0,0 @@ -../aggregate-functions/grouping-id.md \ No newline at end of file diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/grouping.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/grouping.md deleted file mode 120000 index 13f73128503..00000000000 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/grouping.md +++ /dev/null @@ -1 +0,0 @@ -../aggregate-functions/grouping.md \ No newline at end of file diff --git a/sidebars.json b/sidebars.json index 4cd8c045bca..58f2844c1fa 100644 --- a/sidebars.json +++ b/sidebars.json @@ -1645,7 +1645,9 @@ "sql-manual/sql-functions/scalar-functions/other-functions/convert-to", "sql-manual/sql-functions/scalar-functions/other-functions/esquery", "sql-manual/sql-functions/scalar-functions/other-functions/field", - "sql-manual/sql-functions/scalar-functions/other-functions/g" + "sql-manual/sql-functions/scalar-functions/other-functions/g", + "sql-manual/sql-functions/scalar-functions/other-functions/grouping", + "sql-manual/sql-functions/scalar-functions/other-functions/grouping-id" ] }, { @@ -1703,8 +1705,6 @@ "sql-manual/sql-functions/aggregate-functions/group-bit-xor", "sql-manual/sql-functions/aggregate-functions/group-bitmap-xor", "sql-manual/sql-functions/aggregate-functions/group-concat", - "sql-manual/sql-functions/aggregate-functions/grouping", - "sql-manual/sql-functions/aggregate-functions/grouping-id", "sql-manual/sql-functions/aggregate-functions/histogram", "sql-manual/sql-functions/aggregate-functions/hll-raw-agg", "sql-manual/sql-functions/aggregate-functions/hll-union-agg", @@ -1793,8 +1793,6 @@ "sql-manual/sql-functions/window-functions/group-bit-xor", "sql-manual/sql-functions/window-functions/group-bitmap-xor", "sql-manual/sql-functions/window-functions/group-concat", - "sql-manual/sql-functions/window-functions/grouping", - "sql-manual/sql-functions/window-functions/grouping-id", "sql-manual/sql-functions/window-functions/histogram", "sql-manual/sql-functions/window-functions/hll-raw-agg", "sql-manual/sql-functions/window-functions/hll-union-agg", --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
