You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by li...@apache.org on 2022/05/25 08:44:24 UTC
[incubator-doris] branch master updated: [doc] Add manual for Array data type and functions (#9700)
This is an automated email from the ASF dual-hosted git repository.
lihaopeng pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-doris.git
The following commit(s) were added to refs/heads/master by this push:
new 2ad691edf7 [doc] Add manual for Array data type and functions (#9700)
2ad691edf7 is described below
commit 2ad691edf72a76d6d0967f0a878724046ebc3dad
Author: camby <10...@qq.com>
AuthorDate: Wed May 25 16:44:20 2022 +0800
[doc] Add manual for Array data type and functions (#9700)
Co-authored-by: cambyzju <zh...@baidu.com>
---
docs/.vuepress/sidebar/en.js | 11 +++
docs/.vuepress/sidebar/zh-CN.js | 11 +++
.../array-functions/array_contains.md | 65 +++++++++++++++++
.../array-functions/array_position.md | 65 +++++++++++++++++
.../sql-functions/array-functions/element_at.md | 81 +++++++++++++++++++++
.../sql-manual/sql-reference/Data-Types/ARRAY.md | 84 ++++++++++++++++++++++
.../array-functions/array_contains.md | 65 +++++++++++++++++
.../array-functions/array_position.md | 65 +++++++++++++++++
.../sql-functions/array-functions/element_at.md | 81 +++++++++++++++++++++
.../sql-manual/sql-reference/Data-Types/ARRAY.md | 84 ++++++++++++++++++++++
10 files changed, 612 insertions(+)
diff --git a/docs/.vuepress/sidebar/en.js b/docs/.vuepress/sidebar/en.js
index cb677e32d3..a05ad55e7c 100644
--- a/docs/.vuepress/sidebar/en.js
+++ b/docs/.vuepress/sidebar/en.js
@@ -547,6 +547,16 @@ module.exports = [
"WINDOW-FUNCTION-ROW-NUMBER",
],
},
+ {
+ title: "Array Functions",
+ directoryPath: "array-functions/",
+ initialOpenGroupIndex: -1,
+ children: [
+ "array_contains",
+ "array_position",
+ "element_at",
+ ],
+ },
"cast",
"digital-masking",
],
@@ -812,6 +822,7 @@ module.exports = [
"STRING",
"TINYINT",
"VARCHAR",
+ "ARRAY",
],
},
{
diff --git a/docs/.vuepress/sidebar/zh-CN.js b/docs/.vuepress/sidebar/zh-CN.js
index e1fad83642..65a972ca13 100644
--- a/docs/.vuepress/sidebar/zh-CN.js
+++ b/docs/.vuepress/sidebar/zh-CN.js
@@ -547,6 +547,16 @@ module.exports = [
"WINDOW-FUNCTION-ROW-NUMBER",
],
},
+ {
+ title: "Array函数",
+ directoryPath: "array-functions/",
+ initialOpenGroupIndex: -1,
+ children: [
+ "array_contains",
+ "array_position",
+ "element_at",
+ ],
+ },
"cast",
"digital-masking",
],
@@ -812,6 +822,7 @@ module.exports = [
"STRING",
"TINYINT",
"VARCHAR",
+ "ARRAY",
],
},
{
diff --git a/docs/en/sql-manual/sql-functions/array-functions/array_contains.md b/docs/en/sql-manual/sql-functions/array-functions/array_contains.md
new file mode 100644
index 0000000000..57091a57ed
--- /dev/null
+++ b/docs/en/sql-manual/sql-functions/array-functions/array_contains.md
@@ -0,0 +1,65 @@
+---
+{
+ "title": "array_contains",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## array_contains
+
+### description
+
+#### Syntax
+
+`BOOLEAN array_contains(ARRAY<T> arr, T value)`
+
+Check if a value presents in an array column. Return below values:
+
+```
+1 - if value presents in an array;
+0 - if value does not present in an array;
+NULL - when array is NULL;
+```
+
+### notice
+
+`Only supported in vectorized engine`
+
+### example
+
+```
+mysql> set enable_vectorized_engine=true;
+
+mysql> SELECT id,c_array,array_contains(c_array, 5) FROM `array_test`;
++------+-----------------+------------------------------+
+| id | c_array | array_contains(`c_array`, 5) |
++------+-----------------+------------------------------+
+| 1 | [1, 2, 3, 4, 5] | 1 |
+| 2 | [6, 7, 8] | 0 |
+| 3 | [] | 0 |
+| 4 | NULL | NULL |
++------+-----------------+------------------------------+
+```
+
+### keywords
+
+ARRAY_CONTAINS
diff --git a/docs/en/sql-manual/sql-functions/array-functions/array_position.md b/docs/en/sql-manual/sql-functions/array-functions/array_position.md
new file mode 100644
index 0000000000..dd47628c27
--- /dev/null
+++ b/docs/en/sql-manual/sql-functions/array-functions/array_position.md
@@ -0,0 +1,65 @@
+---
+{
+ "title": "array_position",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## array_position
+
+### description
+
+#### Syntax
+
+`BIGINT array_position(ARRAY<T> arr, T value)`
+
+Returns a position/index of first occurrence of the `value` in the given array.
+
+```
+position - value position in array (starts with 1);
+0 - if value does not present in the array;
+NULL - when array is NULL or value is NULL.
+```
+
+### notice
+
+`Only supported in vectorized engine`
+
+### example
+
+```
+mysql> set enable_vectorized_engine=true;
+
+mysql> SELECT id,c_array,array_position(c_array, 5) FROM `array_test`;
++------+-----------------+------------------------------+
+| id | c_array | array_position(`c_array`, 5) |
++------+-----------------+------------------------------+
+| 1 | [1, 2, 3, 4, 5] | 5 |
+| 2 | [6, 7, 8] | 0 |
+| 3 | [] | 0 |
+| 4 | NULL | NULL |
++------+-----------------+------------------------------+
+```
+
+### keywords
+
+ARRAY_POSITION
diff --git a/docs/en/sql-manual/sql-functions/array-functions/element_at.md b/docs/en/sql-manual/sql-functions/array-functions/element_at.md
new file mode 100644
index 0000000000..34e083d5d7
--- /dev/null
+++ b/docs/en/sql-manual/sql-functions/array-functions/element_at.md
@@ -0,0 +1,81 @@
+---
+{
+ "title": "element_at",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## element_at
+
+### description
+
+#### Syntax
+
+`T element_at(ARRAY<T> arr, BIGINT position)`
+
+`T arr[position]`
+
+Returns an element of an array located at the input position. If there is no element at the position, return NULL.
+
+`position` is 1-based and support negtive number.
+
+### notice
+
+`Only supported in vectorized engine`
+
+### example
+
+positive `position` example:
+
+```
+mysql> set enable_vectorized_engine=true;
+
+mysql> SELECT id,c_array,element_at(c_array, 5) FROM `array_test`;
++------+-----------------+--------------------------+
+| id | c_array | element_at(`c_array`, 5) |
++------+-----------------+--------------------------+
+| 1 | [1, 2, 3, 4, 5] | 5 |
+| 2 | [6, 7, 8] | NULL |
+| 3 | [] | NULL |
+| 4 | NULL | NULL |
++------+-----------------+--------------------------+
+```
+
+negtive `position` example:
+
+```
+mysql> set enable_vectorized_engine=true;
+
+mysql> SELECT id,c_array,c_array[-2] FROM `array_test`;
++------+-----------------+----------------------------------+
+| id | c_array | %element_extract%(`c_array`, -2) |
++------+-----------------+----------------------------------+
+| 1 | [1, 2, 3, 4, 5] | 4 |
+| 2 | [6, 7, 8] | 7 |
+| 3 | [] | NULL |
+| 4 | NULL | NULL |
++------+-----------------+----------------------------------+
+```
+
+### keywords
+
+ELEMENT_AT, SUBSCRIPT
diff --git a/docs/en/sql-manual/sql-reference/Data-Types/ARRAY.md b/docs/en/sql-manual/sql-reference/Data-Types/ARRAY.md
new file mode 100644
index 0000000000..a157503bc5
--- /dev/null
+++ b/docs/en/sql-manual/sql-reference/Data-Types/ARRAY.md
@@ -0,0 +1,84 @@
+---
+{
+ "title": "ARRAY",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## ARRAY
+
+### description
+
+ARRAY\<T\>
+
+An array of T-type items, it cannot be used as a key column. Now ARRAY can only used in Duplicate Model Tables.
+
+T-type could be any of:
+
+```
+BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DATE,
+DATETIME, CHAR, VARCHAR, STRING
+```
+
+### example
+
+Create table example:
+
+```
+mysql> CREATE TABLE `array_test` (
+ `id` int(11) NULL COMMENT "",
+ `c_array` ARRAY<int(11)> NULL COMMENT ""
+) ENGINE=OLAP
+DUPLICATE KEY(`id`)
+COMMENT "OLAP"
+DISTRIBUTED BY HASH(`id`) BUCKETS 1
+PROPERTIES (
+"replication_allocation" = "tag.location.default: 1",
+"in_memory" = "false",
+"storage_format" = "V2"
+);
+```
+
+Insert data example:
+
+```
+mysql> INSERT INTO `array_test` VALUES (1, [1,2,3,4,5]);
+mysql> INSERT INTO `array_test` VALUES (2, array(6,7,8)), (3, array()), (4, null);
+```
+
+Select data example:
+
+```
+mysql> SELECT * FROM `array_test`;
++------+-----------------+
+| id | c_array |
++------+-----------------+
+| 1 | [1, 2, 3, 4, 5] |
+| 2 | [6, 7, 8] |
+| 3 | [] |
+| 4 | NULL |
++------+-----------------+
+```
+
+### keywords
+
+ ARRAY, array_contains, array_position, element_at
diff --git a/docs/zh-CN/sql-manual/sql-functions/array-functions/array_contains.md b/docs/zh-CN/sql-manual/sql-functions/array-functions/array_contains.md
new file mode 100644
index 0000000000..8cf1fd52bd
--- /dev/null
+++ b/docs/zh-CN/sql-manual/sql-functions/array-functions/array_contains.md
@@ -0,0 +1,65 @@
+---
+{
+ "title": "array_contains",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## array_contains
+
+### description
+
+#### Syntax
+
+`BOOLEAN array_contains(ARRAY<T> arr, T value)`
+
+判断数组中是否包含value。返回结果如下：
+
+```
+1 - value在数组arr中存在；
+0 - value不存在数组arr中；
+NULL - arr为NULL时。
+```
+
+### notice
+
+`仅支持向量化引擎中使用`
+
+### example
+
+```
+mysql> set enable_vectorized_engine=true;
+
+mysql> SELECT id,c_array,array_contains(c_array, 5) FROM `array_test`;
++------+-----------------+------------------------------+
+| id | c_array | array_contains(`c_array`, 5) |
++------+-----------------+------------------------------+
+| 1 | [1, 2, 3, 4, 5] | 1 |
+| 2 | [6, 7, 8] | 0 |
+| 3 | [] | 0 |
+| 4 | NULL | NULL |
++------+-----------------+------------------------------+
+```
+
+### keywords
+
+ARRAY_CONTAINS
diff --git a/docs/zh-CN/sql-manual/sql-functions/array-functions/array_position.md b/docs/zh-CN/sql-manual/sql-functions/array-functions/array_position.md
new file mode 100644
index 0000000000..bce5f3a5c7
--- /dev/null
+++ b/docs/zh-CN/sql-manual/sql-functions/array-functions/array_position.md
@@ -0,0 +1,65 @@
+---
+{
+ "title": "array_position",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## array_position
+
+### description
+
+#### Syntax
+
+`BIGINT array_position(ARRAY<T> arr, T value)`
+
+返回`value`在数组中第一次出现的位置/索引。
+
+```
+position - value在array中的位置（从1开始计算）；
+0 - 如果value在array中不存在；
+NULL - 如果数组为NULL，或者value为NULL。
+```
+
+### notice
+
+`仅支持向量化引擎中使用`
+
+### example
+
+```
+mysql> set enable_vectorized_engine=true;
+
+mysql> SELECT id,c_array,array_position(c_array, 5) FROM `array_test`;
++------+-----------------+------------------------------+
+| id | c_array | array_position(`c_array`, 5) |
++------+-----------------+------------------------------+
+| 1 | [1, 2, 3, 4, 5] | 5 |
+| 2 | [6, 7, 8] | 0 |
+| 3 | [] | 0 |
+| 4 | NULL | NULL |
++------+-----------------+------------------------------+
+```
+
+### keywords
+
+ARRAY_POSITION
diff --git a/docs/zh-CN/sql-manual/sql-functions/array-functions/element_at.md b/docs/zh-CN/sql-manual/sql-functions/array-functions/element_at.md
new file mode 100644
index 0000000000..873524a4e9
--- /dev/null
+++ b/docs/zh-CN/sql-manual/sql-functions/array-functions/element_at.md
@@ -0,0 +1,81 @@
+---
+{
+ "title": "element_at",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## element_at
+
+### description
+
+#### Syntax
+
+`T element_at(ARRAY<T> arr, BIGINT position)`
+
+`T arr[position]`
+
+返回数组中位置为 `position` 的元素。如果该位置上元素不存在，返回NULL。
+
+`position` 从1开始，并且支持负数。
+
+### notice
+
+`仅支持向量化引擎中使用`
+
+### example
+
+`position` 为正数使用范例:
+
+```
+mysql> set enable_vectorized_engine=true;
+
+mysql> SELECT id,c_array,element_at(c_array, 5) FROM `array_test`;
++------+-----------------+--------------------------+
+| id | c_array | element_at(`c_array`, 5) |
++------+-----------------+--------------------------+
+| 1 | [1, 2, 3, 4, 5] | 5 |
+| 2 | [6, 7, 8] | NULL |
+| 3 | [] | NULL |
+| 4 | NULL | NULL |
++------+-----------------+--------------------------+
+```
+
+`position` 为负数使用范例:
+
+```
+mysql> set enable_vectorized_engine=true;
+
+mysql> SELECT id,c_array,c_array[-2] FROM `array_test`;
++------+-----------------+----------------------------------+
+| id | c_array | %element_extract%(`c_array`, -2) |
++------+-----------------+----------------------------------+
+| 1 | [1, 2, 3, 4, 5] | 4 |
+| 2 | [6, 7, 8] | 7 |
+| 3 | [] | NULL |
+| 4 | NULL | NULL |
++------+-----------------+----------------------------------+
+```
+
+### keywords
+
+ELEMENT_AT, SUBSCRIPT
diff --git a/docs/zh-CN/sql-manual/sql-reference/Data-Types/ARRAY.md b/docs/zh-CN/sql-manual/sql-reference/Data-Types/ARRAY.md
new file mode 100644
index 0000000000..cffe63f3be
--- /dev/null
+++ b/docs/zh-CN/sql-manual/sql-reference/Data-Types/ARRAY.md
@@ -0,0 +1,84 @@
+---
+{
+ "title": "ARRAY",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## ARRAY
+
+### description
+
+ARRAY\<T\>
+
+由T类型元素组成的数组，不能作为key列使用。目前支持在Duplicate模型的表中使用。
+
+T支持的类型有：
+
+```
+BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DATE,
+DATETIME, CHAR, VARCHAR, STRING
+```
+
+### example
+
+建表示例如下：
+
+```
+mysql> CREATE TABLE `array_test` (
+ `id` int(11) NULL COMMENT "",
+ `c_array` ARRAY<int(11)> NULL COMMENT ""
+) ENGINE=OLAP
+DUPLICATE KEY(`id`)
+COMMENT "OLAP"
+DISTRIBUTED BY HASH(`id`) BUCKETS 1
+PROPERTIES (
+"replication_allocation" = "tag.location.default: 1",
+"in_memory" = "false",
+"storage_format" = "V2"
+);
+```
+
+插入数据示例：
+
+```
+mysql> INSERT INTO `array_test` VALUES (1, [1,2,3,4,5]);
+mysql> INSERT INTO `array_test` VALUES (2, array(6,7,8)), (3, array()), (4, null);
+```
+
+查询数据示例：
+
+```
+mysql> SELECT * FROM `array_test`;
++------+-----------------+
+| id | c_array |
++------+-----------------+
+| 1 | [1, 2, 3, 4, 5] |
+| 2 | [6, 7, 8] |
+| 3 | [] |
+| 4 | NULL |
++------+-----------------+
+```
+
+### keywords
+
+ ARRAY, array_contains, array_position, element_at
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org