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/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 16999ef02d [Vectorized][Function] support date_trunc and countequal
function (#13039)
16999ef02d is described below
commit 16999ef02d6af72f60d81e8bbdfc35a47b4b4b1d
Author: zhangstar333 <87313068+zhangstar...@users.noreply.github.com>
AuthorDate: Wed Oct 12 10:01:09 2022 +0800
[Vectorized][Function] support date_trunc and countequal function (#13039)
---
.../vec/functions/array/function_array_index.cpp | 4 +
be/src/vec/functions/array/function_array_index.h | 6 +
be/src/vec/functions/function_timestamp.cpp | 105 +++++++++++++-
be/src/vec/runtime/vdatetime_value.cpp | 160 +++++++++++++++++++++
be/src/vec/runtime/vdatetime_value.h | 6 +
be/test/vec/function/function_time_test.cpp | 41 ++++++
.../sql-functions/array-functions/countequal.md | 65 +++++++++
.../date-time-functions/date_trunc.md | 100 +++++++++++++
docs/sidebars.json | 2 +
.../sql-functions/array-functions/countequal.md | 65 +++++++++
.../date-time-functions/date_trunc.md | 95 ++++++++++++
gensrc/script/doris_builtins_functions.py | 24 +++-
12 files changed, 670 insertions(+), 3 deletions(-)
diff --git a/be/src/vec/functions/array/function_array_index.cpp
b/be/src/vec/functions/array/function_array_index.cpp
index 0c59b05b77..dec7236963 100644
--- a/be/src/vec/functions/array/function_array_index.cpp
+++ b/be/src/vec/functions/array/function_array_index.cpp
@@ -27,10 +27,14 @@ struct NameArrayContains {
struct NameArrayPosition {
static constexpr auto name = "array_position";
};
+struct NameCountEqual {
+ static constexpr auto name = "countequal";
+};
void register_function_array_index(SimpleFunctionFactory& factory) {
factory.register_function<FunctionArrayIndex<ArrayContainsAction,
NameArrayContains>>();
factory.register_function<FunctionArrayIndex<ArrayPositionAction,
NameArrayPosition>>();
+ factory.register_function<FunctionArrayIndex<ArrayCountEqual,
NameCountEqual>>();
}
} // namespace doris::vectorized
diff --git a/be/src/vec/functions/array/function_array_index.h
b/be/src/vec/functions/array/function_array_index.h
index cd17feff8d..be39c4dcb5 100644
--- a/be/src/vec/functions/array/function_array_index.h
+++ b/be/src/vec/functions/array/function_array_index.h
@@ -41,6 +41,12 @@ struct ArrayPositionAction {
static constexpr void apply(ResultType& current, size_t j) noexcept {
current = j + 1; }
};
+struct ArrayCountEqual {
+ using ResultType = Int64;
+ static constexpr const bool resume_execution = true;
+ static constexpr void apply(ResultType& current, size_t j) noexcept {
++current; }
+};
+
template <typename ConcreteAction, typename Name>
class FunctionArrayIndex : public IFunction {
public:
diff --git a/be/src/vec/functions/function_timestamp.cpp
b/be/src/vec/functions/function_timestamp.cpp
index 3c2f0d5b65..461b317bf6 100644
--- a/be/src/vec/functions/function_timestamp.cpp
+++ b/be/src/vec/functions/function_timestamp.cpp
@@ -33,6 +33,14 @@ namespace doris::vectorized {
struct StrToDate {
static constexpr auto name = "str_to_date";
+ static bool is_variadic() { return false; }
+
+ static DataTypes get_variadic_argument_types() { return {}; }
+
+ static DataTypePtr get_return_type_impl(const DataTypes& arguments) {
+ return make_nullable(std::make_shared<DataTypeDateTime>());
+ }
+
static Status execute(FunctionContext* context, Block& block, const
ColumnNumbers& arguments,
size_t result, size_t input_rows_count) {
auto null_map = ColumnUInt8::create(input_rows_count, 0);
@@ -122,6 +130,14 @@ struct StrToDate {
struct MakeDateImpl {
static constexpr auto name = "makedate";
+ static bool is_variadic() { return false; }
+
+ static DataTypes get_variadic_argument_types() { return {}; }
+
+ static DataTypePtr get_return_type_impl(const DataTypes& arguments) {
+ return make_nullable(std::make_shared<DataTypeDateTime>());
+ }
+
static Status execute(FunctionContext* context, Block& block, const
ColumnNumbers& arguments,
size_t result, size_t input_rows_count) {
auto null_map = ColumnUInt8::create(input_rows_count, 0);
@@ -214,6 +230,81 @@ struct MakeDateImpl {
}
};
+template <typename DateValueType, typename ArgType>
+struct DateTrunc {
+ static constexpr auto name = "date_trunc";
+
+ static bool is_variadic() { return true; }
+
+ static DataTypes get_variadic_argument_types() {
+ if constexpr (std::is_same_v<DateValueType, VecDateTimeValue>) {
+ return {std::make_shared<DataTypeDateTime>(),
std::make_shared<DataTypeString>()};
+ } else {
+ return {std::make_shared<DataTypeDateTimeV2>(),
std::make_shared<DataTypeString>()};
+ }
+ }
+
+ static DataTypePtr get_return_type_impl(const DataTypes& arguments) {
+ if constexpr (std::is_same_v<DateValueType, VecDateTimeValue>) {
+ return make_nullable(std::make_shared<DataTypeDateTime>());
+ } else {
+ return make_nullable(std::make_shared<DataTypeDateTimeV2>());
+ }
+ }
+
+ static Status execute(FunctionContext* context, Block& block, const
ColumnNumbers& arguments,
+ size_t result, size_t input_rows_count) {
+ DCHECK_EQ(arguments.size(), 2);
+ ColumnPtr argument_columns[2];
+ auto null_map = ColumnUInt8::create(input_rows_count, 0);
+ argument_columns[0] =
+
block.get_by_position(arguments[0]).column->convert_to_full_column_if_const();
+ argument_columns[1] =
+
block.get_by_position(arguments[1]).column->convert_to_full_column_if_const();
+ auto datetime_column = static_cast<const
ColumnVector<ArgType>*>(argument_columns[0].get());
+ auto str_column = static_cast<const
ColumnString*>(argument_columns[1].get());
+ auto& rdata = str_column->get_chars();
+ auto& roffsets = str_column->get_offsets();
+
+ ColumnPtr res = ColumnVector<ArgType>::create();
+ executeImpl(datetime_column->get_data(), rdata, roffsets,
+
static_cast<ColumnVector<ArgType>*>(res->assume_mutable().get())->get_data(),
+ null_map->get_data(), input_rows_count);
+
+ block.get_by_position(result).column =
+ ColumnNullable::create(std::move(res), std::move(null_map));
+ return Status::OK();
+ }
+
+ static void executeImpl(const PaddedPODArray<ArgType>& ldata, const
ColumnString::Chars& rdata,
+ const ColumnString::Offsets& roffsets,
PaddedPODArray<ArgType>& res,
+ NullMap& null_map, size_t input_rows_count) {
+ res.resize(input_rows_count);
+ for (size_t i = 0; i < input_rows_count; ++i) {
+ auto dt = binary_cast<ArgType, DateValueType>(ldata[i]);
+ const char* str_data = reinterpret_cast<const
char*>(&rdata[roffsets[i - 1]]);
+ if (std::strncmp("year", str_data, 4) == 0) {
+ null_map[i] = !dt.template datetime_trunc<YEAR>();
+ } else if (std::strncmp("quarter", str_data, 7) == 0) {
+ null_map[i] = !dt.template datetime_trunc<QUARTER>();
+ } else if (std::strncmp("month", str_data, 5) == 0) {
+ null_map[i] = !dt.template datetime_trunc<MONTH>();
+ } else if (std::strncmp("day", str_data, 3) == 0) {
+ null_map[i] = !dt.template datetime_trunc<DAY>();
+ } else if (std::strncmp("hour", str_data, 4) == 0) {
+ null_map[i] = !dt.template datetime_trunc<HOUR>();
+ } else if (std::strncmp("minute", str_data, 6) == 0) {
+ null_map[i] = !dt.template datetime_trunc<MINUTE>();
+ } else if (std::strncmp("second", str_data, 6) == 0) {
+ null_map[i] = !dt.template datetime_trunc<SECOND>();
+ } else {
+ null_map[i] = 1;
+ }
+ res[i] = binary_cast<DateValueType, ArgType>(dt);
+ }
+ }
+};
+
class FromDays : public IFunction {
public:
static constexpr auto name = "from_days";
@@ -488,8 +579,14 @@ public:
size_t get_number_of_arguments() const override { return 2; }
+ bool is_variadic() const override { return Impl::is_variadic(); }
+
+ DataTypes get_variadic_argument_types_impl() const override {
+ return Impl::get_variadic_argument_types();
+ }
+
DataTypePtr get_return_type_impl(const DataTypes& arguments) const
override {
- return make_nullable(std::make_shared<DataTypeDateTime>());
+ return Impl::get_return_type_impl(arguments);
}
bool use_default_implementation_for_constants() const override { return
true; }
@@ -501,13 +598,17 @@ public:
};
using FunctionStrToDate = FunctionOtherTypesToDateType<StrToDate>;
-
using FunctionMakeDate = FunctionOtherTypesToDateType<MakeDateImpl>;
+using FunctionDateTrunc =
FunctionOtherTypesToDateType<DateTrunc<VecDateTimeValue, Int64>>;
+using FunctionDateTruncV2 =
+
FunctionOtherTypesToDateType<DateTrunc<DateV2Value<DateTimeV2ValueType>,
UInt64>>;
void register_function_timestamp(SimpleFunctionFactory& factory) {
factory.register_function<FunctionStrToDate>();
factory.register_function<FunctionMakeDate>();
factory.register_function<FromDays>();
+ factory.register_function<FunctionDateTrunc>();
+ factory.register_function<FunctionDateTruncV2>();
factory.register_function<FunctionUnixTimestamp<UnixTimeStampImpl>>();
factory.register_function<FunctionUnixTimestamp<UnixTimeStampDateImpl<DataTypeDate>>>();
diff --git a/be/src/vec/runtime/vdatetime_value.cpp
b/be/src/vec/runtime/vdatetime_value.cpp
index 1696e57d89..28ecf960f2 100644
--- a/be/src/vec/runtime/vdatetime_value.cpp
+++ b/be/src/vec/runtime/vdatetime_value.cpp
@@ -1668,6 +1668,67 @@ void VecDateTimeValue::set_time(uint32_t year, uint32_t
month, uint32_t day, uin
_second = second;
}
+template <TimeUnit unit>
+bool VecDateTimeValue::datetime_trunc() {
+ if (!is_valid_date()) {
+ return false;
+ }
+ switch (unit) {
+ case SECOND: {
+ break;
+ }
+ case MINUTE: {
+ _second = 0;
+ break;
+ }
+ case HOUR: {
+ _second = 0;
+ _minute = 0;
+ break;
+ }
+ case DAY: {
+ _second = 0;
+ _minute = 0;
+ _hour = 0;
+ break;
+ }
+ case MONTH: {
+ _second = 0;
+ _minute = 0;
+ _hour = 0;
+ _day = 1;
+ break;
+ }
+ case QUARTER: {
+ _second = 0;
+ _minute = 0;
+ _hour = 0;
+ _day = 1;
+ if (_month <= 3) {
+ _month = 1;
+ } else if (_month <= 6) {
+ _month = 4;
+ } else if (_month <= 9) {
+ _month = 7;
+ } else {
+ _month = 10;
+ }
+ break;
+ }
+ case YEAR: {
+ _second = 0;
+ _minute = 0;
+ _hour = 0;
+ _day = 1;
+ _month = 1;
+ break;
+ }
+ default:
+ return false;
+ }
+ return true;
+}
+
template <typename T>
void VecDateTimeValue::create_from_date_v2(DateV2Value<T>& value, TimeType
type) {
if constexpr (std::is_same_v<T, DateV2ValueType>) {
@@ -2595,6 +2656,78 @@ bool DateV2Value<T>::date_add_interval(const
TimeInterval& interval) {
return true;
}
+template <typename T>
+template <TimeUnit unit>
+bool DateV2Value<T>::datetime_trunc() {
+ if constexpr (is_datetime) {
+ if (!is_valid_date()) {
+ return false;
+ }
+ switch (unit) {
+ case SECOND: {
+ date_v2_value_.microsecond_ = 0;
+ break;
+ }
+ case MINUTE: {
+ date_v2_value_.microsecond_ = 0;
+ date_v2_value_.second_ = 0;
+ break;
+ }
+ case HOUR: {
+ date_v2_value_.microsecond_ = 0;
+ date_v2_value_.second_ = 0;
+ date_v2_value_.minute_ = 0;
+ break;
+ }
+ case DAY: {
+ date_v2_value_.microsecond_ = 0;
+ date_v2_value_.second_ = 0;
+ date_v2_value_.minute_ = 0;
+ date_v2_value_.hour_ = 0;
+ break;
+ }
+ case MONTH: {
+ date_v2_value_.microsecond_ = 0;
+ date_v2_value_.second_ = 0;
+ date_v2_value_.minute_ = 0;
+ date_v2_value_.hour_ = 0;
+ date_v2_value_.day_ = 1;
+ break;
+ }
+ case QUARTER: {
+ date_v2_value_.microsecond_ = 0;
+ date_v2_value_.second_ = 0;
+ date_v2_value_.minute_ = 0;
+ date_v2_value_.hour_ = 0;
+ date_v2_value_.day_ = 1;
+ if (date_v2_value_.month_ <= 3) {
+ date_v2_value_.month_ = 1;
+ } else if (date_v2_value_.month_ <= 6) {
+ date_v2_value_.month_ = 4;
+ } else if (date_v2_value_.month_ <= 9) {
+ date_v2_value_.month_ = 7;
+ } else {
+ date_v2_value_.month_ = 10;
+ }
+ break;
+ }
+ case YEAR: {
+ date_v2_value_.microsecond_ = 0;
+ date_v2_value_.second_ = 0;
+ date_v2_value_.minute_ = 0;
+ date_v2_value_.hour_ = 0;
+ date_v2_value_.day_ = 1;
+ date_v2_value_.month_ = 1;
+ break;
+ }
+ default:
+ return false;
+ }
+ return true;
+ }
+ return false;
+}
+
template <typename T>
bool DateV2Value<T>::unix_timestamp(int64_t* timestamp, const std::string&
timezone) const {
cctz::time_zone ctz;
@@ -3266,4 +3399,31 @@ template bool
DateV2Value<DateTimeV2ValueType>::date_add_interval<TimeUnit::QUAR
template bool
DateV2Value<DateTimeV2ValueType>::date_add_interval<TimeUnit::WEEK>(
const TimeInterval& interval);
+template bool VecDateTimeValue::datetime_trunc<TimeUnit::SECOND>();
+template bool VecDateTimeValue::datetime_trunc<TimeUnit::MINUTE>();
+template bool VecDateTimeValue::datetime_trunc<TimeUnit::HOUR>();
+template bool VecDateTimeValue::datetime_trunc<TimeUnit::DAY>();
+template bool VecDateTimeValue::datetime_trunc<TimeUnit::MONTH>();
+template bool VecDateTimeValue::datetime_trunc<TimeUnit::YEAR>();
+template bool VecDateTimeValue::datetime_trunc<TimeUnit::QUARTER>();
+template bool VecDateTimeValue::datetime_trunc<TimeUnit::WEEK>();
+
+template bool DateV2Value<DateV2ValueType>::datetime_trunc<TimeUnit::SECOND>();
+template bool DateV2Value<DateV2ValueType>::datetime_trunc<TimeUnit::MINUTE>();
+template bool DateV2Value<DateV2ValueType>::datetime_trunc<TimeUnit::HOUR>();
+template bool DateV2Value<DateV2ValueType>::datetime_trunc<TimeUnit::DAY>();
+template bool DateV2Value<DateV2ValueType>::datetime_trunc<TimeUnit::MONTH>();
+template bool DateV2Value<DateV2ValueType>::datetime_trunc<TimeUnit::YEAR>();
+template bool
DateV2Value<DateV2ValueType>::datetime_trunc<TimeUnit::QUARTER>();
+template bool DateV2Value<DateV2ValueType>::datetime_trunc<TimeUnit::WEEK>();
+
+template bool
DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::SECOND>();
+template bool
DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::MINUTE>();
+template bool
DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::HOUR>();
+template bool
DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::DAY>();
+template bool
DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::MONTH>();
+template bool
DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::YEAR>();
+template bool
DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::QUARTER>();
+template bool
DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::WEEK>();
+
} // namespace doris::vectorized
diff --git a/be/src/vec/runtime/vdatetime_value.h
b/be/src/vec/runtime/vdatetime_value.h
index 7af0c273b1..edf918d5fb 100644
--- a/be/src/vec/runtime/vdatetime_value.h
+++ b/be/src/vec/runtime/vdatetime_value.h
@@ -461,6 +461,9 @@ public:
template <TimeUnit unit>
bool date_add_interval(const TimeInterval& interval);
+ template <TimeUnit unit>
+ bool datetime_trunc(); //datetime trunc, like trunc minute = 0
+
//unix_timestamp is called with a timezone argument,
//it returns seconds of the value of date literal since '1970-01-01
00:00:00' UTC
bool unix_timestamp(int64_t* timestamp, const std::string& timezone) const;
@@ -912,6 +915,9 @@ public:
template <TimeUnit unit>
bool date_add_interval(const TimeInterval& interval);
+ template <TimeUnit unit>
+ bool datetime_trunc(); //datetime trunc, like trunc minute = 0
+
//unix_timestamp is called with a timezone argument,
//it returns seconds of the value of date literal since '1970-01-01
00:00:00' UTC
bool unix_timestamp(int64_t* timestamp, const std::string& timezone) const;
diff --git a/be/test/vec/function/function_time_test.cpp
b/be/test/vec/function/function_time_test.cpp
index 4f6a49ba23..18455a4566 100644
--- a/be/test/vec/function/function_time_test.cpp
+++ b/be/test/vec/function/function_time_test.cpp
@@ -1620,6 +1620,47 @@ TEST(VTimestampFunctionsTest, dayname_test) {
}
}
+TEST(VTimestampFunctionsTest, datetrunc_test) {
+ std::string func_name = "date_trunc";
+ {
+ InputTypeSet input_types = {TypeIndex::DateTime, TypeIndex::String};
+
+ DataSet data_set = {{{std::string("2022-10-08 11:44:23"),
std::string("second")},
+ str_to_date_time("2022-10-08 11:44:23")},
+ {{std::string("2022-10-08 11:44:23"),
std::string("minute")},
+ str_to_date_time("2022-10-08 11:44:00")},
+ {{std::string("2022-10-08 11:44:23"),
std::string("hour")},
+ str_to_date_time("2022-10-08 11:00:00")},
+ {{std::string("2022-10-08 11:44:23"),
std::string("day")},
+ str_to_date_time("2022-10-08 00:00:00")},
+ {{std::string("2022-10-08 11:44:23"),
std::string("month")},
+ str_to_date_time("2022-10-01 00:00:00")},
+ {{std::string("2022-10-08 11:44:23"),
std::string("year")},
+ str_to_date_time("2022-01-01 00:00:00")}};
+
+ check_function<DataTypeDateTime, true>(func_name, input_types,
data_set);
+ }
+
+ {
+ InputTypeSet input_types = {TypeIndex::DateTimeV2, TypeIndex::String};
+
+ DataSet data_set = {{{std::string("2022-10-08 11:44:23.123"),
std::string("second")},
+ str_to_datetime_v2("2022-10-08 11:44:23.000",
"%Y-%m-%d %H:%i:%s.%f")},
+ {{std::string("2022-10-08 11:44:23"),
std::string("minute")},
+ str_to_datetime_v2("2022-10-08 11:44:00",
"%Y-%m-%d %H:%i:%s")},
+ {{std::string("2022-10-08 11:44:23"),
std::string("hour")},
+ str_to_datetime_v2("2022-10-08 11:00:00",
"%Y-%m-%d %H:%i:%s")},
+ {{std::string("2022-10-08 11:44:23"),
std::string("day")},
+ str_to_datetime_v2("2022-10-08 00:00:00",
"%Y-%m-%d %H:%i:%s")},
+ {{std::string("2022-10-08 11:44:23"),
std::string("month")},
+ str_to_datetime_v2("2022-10-01 00:00:00",
"%Y-%m-%d %H:%i:%s")},
+ {{std::string("2022-10-08 11:44:23"),
std::string("year")},
+ str_to_datetime_v2("2022-01-01 00:00:00",
"%Y-%m-%d %H:%i:%s")}};
+
+ check_function<DataTypeDateTimeV2, true>(func_name, input_types,
data_set);
+ }
+}
+
TEST(VTimestampFunctionsTest, hours_add_v2_test) {
std::string func_name = "hours_add";
diff --git
a/docs/en/docs/sql-manual/sql-functions/array-functions/countequal.md
b/docs/en/docs/sql-manual/sql-functions/array-functions/countequal.md
new file mode 100644
index 0000000000..f743774964
--- /dev/null
+++ b/docs/en/docs/sql-manual/sql-functions/array-functions/countequal.md
@@ -0,0 +1,65 @@
+---
+{
+ "title": "countequal",
+ "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.
+-->
+
+## countequal
+
+### description
+
+#### Syntax
+
+`BIGINT countequal(ARRAY<T> arr, T value)`
+
+Returns a number of the `value` in the given array.
+
+```
+num - how many the value number in array;
+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 *, countEqual(c_array,5) from array_test;
++------+-----------------+--------------------------+
+| id | c_array | countequal(`c_array`, 5) |
++------+-----------------+--------------------------+
+| 1 | [1, 2, 3, 4, 5] | 1 |
+| 2 | [6, 7, 8] | 0 |
+| 3 | [] | 0 |
+| 4 | NULL | NULL |
++------+-----------------+--------------------------+
+```
+
+### keywords
+
+ARRAY,COUNTEQUAL,
diff --git
a/docs/en/docs/sql-manual/sql-functions/date-time-functions/date_trunc.md
b/docs/en/docs/sql-manual/sql-functions/date-time-functions/date_trunc.md
new file mode 100644
index 0000000000..385d43ba70
--- /dev/null
+++ b/docs/en/docs/sql-manual/sql-functions/date-time-functions/date_trunc.md
@@ -0,0 +1,100 @@
+---
+{
+ "title": "date_trunc",
+ "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.
+-->
+
+## date_trunc
+### Description
+#### Syntax
+
+`DATETIME DATE_TRUNC(DATETIME datetime,VARCHAR unit)`
+
+
+Truncates datetime in the specified time unit.
+
+datetime is a legal date expression.
+
+unit is the time unit you want to truncate. The optional values are as
follows: [`second`,`minute`,`hour`,`day`,`month`,`quarter`,`year`]。
+If unit does not meet the above optional values, the result will return NULL.
+### example
+
+```
+mysql> select date_trunc('2010-12-02 19:28:30', 'second');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'second') |
++-------------------------------------------------+
+| 2010-12-02 19:28:30 |
++-------------------------------------------------+
+
+mysql> select date_trunc('2010-12-02 19:28:30', 'minute');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'minute') |
++-------------------------------------------------+
+| 2010-12-02 19:28:00 |
++-------------------------------------------------+
+
+mysql> select date_trunc('2010-12-02 19:28:30', 'hour');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'hour') |
++-------------------------------------------------+
+| 2010-12-02 19:00:00 |
++-------------------------------------------------+
+
+mysql> select date_trunc('2010-12-02 19:28:30', 'day');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'day') |
++-------------------------------------------------+
+| 2010-12-02 00:00:00 |
++-------------------------------------------------+
+
+mysql> select date_trunc('2010-12-02 19:28:30', 'week');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'week') |
++-------------------------------------------------+
+| 2010-11-28 00:00:00 |
++-------------------------------------------------+
+
+mysql> select date_trunc('2010-12-02 19:28:30', 'month');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'month') |
++-------------------------------------------------+
+| 2010-12-01 00:00:00 |
++-------------------------------------------------+
+
+mysql> select date_trunc('2010-12-02 19:28:30', 'quarter');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'quarter') |
++-------------------------------------------------+
+| 2010-10-01 00:00:00 |
++-------------------------------------------------+
+
+mysql> select date_trunc('2010-12-02 19:28:30', 'year');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'year') |
++-------------------------------------------------+
+| 2010-01-01 00:00:00 |
++-------------------------------------------------+
+```
+### keywords
+ DATE_TRUNC,DATE,DATETIME
diff --git a/docs/sidebars.json b/docs/sidebars.json
index 3b423dc338..03e3883ecb 100644
--- a/docs/sidebars.json
+++ b/docs/sidebars.json
@@ -251,6 +251,7 @@
"sql-manual/sql-functions/array-functions/array_intersect",
"sql-manual/sql-functions/array-functions/arrays_overlap",
"sql-manual/sql-functions/array-functions/array_min",
+
"sql-manual/sql-functions/array-functions/countequal",
"sql-manual/sql-functions/array-functions/element_at",
"sql-manual/sql-functions/array-functions/array_avg",
"sql-manual/sql-functions/array-functions/size",
@@ -300,6 +301,7 @@
"sql-manual/sql-functions/date-time-functions/timestampdiff",
"sql-manual/sql-functions/date-time-functions/date_add",
"sql-manual/sql-functions/date-time-functions/date_sub",
+
"sql-manual/sql-functions/date-time-functions/date_trunc",
"sql-manual/sql-functions/date-time-functions/date_format",
"sql-manual/sql-functions/date-time-functions/datediff",
"sql-manual/sql-functions/date-time-functions/minutes_add",
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/array-functions/countequal.md
b/docs/zh-CN/docs/sql-manual/sql-functions/array-functions/countequal.md
new file mode 100644
index 0000000000..761d79881c
--- /dev/null
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/array-functions/countequal.md
@@ -0,0 +1,65 @@
+---
+{
+ "title": "countequal",
+ "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.
+-->
+
+## countequal
+
+### description
+
+#### Syntax
+
+`BIGINT countequal(ARRAY<T> arr, T value)`
+
+判断数组中包含value元素的个数。返回结果如下：
+
+```
+num - value在array中的数量；
+0 - value不存在数组arr中；
+NULL - 如果数组为NULL，或者value为NULL。
+```
+
+### notice
+
+`仅支持向量化引擎中使用`
+
+### example
+
+```
+mysql> set enable_vectorized_engine=true;
+
+mysql> select *, countEqual(c_array,5) from array_test;
++------+-----------------+--------------------------+
+| id | c_array | countequal(`c_array`, 5) |
++------+-----------------+--------------------------+
+| 1 | [1, 2, 3, 4, 5] | 1 |
+| 2 | [6, 7, 8] | 0 |
+| 3 | [] | 0 |
+| 4 | NULL | NULL |
++------+-----------------+--------------------------+
+```
+
+### keywords
+
+ARRAY,COUNTEQUAL
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/date_trunc.md
b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/date_trunc.md
new file mode 100644
index 0000000000..17753027d7
--- /dev/null
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/date_trunc.md
@@ -0,0 +1,95 @@
+---
+{
+ "title": "date_trunc",
+ "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.
+-->
+
+## date_trunc
+### description
+#### Syntax
+
+`DATETIME DATE_TRUNC(DATETIME datetime,VARCHAR unit)`
+
+
+将datetime按照指定的时间单位截断。
+
+datetime 参数是合法的日期表达式。
+
+unit
参数是您希望截断的时间间隔，可选的值如下：[`second`,`minute`,`hour`,`day`,`month`,`quarter`,`year`]。
+如果unit 不符合上述可选值，结果将返回NULL。
+### example
+
+```
+mysql> select date_trunc('2010-12-02 19:28:30', 'second');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'second') |
++-------------------------------------------------+
+| 2010-12-02 19:28:30 |
++-------------------------------------------------+
+
+mysql> select date_trunc('2010-12-02 19:28:30', 'minute');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'minute') |
++-------------------------------------------------+
+| 2010-12-02 19:28:00 |
++-------------------------------------------------+
+
+mysql> select date_trunc('2010-12-02 19:28:30', 'hour');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'hour') |
++-------------------------------------------------+
+| 2010-12-02 19:00:00 |
++-------------------------------------------------+
+
+mysql> select date_trunc('2010-12-02 19:28:30', 'day');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'day') |
++-------------------------------------------------+
+| 2010-12-02 00:00:00 |
++-------------------------------------------------+
+
+mysql> select date_trunc('2010-12-02 19:28:30', 'month');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'month') |
++-------------------------------------------------+
+| 2010-12-01 00:00:00 |
++-------------------------------------------------+
+
+mysql> select date_trunc('2010-12-02 19:28:30', 'quarter');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'quarter') |
++-------------------------------------------------+
+| 2010-10-01 00:00:00 |
++-------------------------------------------------+
+
+mysql> select date_trunc('2010-12-02 19:28:30', 'year');
++-------------------------------------------------+
+| date_trunc('2010-12-02 19:28:30', 'year') |
++-------------------------------------------------+
+| 2010-01-01 00:00:00 |
++-------------------------------------------------+
+```
+
+### keywords
+
+ DATE_TRUNC,DATE,TRUNC
diff --git a/gensrc/script/doris_builtins_functions.py
b/gensrc/script/doris_builtins_functions.py
index d2439fdfa6..2070a546ff 100755
--- a/gensrc/script/doris_builtins_functions.py
+++ b/gensrc/script/doris_builtins_functions.py
@@ -166,6 +166,22 @@ visible_functions = [
[['array_contains'], 'BOOLEAN', ['ARRAY_VARCHAR', 'VARCHAR'], '', '', '',
'vec', ''],
[['array_contains'], 'BOOLEAN', ['ARRAY_STRING', 'STRING'], '', '', '',
'vec', ''],
+ [['countequal'], 'BIGINT', ['ARRAY_BOOLEAN', 'BOOLEAN'], '', '', '',
'vec', ''],
+ [['countequal'], 'BIGINT', ['ARRAY_TINYINT', 'TINYINT'], '', '', '',
'vec', ''],
+ [['countequal'], 'BIGINT', ['ARRAY_SMALLINT', 'SMALLINT'], '', '', '',
'vec', ''],
+ [['countequal'], 'BIGINT', ['ARRAY_INT', 'INT'], '', '', '', 'vec', ''],
+ [['countequal'], 'BIGINT', ['ARRAY_BIGINT', 'BIGINT'], '', '', '', 'vec',
''],
+ [['countequal'], 'BIGINT', ['ARRAY_LARGEINT', 'LARGEINT'], '', '', '',
'vec', ''],
+ [['countequal'], 'BIGINT', ['ARRAY_DATETIME', 'DATETIME'], '', '', '',
'vec', ''],
+ [['countequal'], 'BIGINT', ['ARRAY_DATE', 'DATE'], '', '', '', 'vec', ''],
+ [['countequal'], 'BIGINT', ['ARRAY_DATETIMEV2', 'DATETIMEV2'], '', '', '',
'vec', ''],
+ [['countequal'], 'BIGINT', ['ARRAY_DATEV2', 'DATEV2'], '', '', '', 'vec',
''],
+ [['countequal'], 'BIGINT', ['ARRAY_FLOAT', 'FLOAT'], '', '', '', 'vec',
''],
+ [['countequal'], 'BIGINT', ['ARRAY_DOUBLE', 'DOUBLE'], '', '', '', 'vec',
''],
+ [['countequal'], 'BIGINT', ['ARRAY_DECIMALV2', 'DECIMALV2'], '', '', '',
'vec', ''],
+ [['countequal'], 'BIGINT', ['ARRAY_VARCHAR', 'VARCHAR'], '', '', '',
'vec', ''],
+ [['countequal'], 'BIGINT', ['ARRAY_STRING', 'STRING'], '', '', '', 'vec',
''],
+
[['array_position'], 'BIGINT', ['ARRAY_BOOLEAN', 'BOOLEAN'], '', '', '',
'vec', ''],
[['array_position'], 'BIGINT', ['ARRAY_TINYINT', 'TINYINT'], '', '', '',
'vec', ''],
[['array_position'], 'BIGINT', ['ARRAY_SMALLINT', 'SMALLINT'], '', '', '',
'vec', ''],
@@ -466,7 +482,13 @@ visible_functions = [
'_ZN5doris18TimestampFunctions7to_daysEPN9doris_udf15FunctionContextERKNS1_11DateTimeValE',
'', '', 'vec', 'ALWAYS_NULLABLE'],
- [['year'], 'INT', ['DATETIME'],
+ [['date_trunc'], 'DATETIME', ['DATETIME', 'VARCHAR'],
+ '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+
+ [['date_trunc'], 'DATETIMEV2', ['DATETIMEV2', 'VARCHAR'],
+ '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+
+ [['year'], 'INT', ['DATETIME'],
'_ZN5doris18TimestampFunctions4yearEPN9doris_udf15FunctionContextERKNS1_11DateTimeValE',
'', '', 'vec', 'ALWAYS_NULLABLE'],
[['month'], 'INT', ['DATETIME'],
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
