This is an automated email from the ASF dual-hosted git repository.
dataroaring pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 0ba13af8ff [feature](running_difference) support running_difference
function (#13737)
0ba13af8ff is described below
commit 0ba13af8ffe7fb6610f688cb9e928501a69de48b
Author: Yixi Zhang <[email protected]>
AuthorDate: Fri Nov 11 21:22:56 2022 +0800
[feature](running_difference) support running_difference function (#13737)
---
be/src/vec/CMakeLists.txt | 1 +
.../vec/functions/function_running_difference.cpp | 26 +++
be/src/vec/functions/function_running_difference.h | 138 ++++++++++++++++
be/src/vec/functions/simple_function_factory.h | 2 +
be/test/CMakeLists.txt | 1 +
.../function/function_running_difference_test.cpp | 79 +++++++++
be/test/vec/function/function_test_util.h | 5 +-
.../math-functions/running_difference.md | 180 +++++++++++++++++++++
.../math-functions/running_difference.md | 177 ++++++++++++++++++++
gensrc/script/doris_builtins_functions.py | 16 +-
.../math_functions/test_running_difference.out | 50 ++++++
.../math_functions/test_running_difference.sql | 73 +++++++++
12 files changed, 745 insertions(+), 3 deletions(-)
diff --git a/be/src/vec/CMakeLists.txt b/be/src/vec/CMakeLists.txt
index 4602bc929b..e05c40255a 100644
--- a/be/src/vec/CMakeLists.txt
+++ b/be/src/vec/CMakeLists.txt
@@ -210,6 +210,7 @@ set(VEC_FILES
functions/url/function_url.cpp
functions/functions_multi_string_position.cpp
functions/functions_multi_string_search.cpp
+ functions/function_running_difference.cpp
olap/vgeneric_iterators.cpp
olap/vcollect_iterator.cpp
olap/block_reader.cpp
diff --git a/be/src/vec/functions/function_running_difference.cpp
b/be/src/vec/functions/function_running_difference.cpp
new file mode 100644
index 0000000000..b4abda6b1d
--- /dev/null
+++ b/be/src/vec/functions/function_running_difference.cpp
@@ -0,0 +1,26 @@
+// 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/functions/function_running_difference.h"
+
+namespace doris::vectorized {
+
+void register_function_running_difference(SimpleFunctionFactory& factory) {
+ factory.register_function<FunctionRunningDifference>();
+}
+
+} // namespace doris::vectorized
diff --git a/be/src/vec/functions/function_running_difference.h
b/be/src/vec/functions/function_running_difference.h
new file mode 100644
index 0000000000..b9b53892f9
--- /dev/null
+++ b/be/src/vec/functions/function_running_difference.h
@@ -0,0 +1,138 @@
+// 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.
+
+#pragma once
+
+#include "common/status.h"
+#include "vec/columns/column.h"
+#include "vec/columns/column_array.h"
+#include "vec/columns/column_decimal.h"
+#include "vec/columns/column_nullable.h"
+#include "vec/columns/column_string.h"
+#include "vec/columns/columns_number.h"
+#include "vec/common/assert_cast.h"
+#include "vec/common/typeid_cast.h"
+#include "vec/data_types/data_type.h"
+#include "vec/data_types/data_type_date.h"
+#include "vec/data_types/data_type_date_time.h"
+#include "vec/data_types/data_type_nullable.h"
+#include "vec/data_types/data_type_number.h"
+#include "vec/data_types/data_type_time_v2.h"
+#include "vec/data_types/number_traits.h"
+#include "vec/functions/function.h"
+#include "vec/functions/simple_function_factory.h"
+
+namespace doris::vectorized {
+
+class FunctionRunningDifference : public IFunction {
+public:
+ static constexpr auto name = "running_difference";
+
+ static FunctionPtr create() { return
std::make_shared<FunctionRunningDifference>(); }
+
+ String get_name() const override { return name; }
+
+ size_t get_number_of_arguments() const override { return 1; }
+
+ bool use_default_implementation_for_nulls() const override { return false;
}
+
+ bool use_default_implementation_for_constants() const override { return
true; }
+
+ template <typename SrcFieldType>
+ using DstFieldType =
+ typename NumberTraits::ResultOfSubtraction<SrcFieldType,
SrcFieldType>::Type;
+ DataTypePtr get_return_type_impl(const DataTypes& arguments) const
override {
+ bool is_nullable = arguments[0]->is_nullable();
+ auto nested_type = remove_nullable(arguments[0]);
+ WhichDataType which(nested_type);
+ //return type is promoted to prevent result overflow
+ //like: input is int32 ---> return type will be int64
+ DataTypePtr return_type = nullptr;
+ if (which.is_uint8() || which.is_int8()) {
+ return_type = std::make_shared<DataTypeInt16>();
+ } else if (which.is_uint16() || which.is_int16()) {
+ return_type = std::make_shared<DataTypeInt32>();
+ } else if (which.is_uint32() || which.is_uint64() || which.is_int32())
{
+ return_type = std::make_shared<DataTypeInt64>();
+ } else if (which.is_int64() || which.is_int128()) {
+ return_type = std::make_shared<DataTypeInt128>();
+ } else if (which.is_float32() || which.is_float64()) {
+ return_type = std::make_shared<DataTypeFloat64>();
+ } else if (which.is_decimal()) {
+ return_type = nested_type;
+ } else if (which.is_date_time() || which.is_date_time_v2()) {
+ return_type = std::make_shared<DataTypeFloat64>();
+ } else if (which.is_date() || which.is_date_v2()) {
+ return_type = std::make_shared<DataTypeInt32>();
+ }
+
+ return_type = is_nullable ? make_nullable(return_type) : return_type;
+ const ColumnsWithTypeAndName subtract_cols {{nullptr, arguments[0],
"first_arg"},
+ {nullptr, arguments[0],
"second_arg"}};
+ if (which.is_date_time() || which.is_date_time_v2()) {
+ func_subtract = SimpleFunctionFactory::instance().get_function(
+ "timediff", subtract_cols, return_type);
+ } else if (which.is_date() || which.is_date_v2()) {
+ func_subtract = SimpleFunctionFactory::instance().get_function(
+ "datediff", subtract_cols, return_type);
+ } else {
+ func_subtract = SimpleFunctionFactory::instance().get_function(
+ "subtract", subtract_cols, return_type);
+ }
+ func_return_type = return_type;
+ return return_type;
+ }
+
+ Status execute_impl(FunctionContext* context, Block& block, const
ColumnNumbers& arguments,
+ size_t result, size_t input_rows_count) override {
+ if (is_column_const(*block.get_by_position(arguments[0]).column)) {
+ auto res =
func_return_type->create_column_const_with_default_value(input_rows_count);
+ block.replace_by_position(result, std::move(res));
+ return Status::OK();
+ }
+
+ auto arg_first = block.get_by_position(arguments[0]).column;
+ auto arg_type = block.get_by_position(arguments[0]).type;
+ auto arg_second = arg_type->create_column();
+
+ if (is_first_block) {
+ arg_second->insert_from(*arg_first, 0);
+ is_first_block = false;
+ } else {
+ arg_second->insert_data(last_value.c_str(), last_value.length());
+ }
+ arg_second->insert_range_from(*arg_first, 0, input_rows_count - 1);
+ last_value = arg_first->get_data_at(input_rows_count - 1).to_string();
+
+ Block temporary_block {
+ ColumnsWithTypeAndName {block.get_by_position(arguments[0]),
+ {std::move(arg_second), arg_type,
"second_arg"},
+ block.get_by_position(result)}};
+
+ func_subtract->execute(context, temporary_block, {0, 1}, 2,
input_rows_count);
+ block.get_by_position(result).column =
temporary_block.get_by_position(2).column;
+ return Status::OK();
+ }
+
+private:
+ mutable FunctionBasePtr func_subtract;
+ mutable DataTypePtr func_return_type;
+ bool is_first_block = true;
+ std::string last_value;
+};
+
+} // 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 fbe28338d1..110d410fae 100644
--- a/be/src/vec/functions/simple_function_factory.h
+++ b/be/src/vec/functions/simple_function_factory.h
@@ -54,6 +54,7 @@ void register_function_is_not_null(SimpleFunctionFactory&
factory);
void register_function_to_time_function(SimpleFunctionFactory& factory);
void register_function_time_of_function(SimpleFunctionFactory& factory);
void register_function_string(SimpleFunctionFactory& factory);
+void register_function_running_difference(SimpleFunctionFactory& factory);
void register_function_date_time_to_string(SimpleFunctionFactory& factory);
void register_function_date_time_string_to_string(SimpleFunctionFactory&
factory);
void register_function_in(SimpleFunctionFactory& factory);
@@ -193,6 +194,7 @@ public:
register_function_to_time_function(instance);
register_function_time_of_function(instance);
register_function_string(instance);
+ register_function_running_difference(instance);
register_function_in(instance);
register_function_if(instance);
register_function_nullif(instance);
diff --git a/be/test/CMakeLists.txt b/be/test/CMakeLists.txt
index 2a694cab8d..bfae619cbf 100644
--- a/be/test/CMakeLists.txt
+++ b/be/test/CMakeLists.txt
@@ -366,6 +366,7 @@ set(VEC_TEST_FILES
vec/function/function_test_util.cpp
vec/function/function_url_test.cpp
vec/function/table_function_test.cpp
+ vec/function/function_running_difference_test.cpp
vec/runtime/vdata_stream_test.cpp
vec/runtime/vdatetime_value_test.cpp
vec/utils/arrow_column_to_doris_column_test.cpp
diff --git a/be/test/vec/function/function_running_difference_test.cpp
b/be/test/vec/function/function_running_difference_test.cpp
new file mode 100644
index 0000000000..6b002097b7
--- /dev/null
+++ b/be/test/vec/function/function_running_difference_test.cpp
@@ -0,0 +1,79 @@
+// 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 <gtest/gtest.h>
+#include <time.h>
+
+#include <any>
+#include <cmath>
+#include <iostream>
+#include <string>
+
+#include "function_test_util.h"
+namespace doris::vectorized {
+using namespace ut_type;
+TEST(FunctionRunningDifferenceTest, function_running_difference_test) {
+ std::string func_name = "running_difference";
+ {
+ InputTypeSet input_types = {TypeIndex::Int32};
+
+ DataSet data_set = {{{Null()}, Null()},
+ {{(int32_t)1}, Null()},
+ {{(int32_t)2}, (int64_t)1},
+ {{(int32_t)3}, (int64_t)1},
+ {{(int32_t)5}, (int64_t)2}};
+
+ check_function<DataTypeInt64, true>(func_name, input_types, data_set);
+ }
+ {
+ InputTypeSet input_types = {TypeIndex::Float64};
+ DataSet data_set = {{{(double)0.0}, (double)0.0},
+ {{Null()}, Null()},
+ {{(double)2.33}, Null()},
+ {{(double)8.45}, (double)6.12},
+ {{(double)4.22}, (double)-4.23}};
+ check_function<DataTypeFloat64, true>(func_name, input_types,
data_set);
+ }
+ {
+ InputTypeSet input_types = {TypeIndex::DateTime};
+ DataSet data_set = {{{std::string("2019-07-18 12:00:00")},
(double)0.0},
+ {{std::string("2019-07-18 12:00:05")},
(double)5.0},
+ {{std::string("2019-07-18 12:00:06")},
(double)1.0},
+ {{std::string("2019-07-18 12:00:08")},
(double)2.0},
+ {{std::string("2019-07-18 12:00:10")},
(double)2.0}};
+ check_function<DataTypeFloat64, true>(func_name, input_types,
data_set);
+ }
+ {
+ InputTypeSet input_types = {TypeIndex::Date};
+ DataSet data_set = {{{std::string("2019-07-18")}, (int32_t)0},
+ {{std::string("2019-08-19")}, (int32_t)32},
+ {{std::string("2019-07-20")}, (int32_t)-30},
+ {{std::string("2019-07-22")}, (int32_t)2},
+ {{std::string("2019-08-01")}, (int32_t)10}};
+ check_function<DataTypeInt32, true>(func_name, input_types, data_set);
+ }
+ {
+ InputTypeSet input_types = {TypeIndex::Date};
+ DataSet data_set = {{{Null()}, Null()},
+ {{std::string("2019-08-19")}, Null()},
+ {{std::string("2019-07-20")}, (int32_t)-30},
+ {{std::string("2019-07-22")}, (int32_t)2},
+ {{std::string("2019-08-01")}, (int32_t)10}};
+ check_function<DataTypeInt32, true>(func_name, input_types, data_set);
+ }
+}
+} // namespace doris::vectorized
\ No newline at end of file
diff --git a/be/test/vec/function/function_test_util.h
b/be/test/vec/function/function_test_util.h
index e48c4695fa..49e75cf6be 100644
--- a/be/test/vec/function/function_test_util.h
+++ b/be/test/vec/function/function_test_util.h
@@ -291,9 +291,10 @@ Status check_function(const std::string& func_name, const
InputTypeSet& input_ty
if constexpr (std::is_same_v<ReturnType,
DataTypeDecimal<Decimal128>>) {
const auto& column_data =
field.get<DecimalField<Decimal128>>().get_value();
EXPECT_EQ(expect_data.value, column_data.value) << " at
row " << i;
- } else if constexpr (std::is_same_v<ReturnType,
DataTypeFloat32>) {
+ } else if constexpr (std::is_same_v<ReturnType,
DataTypeFloat32> ||
+ std::is_same_v<ReturnType,
DataTypeFloat64>) {
const auto& column_data =
field.get<DataTypeFloat64::FieldType>();
- EXPECT_EQ(expect_data, column_data) << " at row " << i;
+ EXPECT_DOUBLE_EQ(expect_data, column_data) << " at row "
<< i;
} else {
const auto& column_data = field.get<typename
ReturnType::FieldType>();
EXPECT_EQ(expect_data, column_data) << " at row " << i;
diff --git
a/docs/en/docs/sql-manual/sql-functions/math-functions/running_difference.md
b/docs/en/docs/sql-manual/sql-functions/math-functions/running_difference.md
new file mode 100644
index 0000000000..fd19f187a5
--- /dev/null
+++ b/docs/en/docs/sql-manual/sql-functions/math-functions/running_difference.md
@@ -0,0 +1,180 @@
+---
+{
+ "title": "running_difference",
+ "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.
+-->
+
+## running_difference
+
+### description
+#### Syntax
+
+`running_difference(x);`
+
+Calculates the difference between successive row values in the data block.
+The result of the function depends on the affected data blocks and the order
of data in the block.
+
+The rows order used during the calculation of running_difference can differ
from the order of rows returned to the user. To prevent that you can make a
subquery with **ORDER BY** and call the function from outside the subquery.
+
+#### Arguments
+`x` - A list of
data.TINYINT,SMALLINT,INT,BIGINT,LARGEINT,FLOAT,DOUBLE,DATE,DATETIME,DECIMAL
+
+##### Returned value
+Returns 0 for the first row and the difference from the previous row for each
subsequent row
+
+### example
+
+```sql
+DROP TABLE IF EXISTS running_difference_test;
+
+CREATE TABLE running_difference_test (
+ `id` int NOT NULL COMMENT 'id' ,
+ `day` date COMMENT 'day',
+ `time_val` datetime COMMENT 'time_val',
+ `doublenum` double NULL COMMENT 'doublenum'
+ )
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT into running_difference_test (id,day, time_val,doublenum) values ('1',
'2022-10-28', '2022-03-12 10:41:00', null),
+ ('2','2022-10-27',
'2022-03-12 10:41:02', 2.6),
+ ('3','2022-10-28',
'2022-03-12 10:41:03', 2.5),
+ ('4','2022-9-29',
'2022-03-12 10:41:03', null),
+ ('5','2022-10-31',
'2022-03-12 10:42:01', 3.3),
+ ('6', '2022-11-08',
'2022-03-12 11:05:04', 4.7);
+
+SELECT * from running_difference_test ORDER BY id ASC;
+
++------+------------+---------------------+-----------+
+| id | day | time_val | doublenum |
++------+------------+---------------------+-----------+
+| 1 | 2022-10-28 | 2022-03-12 10:41:00 | NULL |
+| 2 | 2022-10-27 | 2022-03-12 10:41:02 | 2.6 |
+| 3 | 2022-10-28 | 2022-03-12 10:41:03 | 2.5 |
+| 4 | 2022-09-29 | 2022-03-12 10:41:03 | NULL |
+| 5 | 2022-10-31 | 2022-03-12 10:42:01 | 3.3 |
+| 6 | 2022-11-08 | 2022-03-12 11:05:04 | 4.7 |
++------+------------+---------------------+-----------+
+
+SELECT
+ id,
+ running_difference(id) AS delta
+FROM
+(
+ SELECT
+ id,
+ day,
+ time_val,
+ doublenum
+ FROM running_difference_test
+)as runningDifference ORDER BY id ASC;
+
++------+-------+
+| id | delta |
++------+-------+
+| 1 | 0 |
+| 2 | 1 |
+| 3 | 1 |
+| 4 | 1 |
+| 5 | 1 |
+| 6 | 1 |
++------+-------+
+
+SELECT
+ day,
+ running_difference(day) AS delta
+FROM
+(
+ SELECT
+ id,
+ day,
+ time_val,
+ doublenum
+ FROM running_difference_test
+)as runningDifference ORDER BY id ASC;
+
++------------+-------+
+| day | delta |
++------------+-------+
+| 2022-10-28 | 0 |
+| 2022-10-27 | -1 |
+| 2022-10-28 | 1 |
+| 2022-09-29 | -29 |
+| 2022-10-31 | 32 |
+| 2022-11-08 | 8 |
++------------+-------+
+
+SELECT
+ time_val,
+ running_difference(time_val) AS delta
+FROM
+(
+ SELECT
+ id,
+ day,
+ time_val,
+ doublenum
+ FROM running_difference_test
+)as runningDifference ORDER BY id ASC;
+
++---------------------+-------+
+| time_val | delta |
++---------------------+-------+
+| 2022-03-12 10:41:00 | 0 |
+| 2022-03-12 10:41:02 | 2 |
+| 2022-03-12 10:41:03 | 1 |
+| 2022-03-12 10:41:03 | 0 |
+| 2022-03-12 10:42:01 | 58 |
+| 2022-03-12 11:05:04 | 1383 |
++---------------------+-------+
+
+SELECT
+ doublenum,
+ running_difference(doublenum) AS delta
+FROM
+(
+ SELECT
+ id,
+ day,
+ time_val,
+ doublenum
+ FROM running_difference_test
+)as runningDifference ORDER BY id ASC;
+
++-----------+----------------------+
+| doublenum | delta |
++-----------+----------------------+
+| NULL | NULL |
+| 2.6 | NULL |
+| 2.5 | -0.10000000000000009 |
+| NULL | NULL |
+| 3.3 | NULL |
+| 4.7 | 1.4000000000000004 |
++-----------+----------------------+
+
+```
+
+### keywords
+
+running_difference
\ No newline at end of file
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/math-functions/running_difference.md
b/docs/zh-CN/docs/sql-manual/sql-functions/math-functions/running_difference.md
new file mode 100644
index 0000000000..c9f3f00306
--- /dev/null
+++
b/docs/zh-CN/docs/sql-manual/sql-functions/math-functions/running_difference.md
@@ -0,0 +1,177 @@
+---
+{
+ "title": "running_difference",
+ "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.
+-->
+
+## running_difference
+### description
+#### Syntax
+
+`running_difference(x);`
+计算数据块中连续行值的差值。该函数的结果取决于受影响的数据块和块中数据的顺序。
+
+计算 running_difference 期间使用的行顺序可能与返回给用户的行顺序不同。为防止您可以使用 **ORDER BY**
进行子查询并从子查询外部调用该函数。
+
+#### Arguments
+`x` -
一列数据.数据类型可以是TINYINT,SMALLINT,INT,BIGINT,LARGEINT,FLOAT,DOUBLE,DATE,DATETIME,DECIMAL
+
+##### Returned value
+第一行返回 0，随后的每一行返回与前一行的差值。
+
+### example
+
+```sql
+DROP TABLE IF EXISTS running_difference_test;
+
+CREATE TABLE running_difference_test (
+ `id` int NOT NULL COMMENT 'id' ,
+ `day` date COMMENT 'day',
+ `time_val` datetime COMMENT 'time_val',
+ `doublenum` double NULL COMMENT 'doublenum'
+ )
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT into running_difference_test (id,day, time_val,doublenum) values ('1',
'2022-10-28', '2022-03-12 10:41:00', null),
+ ('2','2022-10-27',
'2022-03-12 10:41:02', 2.6),
+ ('3','2022-10-28',
'2022-03-12 10:41:03', 2.5),
+ ('4','2022-9-29',
'2022-03-12 10:41:03', null),
+ ('5','2022-10-31',
'2022-03-12 10:42:01', 3.3),
+ ('6', '2022-11-08',
'2022-03-12 11:05:04', 4.7);
+
+SELECT * from running_difference_test ORDER BY id ASC;
+
++------+------------+---------------------+-----------+
+| id | day | time_val | doublenum |
++------+------------+---------------------+-----------+
+| 1 | 2022-10-28 | 2022-03-12 10:41:00 | NULL |
+| 2 | 2022-10-27 | 2022-03-12 10:41:02 | 2.6 |
+| 3 | 2022-10-28 | 2022-03-12 10:41:03 | 2.5 |
+| 4 | 2022-09-29 | 2022-03-12 10:41:03 | NULL |
+| 5 | 2022-10-31 | 2022-03-12 10:42:01 | 3.3 |
+| 6 | 2022-11-08 | 2022-03-12 11:05:04 | 4.7 |
++------+------------+---------------------+-----------+
+
+SELECT
+ id,
+ running_difference(id) AS delta
+FROM
+(
+ SELECT
+ id,
+ day,
+ time_val,
+ doublenum
+ FROM running_difference_test
+)as runningDifference ORDER BY id ASC;
+
++------+-------+
+| id | delta |
++------+-------+
+| 1 | 0 |
+| 2 | 1 |
+| 3 | 1 |
+| 4 | 1 |
+| 5 | 1 |
+| 6 | 1 |
++------+-------+
+
+SELECT
+ day,
+ running_difference(day) AS delta
+FROM
+(
+ SELECT
+ id,
+ day,
+ time_val,
+ doublenum
+ FROM running_difference_test
+)as runningDifference ORDER BY id ASC;
+
++------------+-------+
+| day | delta |
++------------+-------+
+| 2022-10-28 | 0 |
+| 2022-10-27 | -1 |
+| 2022-10-28 | 1 |
+| 2022-09-29 | -29 |
+| 2022-10-31 | 32 |
+| 2022-11-08 | 8 |
++------------+-------+
+
+SELECT
+ time_val,
+ running_difference(time_val) AS delta
+FROM
+(
+ SELECT
+ id,
+ day,
+ time_val,
+ doublenum
+ FROM running_difference_test
+)as runningDifference ORDER BY id ASC;
+
++---------------------+-------+
+| time_val | delta |
++---------------------+-------+
+| 2022-03-12 10:41:00 | 0 |
+| 2022-03-12 10:41:02 | 2 |
+| 2022-03-12 10:41:03 | 1 |
+| 2022-03-12 10:41:03 | 0 |
+| 2022-03-12 10:42:01 | 58 |
+| 2022-03-12 11:05:04 | 1383 |
++---------------------+-------+
+
+SELECT
+ doublenum,
+ running_difference(doublenum) AS delta
+FROM
+(
+ SELECT
+ id,
+ day,
+ time_val,
+ doublenum
+ FROM running_difference_test
+)as runningDifference ORDER BY id ASC;
+
++-----------+----------------------+
+| doublenum | delta |
++-----------+----------------------+
+| NULL | NULL |
+| 2.6 | NULL |
+| 2.5 | -0.10000000000000009 |
+| NULL | NULL |
+| 3.3 | NULL |
+| 4.7 | 1.4000000000000004 |
++-----------+----------------------+
+
+```
+
+### keywords
+
+running_difference
diff --git a/gensrc/script/doris_builtins_functions.py
b/gensrc/script/doris_builtins_functions.py
index e6aae500c5..7286afef02 100755
--- a/gensrc/script/doris_builtins_functions.py
+++ b/gensrc/script/doris_builtins_functions.py
@@ -237,7 +237,7 @@ visible_functions = [
[['array_difference'], 'ARRAY_BIGINT', ['ARRAY_INT'], '', '', '',
'vec', ''],
[['array_difference'], 'ARRAY_LARGEINT', ['ARRAY_BIGINT'], '', '', '',
'vec', ''],
[['array_difference'], 'ARRAY_LARGEINT', ['ARRAY_LARGEINT'], '', '', '',
'vec', ''],
- [['array_difference'], 'ARRAY_FLOAT', ['ARRAY_FLOAT'], '', '', '',
'vec', ''],
+ [['array_difference'], 'ARRAY_DOUBLE', ['ARRAY_FLOAT'], '', '', '',
'vec', ''],
[['array_difference'], 'ARRAY_DOUBLE', ['ARRAY_DOUBLE'], '', '', '',
'vec', ''],
[['array_difference'], 'ARRAY_DECIMALV2', ['ARRAY_DECIMALV2'], '', '', '',
'vec', ''],
@@ -2220,6 +2220,20 @@ visible_functions = [
[['sub_replace'], 'VARCHAR', ['VARCHAR', 'VARCHAR', 'INT'],'','', '',
'vec', 'ALWAYS_NULLABLE'],
[['sub_replace'], 'VARCHAR', ['VARCHAR', 'VARCHAR', 'INT', 'INT'],'','',
'', 'vec', 'ALWAYS_NULLABLE'],
+ # runningdifference
+ [['running_difference'], 'SMALLINT', ['TINYINT'], '', '', '', 'vec', ''],
+ [['running_difference'], 'INT', ['SMALLINT'], '', '', '', 'vec', ''],
+ [['running_difference'], 'BIGINT', ['INT'], '', '', '', 'vec', ''],
+ [['running_difference'], 'LARGEINT', ['BIGINT'], '', '', '', 'vec', ''],
+ [['running_difference'], 'LARGEINT', ['LARGEINT'], '', '', '', 'vec', ''],
+ [['running_difference'], 'DOUBLE', ['FLOAT'], '', '', '', 'vec', ''],
+ [['running_difference'], 'DOUBLE', ['DOUBLE'], '', '', '', 'vec', ''],
+ [['running_difference'], 'DECIMALV2', ['DECIMALV2'], '', '', '', 'vec',
''],
+ [['running_difference'], 'INT', ['DATE'], '', '', '', 'vec', ''],
+ [['running_difference'], 'INT', ['DATEV2'], '', '', '', 'vec', ''],
+ [['running_difference'], 'DOUBLE', ['DATETIME'], '', '', '', 'vec', ''],
+ [['running_difference'], 'DOUBLE', ['DATETIMEV2'], '', '', '', 'vec', ''],
+
# Longtext function
[['substr', 'substring'], 'STRING', ['STRING', 'INT'],
'_ZN5doris15StringFunctions9substringEPN'
diff --git
a/regression-test/data/query_p0/sql_functions/math_functions/test_running_difference.out
b/regression-test/data/query_p0/sql_functions/math_functions/test_running_difference.out
new file mode 100644
index 0000000000..4399a055d8
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/math_functions/test_running_difference.out
@@ -0,0 +1,50 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !test_running_difference --
+0
+
+-- !test_running_difference_2 --
+0
+
+-- !test_running_difference_3 --
+6
+
+-- !test_running_difference_4 --
+1 2022-10-28 2022-03-12T10:41 \N
+2 2022-10-27 2022-03-12T10:41:02 2.6
+3 2022-10-28 2022-03-12T10:41:03 2.5
+4 2022-09-29 2022-03-12T10:41:03 \N
+5 2022-10-31 2022-03-12T10:42:01 3.3
+6 2022-11-08 2022-03-12T11:05:04 4.7
+
+-- !test_running_difference_5 --
+1 0
+2 1
+3 1
+4 1
+5 1
+6 1
+
+-- !test_running_difference_6 --
+2022-10-28 0
+2022-10-27 -1
+2022-10-28 1
+2022-09-29 -29
+2022-10-31 32
+2022-11-08 8
+
+-- !test_running_difference_7 --
+2022-03-12T10:41 0.0
+2022-03-12T10:41:02 2.0
+2022-03-12T10:41:03 1.0
+2022-03-12T10:41:03 0.0
+2022-03-12T10:42:01 58.0
+2022-03-12T11:05:04 1383.0
+
+-- !test_running_difference_8 --
+\N \N
+2.6 \N
+2.5 -0.10000000000000009
+\N \N
+3.3 \N
+4.7 1.4000000000000004
+
diff --git
a/regression-test/suites/query_p0/sql_functions/math_functions/test_running_difference.sql
b/regression-test/suites/query_p0/sql_functions/math_functions/test_running_difference.sql
new file mode 100644
index 0000000000..e36225dc47
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/math_functions/test_running_difference.sql
@@ -0,0 +1,73 @@
+DROP TABLE IF EXISTS running_difference_test;
+
+CREATE TABLE running_difference_test (
+ `id` int NOT NULL COMMENT 'id' ,
+ `day` date COMMENT 'day',
+ `time_val` datetime COMMENT 'time_val',
+ `doublenum` double NULL COMMENT 'doublenum'
+ )
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT into running_difference_test (id,day, time_val,doublenum) values ('1',
'2022-10-28', '2022-03-12 10:41:00', null),
+ ('2','2022-10-27',
'2022-03-12 10:41:02', 2.6),
+ ('3','2022-10-28',
'2022-03-12 10:41:03', 2.5),
+ ('4','2022-9-29',
'2022-03-12 10:41:03', null),
+ ('5','2022-10-31',
'2022-03-12 10:42:01', 3.3),
+ ('6', '2022-11-08',
'2022-03-12 11:05:04', 4.7);
+SELECT * from running_difference_test ORDER BY id ASC;
+
+SELECT
+ id,
+ running_difference(id) AS delta
+FROM
+(
+ SELECT
+ id,
+ day,
+ time_val,
+ doublenum
+ FROM running_difference_test
+)as runningDifference ORDER BY id ASC;
+
+SELECT
+ day,
+ running_difference(day) AS delta
+FROM
+(
+ SELECT
+ id,
+ day,
+ time_val,
+ doublenum
+ FROM running_difference_test
+)as runningDifference ORDER BY id ASC;
+
+SELECT
+ time_val,
+ running_difference(time_val) AS delta
+FROM
+(
+ SELECT
+ id,
+ day,
+ time_val,
+ doublenum
+ FROM running_difference_test
+)as runningDifference ORDER BY id ASC;
+
+SELECT
+ doublenum,
+ running_difference(doublenum) AS delta
+FROM
+(
+ SELECT
+ id,
+ day,
+ time_val,
+ doublenum
+ FROM running_difference_test
+)as runningDifference ORDER BY id ASC;
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
