This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch branch-1.2-lts
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-1.2-lts by this push:
new 378af07893 [Feature](function) Support width_bucket function (#14396)
378af07893 is described below
commit 378af078935075966927dcf253f5c95019201a4a
Author: abmdocrt <[email protected]>
AuthorDate: Thu Jan 12 13:59:21 2023 +0800
[Feature](function) Support width_bucket function (#14396)
---
be/src/vec/CMakeLists.txt | 2 +
be/src/vec/functions/function_width_bucket.cpp | 142 +++++++++++++++++++
be/src/vec/functions/simple_function_factory.h | 2 +
.../docs/sql-manual/sql-functions/width-bucket.md | 154 +++++++++++++++++++++
docs/sidebars.json | 3 +-
.../docs/sql-manual/sql-functions/width-bucket.md | 153 ++++++++++++++++++++
gensrc/script/doris_builtins_functions.py | 13 ++
.../test_width_bucket_function.out | 95 +++++++++++++
.../test_width_bucket_function.groovy | 88 ++++++++++++
9 files changed, 651 insertions(+), 1 deletion(-)
diff --git a/be/src/vec/CMakeLists.txt b/be/src/vec/CMakeLists.txt
index 723f83758f..4220b23e00 100644
--- a/be/src/vec/CMakeLists.txt
+++ b/be/src/vec/CMakeLists.txt
@@ -228,6 +228,8 @@ set(VEC_FILES
functions/functions_multi_string_position.cpp
functions/functions_multi_string_search.cpp
functions/function_running_difference.cpp
+ functions/function_width_bucket.cpp
+
olap/vgeneric_iterators.cpp
olap/vcollect_iterator.cpp
olap/block_reader.cpp
diff --git a/be/src/vec/functions/function_width_bucket.cpp
b/be/src/vec/functions/function_width_bucket.cpp
new file mode 100644
index 0000000000..e88a936816
--- /dev/null
+++ b/be/src/vec/functions/function_width_bucket.cpp
@@ -0,0 +1,142 @@
+// 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.
+
+#include "vec/columns/columns_number.h"
+#include "vec/data_types/data_type_nullable.h"
+#include "vec/data_types/data_type_number.h"
+#include "vec/functions/function.h"
+#include "vec/functions/function_helpers.h"
+#include "vec/functions/simple_function_factory.h"
+
+namespace doris::vectorized {
+class FunctionWidthBucket : public IFunction {
+public:
+ static constexpr auto name = "width_bucket";
+ static FunctionPtr create() { return
std::make_shared<FunctionWidthBucket>(); }
+
+ /// Get function name.
+ String get_name() const override { return name; }
+
+ bool is_variadic() const override { return false; }
+
+ size_t get_number_of_arguments() const override { return 4; }
+
+ DataTypePtr get_return_type_impl(const DataTypes& arguments) const
override {
+ return std::make_shared<DataTypeInt64>();
+ }
+
+ Status execute_impl(FunctionContext* context, Block& block, const
ColumnNumbers& arguments,
+ size_t result, size_t input_rows_count) override {
+ ColumnPtr expr_ptr =
+
block.get_by_position(arguments[0]).column->convert_to_full_column_if_const();
+ ColumnPtr min_value_ptr =
+
block.get_by_position(arguments[1]).column->convert_to_full_column_if_const();
+ ColumnPtr max_value_ptr =
+
block.get_by_position(arguments[2]).column->convert_to_full_column_if_const();
+ ColumnPtr num_buckets_ptr =
+
block.get_by_position(arguments[3]).column->convert_to_full_column_if_const();
+ int64_t num_buckets = num_buckets_ptr->get_int(0);
+
+ auto nested_column_ptr = ColumnInt64::create(input_rows_count, 0);
+ DataTypePtr expr_type = block.get_by_position(arguments[0]).type;
+
+ _execute_by_type(*expr_ptr, *min_value_ptr, *max_value_ptr,
num_buckets, *nested_column_ptr,
+ expr_type);
+
+ block.replace_by_position(result, std::move(nested_column_ptr));
+ return Status::OK();
+ }
+
+private:
+ template <typename ColumnType>
+ void _execute(const IColumn& expr_column, const IColumn& min_value_column,
+ const IColumn& max_value_column, const int64_t num_buckets,
+ IColumn& nested_column) {
+ const ColumnType& expr_column_concrete = reinterpret_cast<const
ColumnType&>(expr_column);
+ const ColumnType& min_value_column_concrete =
+ reinterpret_cast<const ColumnType&>(min_value_column);
+ const ColumnType& max_value_column_concrete =
+ reinterpret_cast<const ColumnType&>(max_value_column);
+ ColumnInt64& nested_column_concrete =
reinterpret_cast<ColumnInt64&>(nested_column);
+
+ auto min_value = min_value_column_concrete.get_data()[0];
+ auto max_value = max_value_column_concrete.get_data()[0];
+
+ size_t input_rows_count = expr_column.size();
+
+ for (size_t i = 0; i < input_rows_count; ++i) {
+ if (expr_column_concrete.get_data()[i] < min_value) {
+ continue;
+ } else if (expr_column_concrete.get_data()[i] >= max_value) {
+ nested_column_concrete.get_data()[i] = num_buckets + 1;
+ } else {
+ if ((max_value - min_value) / num_buckets == 0) {
+ continue;
+ }
+ nested_column_concrete.get_data()[i] =
+ (int64_t)(1 + (expr_column_concrete.get_data()[i] -
min_value) /
+ ((max_value - min_value) /
num_buckets));
+ }
+ }
+ }
+
+ void _execute_by_type(const IColumn& expr_column, const IColumn&
min_value_column,
+ const IColumn& max_value_column, const int64_t
num_buckets,
+ IColumn& nested_column_column, DataTypePtr&
expr_type) {
+ WhichDataType which(remove_nullable(expr_type));
+ if (which.is_int8()) {
+ _execute<ColumnInt8>(expr_column, min_value_column,
max_value_column, num_buckets,
+ nested_column_column);
+ } else if (which.is_int16()) {
+ _execute<ColumnInt16>(expr_column, min_value_column,
max_value_column, num_buckets,
+ nested_column_column);
+ } else if (which.is_int32()) {
+ _execute<ColumnInt32>(expr_column, min_value_column,
max_value_column, num_buckets,
+ nested_column_column);
+ } else if (which.is_int64()) {
+ _execute<ColumnInt64>(expr_column, min_value_column,
max_value_column, num_buckets,
+ nested_column_column);
+ } else if (which.is_float32()) {
+ _execute<ColumnFloat32>(expr_column, min_value_column,
max_value_column, num_buckets,
+ nested_column_column);
+ } else if (which.is_float64()) {
+ _execute<ColumnFloat64>(expr_column, min_value_column,
max_value_column, num_buckets,
+ nested_column_column);
+ } else if (which.is_decimal128()) {
+ _execute<ColumnDecimal128>(expr_column, min_value_column,
max_value_column, num_buckets,
+ nested_column_column);
+ } else if (which.is_date()) {
+ _execute<ColumnDate>(expr_column, min_value_column,
max_value_column, num_buckets,
+ nested_column_column);
+ } else if (which.is_date_v2()) {
+ _execute<ColumnDateV2>(expr_column, min_value_column,
max_value_column, num_buckets,
+ nested_column_column);
+ } else if (which.is_date_time()) {
+ _execute<ColumnDateTime>(expr_column, min_value_column,
max_value_column, num_buckets,
+ nested_column_column);
+ } else if (which.is_date_time_v2()) {
+ _execute<ColumnDateTimeV2>(expr_column, min_value_column,
max_value_column, num_buckets,
+ nested_column_column);
+ }
+ }
+};
+
+void register_function_width_bucket(SimpleFunctionFactory& factory) {
+ factory.register_function<FunctionWidthBucket>();
+}
+
+} // namespace doris::vectorized
\ No newline at end of file
diff --git a/be/src/vec/functions/simple_function_factory.h
b/be/src/vec/functions/simple_function_factory.h
index 0839870a67..a8bcb3383b 100644
--- a/be/src/vec/functions/simple_function_factory.h
+++ b/be/src/vec/functions/simple_function_factory.h
@@ -82,6 +82,7 @@ void register_function_array(SimpleFunctionFactory& factory);
void register_function_geo(SimpleFunctionFactory& factory);
void register_function_multi_string_position(SimpleFunctionFactory& factory);
void register_function_multi_string_search(SimpleFunctionFactory& factory);
+void register_function_width_bucket(SimpleFunctionFactory& factory);
void register_function_encryption(SimpleFunctionFactory& factory);
void register_function_regexp_extract(SimpleFunctionFactory& factory);
@@ -228,6 +229,7 @@ public:
register_function_url(instance);
register_function_multi_string_position(instance);
register_function_multi_string_search(instance);
+ register_function_width_bucket(instance);
});
return instance;
}
diff --git a/docs/en/docs/sql-manual/sql-functions/width-bucket.md
b/docs/en/docs/sql-manual/sql-functions/width-bucket.md
new file mode 100644
index 0000000000..af81320cdd
--- /dev/null
+++ b/docs/en/docs/sql-manual/sql-functions/width-bucket.md
@@ -0,0 +1,154 @@
+---
+{
+ "title": "width_bucket",
+ "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.
+-->
+
+## width_bucket
+
+### Description
+
+Constructs equi-width histograms, in which the histogram range is divided into
intervals of identical size, and returns the bucket number into which the value
of an expression falls, after it has been evaluated. The function returns an
integer value or null (if any input is null).
+
+#### Syntax
+
+```sql
+width_bucket(expr, min_value, max_value, num_buckets)
+```
+
+#### Arguments
+`expr` -
+The expression for which the histogram is created. This expression must
evaluate to a numeric value or to a value that can be implicitly converted to a
numeric value.
+
+The value must be within the range of `-(2^53 - 1)` to `2^53 - 1` (inclusive).
+
+`min_value` and `max_value` -
+The low and high end points of the acceptable range for the expression. The
end points must also evaluate to numeric values and not be equal.
+
+The low and high end points must be within the range of `-(2^53 - 1)` to `2^53
- 1` (inclusive). In addition, the difference between these points must be less
than `2^53` (i.e. `abs(max_value - min_value) < 2^53)`.
+
+`num_buckets` -
+The desired number of buckets; must be a positive integer value. A value from
the expression is assigned to each bucket, and the function then returns the
corresponding bucket number.
+
+
+#### Returned value
+It returns the bucket number into which the value of an expression falls.
+
+When an expression falls outside the range, the function returns:
+
+`0` if the expression is less than `min_value`.
+
+`num_buckets + 1` if the expression is greater than or equal to `max_value`.
+
+`null` if any input is `null`.
+
+### example
+
+```sql
+DROP TABLE IF EXISTS width_bucket_test;
+
+CREATE TABLE IF NOT EXISTS width_bucket_test (
+ `k1` int NULL COMMENT "",
+ `v1` date NULL COMMENT "",
+ `v2` double NULL COMMENT "",
+ `v3` bigint NULL COMMENT ""
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k1`)
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "storage_format" = "V2"
+ );
+
+INSERT INTO width_bucket_test VALUES (1, "2022-11-18", 290000.00, 290000),
+ (2, "2023-11-18", 320000.00, 320000),
+ (3, "2024-11-18", 399999.99, 399999),
+ (4, "2025-11-18", 400000.00, 400000),
+ (5, "2026-11-18", 470000.00, 470000),
+ (6, "2027-11-18", 510000.00, 510000),
+ (7, "2028-11-18", 610000.00, 610000),
+ (8, null, null, null);
+
+SELECT * FROM width_bucket_test ORDER BY k1;
+
++------+------------+-----------+--------+
+| k1 | v1 | v2 | v3 |
++------+------------+-----------+--------+
+| 1 | 2022-11-18 | 290000 | 290000 |
+| 2 | 2023-11-18 | 320000 | 320000 |
+| 3 | 2024-11-18 | 399999.99 | 399999 |
+| 4 | 2025-11-18 | 400000 | 400000 |
+| 5 | 2026-11-18 | 470000 | 470000 |
+| 6 | 2027-11-18 | 510000 | 510000 |
+| 7 | 2028-11-18 | 610000 | 610000 |
+| 8 | NULL | NULL | NULL |
++------+------------+-----------+--------+
+
+SELECT k1, v1, v2, v3, width_bucket(v1, date('2023-11-18'),
date('2027-11-18'), 4) AS w FROM width_bucket_test ORDER BY k1;
+
++------+------------+-----------+--------+------+
+| k1 | v1 | v2 | v3 | w |
++------+------------+-----------+--------+------+
+| 1 | 2022-11-18 | 290000 | 290000 | 0 |
+| 2 | 2023-11-18 | 320000 | 320000 | 1 |
+| 3 | 2024-11-18 | 399999.99 | 399999 | 2 |
+| 4 | 2025-11-18 | 400000 | 400000 | 3 |
+| 5 | 2026-11-18 | 470000 | 470000 | 4 |
+| 6 | 2027-11-18 | 510000 | 510000 | 5 |
+| 7 | 2028-11-18 | 610000 | 610000 | 5 |
+| 8 | NULL | NULL | NULL | NULL |
++------+------------+-----------+--------+------+
+
+SELECT k1, v1, v2, v3, width_bucket(v2, 200000, 600000, 4) AS w FROM
width_bucket_test ORDER BY k1;
+
++------+------------+-----------+--------+------+
+| k1 | v1 | v2 | v3 | w |
++------+------------+-----------+--------+------+
+| 1 | 2022-11-18 | 290000 | 290000 | 1 |
+| 2 | 2023-11-18 | 320000 | 320000 | 2 |
+| 3 | 2024-11-18 | 399999.99 | 399999 | 2 |
+| 4 | 2025-11-18 | 400000 | 400000 | 3 |
+| 5 | 2026-11-18 | 470000 | 470000 | 3 |
+| 6 | 2027-11-18 | 510000 | 510000 | 4 |
+| 7 | 2028-11-18 | 610000 | 610000 | 5 |
+| 8 | NULL | NULL | NULL | NULL |
++------+------------+-----------+--------+------+
+
+SELECT k1, v1, v2, v3, width_bucket(v3, 200000, 600000, 4) AS w FROM
width_bucket_test ORDER BY k1;
+
++------+------------+-----------+--------+------+
+| k1 | v1 | v2 | v3 | w |
++------+------------+-----------+--------+------+
+| 1 | 2022-11-18 | 290000 | 290000 | 1 |
+| 2 | 2023-11-18 | 320000 | 320000 | 2 |
+| 3 | 2024-11-18 | 399999.99 | 399999 | 2 |
+| 4 | 2025-11-18 | 400000 | 400000 | 3 |
+| 5 | 2026-11-18 | 470000 | 470000 | 3 |
+| 6 | 2027-11-18 | 510000 | 510000 | 4 |
+| 7 | 2028-11-18 | 610000 | 610000 | 5 |
+| 8 | NULL | NULL | NULL | NULL |
++------+------------+-----------+--------+------+
+
+```
+### keywords
+WIDTH_BUCKET
\ No newline at end of file
diff --git a/docs/sidebars.json b/docs/sidebars.json
index 3b439925eb..439cc9e03f 100644
--- a/docs/sidebars.json
+++ b/docs/sidebars.json
@@ -666,7 +666,8 @@
]
},
"sql-manual/sql-functions/cast",
- "sql-manual/sql-functions/digital-masking"
+ "sql-manual/sql-functions/digital-masking",
+ "sql-manual/sql-functions/width-bucket"
]
},
{
diff --git a/docs/zh-CN/docs/sql-manual/sql-functions/width-bucket.md
b/docs/zh-CN/docs/sql-manual/sql-functions/width-bucket.md
new file mode 100644
index 0000000000..6aad2426f7
--- /dev/null
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/width-bucket.md
@@ -0,0 +1,153 @@
+---
+{
+ "title": "width_bucket",
+ "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.
+-->
+
+## width_bucket
+
+### Description
+
+构造等宽直方图，其中直方图范围被划分为相同大小的区间，并在计算后返回表达式的值所在的桶号。该函数返回一个整数值或空值（如果任何输入为空值则返回空值）。
+
+#### Syntax
+
+```sql
+width_bucket(expr, min_value, max_value, num_buckets)
+```
+
+#### Arguments
+`expr` -
+创建直方图的表达式。此表达式必须计算为数值或可隐式转换为数值的值。
+
+此值的范围必须为 `-(2^53 - 1)` 到 `2^53 - 1` (含).
+
+`min_value` 和 `max_value` -
+表达式可接受范围的最低值点和最高值点。这两个参数必须为数值并且不相等。
+
+最低值点和最高值点的范围必须为 `-(2^53 - 1)` to `2^53 - 1` (含)). 此外，最高值点与最低值点的差必须小于 `2^53`
(例如： `abs(max_value - min_value) < 2^53)`.
+
+`num_buckets` -
+分桶的数量，必须是正整数值。将表达式中的一个值分配给每个存储桶，然后该函数返回相应的存储桶编号。
+
+#### Returned value
+返回表达式值所在的桶号。
+
+当表达式超出范围时，函数返回规则如下：
+
+如果表达式的值小于`min_value`返回`0`.
+
+如果表达式的值大于或等于`max_value`返回`num_buckets + 1`.
+
+如果任意参数为`null`返回`null`.
+
+### example
+
+```sql
+DROP TABLE IF EXISTS width_bucket_test;
+
+CREATE TABLE IF NOT EXISTS width_bucket_test (
+ `k1` int NULL COMMENT "",
+ `v1` date NULL COMMENT "",
+ `v2` double NULL COMMENT "",
+ `v3` bigint NULL COMMENT ""
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k1`)
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "storage_format" = "V2"
+ );
+
+INSERT INTO width_bucket_test VALUES (1, "2022-11-18", 290000.00, 290000),
+ (2, "2023-11-18", 320000.00, 320000),
+ (3, "2024-11-18", 399999.99, 399999),
+ (4, "2025-11-18", 400000.00, 400000),
+ (5, "2026-11-18", 470000.00, 470000),
+ (6, "2027-11-18", 510000.00, 510000),
+ (7, "2028-11-18", 610000.00, 610000),
+ (8, null, null, null);
+
+SELECT * FROM width_bucket_test ORDER BY k1;
+
++------+------------+-----------+--------+
+| k1 | v1 | v2 | v3 |
++------+------------+-----------+--------+
+| 1 | 2022-11-18 | 290000 | 290000 |
+| 2 | 2023-11-18 | 320000 | 320000 |
+| 3 | 2024-11-18 | 399999.99 | 399999 |
+| 4 | 2025-11-18 | 400000 | 400000 |
+| 5 | 2026-11-18 | 470000 | 470000 |
+| 6 | 2027-11-18 | 510000 | 510000 |
+| 7 | 2028-11-18 | 610000 | 610000 |
+| 8 | NULL | NULL | NULL |
++------+------------+-----------+--------+
+
+SELECT k1, v1, v2, v3, width_bucket(v1, date('2023-11-18'),
date('2027-11-18'), 4) AS w FROM width_bucket_test ORDER BY k1;
+
++------+------------+-----------+--------+------+
+| k1 | v1 | v2 | v3 | w |
++------+------------+-----------+--------+------+
+| 1 | 2022-11-18 | 290000 | 290000 | 0 |
+| 2 | 2023-11-18 | 320000 | 320000 | 1 |
+| 3 | 2024-11-18 | 399999.99 | 399999 | 2 |
+| 4 | 2025-11-18 | 400000 | 400000 | 3 |
+| 5 | 2026-11-18 | 470000 | 470000 | 4 |
+| 6 | 2027-11-18 | 510000 | 510000 | 5 |
+| 7 | 2028-11-18 | 610000 | 610000 | 5 |
+| 8 | NULL | NULL | NULL | NULL |
++------+------------+-----------+--------+------+
+
+SELECT k1, v1, v2, v3, width_bucket(v2, 200000, 600000, 4) AS w FROM
width_bucket_test ORDER BY k1;
+
++------+------------+-----------+--------+------+
+| k1 | v1 | v2 | v3 | w |
++------+------------+-----------+--------+------+
+| 1 | 2022-11-18 | 290000 | 290000 | 1 |
+| 2 | 2023-11-18 | 320000 | 320000 | 2 |
+| 3 | 2024-11-18 | 399999.99 | 399999 | 2 |
+| 4 | 2025-11-18 | 400000 | 400000 | 3 |
+| 5 | 2026-11-18 | 470000 | 470000 | 3 |
+| 6 | 2027-11-18 | 510000 | 510000 | 4 |
+| 7 | 2028-11-18 | 610000 | 610000 | 5 |
+| 8 | NULL | NULL | NULL | NULL |
++------+------------+-----------+--------+------+
+
+SELECT k1, v1, v2, v3, width_bucket(v3, 200000, 600000, 4) AS w FROM
width_bucket_test ORDER BY k1;
+
++------+------------+-----------+--------+------+
+| k1 | v1 | v2 | v3 | w |
++------+------------+-----------+--------+------+
+| 1 | 2022-11-18 | 290000 | 290000 | 1 |
+| 2 | 2023-11-18 | 320000 | 320000 | 2 |
+| 3 | 2024-11-18 | 399999.99 | 399999 | 2 |
+| 4 | 2025-11-18 | 400000 | 400000 | 3 |
+| 5 | 2026-11-18 | 470000 | 470000 | 3 |
+| 6 | 2027-11-18 | 510000 | 510000 | 4 |
+| 7 | 2028-11-18 | 610000 | 610000 | 5 |
+| 8 | NULL | NULL | NULL | NULL |
++------+------------+-----------+--------+------+
+
+```
+### keywords
+WIDTH_BUCKET
\ No newline at end of file
diff --git a/gensrc/script/doris_builtins_functions.py
b/gensrc/script/doris_builtins_functions.py
index cc91a7c66a..92e6c93fcf 100755
--- a/gensrc/script/doris_builtins_functions.py
+++ b/gensrc/script/doris_builtins_functions.py
@@ -2355,6 +2355,19 @@ visible_functions = [
[['sub_replace'], 'VARCHAR', ['VARCHAR', 'VARCHAR', 'INT'],'','', '',
'vec', 'ALWAYS_NULLABLE'],
[['sub_replace'], 'VARCHAR', ['VARCHAR', 'VARCHAR', 'INT', 'INT'],'','',
'', 'vec', 'ALWAYS_NULLABLE'],
+ # width_bucket
+ [['width_bucket'], 'BIGINT', ['TINYINT','TINYINT','TINYINT','TINYINT'],
'', '', '', 'vec', ''],
+ [['width_bucket'], 'BIGINT',
['SMALLINT','SMALLINT','SMALLINT','SMALLINT'], '', '', '', 'vec', ''],
+ [['width_bucket'], 'BIGINT', ['INT','INT','INT','INT'], '', '', '', 'vec',
''],
+ [['width_bucket'], 'BIGINT', ['BIGINT','BIGINT','BIGINT','BIGINT'], '',
'', '', 'vec', ''],
+ [['width_bucket'], 'BIGINT', ['FLOAT','FLOAT','FLOAT','BIGINT'], '', '',
'', 'vec', ''],
+ [['width_bucket'], 'BIGINT', ['DOUBLE','DOUBLE','DOUBLE','BIGINT'], '',
'', '', 'vec', ''],
+ [['width_bucket'], 'BIGINT',
['DECIMALV2','DECIMALV2','DECIMALV2','BIGINT'], '', '', '', 'vec', ''],
+ [['width_bucket'], 'BIGINT', ['DATE','DATE','DATE','BIGINT'], '', '', '',
'vec', ''],
+ [['width_bucket'], 'BIGINT', ['DATEV2','DATEV2','DATEV2','BIGINT'], '',
'', '', 'vec', ''],
+ [['width_bucket'], 'BIGINT', ['DATETIME','DATETIME','DATETIME','BIGINT'],
'', '', '', 'vec', ''],
+ [['width_bucket'], 'BIGINT',
['DATETIMEV2','DATETIMEV2','DATETIMEV2','BIGINT'], '', '', '', 'vec', ''],
+
# runningdifference
[['running_difference'], 'SMALLINT', ['TINYINT'], '', '', '', 'vec', ''],
[['running_difference'], 'INT', ['SMALLINT'], '', '', '', 'vec', ''],
diff --git
a/regression-test/data/query_p0/sql_functions/width_bucket_fuctions/test_width_bucket_function.out
b/regression-test/data/query_p0/sql_functions/width_bucket_fuctions/test_width_bucket_function.out
new file mode 100644
index 0000000000..6f2348ac3e
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/width_bucket_fuctions/test_width_bucket_function.out
@@ -0,0 +1,95 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+0
+
+-- !sql --
+\N
+
+-- !sql --
+5
+
+-- !sql --
+2
+
+-- !sql --
+1
+
+-- !sql --
+2
+
+-- !select --
+1 2022-11-18 290000.0 290000
+2 2023-11-18 320000.0 320000
+3 2024-11-18 399999.99 399999
+4 2025-11-18 400000.0 400000
+5 2026-11-18 470000.0 470000
+6 2027-11-18 510000.0 510000
+7 2028-11-18 610000.0 610000
+
+-- !select --
+1 0
+2 1
+3 2
+4 3
+5 4
+6 5
+7 5
+
+-- !select --
+1 1
+2 2
+3 2
+4 3
+5 3
+6 4
+7 5
+
+-- !select --
+1 1
+2 2
+3 2
+4 3
+5 3
+6 4
+7 5
+
+-- !select --
+1 2022-11-18 290000.0 290000
+2 2023-11-18 320000.0 320000
+3 2024-11-18 399999.99 399999
+4 2025-11-18 400000.0 400000
+5 2026-11-18 470000.0 470000
+6 2027-11-18 510000.0 510000
+7 2028-11-18 610000.0 610000
+8 \N \N \N
+
+-- !select --
+1 0
+2 1
+3 2
+4 3
+5 4
+6 5
+7 5
+8 \N
+
+-- !select --
+1 1
+2 2
+3 2
+4 3
+5 3
+6 4
+7 5
+8 \N
+
+-- !select --
+1 1
+2 2
+3 2
+4 3
+5 3
+6 4
+7 5
+8 \N
+
diff --git
a/regression-test/suites/query_p0/sql_functions/width_bucket_fuctions/test_width_bucket_function.groovy
b/regression-test/suites/query_p0/sql_functions/width_bucket_fuctions/test_width_bucket_function.groovy
new file mode 100644
index 0000000000..32d720e691
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/width_bucket_fuctions/test_width_bucket_function.groovy
@@ -0,0 +1,88 @@
+// 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.
+
+suite("test_width_bucket_function") {
+ sql "set enable_vectorized_engine = true"
+
+ qt_sql "select width_bucket(1, 2, 3, 2)"
+ qt_sql "select width_bucket(null, 2, 3, 2)"
+ qt_sql "select width_bucket(6, 2, 6, 4)"
+ qt_sql "select width_bucket(3, 2, 6, 4)"
+ qt_sql "select width_bucket(29000.0, 20000, 60000, 4)"
+ qt_sql "select width_bucket(date('2022-11-18'), date('2022-11-17'),
date('2022-11-19'), 2)"
+
+ def tableName1 = "tbl_test_width_bucket_function_not_null"
+ sql "DROP TABLE IF EXISTS ${tableName1}"
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName1} (
+ `k1` int NOT NULL COMMENT "",
+ `v1` date NOT NULL COMMENT "",
+ `v2` double NOT NULL COMMENT "",
+ `v3` bigint NOT NULL COMMENT ""
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k1`)
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "storage_format" = "V2"
+ )
+ """
+ sql """ INSERT INTO ${tableName1} VALUES (1, "2022-11-18", 290000.00,
290000) """
+ sql """ INSERT INTO ${tableName1} VALUES (2, "2023-11-18", 320000.00,
320000) """
+ sql """ INSERT INTO ${tableName1} VALUES (3, "2024-11-18", 399999.99,
399999) """
+ sql """ INSERT INTO ${tableName1} VALUES (4, "2025-11-18", 400000.00,
400000) """
+ sql """ INSERT INTO ${tableName1} VALUES (5, "2026-11-18", 470000.00,
470000) """
+ sql """ INSERT INTO ${tableName1} VALUES (6, "2027-11-18", 510000.00,
510000) """
+ sql """ INSERT INTO ${tableName1} VALUES (7, "2028-11-18", 610000.00,
610000) """
+
+ qt_select "SELECT * FROM ${tableName1} ORDER BY k1"
+
+ qt_select "SELECT k1, width_bucket(v1, date('2023-11-18'),
date('2027-11-18'), 4) FROM ${tableName1} ORDER BY k1"
+ qt_select "SELECT k1, width_bucket(v2, 200000, 600000, 4) FROM
${tableName1} ORDER BY k1"
+ qt_select "SELECT k1, width_bucket(v3, 200000, 600000, 4) FROM
${tableName1} ORDER BY k1"
+
+ def tableName2 = "tbl_test_width_bucket_function_null"
+ sql "DROP TABLE IF EXISTS ${tableName2}"
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName2} (
+ `k1` int NULL COMMENT "",
+ `v1` date NULL COMMENT "",
+ `v2` double NULL COMMENT "",
+ `v3` bigint NULL COMMENT ""
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k1`)
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "storage_format" = "V2"
+ )
+ """
+ sql """ INSERT INTO ${tableName2} VALUES (1, "2022-11-18", 290000.00,
290000) """
+ sql """ INSERT INTO ${tableName2} VALUES (2, "2023-11-18", 320000.00,
320000) """
+ sql """ INSERT INTO ${tableName2} VALUES (3, "2024-11-18", 399999.99,
399999) """
+ sql """ INSERT INTO ${tableName2} VALUES (4, "2025-11-18", 400000.00,
400000) """
+ sql """ INSERT INTO ${tableName2} VALUES (5, "2026-11-18", 470000.00,
470000) """
+ sql """ INSERT INTO ${tableName2} VALUES (6, "2027-11-18", 510000.00,
510000) """
+ sql """ INSERT INTO ${tableName2} VALUES (7, "2028-11-18", 610000.00,
610000) """
+ sql """ INSERT INTO ${tableName2} VALUES (8, null, null, null) """
+
+ qt_select "SELECT * FROM ${tableName2} ORDER BY k1"
+
+ qt_select "SELECT k1, width_bucket(v1, date('2023-11-18'),
date('2027-11-18'), 4) FROM ${tableName2} ORDER BY k1"
+ qt_select "SELECT k1, width_bucket(v2, 200000, 600000, 4) FROM
${tableName2} ORDER BY k1"
+ qt_select "SELECT k1, width_bucket(v3, 200000, 600000, 4) FROM
${tableName2} ORDER BY k1"
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
