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 b6ba654f5b [Feature](Sequence) Support sequence_match and
sequence_count functions (#13785)
b6ba654f5b is described below
commit b6ba654f5bdb336ef36374f5a9e0fc41cc8379a8
Author: abmdocrt <[email protected]>
AuthorDate: Fri Nov 11 13:38:45 2022 +0800
[Feature](Sequence) Support sequence_match and sequence_count functions
(#13785)
---
be/src/vec/CMakeLists.txt | 1 +
.../aggregate_function_sequence_match.cpp | 66 ++
.../aggregate_function_sequence_match.h | 722 +++++++++++++++++++++
.../aggregate_function_simple_factory.cpp | 2 +
be/src/vec/io/io_helper.h | 18 +
be/test/CMakeLists.txt | 1 +
.../vec_sequence_match_test.cpp | 424 ++++++++++++
.../aggregate-functions/sequence_count.md | 254 ++++++++
.../aggregate-functions/sequence_match.md | 256 ++++++++
docs/sidebars.json | 4 +-
.../aggregate-functions/sequence_count.md | 254 ++++++++
.../aggregate-functions/sequence_match.md | 256 ++++++++
.../WINDOW-FUNCTION-WINDOW-FUNNEL.md | 2 +-
.../apache/doris/analysis/FunctionCallExpr.java | 116 +++-
.../apache/doris/catalog/AggregateFunction.java | 2 +-
.../java/org/apache/doris/catalog/FunctionSet.java | 90 +++
.../org/apache/doris/analysis/AggregateTest.java | 211 +++++-
.../test_aggregate_sequence_match.out | 115 ++++
.../test_aggregate_sequence_match.sql | 105 +++
19 files changed, 2891 insertions(+), 8 deletions(-)
diff --git a/be/src/vec/CMakeLists.txt b/be/src/vec/CMakeLists.txt
index 991b2d1217..4602bc929b 100644
--- a/be/src/vec/CMakeLists.txt
+++ b/be/src/vec/CMakeLists.txt
@@ -20,6 +20,7 @@ set(LIBRARY_OUTPUT_PATH "${BUILD_DIR}/src/vec")
set(EXECUTABLE_OUTPUT_PATH "${BUILD_DIR}/src/vec")
set(VEC_FILES
+ aggregate_functions/aggregate_function_sequence_match.cpp
aggregate_functions/aggregate_function_retention.cpp
aggregate_functions/aggregate_function_window_funnel.cpp
aggregate_functions/aggregate_function_avg.cpp
diff --git
a/be/src/vec/aggregate_functions/aggregate_function_sequence_match.cpp
b/be/src/vec/aggregate_functions/aggregate_function_sequence_match.cpp
new file mode 100644
index 0000000000..91c65fd188
--- /dev/null
+++ b/be/src/vec/aggregate_functions/aggregate_function_sequence_match.cpp
@@ -0,0 +1,66 @@
+// 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/aggregate_functions/aggregate_function_sequence_match.h"
+
+#include "common/logging.h"
+#include "vec/aggregate_functions/aggregate_function_simple_factory.h"
+#include "vec/aggregate_functions/factory_helpers.h"
+#include "vec/aggregate_functions/helpers.h"
+
+namespace doris::vectorized {
+
+template <template <typename, typename> typename AggregateFunction>
+AggregateFunctionPtr create_aggregate_function_sequence_base(const
std::string& name,
+ const DataTypes&
argument_types,
+ const Array&
parameters,
+ const bool
result_is_nullable) {
+ const auto arg_count = argument_types.size();
+
+ if (arg_count < 4) {
+ LOG(WARNING) << "Aggregate function " + name + " requires at least 4
arguments.";
+ return nullptr;
+ }
+ if (arg_count - 2 > max_events) {
+ LOG(WARNING) << "Aggregate function " + name + " supports up to " +
+ std::to_string(max_events) + " event
arguments.";
+ return nullptr;
+ }
+
+ if (WhichDataType(remove_nullable(argument_types[1])).is_date_time_v2()) {
+ return
std::make_shared<AggregateFunction<DateV2Value<DateTimeV2ValueType>, UInt64>>(
+ argument_types);
+ } else if
(WhichDataType(remove_nullable(argument_types[1])).is_date_time()) {
+ return std::make_shared<AggregateFunction<VecDateTimeValue,
Int64>>(argument_types);
+ } else if (WhichDataType(remove_nullable(argument_types[1])).is_date_v2())
{
+ return
std::make_shared<AggregateFunction<DateV2Value<DateV2ValueType>, UInt32>>(
+ argument_types);
+ } else {
+ LOG(FATAL) << "Only support Date and DateTime type as timestamp
argument!";
+ return nullptr;
+ }
+}
+
+void
register_aggregate_function_sequence_match(AggregateFunctionSimpleFactory&
factory) {
+ factory.register_function(
+ "sequence_match",
+
create_aggregate_function_sequence_base<AggregateFunctionSequenceMatch>);
+ factory.register_function(
+ "sequence_count",
+
create_aggregate_function_sequence_base<AggregateFunctionSequenceCount>);
+}
+} // namespace doris::vectorized
diff --git a/be/src/vec/aggregate_functions/aggregate_function_sequence_match.h
b/be/src/vec/aggregate_functions/aggregate_function_sequence_match.h
new file mode 100644
index 0000000000..d9daee8dfd
--- /dev/null
+++ b/be/src/vec/aggregate_functions/aggregate_function_sequence_match.h
@@ -0,0 +1,722 @@
+// 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.
+
+// This file is copied from
+//
https://github.com/ClickHouse/ClickHouse/blob/master/AggregateFunctionSequenceMatch.h
+// and modified by Doris
+
+#pragma once
+
+#include <bitset>
+
+#include "common/logging.h"
+#include "vec/aggregate_functions/aggregate_function.h"
+#include "vec/columns/column_array.h"
+#include "vec/columns/columns_number.h"
+#include "vec/data_types/data_type_decimal.h"
+#include "vec/io/io_helper.h"
+#include "vec/io/var_int.h"
+
+namespace doris::vectorized {
+
+template <template <typename> class Comparator>
+struct ComparePairFirst final {
+ template <typename T1, typename T2>
+ bool operator()(const std::pair<T1, T2>& lhs, const std::pair<T1, T2>&
rhs) const {
+ return Comparator<T1> {}(lhs.first, rhs.first);
+ }
+};
+
+static constexpr size_t max_events = 32;
+
+/// Max number of iterations to match the pattern against a sequence,
exception thrown when exceeded
+constexpr auto sequence_match_max_iterations = 1000000;
+
+template <typename DateValueType, typename NativeType, typename Derived>
+struct AggregateFunctionSequenceMatchData final {
+ using Timestamp = DateValueType;
+ using Events = std::bitset<max_events>;
+ using TimestampEvents = std::pair<Timestamp, Events>;
+ using Comparator = ComparePairFirst<std::less>;
+
+ AggregateFunctionSequenceMatchData() { reset(); }
+
+public:
+ const std::string get_pattern() const { return pattern; }
+
+ size_t get_arg_count() const { return arg_count; }
+
+ void init(const std::string pattern, size_t arg_count) {
+ if (!init_flag) {
+ this->pattern = pattern;
+ this->arg_count = arg_count;
+ parse_pattern();
+ init_flag = true;
+ }
+ }
+
+ void reset() {
+ sorted = true;
+ init_flag = false;
+ pattern_has_time = false;
+ pattern = "";
+ arg_count = 0;
+ conditions_met.reset();
+ conditions_in_pattern.reset();
+
+ events_list.clear();
+ actions.clear();
+ dfa_states.clear();
+ }
+
+ void add(const Timestamp& timestamp, const Events& events) {
+ /// store information exclusively for rows with at least one event
+ if (events.any()) {
+ events_list.emplace_back(timestamp, events);
+ sorted = false;
+ conditions_met |= events;
+ }
+ }
+
+ void merge(const AggregateFunctionSequenceMatchData& other) {
+ if (other.events_list.empty()) return;
+
+ events_list.insert(std::begin(other.events_list),
std::end(other.events_list));
+ sorted = false;
+ conditions_met |= other.conditions_met;
+ }
+
+ void sort() {
+ if (sorted) return;
+
+ std::sort(std::begin(events_list), std::end(events_list), Comparator
{});
+ sorted = true;
+ }
+
+ void write(BufferWritable& buf) const {
+ write_binary(sorted, buf);
+ write_binary(events_list.size(), buf);
+
+ for (const auto& events : events_list) {
+ write_binary(events.first, buf);
+ write_binary(events.second.to_ulong(), buf);
+ }
+
+ UInt32 conditions_met_value = conditions_met.to_ulong();
+ write_binary(conditions_met_value, buf);
+
+ write_binary(pattern, buf);
+ write_binary(arg_count, buf);
+ }
+
+ void read(BufferReadable& buf) {
+ read_binary(sorted, buf);
+
+ size_t events_list_size;
+ read_binary(events_list_size, buf);
+
+ events_list.clear();
+ events_list.reserve(events_list_size);
+
+ for (size_t i = 0; i < events_list_size; ++i) {
+ Timestamp timestamp;
+ read_binary(timestamp, buf);
+
+ UInt64 events;
+ read_binary(events, buf);
+
+ events_list.emplace_back(timestamp, Events {events});
+ }
+
+ UInt32 conditions_met_value;
+ read_binary(conditions_met_value, buf);
+ conditions_met = conditions_met_value;
+
+ read_binary(pattern, buf);
+ read_binary(arg_count, buf);
+ }
+
+private:
+ enum class PatternActionType {
+ SpecificEvent,
+ AnyEvent,
+ KleeneStar,
+ TimeLessOrEqual,
+ TimeLess,
+ TimeGreaterOrEqual,
+ TimeGreater,
+ TimeEqual
+ };
+
+ struct PatternAction final {
+ PatternActionType type;
+ std::uint64_t extra;
+
+ PatternAction() = default;
+ explicit PatternAction(const PatternActionType type_, const
std::uint64_t extra_ = 0)
+ : type {type_}, extra {extra_} {}
+ };
+
+ using PatternActions = PODArrayWithStackMemory<PatternAction, 64>;
+
+ Derived& derived() { return static_cast<Derived&>(*this); }
+
+ void parse_pattern() {
+ actions.clear();
+ actions.emplace_back(PatternActionType::KleeneStar);
+
+ dfa_states.clear();
+ dfa_states.emplace_back(true);
+
+ pattern_has_time = false;
+
+ const char* pos = pattern.data();
+ const char* begin = pos;
+ const char* end = pos + pattern.size();
+
+ // Pattern is checked in fe, so pattern should be vaild here, we check
it and if pattern is invalid, we return.
+ auto throw_exception = [&](const std::string& msg) {
+ LOG(WARNING) << msg + " '" + std::string(pos, end) + "' at
position " +
+ std::to_string(pos - begin);
+ };
+
+ auto match = [&pos, end](const char* str) mutable {
+ size_t length = strlen(str);
+ if (pos + length <= end && 0 == memcmp(pos, str, length)) {
+ pos += length;
+ return true;
+ }
+ return false;
+ };
+
+ while (pos < end) {
+ if (match("(?")) {
+ if (match("t")) {
+ PatternActionType type;
+
+ if (match("<="))
+ type = PatternActionType::TimeLessOrEqual;
+ else if (match("<"))
+ type = PatternActionType::TimeLess;
+ else if (match(">="))
+ type = PatternActionType::TimeGreaterOrEqual;
+ else if (match(">"))
+ type = PatternActionType::TimeGreater;
+ else if (match("=="))
+ type = PatternActionType::TimeEqual;
+ else {
+ throw_exception("Unknown time condition");
+ return;
+ }
+
+ NativeType duration = 0;
+ const auto* prev_pos = pos;
+ pos = try_read_first_int_text(duration, pos, end);
+ if (pos == prev_pos) {
+ throw_exception("Could not parse number");
+ return;
+ }
+
+ if (actions.back().type !=
PatternActionType::SpecificEvent &&
+ actions.back().type != PatternActionType::AnyEvent &&
+ actions.back().type != PatternActionType::KleeneStar) {
+ throw_exception(
+ "Temporal condition should be preceded by an
event condition");
+ return;
+ }
+
+ pattern_has_time = true;
+ actions.emplace_back(type, duration);
+ } else {
+ UInt64 event_number = 0;
+ const auto* prev_pos = pos;
+ pos = try_read_first_int_text(event_number, pos, end);
+ if (pos == prev_pos) throw_exception("Could not parse
number");
+
+ if (event_number > arg_count - 1) {
+ throw_exception("Event number " +
std::to_string(event_number) +
+ " is out of range");
+ return;
+ }
+
+ actions.emplace_back(PatternActionType::SpecificEvent,
event_number - 1);
+ dfa_states.back().transition =
DFATransition::SpecificEvent;
+ dfa_states.back().event =
static_cast<uint32_t>(event_number - 1);
+ dfa_states.emplace_back();
+ conditions_in_pattern.set(event_number - 1);
+ }
+
+ if (!match(")")) {
+ throw_exception("Expected closing parenthesis, found");
+ return;
+ }
+
+ } else if (match(".*")) {
+ actions.emplace_back(PatternActionType::KleeneStar);
+ dfa_states.back().has_kleene = true;
+ } else if (match(".")) {
+ actions.emplace_back(PatternActionType::AnyEvent);
+ dfa_states.back().transition = DFATransition::AnyEvent;
+ dfa_states.emplace_back();
+ } else {
+ throw_exception("Could not parse pattern, unexpected starting
symbol");
+ return;
+ }
+ }
+ }
+
+public:
+ /// Uses a DFA based approach in order to better handle patterns without
+ /// time assertions.
+ ///
+ /// NOTE: This implementation relies on the assumption that the pattern is
*small*.
+ ///
+ /// This algorithm performs in O(mn) (with m the number of DFA states and
N the number
+ /// of events) with a memory consumption and memory allocations in O(m).
It means that
+ /// if n >>> m (which is expected to be the case), this algorithm can be
considered linear.
+ template <typename EventEntry>
+ bool dfa_match(EventEntry& events_it, const EventEntry events_end) const {
+ using ActiveStates = std::vector<bool>;
+ /// Those two vectors keep track of which states should be considered
for the current
+ /// event as well as the states which should be considered for the
next event.
+ ActiveStates active_states(dfa_states.size(), false);
+ ActiveStates next_active_states(dfa_states.size(), false);
+ active_states[0] = true;
+
+ /// Keeps track of dead-ends in order not to iterate over all the
events to realize that
+ /// the match failed.
+ size_t n_active = 1;
+
+ for (/* empty */; events_it != events_end && n_active > 0 &&
!active_states.back();
+ ++events_it) {
+ n_active = 0;
+ next_active_states.assign(dfa_states.size(), false);
+
+ for (size_t state = 0; state < dfa_states.size(); ++state) {
+ if (!active_states[state]) {
+ continue;
+ }
+
+ switch (dfa_states[state].transition) {
+ case DFATransition::None:
+ break;
+ case DFATransition::AnyEvent:
+ next_active_states[state + 1] = true;
+ ++n_active;
+ break;
+ case DFATransition::SpecificEvent:
+ if (events_it->second.test(dfa_states[state].event)) {
+ next_active_states[state + 1] = true;
+ ++n_active;
+ }
+ break;
+ }
+
+ if (dfa_states[state].has_kleene) {
+ next_active_states[state] = true;
+ ++n_active;
+ }
+ }
+ swap(active_states, next_active_states);
+ }
+
+ return active_states.back();
+ }
+
+ template <typename EventEntry>
+ bool backtracking_match(EventEntry& events_it, const EventEntry
events_end) const {
+ const auto action_begin = std::begin(actions);
+ const auto action_end = std::end(actions);
+ auto action_it = action_begin;
+
+ const auto events_begin = events_it;
+ auto base_it = events_it;
+
+ /// an iterator to action plus an iterator to row in events list plus
timestamp at the start of sequence
+ using backtrack_info = std::tuple<decltype(action_it), EventEntry,
EventEntry>;
+ std::stack<backtrack_info> back_stack;
+
+ /// backtrack if possible
+ const auto do_backtrack = [&] {
+ while (!back_stack.empty()) {
+ auto& top = back_stack.top();
+
+ action_it = std::get<0>(top);
+ events_it = std::next(std::get<1>(top));
+ base_it = std::get<2>(top);
+
+ back_stack.pop();
+
+ if (events_it != events_end) return true;
+ }
+
+ return false;
+ };
+
+ size_t i = 0;
+ while (action_it != action_end && events_it != events_end) {
+ if (action_it->type == PatternActionType::SpecificEvent) {
+ if (events_it->second.test(action_it->extra)) {
+ /// move to the next action and events
+ base_it = events_it;
+ ++action_it, ++events_it;
+ } else if (!do_backtrack())
+ /// backtracking failed, bail out
+ break;
+ } else if (action_it->type == PatternActionType::AnyEvent) {
+ base_it = events_it;
+ ++action_it, ++events_it;
+ } else if (action_it->type == PatternActionType::KleeneStar) {
+ back_stack.emplace(action_it, events_it, base_it);
+ base_it = events_it;
+ ++action_it;
+ } else if (action_it->type == PatternActionType::TimeLessOrEqual) {
+ if (events_it->first.second_diff(base_it->first) <=
action_it->extra) {
+ /// condition satisfied, move onto next action
+ back_stack.emplace(action_it, events_it, base_it);
+ base_it = events_it;
+ ++action_it;
+ } else if (!do_backtrack())
+ break;
+ } else if (action_it->type == PatternActionType::TimeLess) {
+ if (events_it->first.second_diff(base_it->first) <
action_it->extra) {
+ back_stack.emplace(action_it, events_it, base_it);
+ base_it = events_it;
+ ++action_it;
+ } else if (!do_backtrack())
+ break;
+ } else if (action_it->type ==
PatternActionType::TimeGreaterOrEqual) {
+ if (events_it->first.second_diff(base_it->first) >=
action_it->extra) {
+ back_stack.emplace(action_it, events_it, base_it);
+ base_it = events_it;
+ ++action_it;
+ } else if (++events_it == events_end && !do_backtrack())
+ break;
+ } else if (action_it->type == PatternActionType::TimeGreater) {
+ if (events_it->first.second_diff(base_it->first) >
action_it->extra) {
+ back_stack.emplace(action_it, events_it, base_it);
+ base_it = events_it;
+ ++action_it;
+ } else if (++events_it == events_end && !do_backtrack())
+ break;
+ } else if (action_it->type == PatternActionType::TimeEqual) {
+ if (events_it->first.second_diff(base_it->first) ==
action_it->extra) {
+ back_stack.emplace(action_it, events_it, base_it);
+ base_it = events_it;
+ ++action_it;
+ } else if (++events_it == events_end && !do_backtrack())
+ break;
+ } else {
+ LOG(WARNING) << "Unknown PatternActionType";
+ return false;
+ }
+
+ if (++i > sequence_match_max_iterations) {
+ LOG(WARNING)
+ << "Pattern application proves too difficult,
exceeding max iterations (" +
+
std::to_string(sequence_match_max_iterations) + ")";
+ return false;
+ }
+ }
+
+ /// if there are some actions remaining
+ if (action_it != action_end) {
+ /// match multiple empty strings at end
+ while (action_it->type == PatternActionType::KleeneStar ||
+ action_it->type == PatternActionType::TimeLessOrEqual ||
+ action_it->type == PatternActionType::TimeLess ||
+ (action_it->type == PatternActionType::TimeGreaterOrEqual &&
+ action_it->extra == 0))
+ ++action_it;
+ }
+
+ if (events_it == events_begin) ++events_it;
+
+ return action_it == action_end;
+ }
+
+ /// Splits the pattern into deterministic parts separated by
non-deterministic fragments
+ /// (time constraints and Kleene stars), and tries to match the
deterministic parts in their specified order,
+ /// ignoring the non-deterministic fragments.
+ /// This function can quickly check that a full match is not possible if
some deterministic fragment is missing.
+ template <typename EventEntry>
+ bool could_match_deterministic_parts(const EventEntry events_begin, const
EventEntry events_end,
+ bool limit_iterations = true) const {
+ size_t events_processed = 0;
+ auto events_it = events_begin;
+
+ const auto actions_end = std::end(actions);
+ auto actions_it = std::begin(actions);
+ auto det_part_begin = actions_it;
+
+ auto match_deterministic_part = [&events_it, events_end,
&events_processed, det_part_begin,
+ actions_it, limit_iterations]() {
+ auto events_it_init = events_it;
+ auto det_part_it = det_part_begin;
+
+ while (det_part_it != actions_it && events_it != events_end) {
+ /// matching any event
+ if (det_part_it->type == PatternActionType::AnyEvent)
++events_it, ++det_part_it;
+
+ /// matching specific event
+ else {
+ if (events_it->second.test(det_part_it->extra))
++events_it, ++det_part_it;
+
+ /// abandon current matching, try to match the
deterministic fragment further in the list
+ else {
+ events_it = ++events_it_init;
+ det_part_it = det_part_begin;
+ }
+ }
+
+ if (limit_iterations && ++events_processed >
sequence_match_max_iterations) {
+ LOG(WARNING) << "Pattern application proves too difficult,
exceeding max "
+ "iterations are " +
+
std::to_string(sequence_match_max_iterations);
+ return false;
+ }
+ }
+
+ return det_part_it == actions_it;
+ };
+
+ for (; actions_it != actions_end; ++actions_it)
+ if (actions_it->type != PatternActionType::SpecificEvent &&
+ actions_it->type != PatternActionType::AnyEvent) {
+ if (!match_deterministic_part()) return false;
+ det_part_begin = std::next(actions_it);
+ }
+
+ return match_deterministic_part();
+ }
+
+private:
+ enum class DFATransition : char {
+ /// .-------.
+ /// | |
+ /// `-------'
+ None,
+ /// .-------. (?[0-9])
+ /// | | ----------
+ /// `-------'
+ SpecificEvent,
+ /// .-------. .
+ /// | | ----------
+ /// `-------'
+ AnyEvent,
+ };
+
+ struct DFAState {
+ explicit DFAState(bool has_kleene_ = false)
+ : has_kleene {has_kleene_}, event {0}, transition
{DFATransition::None} {}
+
+ /// .-------.
+ /// | | - - -
+ /// `-------'
+ /// |_^
+ bool has_kleene;
+ /// In the case of a state transitions with a `SpecificEvent`,
+ /// `event` contains the value of the event.
+ uint32_t event;
+ /// The kind of transition out of this state.
+ DFATransition transition;
+ };
+
+ using DFAStates = std::vector<DFAState>;
+
+public:
+ bool sorted = true;
+ PODArrayWithStackMemory<TimestampEvents, 64> events_list;
+ // sequenceMatch conditions met at least once in events_list
+ std::bitset<max_events> conditions_met;
+ // sequenceMatch conditions met at least once in the pattern
+ std::bitset<max_events> conditions_in_pattern;
+ // `True` if the parsed pattern contains time assertions (?t...), `false`
otherwise.
+ bool pattern_has_time;
+
+private:
+ std::string pattern;
+ size_t arg_count;
+ bool init_flag = false;
+
+ PatternActions actions;
+ DFAStates dfa_states;
+};
+
+template <typename DateValueType, typename NativeType, typename Derived>
+class AggregateFunctionSequenceBase
+ : public IAggregateFunctionDataHelper<
+ AggregateFunctionSequenceMatchData<DateValueType,
NativeType, Derived>, Derived> {
+public:
+ AggregateFunctionSequenceBase(const DataTypes& arguments)
+ : IAggregateFunctionDataHelper<
+ AggregateFunctionSequenceMatchData<DateValueType,
NativeType, Derived>,
+ Derived>(arguments, {}) {
+ arg_count = arguments.size();
+ }
+
+ void reset(AggregateDataPtr __restrict place) const override {
this->data(place).reset(); }
+
+ void add(AggregateDataPtr __restrict place, const IColumn** columns, const
size_t row_num,
+ Arena*) const override {
+ std::string pattern =
+ assert_cast<const
ColumnString*>(columns[0])->get_data_at(0).to_string();
+ this->data(place).init(pattern, arg_count);
+
+ const auto& timestamp =
+ static_cast<const
ColumnVector<NativeType>&>(*columns[1]).get_data()[row_num];
+ typename AggregateFunctionSequenceMatchData<DateValueType, NativeType,
Derived>::Events
+ events;
+
+ for (auto i = 2; i < arg_count; i++) {
+ const auto event = assert_cast<const
ColumnUInt8*>(columns[i])->get_data()[row_num];
+ events.set(i - 2, event);
+ }
+
+ this->data(place).add(binary_cast<NativeType,
DateValueType>(timestamp), events);
+ }
+
+ void merge(AggregateDataPtr __restrict place, ConstAggregateDataPtr rhs,
+ Arena*) const override {
+ const std::string pattern = this->data(rhs).get_pattern();
+ size_t arg_count = this->data(rhs).get_arg_count();
+ this->data(place).init(pattern, arg_count);
+ this->data(place).merge(this->data(rhs));
+ }
+
+ void serialize(ConstAggregateDataPtr __restrict place, BufferWritable&
buf) const override {
+ this->data(place).write(buf);
+ }
+
+ void deserialize(AggregateDataPtr __restrict place, BufferReadable& buf,
+ Arena*) const override {
+ this->data(place).read(buf);
+ const std::string pattern = this->data(place).get_pattern();
+ size_t arg_count = this->data(place).get_arg_count();
+ this->data(place).init(pattern, arg_count);
+ }
+
+private:
+ size_t arg_count;
+};
+
+template <typename DateValueType, typename NativeType>
+class AggregateFunctionSequenceMatch final
+ : public AggregateFunctionSequenceBase<
+ DateValueType, NativeType,
+ AggregateFunctionSequenceMatch<DateValueType, NativeType>> {
+public:
+ AggregateFunctionSequenceMatch(const DataTypes& arguments, const String&
pattern_)
+ : AggregateFunctionSequenceBase<
+ DateValueType, NativeType,
+ AggregateFunctionSequenceMatch<DateValueType,
NativeType>>(arguments,
+
pattern_) {}
+
+ using AggregateFunctionSequenceBase<DateValueType, NativeType,
+
AggregateFunctionSequenceMatch<DateValueType, NativeType>>::
+ AggregateFunctionSequenceBase;
+
+ String get_name() const override { return "sequence_match"; }
+
+ DataTypePtr get_return_type() const override { return
std::make_shared<DataTypeUInt8>(); }
+
+ void insert_result_into(ConstAggregateDataPtr __restrict place, IColumn&
to) const override {
+ auto& output = assert_cast<ColumnUInt8&>(to).get_data();
+ if (!this->data(place).conditions_in_pattern.any()) {
+ output.push_back(false);
+ return;
+ }
+
+ if ((this->data(place).conditions_in_pattern &
this->data(place).conditions_met) !=
+ this->data(place).conditions_in_pattern) {
+ output.push_back(false);
+ return;
+ }
+ this->data(const_cast<AggregateDataPtr>(place)).sort();
+
+ const auto& data_ref = this->data(place);
+
+ const auto events_begin = std::begin(data_ref.events_list);
+ const auto events_end = std::end(data_ref.events_list);
+ auto events_it = events_begin;
+
+ bool match = (this->data(place).pattern_has_time
+ ?
(this->data(place).could_match_deterministic_parts(events_begin,
+
events_end) &&
+
this->data(place).backtracking_match(events_it, events_end))
+ : this->data(place).dfa_match(events_it,
events_end));
+ output.push_back(match);
+ }
+};
+
+template <typename DateValueType, typename NativeType>
+class AggregateFunctionSequenceCount final
+ : public AggregateFunctionSequenceBase<
+ DateValueType, NativeType,
+ AggregateFunctionSequenceCount<DateValueType, NativeType>> {
+public:
+ AggregateFunctionSequenceCount(const DataTypes& arguments, const String&
pattern_)
+ : AggregateFunctionSequenceBase<
+ DateValueType, NativeType,
+ AggregateFunctionSequenceCount<DateValueType,
NativeType>>(arguments,
+
pattern_) {}
+
+ using AggregateFunctionSequenceBase<DateValueType, NativeType,
+
AggregateFunctionSequenceCount<DateValueType, NativeType>>::
+ AggregateFunctionSequenceBase;
+
+ String get_name() const override { return "sequence_count"; }
+
+ DataTypePtr get_return_type() const override { return
std::make_shared<DataTypeInt64>(); }
+
+ void insert_result_into(ConstAggregateDataPtr __restrict place, IColumn&
to) const override {
+ auto& output = assert_cast<ColumnInt64&>(to).get_data();
+ if (!this->data(place).conditions_in_pattern.any()) {
+ output.push_back(0);
+ return;
+ }
+
+ if ((this->data(place).conditions_in_pattern &
this->data(place).conditions_met) !=
+ this->data(place).conditions_in_pattern) {
+ output.push_back(0);
+ return;
+ }
+ this->data(const_cast<AggregateDataPtr>(place)).sort();
+ output.push_back(count(place));
+ }
+
+private:
+ UInt64 count(ConstAggregateDataPtr __restrict place) const {
+ const auto& data_ref = this->data(place);
+
+ const auto events_begin = std::begin(data_ref.events_list);
+ const auto events_end = std::end(data_ref.events_list);
+ auto events_it = events_begin;
+
+ size_t count = 0;
+ // check if there is a chance of matching the sequence at least once
+ if (this->data(place).could_match_deterministic_parts(events_begin,
events_end)) {
+ while (events_it != events_end &&
+ this->data(place).backtracking_match(events_it, events_end))
+ ++count;
+ }
+
+ return count;
+ }
+};
+
+} // namespace doris::vectorized
diff --git
a/be/src/vec/aggregate_functions/aggregate_function_simple_factory.cpp
b/be/src/vec/aggregate_functions/aggregate_function_simple_factory.cpp
index 9d4db9182e..e32b38bbba 100644
--- a/be/src/vec/aggregate_functions/aggregate_function_simple_factory.cpp
+++ b/be/src/vec/aggregate_functions/aggregate_function_simple_factory.cpp
@@ -52,6 +52,7 @@ void
register_aggregate_function_retention(AggregateFunctionSimpleFactory& facto
void
register_aggregate_function_percentile_approx(AggregateFunctionSimpleFactory&
factory);
void
register_aggregate_function_orthogonal_bitmap(AggregateFunctionSimpleFactory&
factory);
void register_aggregate_function_collect_list(AggregateFunctionSimpleFactory&
factory);
+void
register_aggregate_function_sequence_match(AggregateFunctionSimpleFactory&
factory);
AggregateFunctionSimpleFactory& AggregateFunctionSimpleFactory::instance() {
static std::once_flag oc;
@@ -78,6 +79,7 @@ AggregateFunctionSimpleFactory&
AggregateFunctionSimpleFactory::instance() {
register_aggregate_function_retention(instance);
register_aggregate_function_orthogonal_bitmap(instance);
register_aggregate_function_collect_list(instance);
+ register_aggregate_function_sequence_match(instance);
// if you only register function with no nullable, and wants to add
nullable automatically, you should place function above this line
register_aggregate_function_combinator_null(instance);
diff --git a/be/src/vec/io/io_helper.h b/be/src/vec/io/io_helper.h
index 928f425e0e..d8b7edfd2e 100644
--- a/be/src/vec/io/io_helper.h
+++ b/be/src/vec/io/io_helper.h
@@ -359,6 +359,24 @@ bool try_read_int_text(T& x, ReadBuffer& buf) {
return read_int_text_impl<T>(x, buf);
}
+template <typename T>
+static inline const char* try_read_first_int_text(T& x, const char* pos, const
char* end) {
+ const int len = end - pos;
+ int i = 0;
+ while (i < len) {
+ if (pos[i] >= '0' && pos[i] <= '9') {
+ i++;
+ } else {
+ break;
+ }
+ }
+ const char* int_end = pos + i;
+ ReadBuffer in((char*)pos, int_end - pos);
+ const size_t count = in.count();
+ try_read_int_text(x, in);
+ return pos + count;
+}
+
template <typename T>
bool try_read_float_text(T& x, ReadBuffer& in) {
return read_float_text_fast_impl<T>(x, in);
diff --git a/be/test/CMakeLists.txt b/be/test/CMakeLists.txt
index 85fbb1851d..2a694cab8d 100644
--- a/be/test/CMakeLists.txt
+++ b/be/test/CMakeLists.txt
@@ -330,6 +330,7 @@ set(VEC_TEST_FILES
vec/aggregate_functions/agg_min_max_test.cpp
vec/aggregate_functions/vec_window_funnel_test.cpp
vec/aggregate_functions/vec_retention_test.cpp
+ vec/aggregate_functions/vec_sequence_match_test.cpp
vec/aggregate_functions/agg_min_max_by_test.cpp
vec/columns/column_decimal_test.cpp
vec/core/block_test.cpp
diff --git a/be/test/vec/aggregate_functions/vec_sequence_match_test.cpp
b/be/test/vec/aggregate_functions/vec_sequence_match_test.cpp
new file mode 100644
index 0000000000..763ecdc08c
--- /dev/null
+++ b/be/test/vec/aggregate_functions/vec_sequence_match_test.cpp
@@ -0,0 +1,424 @@
+// 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 "gtest/gtest.h"
+#include "vec/aggregate_functions/aggregate_function.h"
+#include "vec/aggregate_functions/aggregate_function_simple_factory.h"
+#include "vec/columns/column_vector.h"
+#include "vec/data_types/data_type.h"
+#include "vec/data_types/data_type_date_time.h"
+#include "vec/data_types/data_type_number.h"
+#include "vec/data_types/data_type_string.h"
+
+namespace doris::vectorized {
+
+void
register_aggregate_function_sequence_match(AggregateFunctionSimpleFactory&
factory);
+
+class VSequenceMatchTest : public testing::Test {
+public:
+ AggregateFunctionPtr agg_function_sequence_match;
+ AggregateFunctionPtr agg_function_sequence_count;
+
+ VSequenceMatchTest() {}
+
+ void SetUp() {
+ AggregateFunctionSimpleFactory factory =
AggregateFunctionSimpleFactory::instance();
+ DataTypes data_types = {
+ std::make_shared<DataTypeString>(),
std::make_shared<DataTypeDateTime>(),
+ std::make_shared<DataTypeUInt8>(),
std::make_shared<DataTypeUInt8>(),
+ std::make_shared<DataTypeUInt8>()};
+ Array array;
+ agg_function_sequence_match = factory.get("sequence_match",
data_types, array, false);
+ EXPECT_NE(agg_function_sequence_match, nullptr);
+ agg_function_sequence_count = factory.get("sequence_count",
data_types, array, false);
+ EXPECT_NE(agg_function_sequence_count, nullptr);
+ }
+
+ void TearDown() {}
+};
+
+TEST_F(VSequenceMatchTest, testMatchEmpty) {
+ std::unique_ptr<char[]> memory(new
char[agg_function_sequence_match->size_of_data()]);
+ AggregateDataPtr place = memory.get();
+ agg_function_sequence_match->create(place);
+
+ ColumnString buf;
+ VectorBufferWriter buf_writer(buf);
+ agg_function_sequence_match->serialize(place, buf_writer);
+ buf_writer.commit();
+ VectorBufferReader buf_reader(buf.get_data_at(0));
+ agg_function_sequence_match->deserialize(place, buf_reader, nullptr);
+
+ std::unique_ptr<char[]> memory2(new
char[agg_function_sequence_match->size_of_data()]);
+ AggregateDataPtr place2 = memory2.get();
+ agg_function_sequence_match->create(place2);
+
+ agg_function_sequence_match->merge(place, place2, nullptr);
+ ColumnVector<UInt8> column_result;
+ agg_function_sequence_match->insert_result_into(place, column_result);
+ EXPECT_EQ(column_result.get_data()[0], 0);
+
+ ColumnVector<UInt8> column_result2;
+ agg_function_sequence_match->insert_result_into(place2, column_result2);
+ EXPECT_EQ(column_result2.get_data()[0], 0);
+
+ agg_function_sequence_match->destroy(place);
+ agg_function_sequence_match->destroy(place2);
+}
+
+TEST_F(VSequenceMatchTest, testCountEmpty) {
+ std::unique_ptr<char[]> memory(new
char[agg_function_sequence_count->size_of_data()]);
+ AggregateDataPtr place = memory.get();
+ agg_function_sequence_count->create(place);
+
+ ColumnString buf;
+ VectorBufferWriter buf_writer(buf);
+ agg_function_sequence_count->serialize(place, buf_writer);
+ buf_writer.commit();
+ VectorBufferReader buf_reader(buf.get_data_at(0));
+ agg_function_sequence_count->deserialize(place, buf_reader, nullptr);
+
+ std::unique_ptr<char[]> memory2(new
char[agg_function_sequence_count->size_of_data()]);
+ AggregateDataPtr place2 = memory2.get();
+ agg_function_sequence_count->create(place2);
+
+ agg_function_sequence_count->merge(place, place2, nullptr);
+ ColumnVector<Int64> column_result;
+ agg_function_sequence_count->insert_result_into(place, column_result);
+ EXPECT_EQ(column_result.get_data()[0], 0);
+
+ ColumnVector<Int64> column_result2;
+ agg_function_sequence_count->insert_result_into(place2, column_result2);
+ EXPECT_EQ(column_result2.get_data()[0], 0);
+
+ agg_function_sequence_count->destroy(place);
+ agg_function_sequence_count->destroy(place2);
+}
+
+TEST_F(VSequenceMatchTest, testMatchSerialize) {
+ const int NUM_CONDS = 4;
+ auto column_pattern = ColumnString::create();
+ for (int i = 0; i < NUM_CONDS; i++) {
+ column_pattern->insert("(?1)(?2)");
+ }
+
+ auto column_timestamp = ColumnVector<Int64>::create();
+ for (int i = 0; i < NUM_CONDS; i++) {
+ VecDateTimeValue time_value;
+ time_value.set_time(2022, 11, 2, 0, 0, i);
+ column_timestamp->insert_data((char*)&time_value, 0);
+ }
+
+ auto column_event1 = ColumnVector<UInt8>::create();
+ column_event1->insert(1);
+ column_event1->insert(0);
+ column_event1->insert(0);
+ column_event1->insert(0);
+
+ auto column_event2 = ColumnVector<UInt8>::create();
+ column_event2->insert(0);
+ column_event2->insert(1);
+ column_event2->insert(0);
+ column_event2->insert(0);
+
+ auto column_event3 = ColumnVector<UInt8>::create();
+ column_event3->insert(0);
+ column_event3->insert(0);
+ column_event3->insert(1);
+ column_event3->insert(0);
+
+ std::unique_ptr<char[]> memory(new
char[agg_function_sequence_match->size_of_data()]);
+ AggregateDataPtr place = memory.get();
+ agg_function_sequence_match->create(place);
+ const IColumn* column[5] = {column_pattern.get(), column_timestamp.get(),
column_event1.get(),
+ column_event2.get(), column_event3.get()};
+ for (int i = 0; i < NUM_CONDS; i++) {
+ agg_function_sequence_match->add(place, column, i, nullptr);
+ }
+
+ ColumnString buf;
+ VectorBufferWriter buf_writer(buf);
+ agg_function_sequence_match->serialize(place, buf_writer);
+ buf_writer.commit();
+
+ std::unique_ptr<char[]> memory2(new
char[agg_function_sequence_match->size_of_data()]);
+ AggregateDataPtr place2 = memory2.get();
+ agg_function_sequence_match->create(place2);
+
+ VectorBufferReader buf_reader(buf.get_data_at(0));
+ agg_function_sequence_match->deserialize(place2, buf_reader, nullptr);
+
+ ColumnVector<UInt8> column_result;
+ agg_function_sequence_match->insert_result_into(place, column_result);
+ EXPECT_EQ(column_result.get_data()[0], 1);
+ agg_function_sequence_match->destroy(place);
+
+ ColumnVector<UInt8> column_result2;
+ agg_function_sequence_match->insert_result_into(place2, column_result2);
+ EXPECT_EQ(column_result2.get_data()[0], 1);
+ agg_function_sequence_match->destroy(place2);
+}
+
+TEST_F(VSequenceMatchTest, testCountSerialize) {
+ AggregateFunctionSimpleFactory factory =
AggregateFunctionSimpleFactory::instance();
+ DataTypes data_types = {std::make_shared<DataTypeString>(),
+ std::make_shared<DataTypeDateTime>(),
std::make_shared<DataTypeUInt8>(),
+ std::make_shared<DataTypeUInt8>()};
+ Array array;
+ agg_function_sequence_count = factory.get("sequence_count", data_types,
array, false);
+ EXPECT_NE(agg_function_sequence_count, nullptr);
+
+ const int NUM_CONDS = 4;
+ auto column_pattern = ColumnString::create();
+ for (int i = 0; i < NUM_CONDS; i++) {
+ column_pattern->insert("(?1)(?2)");
+ }
+
+ auto column_timestamp = ColumnVector<Int64>::create();
+ for (int i = 0; i < NUM_CONDS; i++) {
+ VecDateTimeValue time_value;
+ time_value.set_time(2022, 11, 2, 0, 0, i);
+ column_timestamp->insert_data((char*)&time_value, 0);
+ }
+
+ auto column_event1 = ColumnVector<UInt8>::create();
+ column_event1->insert(1);
+ column_event1->insert(0);
+ column_event1->insert(1);
+ column_event1->insert(0);
+
+ auto column_event2 = ColumnVector<UInt8>::create();
+ column_event2->insert(0);
+ column_event2->insert(1);
+ column_event2->insert(0);
+ column_event2->insert(1);
+
+ std::unique_ptr<char[]> memory(new
char[agg_function_sequence_count->size_of_data()]);
+ AggregateDataPtr place = memory.get();
+ agg_function_sequence_count->create(place);
+ const IColumn* column[4] = {column_pattern.get(), column_timestamp.get(),
column_event1.get(),
+ column_event2.get()};
+ for (int i = 0; i < NUM_CONDS; i++) {
+ agg_function_sequence_count->add(place, column, i, nullptr);
+ }
+
+ ColumnString buf;
+ VectorBufferWriter buf_writer(buf);
+ agg_function_sequence_count->serialize(place, buf_writer);
+ buf_writer.commit();
+
+ std::unique_ptr<char[]> memory2(new
char[agg_function_sequence_count->size_of_data()]);
+ AggregateDataPtr place2 = memory2.get();
+ agg_function_sequence_count->create(place2);
+
+ VectorBufferReader buf_reader(buf.get_data_at(0));
+ agg_function_sequence_count->deserialize(place2, buf_reader, nullptr);
+
+ ColumnVector<Int64> column_result;
+ agg_function_sequence_count->insert_result_into(place, column_result);
+ EXPECT_EQ(column_result.get_data()[0], 2);
+ agg_function_sequence_count->destroy(place);
+
+ ColumnVector<Int64> column_result2;
+ agg_function_sequence_count->insert_result_into(place2, column_result2);
+ EXPECT_EQ(column_result2.get_data()[0], 2);
+ agg_function_sequence_count->destroy(place2);
+}
+
+TEST_F(VSequenceMatchTest, testMatchReverseSortedSerializeMerge) {
+ AggregateFunctionSimpleFactory factory =
AggregateFunctionSimpleFactory::instance();
+ DataTypes data_types = {std::make_shared<DataTypeString>(),
+ std::make_shared<DataTypeDateTime>(),
std::make_shared<DataTypeUInt8>(),
+ std::make_shared<DataTypeUInt8>()};
+ Array array;
+ agg_function_sequence_match = factory.get("sequence_match", data_types,
array, false);
+ EXPECT_NE(agg_function_sequence_match, nullptr);
+
+ const int NUM_CONDS = 2;
+ auto column_pattern = ColumnString::create();
+ for (int i = 0; i < NUM_CONDS; i++) {
+ column_pattern->insert("(?1)(?2)");
+ }
+
+ auto column_timestamp = ColumnVector<Int64>::create();
+ for (int i = 0; i < NUM_CONDS; i++) {
+ VecDateTimeValue time_value;
+ time_value.set_time(2022, 11, 2, 0, 0, NUM_CONDS - i);
+ column_timestamp->insert_data((char*)&time_value, 0);
+ }
+
+ auto column_event1 = ColumnVector<UInt8>::create();
+ column_event1->insert(0);
+ column_event1->insert(1);
+
+ auto column_event2 = ColumnVector<UInt8>::create();
+ column_event2->insert(0);
+ column_event2->insert(0);
+
+ std::unique_ptr<char[]> memory(new
char[agg_function_sequence_match->size_of_data()]);
+ AggregateDataPtr place = memory.get();
+ agg_function_sequence_match->create(place);
+ const IColumn* column[4] = {column_pattern.get(), column_timestamp.get(),
column_event1.get(),
+ column_event2.get()};
+ for (int i = 0; i < NUM_CONDS; i++) {
+ agg_function_sequence_match->add(place, column, i, nullptr);
+ }
+
+ ColumnString buf;
+ VectorBufferWriter buf_writer(buf);
+ agg_function_sequence_match->serialize(place, buf_writer);
+ buf_writer.commit();
+ agg_function_sequence_match->destroy(place);
+
+ std::unique_ptr<char[]> memory2(new
char[agg_function_sequence_match->size_of_data()]);
+ AggregateDataPtr place2 = memory2.get();
+ agg_function_sequence_match->create(place2);
+
+ VectorBufferReader buf_reader(buf.get_data_at(0));
+ agg_function_sequence_match->deserialize(place2, buf_reader, nullptr);
+
+ ColumnVector<UInt8> column_result;
+ agg_function_sequence_match->insert_result_into(place2, column_result);
+ EXPECT_EQ(column_result.get_data()[0], 0);
+
+ auto column_timestamp2 = ColumnVector<Int64>::create();
+ for (int i = 0; i < NUM_CONDS; i++) {
+ VecDateTimeValue time_value;
+ time_value.set_time(2022, 11, 2, 0, 1, NUM_CONDS - i);
+ column_timestamp2->insert_data((char*)&time_value, 0);
+ }
+
+ auto column_event3 = ColumnVector<UInt8>::create();
+ column_event3->insert(0);
+ column_event3->insert(0);
+
+ auto column_event4 = ColumnVector<UInt8>::create();
+ column_event4->insert(0);
+ column_event4->insert(1);
+
+ std::unique_ptr<char[]> memory3(new
char[agg_function_sequence_match->size_of_data()]);
+ AggregateDataPtr place3 = memory3.get();
+ agg_function_sequence_match->create(place3);
+ const IColumn* column2[4] = {column_pattern.get(),
column_timestamp2.get(), column_event3.get(),
+ column_event4.get()};
+ for (int i = 0; i < NUM_CONDS; i++) {
+ agg_function_sequence_match->add(place3, column2, i, nullptr);
+ }
+
+ agg_function_sequence_match->merge(place2, place3, nullptr);
+
+ ColumnVector<UInt8> column_result2;
+ agg_function_sequence_match->insert_result_into(place2, column_result2);
+ EXPECT_EQ(column_result2.get_data()[0], 1);
+
+ agg_function_sequence_match->destroy(place2);
+ agg_function_sequence_match->destroy(place3);
+}
+
+TEST_F(VSequenceMatchTest, testCountReverseSortedSerializeMerge) {
+ AggregateFunctionSimpleFactory factory =
AggregateFunctionSimpleFactory::instance();
+ DataTypes data_types = {std::make_shared<DataTypeString>(),
+ std::make_shared<DataTypeDateTime>(),
std::make_shared<DataTypeUInt8>(),
+ std::make_shared<DataTypeUInt8>()};
+ Array array;
+ agg_function_sequence_count = factory.get("sequence_count", data_types,
array, false);
+ EXPECT_NE(agg_function_sequence_count, nullptr);
+
+ const int NUM_CONDS = 2;
+ auto column_pattern = ColumnString::create();
+ for (int i = 0; i < NUM_CONDS; i++) {
+ column_pattern->insert("(?1)(?2)");
+ }
+
+ auto column_timestamp = ColumnVector<Int64>::create();
+ for (int i = 0; i < NUM_CONDS; i++) {
+ VecDateTimeValue time_value;
+ time_value.set_time(2022, 11, 2, 0, 0, NUM_CONDS - i);
+ column_timestamp->insert_data((char*)&time_value, 0);
+ }
+
+ auto column_event1 = ColumnVector<UInt8>::create();
+ column_event1->insert(0);
+ column_event1->insert(1);
+
+ auto column_event2 = ColumnVector<UInt8>::create();
+ column_event2->insert(1);
+ column_event2->insert(0);
+
+ std::unique_ptr<char[]> memory(new
char[agg_function_sequence_count->size_of_data()]);
+ AggregateDataPtr place = memory.get();
+ agg_function_sequence_count->create(place);
+ const IColumn* column[4] = {column_pattern.get(), column_timestamp.get(),
column_event1.get(),
+ column_event2.get()};
+ for (int i = 0; i < NUM_CONDS; i++) {
+ agg_function_sequence_count->add(place, column, i, nullptr);
+ }
+
+ ColumnString buf;
+ VectorBufferWriter buf_writer(buf);
+ agg_function_sequence_count->serialize(place, buf_writer);
+ buf_writer.commit();
+ agg_function_sequence_count->destroy(place);
+
+ std::unique_ptr<char[]> memory2(new
char[agg_function_sequence_count->size_of_data()]);
+ AggregateDataPtr place2 = memory2.get();
+ agg_function_sequence_count->create(place2);
+
+ VectorBufferReader buf_reader(buf.get_data_at(0));
+ agg_function_sequence_count->deserialize(place2, buf_reader, nullptr);
+
+ ColumnVector<Int64> column_result;
+ agg_function_sequence_count->insert_result_into(place2, column_result);
+ EXPECT_EQ(column_result.get_data()[0], 1);
+
+ auto column_timestamp2 = ColumnVector<Int64>::create();
+ for (int i = 0; i < NUM_CONDS; i++) {
+ VecDateTimeValue time_value;
+ time_value.set_time(2022, 11, 2, 0, 1, NUM_CONDS - i);
+ column_timestamp2->insert_data((char*)&time_value, 0);
+ }
+
+ auto column_event3 = ColumnVector<UInt8>::create();
+ column_event3->insert(0);
+ column_event3->insert(1);
+
+ auto column_event4 = ColumnVector<UInt8>::create();
+ column_event4->insert(1);
+ column_event4->insert(0);
+
+ std::unique_ptr<char[]> memory3(new
char[agg_function_sequence_count->size_of_data()]);
+ AggregateDataPtr place3 = memory3.get();
+ agg_function_sequence_count->create(place3);
+ const IColumn* column2[4] = {column_pattern.get(),
column_timestamp2.get(), column_event3.get(),
+ column_event4.get()};
+ for (int i = 0; i < NUM_CONDS; i++) {
+ agg_function_sequence_count->add(place3, column2, i, nullptr);
+ }
+
+ agg_function_sequence_count->merge(place2, place3, nullptr);
+
+ ColumnVector<Int64> column_result2;
+ agg_function_sequence_count->insert_result_into(place2, column_result2);
+ EXPECT_EQ(column_result2.get_data()[0], 2);
+
+ agg_function_sequence_count->destroy(place2);
+ agg_function_sequence_count->destroy(place3);
+}
+
+} // namespace doris::vectorized
\ No newline at end of file
diff --git
a/docs/en/docs/sql-manual/sql-functions/aggregate-functions/sequence_count.md
b/docs/en/docs/sql-manual/sql-functions/aggregate-functions/sequence_count.md
new file mode 100644
index 0000000000..68abb404dc
--- /dev/null
+++
b/docs/en/docs/sql-manual/sql-functions/aggregate-functions/sequence_count.md
@@ -0,0 +1,254 @@
+---
+{
+ "title": "SEQUENCE-COUNT",
+ "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.
+-->
+
+## SEQUENCE-COUNT
+### Description
+#### Syntax
+
+`sequence_count(pattern, timestamp, cond1, cond2, ...);`
+
+Counts the number of event chains that matched the pattern. The function
searches event chains that do not overlap. It starts to search for the next
chain after the current chain is matched.
+
+**WARNING!**
+
+Events that occur at the same second may lay in the sequence in an undefined
order affecting the result.
+
+#### Arguments
+
+`pattern` — Pattern string.
+
+**Pattern syntax**
+
+`(?N)` — Matches the condition argument at position N. Conditions are numbered
in the `[1, 32]` range. For example, `(?1)` matches the argument passed to the
`cond1` parameter.
+
+`.*` — Matches any number of events. You do not need conditional arguments to
count this element of the pattern.
+
+`(?t operator value)` — Sets the time in seconds that should separate two
events.
+
+We define `t` as the difference in seconds between two times, For example,
pattern `(?1)(?t>1800)(?2)` matches events that occur more than 1800 seconds
from each other. pattern `(?1)(?t>10000)(?2)` matches events that occur more
than 10000 seconds from each other. An arbitrary number of any events can lay
between these events. You can use the `>=`, `>`, `<`, `<=`, `==` operators.
+
+`timestamp` — Column considered to contain time data. Typical data types are
`Date` and `DateTime`. You can also use any of the supported UInt data types.
+
+`cond1`, `cond2` — Conditions that describe the chain of events. Data type:
`UInt8`. You can pass up to 32 condition arguments. The function takes only the
events described in these conditions into account. If the sequence contains
data that isn’t described in a condition, the function skips them.
+
+#### Returned value
+
+Number of non-overlapping event chains that are matched.
+
+### example
+
+**count examples**
+
+```sql
+DROP TABLE IF EXISTS sequence_count_test2;
+
+CREATE TABLE sequence_count_test2(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_count_test2(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 13:28:02',
2),
+ (3, '2022-11-02 16:15:01',
1),
+ (4, '2022-11-02 19:05:04',
2),
+ (5, '2022-11-02 20:08:44',
3);
+
+SELECT * FROM sequence_count_test2 ORDER BY date;
+
++------+---------------------+--------+
+| uid | date | number |
++------+---------------------+--------+
+| 1 | 2022-11-02 10:41:00 | 1 |
+| 2 | 2022-11-02 13:28:02 | 2 |
+| 3 | 2022-11-02 16:15:01 | 1 |
+| 4 | 2022-11-02 19:05:04 | 2 |
+| 5 | 2022-11-02 20:08:44 | 3 |
++------+---------------------+--------+
+
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 3) FROM
sequence_count_test2;
+
++----------------------------------------------------------------+
+| sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 3) |
++----------------------------------------------------------------+
+| 1 |
++----------------------------------------------------------------+
+
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_count_test2;
+
++----------------------------------------------------------------+
+| sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 2) |
++----------------------------------------------------------------+
+| 2 |
++----------------------------------------------------------------+
+
+SELECT sequence_count('(?1)(?t>=10000)(?2)', date, number = 1, number = 2)
FROM sequence_count_test1;
+
++---------------------------------------------------------------------------+
+| sequence_count('(?1)(?t>=3600)(?2)', `date`, `number` = 1, `number` = 2) |
++---------------------------------------------------------------------------+
+| 2 |
++---------------------------------------------------------------------------+
+```
+
+**not count examples**
+
+```sql
+DROP TABLE IF EXISTS sequence_count_test1;
+
+CREATE TABLE sequence_count_test1(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_count_test1(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 11:41:00',
7),
+ (3, '2022-11-02 16:15:01',
3),
+ (4, '2022-11-02 19:05:04',
4),
+ (5, '2022-11-02 21:24:12',
5);
+
+SELECT * FROM sequence_count_test1 ORDER BY date;
+
++------+---------------------+--------+
+| uid | date | number |
++------+---------------------+--------+
+| 1 | 2022-11-02 10:41:00 | 1 |
+| 2 | 2022-11-02 11:41:00 | 7 |
+| 3 | 2022-11-02 16:15:01 | 3 |
+| 4 | 2022-11-02 19:05:04 | 4 |
+| 5 | 2022-11-02 21:24:12 | 5 |
++------+---------------------+--------+
+
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_count_test1;
+
++----------------------------------------------------------------+
+| sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 2) |
++----------------------------------------------------------------+
+| 0 |
++----------------------------------------------------------------+
+
+SELECT sequence_count('(?1)(?2).*', date, number = 1, number = 2) FROM
sequence_count_test1;
+
++------------------------------------------------------------------+
+| sequence_count('(?1)(?2).*', `date`, `number` = 1, `number` = 2) |
++------------------------------------------------------------------+
+| 0 |
++------------------------------------------------------------------+
+
+SELECT sequence_count('(?1)(?t>3600)(?2)', date, number = 1, number = 7) FROM
sequence_count_test1;
+
++--------------------------------------------------------------------------+
+| sequence_count('(?1)(?t>3600)(?2)', `date`, `number` = 1, `number` = 7) |
++--------------------------------------------------------------------------+
+| 0 |
++--------------------------------------------------------------------------+
+```
+
+**special examples**
+
+```sql
+DROP TABLE IF EXISTS sequence_count_test3;
+
+CREATE TABLE sequence_count_test3(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_count_test3(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 11:41:00',
7),
+ (3, '2022-11-02 16:15:01',
3),
+ (4, '2022-11-02 19:05:04',
4),
+ (5, '2022-11-02 21:24:12',
5);
+
+SELECT * FROM sequence_count_test3 ORDER BY date;
+
++------+---------------------+--------+
+| uid | date | number |
++------+---------------------+--------+
+| 1 | 2022-11-02 10:41:00 | 1 |
+| 2 | 2022-11-02 11:41:00 | 7 |
+| 3 | 2022-11-02 16:15:01 | 3 |
+| 4 | 2022-11-02 19:05:04 | 4 |
+| 5 | 2022-11-02 21:24:12 | 5 |
++------+---------------------+--------+
+```
+
+Perform the query:
+
+```sql
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 5) FROM
sequence_count_test3;
+
++----------------------------------------------------------------+
+| sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 5) |
++----------------------------------------------------------------+
+| 1 |
++----------------------------------------------------------------+
+```
+
+This is a very simple example. The function found the event chain where number
5 follows number 1. It skipped number 7,3,4 between them, because the number is
not described as an event. If we want to take this number into account when
searching for the event chain given in the example, we should make a condition
for it.
+
+Now, perform this query:
+
+```sql
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 5, number = 4)
FROM sequence_count_test3;
+
++------------------------------------------------------------------------------+
+| sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 5, `number` = 4)
|
++------------------------------------------------------------------------------+
+| 0
|
++------------------------------------------------------------------------------+
+```
+
+The result is kind of confusing. In this case, the function couldn’t find the
event chain matching the pattern, because the event for number 4 occurred
between 1 and 5. If in the same case we checked the condition for number 6, the
sequence would count the pattern.
+
+```sql
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 5, number = 6)
FROM sequence_count_test3;
+
++------------------------------------------------------------------------------+
+| sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 5, `number` = 6)
|
++------------------------------------------------------------------------------+
+| 1
|
++------------------------------------------------------------------------------+
+```
+
+### keywords
+
+SEQUENCE_COUNT
\ No newline at end of file
diff --git
a/docs/en/docs/sql-manual/sql-functions/aggregate-functions/sequence_match.md
b/docs/en/docs/sql-manual/sql-functions/aggregate-functions/sequence_match.md
new file mode 100644
index 0000000000..27143fcdcb
--- /dev/null
+++
b/docs/en/docs/sql-manual/sql-functions/aggregate-functions/sequence_match.md
@@ -0,0 +1,256 @@
+---
+{
+ "title": "SEQUENCE-MATCH",
+ "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.
+-->
+
+## SEQUENCE-MATCH
+### Description
+#### Syntax
+
+`sequence_match(pattern, timestamp, cond1, cond2, ...);`
+
+Checks whether the sequence contains an event chain that matches the pattern.
+
+**WARNING!**
+
+Events that occur at the same second may lay in the sequence in an undefined
order affecting the result.
+
+#### Arguments
+
+`pattern` — Pattern string.
+
+**Pattern syntax**
+
+`(?N)` — Matches the condition argument at position N. Conditions are numbered
in the `[1, 32]` range. For example, `(?1)` matches the argument passed to the
`cond1` parameter.
+
+`.*` — Matches any number of events. You do not need conditional arguments to
match this element of the pattern.
+
+`(?t operator value)` — Sets the time in seconds that should separate two
events.
+
+We define `t` as the difference in seconds between two times, For example,
pattern `(?1)(?t>1800)(?2)` matches events that occur more than 1800 seconds
from each other. pattern `(?1)(?t>10000)(?2)` matches events that occur more
than 10000 seconds from each other. An arbitrary number of any events can lay
between these events. You can use the `>=`, `>`, `<`, `<=`, `==` operators.
+
+`timestamp` — Column considered to contain time data. Typical data types are
`Date` and `DateTime`. You can also use any of the supported UInt data types.
+
+`cond1`, `cond2` — Conditions that describe the chain of events. Data type:
`UInt8`. You can pass up to 32 condition arguments. The function takes only the
events described in these conditions into account. If the sequence contains
data that isn’t described in a condition, the function skips them.
+
+#### Returned value
+
+1, if the pattern is matched.
+
+0, if the pattern isn’t matched.
+
+### example
+
+**match examples**
+
+```sql
+DROP TABLE IF EXISTS sequence_match_test1;
+
+CREATE TABLE sequence_match_test1(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_match_test1(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 13:28:02',
2),
+ (3, '2022-11-02 16:15:01',
1),
+ (4, '2022-11-02 19:05:04',
2),
+ (5, '2022-11-02 20:08:44',
3);
+
+SELECT * FROM sequence_match_test1 ORDER BY date;
+
++------+---------------------+--------+
+| uid | date | number |
++------+---------------------+--------+
+| 1 | 2022-11-02 10:41:00 | 1 |
+| 2 | 2022-11-02 13:28:02 | 2 |
+| 3 | 2022-11-02 16:15:01 | 1 |
+| 4 | 2022-11-02 19:05:04 | 2 |
+| 5 | 2022-11-02 20:08:44 | 3 |
++------+---------------------+--------+
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 3) FROM
sequence_match_test1;
+
++----------------------------------------------------------------+
+| sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 3) |
++----------------------------------------------------------------+
+| 1 |
++----------------------------------------------------------------+
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_match_test1;
+
++----------------------------------------------------------------+
+| sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 2) |
++----------------------------------------------------------------+
+| 1 |
++----------------------------------------------------------------+
+
+SELECT sequence_match('(?1)(?t>=3600)(?2)', date, number = 1, number = 2) FROM
sequence_match_test1;
+
++---------------------------------------------------------------------------+
+| sequence_match('(?1)(?t>=3600)(?2)', `date`, `number` = 1, `number` = 2) |
++---------------------------------------------------------------------------+
+| 1 |
++---------------------------------------------------------------------------+
+```
+
+**not match examples**
+
+```sql
+DROP TABLE IF EXISTS sequence_match_test2;
+
+CREATE TABLE sequence_match_test2(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_match_test2(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 11:41:00',
7),
+ (3, '2022-11-02 16:15:01',
3),
+ (4, '2022-11-02 19:05:04',
4),
+ (5, '2022-11-02 21:24:12',
5);
+
+SELECT * FROM sequence_match_test2 ORDER BY date;
+
++------+---------------------+--------+
+| uid | date | number |
++------+---------------------+--------+
+| 1 | 2022-11-02 10:41:00 | 1 |
+| 2 | 2022-11-02 11:41:00 | 7 |
+| 3 | 2022-11-02 16:15:01 | 3 |
+| 4 | 2022-11-02 19:05:04 | 4 |
+| 5 | 2022-11-02 21:24:12 | 5 |
++------+---------------------+--------+
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_match_test2;
+
++----------------------------------------------------------------+
+| sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 2) |
++----------------------------------------------------------------+
+| 0 |
++----------------------------------------------------------------+
+
+SELECT sequence_match('(?1)(?2).*', date, number = 1, number = 2) FROM
sequence_match_test2;
+
++------------------------------------------------------------------+
+| sequence_match('(?1)(?2).*', `date`, `number` = 1, `number` = 2) |
++------------------------------------------------------------------+
+| 0 |
++------------------------------------------------------------------+
+
+SELECT sequence_match('(?1)(?t>3600)(?2)', date, number = 1, number = 7) FROM
sequence_match_test2;
+
++--------------------------------------------------------------------------+
+| sequence_match('(?1)(?t>3600)(?2)', `date`, `number` = 1, `number` = 7) |
++--------------------------------------------------------------------------+
+| 0 |
++--------------------------------------------------------------------------+
+```
+
+**special examples**
+
+```sql
+DROP TABLE IF EXISTS sequence_match_test3;
+
+CREATE TABLE sequence_match_test3(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_match_test3(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 11:41:00',
7),
+ (3, '2022-11-02 16:15:01',
3),
+ (4, '2022-11-02 19:05:04',
4),
+ (5, '2022-11-02 21:24:12',
5);
+
+SELECT * FROM sequence_match_test3 ORDER BY date;
+
++------+---------------------+--------+
+| uid | date | number |
++------+---------------------+--------+
+| 1 | 2022-11-02 10:41:00 | 1 |
+| 2 | 2022-11-02 11:41:00 | 7 |
+| 3 | 2022-11-02 16:15:01 | 3 |
+| 4 | 2022-11-02 19:05:04 | 4 |
+| 5 | 2022-11-02 21:24:12 | 5 |
++------+---------------------+--------+
+```
+
+Perform the query:
+
+```sql
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 5) FROM
sequence_match_test3;
+
++----------------------------------------------------------------+
+| sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 5) |
++----------------------------------------------------------------+
+| 1 |
++----------------------------------------------------------------+
+```
+
+This is a very simple example. The function found the event chain where number
5 follows number 1. It skipped number 7,3,4 between them, because the number is
not described as an event. If we want to take this number into account when
searching for the event chain given in the example, we should make a condition
for it.
+
+Now, perform this query:
+
+```sql
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 5, number = 4)
FROM sequence_match_test3;
+
++------------------------------------------------------------------------------+
+| sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 5, `number` = 4)
|
++------------------------------------------------------------------------------+
+| 0
|
++------------------------------------------------------------------------------+
+```
+
+The result is kind of confusing. In this case, the function couldn’t find the
event chain matching the pattern, because the event for number 4 occurred
between 1 and 5. If in the same case we checked the condition for number 6, the
sequence would match the pattern.
+
+```sql
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 5, number = 6)
FROM sequence_match_test3;
+
++------------------------------------------------------------------------------+
+| sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 5, `number` = 6)
|
++------------------------------------------------------------------------------+
+| 1
|
++------------------------------------------------------------------------------+
+```
+
+### keywords
+
+SEQUENCE_MATCH
\ No newline at end of file
diff --git a/docs/sidebars.json b/docs/sidebars.json
index 9284e2fed5..6a1dac72e8 100644
--- a/docs/sidebars.json
+++ b/docs/sidebars.json
@@ -448,7 +448,9 @@
"sql-manual/sql-functions/aggregate-functions/var_samp",
"sql-manual/sql-functions/aggregate-functions/approx_count_distinct",
"sql-manual/sql-functions/aggregate-functions/variance",
-
"sql-manual/sql-functions/aggregate-functions/retention"
+
"sql-manual/sql-functions/aggregate-functions/retention",
+
"sql-manual/sql-functions/aggregate-functions/sequence_match",
+
"sql-manual/sql-functions/aggregate-functions/sequence_count"
]
},
{
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/aggregate-functions/sequence_count.md
b/docs/zh-CN/docs/sql-manual/sql-functions/aggregate-functions/sequence_count.md
new file mode 100644
index 0000000000..ff3d57a6fe
--- /dev/null
+++
b/docs/zh-CN/docs/sql-manual/sql-functions/aggregate-functions/sequence_count.md
@@ -0,0 +1,254 @@
+---
+{
+ "title": "SEQUENCE-COUNT",
+ "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.
+-->
+
+## SEQUENCE-COUNT
+### Description
+#### Syntax
+
+`sequence_count(pattern, timestamp, cond1, cond2, ...);`
+
+计算与模式匹配的事件链的数量。该函数搜索不重叠的事件链。当前链匹配后，它开始搜索下一个链。
+
+**警告!**
+
+在同一秒钟发生的事件可能以未定义的顺序排列在序列中，会影响最终结果。
+
+#### Arguments
+
+`pattern` — 模式字符串.
+
+**模式语法**
+
+`(?N)` — 在位置N匹配条件参数。 条件在编号 `[1, 32]` 范围。 例如, `(?1)` 匹配传递给 `cond1` 参数。
+
+`.*` — 匹配任何事件的数字。 不需要条件参数来匹配这个模式。
+
+`(?t operator value)` — 分开两个事件的时间。 单位为秒。
+
+`t`表示为两个时间的差值，单位为秒。 例如： `(?1)(?t>1800)(?2)` 匹配彼此发生超过1800秒的事件，
`(?1)(?t>10000)(?2)`匹配彼此发生超过10000秒的事件。 这些事件之间可以存在任意数量的任何事件。 您可以使用 `>=`, `>`,
`<`, `<=`, `==` 运算符。
+
+`timestamp` — 包含时间的列。典型的时间类型是： `Date` 和 `DateTime`。也可以使用任何支持的 `UInt` 数据类型。
+
+`cond1`, `cond2` — 事件链的约束条件。 数据类型是： `UInt8`。 最多可以传递32个条件参数。 该函数只考虑这些条件中描述的事件。
如果序列包含未在条件中描述的数据，则函数将跳过这些数据。
+
+#### Returned value
+
+匹配的非重叠事件链数。
+
+### example
+
+**匹配例子**
+
+```sql
+DROP TABLE IF EXISTS sequence_count_test1;
+
+CREATE TABLE sequence_count_test1(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_count_test1(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 13:28:02',
2),
+ (3, '2022-11-02 16:15:01',
1),
+ (4, '2022-11-02 19:05:04',
2),
+ (5, '2022-11-02 20:08:44',
3);
+
+SELECT * FROM sequence_count_test1 ORDER BY date;
+
++------+---------------------+--------+
+| uid | date | number |
++------+---------------------+--------+
+| 1 | 2022-11-02 10:41:00 | 1 |
+| 2 | 2022-11-02 13:28:02 | 2 |
+| 3 | 2022-11-02 16:15:01 | 1 |
+| 4 | 2022-11-02 19:05:04 | 2 |
+| 5 | 2022-11-02 20:08:44 | 3 |
++------+---------------------+--------+
+
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 3) FROM
sequence_count_test1;
+
++----------------------------------------------------------------+
+| sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 3) |
++----------------------------------------------------------------+
+| 1 |
++----------------------------------------------------------------+
+
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_count_test1;
+
++----------------------------------------------------------------+
+| sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 2) |
++----------------------------------------------------------------+
+| 2 |
++----------------------------------------------------------------+
+
+SELECT sequence_count('(?1)(?t>=3600)(?2)', date, number = 1, number = 2) FROM
sequence_count_test1;
+
++---------------------------------------------------------------------------+
+| sequence_count('(?1)(?t>=3600)(?2)', `date`, `number` = 1, `number` = 2) |
++---------------------------------------------------------------------------+
+| 2 |
++---------------------------------------------------------------------------+
+```
+
+**不匹配例子**
+
+```sql
+DROP TABLE IF EXISTS sequence_count_test2;
+
+CREATE TABLE sequence_count_test2(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_count_test2(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 11:41:00',
7),
+ (3, '2022-11-02 16:15:01',
3),
+ (4, '2022-11-02 19:05:04',
4),
+ (5, '2022-11-02 21:24:12',
5);
+
+SELECT * FROM sequence_count_test2 ORDER BY date;
+
++------+---------------------+--------+
+| uid | date | number |
++------+---------------------+--------+
+| 1 | 2022-11-02 10:41:00 | 1 |
+| 2 | 2022-11-02 11:41:00 | 7 |
+| 3 | 2022-11-02 16:15:01 | 3 |
+| 4 | 2022-11-02 19:05:04 | 4 |
+| 5 | 2022-11-02 21:24:12 | 5 |
++------+---------------------+--------+
+
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_count_test2;
+
++----------------------------------------------------------------+
+| sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 2) |
++----------------------------------------------------------------+
+| 0 |
++----------------------------------------------------------------+
+
+SELECT sequence_count('(?1)(?2).*', date, number = 1, number = 2) FROM
sequence_count_test2;
+
++------------------------------------------------------------------+
+| sequence_count('(?1)(?2).*', `date`, `number` = 1, `number` = 2) |
++------------------------------------------------------------------+
+| 0 |
++------------------------------------------------------------------+
+
+SELECT sequence_count('(?1)(?t>3600)(?2)', date, number = 1, number = 7) FROM
sequence_count_test2;
+
++--------------------------------------------------------------------------+
+| sequence_count('(?1)(?t>3600)(?2)', `date`, `number` = 1, `number` = 7) |
++--------------------------------------------------------------------------+
+| 0 |
++--------------------------------------------------------------------------+
+```
+
+**特殊例子**
+
+```sql
+DROP TABLE IF EXISTS sequence_count_test3;
+
+CREATE TABLE sequence_count_test3(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_count_test3(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 11:41:00',
7),
+ (3, '2022-11-02 16:15:01',
3),
+ (4, '2022-11-02 19:05:04',
4),
+ (5, '2022-11-02 21:24:12',
5);
+
+SELECT * FROM sequence_count_test3 ORDER BY date;
+
++------+---------------------+--------+
+| uid | date | number |
++------+---------------------+--------+
+| 1 | 2022-11-02 10:41:00 | 1 |
+| 2 | 2022-11-02 11:41:00 | 7 |
+| 3 | 2022-11-02 16:15:01 | 3 |
+| 4 | 2022-11-02 19:05:04 | 4 |
+| 5 | 2022-11-02 21:24:12 | 5 |
++------+---------------------+--------+
+```
+
+Perform the query:
+
+```sql
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 5) FROM
sequence_count_test3;
+
++----------------------------------------------------------------+
+| sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 5) |
++----------------------------------------------------------------+
+| 1 |
++----------------------------------------------------------------+
+```
+
+上面为一个非常简单的匹配例子， 该函数找到了数字5跟随数字1的事件链。 它跳过了它们之间的数字7，3，4，因为该数字没有被描述为事件。
如果我们想在搜索示例中给出的事件链时考虑这个数字，我们应该为它创建一个条件。
+
+现在，考虑如下执行语句：
+
+```sql
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 5, number = 4)
FROM sequence_count_test3;
+
++------------------------------------------------------------------------------+
+| sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 5, `number` = 4)
|
++------------------------------------------------------------------------------+
+| 0
|
++------------------------------------------------------------------------------+
+```
+
+您可能对这个结果有些许疑惑，在这种情况下，函数找不到与模式匹配的事件链，因为数字4的事件发生在1和5之间。
如果在相同的情况下，我们检查了数字6的条件，则序列将与模式匹配。
+
+```sql
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 5, number = 6)
FROM sequence_count_test3;
+
++------------------------------------------------------------------------------+
+| sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 5, `number` = 6)
|
++------------------------------------------------------------------------------+
+| 1
|
++------------------------------------------------------------------------------+
+```
+
+### keywords
+
+SEQUENCE_COUNT
\ No newline at end of file
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/aggregate-functions/sequence_match.md
b/docs/zh-CN/docs/sql-manual/sql-functions/aggregate-functions/sequence_match.md
new file mode 100644
index 0000000000..c900f1c76d
--- /dev/null
+++
b/docs/zh-CN/docs/sql-manual/sql-functions/aggregate-functions/sequence_match.md
@@ -0,0 +1,256 @@
+---
+{
+ "title": "SEQUENCE-MATCH",
+ "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.
+-->
+
+## SEQUENCE-MATCH
+### Description
+#### Syntax
+
+`sequence_match(pattern, timestamp, cond1, cond2, ...);`
+
+检查序列是否包含与模式匹配的事件链。
+
+**警告!**
+
+在同一秒钟发生的事件可能以未定义的顺序排列在序列中，会影响最终结果。
+
+#### Arguments
+
+`pattern` — 模式字符串.
+
+**模式语法**
+
+`(?N)` — 在位置N匹配条件参数。 条件在编号 `[1, 32]` 范围。 例如, `(?1)` 匹配传递给 `cond1` 参数。
+
+`.*` — 匹配任何事件的数字。 不需要条件参数来匹配这个模式。
+
+`(?t operator value)` — 分开两个事件的时间。 单位为秒。
+
+`t`表示为两个时间的差值，单位为秒。 例如： `(?1)(?t>1800)(?2)` 匹配彼此发生超过1800秒的事件，
`(?1)(?t>10000)(?2)`匹配彼此发生超过10000秒的事件。 这些事件之间可以存在任意数量的任何事件。 您可以使用 `>=`, `>`,
`<`, `<=`, `==` 运算符。
+
+`timestamp` — 包含时间的列。典型的时间类型是： `Date` 和 `DateTime`。也可以使用任何支持的 `UInt` 数据类型。
+
+`cond1`, `cond2` — 事件链的约束条件。 数据类型是： `UInt8`。 最多可以传递32个条件参数。 该函数只考虑这些条件中描述的事件。
如果序列包含未在条件中描述的数据，则函数将跳过这些数据。
+
+#### Returned value
+
+1，如果模式匹配。
+
+0，如果模式不匹配。
+
+### example
+
+**匹配例子**
+
+```sql
+DROP TABLE IF EXISTS sequence_match_test1;
+
+CREATE TABLE sequence_match_test1(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_match_test1(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 13:28:02',
2),
+ (3, '2022-11-02 16:15:01',
1),
+ (4, '2022-11-02 19:05:04',
2),
+ (5, '2022-11-02 20:08:44',
3);
+
+SELECT * FROM sequence_match_test1 ORDER BY date;
+
++------+---------------------+--------+
+| uid | date | number |
++------+---------------------+--------+
+| 1 | 2022-11-02 10:41:00 | 1 |
+| 2 | 2022-11-02 13:28:02 | 2 |
+| 3 | 2022-11-02 16:15:01 | 1 |
+| 4 | 2022-11-02 19:05:04 | 2 |
+| 5 | 2022-11-02 20:08:44 | 3 |
++------+---------------------+--------+
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 3) FROM
sequence_match_test1;
+
++----------------------------------------------------------------+
+| sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 3) |
++----------------------------------------------------------------+
+| 1 |
++----------------------------------------------------------------+
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_match_test1;
+
++----------------------------------------------------------------+
+| sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 2) |
++----------------------------------------------------------------+
+| 1 |
++----------------------------------------------------------------+
+
+SELECT sequence_match('(?1)(?t>=3600)(?2)', date, number = 1, number = 2) FROM
sequence_match_test1;
+
++---------------------------------------------------------------------------+
+| sequence_match('(?1)(?t>=3600)(?2)', `date`, `number` = 1, `number` = 2) |
++---------------------------------------------------------------------------+
+| 1 |
++---------------------------------------------------------------------------+
+```
+
+**不匹配例子**
+
+```sql
+DROP TABLE IF EXISTS sequence_match_test2;
+
+CREATE TABLE sequence_match_test2(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_match_test2(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 11:41:00',
7),
+ (3, '2022-11-02 16:15:01',
3),
+ (4, '2022-11-02 19:05:04',
4),
+ (5, '2022-11-02 21:24:12',
5);
+
+SELECT * FROM sequence_match_test2 ORDER BY date;
+
++------+---------------------+--------+
+| uid | date | number |
++------+---------------------+--------+
+| 1 | 2022-11-02 10:41:00 | 1 |
+| 2 | 2022-11-02 11:41:00 | 7 |
+| 3 | 2022-11-02 16:15:01 | 3 |
+| 4 | 2022-11-02 19:05:04 | 4 |
+| 5 | 2022-11-02 21:24:12 | 5 |
++------+---------------------+--------+
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_match_test2;
+
++----------------------------------------------------------------+
+| sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 2) |
++----------------------------------------------------------------+
+| 0 |
++----------------------------------------------------------------+
+
+SELECT sequence_match('(?1)(?2).*', date, number = 1, number = 2) FROM
sequence_match_test2;
+
++------------------------------------------------------------------+
+| sequence_match('(?1)(?2).*', `date`, `number` = 1, `number` = 2) |
++------------------------------------------------------------------+
+| 0 |
++------------------------------------------------------------------+
+
+SELECT sequence_match('(?1)(?t>3600)(?2)', date, number = 1, number = 7) FROM
sequence_match_test2;
+
++--------------------------------------------------------------------------+
+| sequence_match('(?1)(?t>3600)(?2)', `date`, `number` = 1, `number` = 7) |
++--------------------------------------------------------------------------+
+| 0 |
++--------------------------------------------------------------------------+
+```
+
+**特殊例子**
+
+```sql
+DROP TABLE IF EXISTS sequence_match_test3;
+
+CREATE TABLE sequence_match_test3(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_match_test3(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 11:41:00',
7),
+ (3, '2022-11-02 16:15:01',
3),
+ (4, '2022-11-02 19:05:04',
4),
+ (5, '2022-11-02 21:24:12',
5);
+
+SELECT * FROM sequence_match_test3 ORDER BY date;
+
++------+---------------------+--------+
+| uid | date | number |
++------+---------------------+--------+
+| 1 | 2022-11-02 10:41:00 | 1 |
+| 2 | 2022-11-02 11:41:00 | 7 |
+| 3 | 2022-11-02 16:15:01 | 3 |
+| 4 | 2022-11-02 19:05:04 | 4 |
+| 5 | 2022-11-02 21:24:12 | 5 |
++------+---------------------+--------+
+```
+
+Perform the query:
+
+```sql
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 5) FROM
sequence_match_test3;
+
++----------------------------------------------------------------+
+| sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 5) |
++----------------------------------------------------------------+
+| 1 |
++----------------------------------------------------------------+
+```
+
+上面为一个非常简单的匹配例子， 该函数找到了数字5跟随数字1的事件链。 它跳过了它们之间的数字7，3，4，因为该数字没有被描述为事件。
如果我们想在搜索示例中给出的事件链时考虑这个数字，我们应该为它创建一个条件。
+
+现在，考虑如下执行语句：
+
+```sql
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 5, number = 4)
FROM sequence_match_test3;
+
++------------------------------------------------------------------------------+
+| sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 5, `number` = 4)
|
++------------------------------------------------------------------------------+
+| 0
|
++------------------------------------------------------------------------------+
+```
+
+您可能对这个结果有些许疑惑，在这种情况下，函数找不到与模式匹配的事件链，因为数字4的事件发生在1和5之间。
如果在相同的情况下，我们检查了数字6的条件，则序列将与模式匹配。
+
+```sql
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 5, number = 6)
FROM sequence_match_test3;
+
++------------------------------------------------------------------------------+
+| sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 5, `number` = 6)
|
++------------------------------------------------------------------------------+
+| 1
|
++------------------------------------------------------------------------------+
+```
+
+### keywords
+
+SEQUENCE_MATCH
\ No newline at end of file
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-WINDOW-FUNNEL.md
b/docs/zh-CN/docs/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-WINDOW-FUNNEL.md
index a27ef486f9..93567de318 100644
---
a/docs/zh-CN/docs/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-WINDOW-FUNNEL.md
+++
b/docs/zh-CN/docs/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-WINDOW-FUNNEL.md
@@ -1,7 +1,7 @@
---
{
"title": "WINDOW-FUNCTION-WINDOW-FUNNEL",
- "language": "en"
+ "language": "zh-CN"
}
---
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
index 8a86a2c736..c1307b3f24 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
@@ -999,7 +999,7 @@ public class FunctionCallExpr extends Expr {
throw new AnalysisException("The window params of " + fnName +
" function must be integer");
}
if (!children.get(1).type.isStringType()) {
- throw new AnalysisException("The mode params of " + fnName + "
function must be integer");
+ throw new AnalysisException("The mode params of " + fnName + "
function must be string");
}
if (!children.get(2).type.isDateType()) {
throw new AnalysisException("The 3rd param of " + fnName + "
function must be DATE or DATETIME");
@@ -1039,12 +1039,55 @@ public class FunctionCallExpr extends Expr {
for (int i = 0; i < children.size(); i++) {
if (children.get(i).type != Type.BOOLEAN) {
throw new AnalysisException("All params of "
- + fnName + " function must be boolean");
+ + fnName + " function must be boolean");
}
childTypes[i] = children.get(i).type;
}
fn = getBuiltinFunction(fnName.getFunction(), childTypes,
- Function.CompareMode.IS_NONSTRICT_SUPERTYPE_OF);
+ Function.CompareMode.IS_NONSTRICT_SUPERTYPE_OF);
+ } else if
(fnName.getFunction().equalsIgnoreCase(FunctionSet.SEQUENCE_MATCH)
+ ||
fnName.getFunction().equalsIgnoreCase(FunctionSet.SEQUENCE_COUNT)) {
+ if (fnParams.exprs() == null || fnParams.exprs().size() < 4) {
+ throw new AnalysisException("The " + fnName + " function must
have at least four params");
+ }
+ if (!children.get(0).type.isStringType()) {
+ throw new AnalysisException("The pattern params of " + fnName
+ " function must be string");
+ }
+ if (!children.get(1).type.isDateType()) {
+ throw new AnalysisException("The timestamp params of " +
fnName + " function must be DATE or DATETIME");
+ }
+ String pattern = children.get(0).toSql();
+ int patternLength = pattern.length();
+ pattern = pattern.substring(1, patternLength - 1);
+ if (!parsePattern(pattern)) {
+ throw new AnalysisException("The format of pattern params is
wrong");
+ }
+
+ Type[] childTypes = new Type[children.size()];
+ for (int i = 0; i < 2; i++) {
+ childTypes[i] = children.get(i).type;
+ }
+ for (int i = 2; i < children.size(); i++) {
+ if (children.get(i).type != Type.BOOLEAN) {
+ throw new AnalysisException("The 3th and subsequent params
of "
+ + fnName + " function must be boolean");
+ }
+ childTypes[i] = children.get(i).type;
+ }
+ fn = getBuiltinFunction(fnName.getFunction(), childTypes,
+ Function.CompareMode.IS_NONSTRICT_SUPERTYPE_OF);
+ if (fn != null && fn.getArgs()[1].isDatetime() &&
childTypes[1].isDatetimeV2()) {
+ fn.setArgType(childTypes[1], 1);
+ } else if (fn != null && fn.getArgs()[1].isDatetime() &&
childTypes[1].isDateV2()) {
+ fn.setArgType(ScalarType.DATETIMEV2, 1);
+ }
+ if (fn != null && childTypes[1].isDate()) {
+ // cast date to datetime
+ uncheckedCastChild(ScalarType.DATETIME, 1);
+ } else if (fn != null && childTypes[1].isDateV2()) {
+ // cast date to datetime
+ uncheckedCastChild(ScalarType.DATETIMEV2, 1);
+ }
} else if (fnName.getFunction().equalsIgnoreCase("if")) {
Type[] childTypes = collectChildReturnTypes();
Type assignmentCompatibleType =
ScalarType.getAssignmentCompatibleType(childTypes[1], childTypes[2], true);
@@ -1335,6 +1378,73 @@ public class FunctionCallExpr extends Expr {
}
}
+ private static boolean match(String pattern, int pos, String value) {
+ int length = value.length();
+ int end = pattern.length();
+ return pos + length <= end && pattern.substring(pos, pos +
length).equals(value);
+ }
+
+ private static int parseNumber(String s) {
+
+ String[] n = s.split(""); //array of strings
+ int num = 0;
+ for (String value : n) {
+ // validating numbers
+ if ((value.matches("[0-9]+"))) {
+ num++;
+ } else {
+ return num;
+ }
+ }
+ return num;
+ }
+
+ private static boolean parsePattern(String pattern) {
+ int pos = 0;
+ int len = pattern.length();
+ while (pos < len) {
+ if (match(pattern, pos, "(?")) {
+ pos += 2;
+ if (match(pattern, pos, "t")) {
+ pos += 1;
+ if (match(pattern, pos, "<=") || match(pattern, pos, "==")
+ || match(pattern, pos, ">=")) {
+ pos += 2;
+ } else if (match(pattern, pos, ">") || match(pattern, pos,
"<")) {
+ pos += 1;
+ } else {
+ return false;
+ }
+
+ int numLen = parseNumber(pattern.substring(pos));
+ if (numLen == 0) {
+ return false;
+ } else {
+ pos += numLen;
+ }
+ } else {
+ int numLen = parseNumber(pattern.substring(pos));
+ if (numLen == 0) {
+ return false;
+ } else {
+ pos += numLen;
+ }
+ }
+ if (!match(pattern, pos, ")")) {
+ return false;
+ }
+ pos += 1;
+ } else if (match(pattern, pos, ".*")) {
+ pos += 2;
+ } else if (match(pattern, pos, ".")) {
+ pos += 1;
+ } else {
+ return false;
+ }
+ }
+ return true;
+ }
+
/**
* rewrite alias function to real function
* reset function name, function params and it's children to real
function's
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/catalog/AggregateFunction.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/AggregateFunction.java
index ef9dba15cc..6ce6b37f7d 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/AggregateFunction.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/AggregateFunction.java
@@ -51,7 +51,7 @@ public class AggregateFunction extends Function {
"bitmap_intersect", "orthogonal_bitmap_intersect",
"orthogonal_bitmap_intersect_count", "intersect_count",
"orthogonal_bitmap_union_count", FunctionSet.COUNT,
"approx_count_distinct", "ndv",
FunctionSet.BITMAP_UNION_INT, FunctionSet.BITMAP_UNION_COUNT,
"ndv_no_finalize", FunctionSet.WINDOW_FUNNEL,
- FunctionSet.RETENTION);
+ FunctionSet.RETENTION, FunctionSet.SEQUENCE_MATCH,
FunctionSet.SEQUENCE_COUNT);
public static ImmutableSet<String>
ALWAYS_NULLABLE_AGGREGATE_FUNCTION_NAME_SET =
ImmutableSet.of("stddev_samp", "variance_samp", "var_samp",
"percentile_approx");
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/FunctionSet.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/FunctionSet.java
index acbb0a5a18..53a5af6fb5 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/FunctionSet.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/FunctionSet.java
@@ -1393,6 +1393,10 @@ public class FunctionSet<T> {
public static final String RETENTION = "retention";
+ public static final String SEQUENCE_MATCH = "sequence_match";
+
+ public static final String SEQUENCE_COUNT = "sequence_count";
+
// Populate all the aggregate builtins in the catalog.
// null symbols indicate the function does not need that step of the
evaluation.
// An empty symbol indicates a TODO for the BE to implement the function.
@@ -1484,6 +1488,92 @@ public class FunctionSet<T> {
"",
true, false, true, true));
+ // sequenceMatch
+ addBuiltin(AggregateFunction.createBuiltin(FunctionSet.SEQUENCE_MATCH,
+ Lists.newArrayList(Type.STRING, Type.DATEV2, Type.BOOLEAN),
+ Type.BOOLEAN,
+ Type.VARCHAR,
+ true,
+ "",
+ "",
+ "",
+ "",
+ "",
+ "",
+ "",
+ true, false, true, true));
+
+ addBuiltin(AggregateFunction.createBuiltin(FunctionSet.SEQUENCE_MATCH,
+ Lists.newArrayList(Type.STRING, Type.DATETIME, Type.BOOLEAN),
+ Type.BOOLEAN,
+ Type.VARCHAR,
+ true,
+ "",
+ "",
+ "",
+ "",
+ "",
+ "",
+ "",
+ true, false, true, true));
+
+ addBuiltin(AggregateFunction.createBuiltin(FunctionSet.SEQUENCE_MATCH,
+ Lists.newArrayList(Type.STRING, Type.DATETIMEV2, Type.BOOLEAN),
+ Type.BOOLEAN,
+ Type.VARCHAR,
+ true,
+ "",
+ "",
+ "",
+ "",
+ "",
+ "",
+ "",
+ true, false, true, true));
+
+ // sequenceCount
+ addBuiltin(AggregateFunction.createBuiltin(FunctionSet.SEQUENCE_COUNT,
+ Lists.newArrayList(Type.STRING, Type.DATEV2, Type.BOOLEAN),
+ Type.BIGINT,
+ Type.VARCHAR,
+ true,
+ "",
+ "",
+ "",
+ "",
+ "",
+ "",
+ "",
+ true, false, true, true));
+
+ addBuiltin(AggregateFunction.createBuiltin(FunctionSet.SEQUENCE_COUNT,
+ Lists.newArrayList(Type.STRING, Type.DATETIME, Type.BOOLEAN),
+ Type.BIGINT,
+ Type.VARCHAR,
+ true,
+ "",
+ "",
+ "",
+ "",
+ "",
+ "",
+ "",
+ true, false, true, true));
+
+ addBuiltin(AggregateFunction.createBuiltin(FunctionSet.SEQUENCE_COUNT,
+ Lists.newArrayList(Type.STRING, Type.DATETIMEV2, Type.BOOLEAN),
+ Type.BIGINT,
+ Type.VARCHAR,
+ true,
+ "",
+ "",
+ "",
+ "",
+ "",
+ "",
+ "",
+ true, false, true, true));
+
for (Type t : Type.getSupportedTypes()) {
if (t.isNull()) {
continue; // NULL is handled through type promotion.
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/analysis/AggregateTest.java
b/fe/fe-core/src/test/java/org/apache/doris/analysis/AggregateTest.java
index 40d745ba1c..18b45c6bf3 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/analysis/AggregateTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/analysis/AggregateTest.java
@@ -252,7 +252,7 @@ public class AggregateTest extends TestWithFeService {
UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
} catch (AnalysisException e) {
Assert.assertTrue(
- e.getMessage().contains("The mode params of
window_funnel function must be integer"));
+ e.getMessage().contains("The mode params of
window_funnel function must be string"));
break;
} catch (Exception e) {
Assert.fail("must be AnalysisException.");
@@ -267,7 +267,7 @@ public class AggregateTest extends TestWithFeService {
UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
} catch (AnalysisException e) {
Assert.assertTrue(
- e.getMessage().contains("The mode params of
window_funnel function must be integer"));
+ e.getMessage().contains("The mode params of
window_funnel function must be string"));
break;
} catch (Exception e) {
Assert.fail("must be AnalysisException.");
@@ -320,4 +320,211 @@ public class AggregateTest extends TestWithFeService {
Assert.fail("must be AnalysisException.");
} while (false);
}
+
+ @Test
+ public void testSequenceMatchAnalysisException() throws Exception {
+ ConnectContext ctx = UtFrameUtils.createDefaultCtx();
+
+ // normal.
+ do {
+ String query = "select sequence_match('(?1)(?2)', time_col, empid
= 1, empid = 2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ } while (false);
+
+ do {
+ String query = "select sequence_match('(?1)(?2)', timev2_col,
empid = 1, empid = 2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ } while (false);
+
+ do {
+ String query = "select sequence_match('.*', time_col, empid = 1,
empid = 2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ } while (false);
+
+ do {
+ String query = "select sequence_match('(?1)(?t>1800)(?2)',
time_col, empid = 1, empid = 2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ } while (false);
+
+ // less argument.
+ do {
+ String query = "select sequence_match(time_col, empid = 1, empid =
2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (AnalysisException e) {
+ Assert.assertTrue(e.getMessage().contains("function must have
at least four params"));
+ break;
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ Assert.fail("must be AnalysisException.");
+ } while (false);
+
+ do {
+ String query = "select sequence_match('(?1)(?2)', empid = 1, empid
= 2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (AnalysisException e) {
+ Assert.assertTrue(e.getMessage().contains("function must have
at least four params"));
+ break;
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ Assert.fail("must be AnalysisException.");
+ } while (false);
+
+ // argument with wrong type.
+ do {
+ String query = "select sequence_match(1, time_col, empid = 1,
empid = 2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (AnalysisException e) {
+ Assert.assertTrue(
+ e.getMessage().contains("The pattern params of
sequence_match function must be string"));
+ break;
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ Assert.fail("must be AnalysisException.");
+ } while (false);
+
+ do {
+ String query = "select sequence_match('(?1)(?2)', '1', empid = 1,
empid = 2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (AnalysisException e) {
+ Assert.assertTrue(
+ e.getMessage().contains("The timestamp params of
sequence_match function must be DATE or DATETIME"));
+ break;
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ Assert.fail("must be AnalysisException.");
+ } while (false);
+
+ do {
+ String query = "select sequence_match('(?1)(?2)', time_col, '1',
empid = 2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (AnalysisException e) {
+ Assert.assertTrue(e.getMessage().contains(
+ "The 3th and subsequent params of sequence_match
function must be boolean"));
+ break;
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ Assert.fail("must be AnalysisException.");
+ } while (false);
+
+ do {
+ String query = "select sequence_match('(?1)(?2)', time_col, empid
= 2, '1') from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (AnalysisException e) {
+ Assert.assertTrue(e.getMessage().contains(
+ "The 3th and subsequent params of sequence_match
function must be boolean"));
+ break;
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ Assert.fail("must be AnalysisException.");
+ } while (false);
+
+ // wrong pattern param
+ do {
+ String query = "select sequence_match('(?1)(?)', time_col, empid =
1, empid = 2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (AnalysisException e) {
+ Assert.assertTrue(e.getMessage().contains(
+ "The format of pattern params is wrong"));
+ break;
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ } while (false);
+
+ do {
+ String query = "select sequence_match('(?1', time_col, empid = 1,
empid = 2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (AnalysisException e) {
+ Assert.assertTrue(e.getMessage().contains(
+ "The format of pattern params is wrong"));
+ break;
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ } while (false);
+
+ do {
+ String query = "select sequence_match('(1)(2)', time_col, empid =
1, empid = 2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (AnalysisException e) {
+ Assert.assertTrue(e.getMessage().contains(
+ "The format of pattern params is wrong"));
+ break;
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ } while (false);
+
+ do {
+ String query = "select sequence_match('(?1)(?t)(?2)', time_col,
empid = 1, empid = 2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (AnalysisException e) {
+ Assert.assertTrue(e.getMessage().contains(
+ "The format of pattern params is wrong"));
+ break;
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ } while (false);
+
+ do {
+ String query = "select sequence_match('(?1)(?2).**', time_col,
empid = 1, empid = 2) from "
+ + DB_NAME + "." + TABLE_NAME;
+ try {
+ UtFrameUtils.parseAndAnalyzeStmt(query, ctx);
+ } catch (AnalysisException e) {
+ Assert.assertTrue(e.getMessage().contains(
+ "The format of pattern params is wrong"));
+ break;
+ } catch (Exception e) {
+ Assert.fail("must be AnalysisException.");
+ }
+ } while (false);
+ }
}
diff --git
a/regression-test/data/query_p0/sql_functions/aggregate_functions/test_aggregate_sequence_match.out
b/regression-test/data/query_p0/sql_functions/aggregate_functions/test_aggregate_sequence_match.out
new file mode 100644
index 0000000000..7203aa679d
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/aggregate_functions/test_aggregate_sequence_match.out
@@ -0,0 +1,115 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !test_aggregate_sequence_match --
+0
+
+-- !test_aggregate_sequence_match_2 --
+0
+
+-- !test_aggregate_sequence_match_3 --
+5
+
+-- !test_aggregate_sequence_match_4 --
+1 2022-11-02T10:41 1
+2 2022-11-02T11:41 7
+3 2022-11-02T16:15:01 3
+4 2022-11-02T19:05:04 4
+5 2022-11-02T21:24:12 5
+
+-- !test_aggregate_sequence_match_5 --
+false
+
+-- !test_aggregate_sequence_match_6 --
+0
+
+-- !test_aggregate_sequence_match_7 --
+false
+
+-- !test_aggregate_sequence_match_8 --
+0
+
+-- !test_aggregate_sequence_match_9 --
+false
+
+-- !test_aggregate_sequence_match_10 --
+true
+
+-- !test_aggregate_sequence_match_11 --
+0
+
+-- !test_aggregate_sequence_match_12 --
+1
+
+-- !test_aggregate_sequence_match_13 --
+false
+
+-- !test_aggregate_sequence_match_14 --
+0
+
+-- !test_aggregate_sequence_match_15 --
+0
+
+-- !test_aggregate_sequence_match_16 --
+0
+
+-- !test_aggregate_sequence_match_17 --
+5
+
+-- !test_aggregate_sequence_match_18 --
+1 2022-11-02T10:41 1
+2 2022-11-02T13:28:02 2
+3 2022-11-02T16:15:01 1
+4 2022-11-02T19:05:04 2
+5 2022-11-02T20:08:44 3
+
+-- !test_aggregate_sequence_match_19 --
+true
+
+-- !test_aggregate_sequence_match_20 --
+1
+
+-- !test_aggregate_sequence_match_21 --
+true
+
+-- !test_aggregate_sequence_match_22 --
+2
+
+-- !test_aggregate_sequence_match_23 --
+true
+
+-- !test_aggregate_sequence_match_24 --
+1
+
+-- !test_aggregate_sequence_match_25 --
+0
+
+-- !test_aggregate_sequence_match_26 --
+0
+
+-- !test_aggregate_sequence_match_27 --
+5
+
+-- !test_aggregate_sequence_match_28 --
+1 2022-11-02T10:41 1
+2 2022-11-02T13:28:02 2
+3 2022-11-02T16:15:01 1
+4 2022-11-02T19:05:04 2
+5 2022-11-02T20:08:44 3
+
+-- !test_aggregate_sequence_match_29 --
+true
+
+-- !test_aggregate_sequence_match_30 --
+1
+
+-- !test_aggregate_sequence_match_31 --
+true
+
+-- !test_aggregate_sequence_match_32 --
+2
+
+-- !test_aggregate_sequence_match_33 --
+true
+
+-- !test_aggregate_sequence_match_34 --
+1
+
diff --git
a/regression-test/suites/query_p0/sql_functions/aggregate_functions/test_aggregate_sequence_match.sql
b/regression-test/suites/query_p0/sql_functions/aggregate_functions/test_aggregate_sequence_match.sql
new file mode 100644
index 0000000000..9a37ed3931
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/aggregate_functions/test_aggregate_sequence_match.sql
@@ -0,0 +1,105 @@
+-- test no match
+DROP TABLE IF EXISTS sequence_match_test1;
+
+CREATE TABLE sequence_match_test1(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_match_test1(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 11:41:00',
7),
+ (3, '2022-11-02 16:15:01',
3),
+ (4, '2022-11-02 19:05:04',
4),
+ (5, '2022-11-02 21:24:12',
5);
+
+SELECT * FROM sequence_match_test1 ORDER BY date;
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_match_test1;
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_match_test1;
+
+SELECT sequence_match('(?1)(?2).*', date, number = 1, number = 2) FROM
sequence_match_test1;
+SELECT sequence_count('(?1)(?2).*', date, number = 1, number = 2) FROM
sequence_match_test1;
+
+--not match
+SELECT sequence_match('(?1)(?t>3600)(?2)', date, number = 1, number = 7) FROM
sequence_match_test1;
+--match
+SELECT sequence_match('(?1)(?t>=3600)(?2)', date, number = 1, number = 7) FROM
sequence_match_test1;
+
+--not match
+SELECT sequence_count('(?1)(?t>3600)(?2)', date, number = 1, number = 7) FROM
sequence_match_test1;
+--match
+SELECT sequence_count('(?1)(?t>=3600)(?2)', date, number = 1, number = 7) FROM
sequence_match_test1;
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 4, number = 3)
FROM sequence_match_test1;
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 4, number = 3)
FROM sequence_match_test1;
+
+
+--test match
+
+DROP TABLE IF EXISTS sequence_match_test2;
+
+CREATE TABLE sequence_match_test2(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_match_test2(uid, date, number) values (1, '2022-11-02
10:41:00', 1),
+ (2, '2022-11-02 13:28:02',
2),
+ (3, '2022-11-02 16:15:01',
1),
+ (4, '2022-11-02 19:05:04',
2),
+ (5, '2022-11-02 20:08:44',
3);
+
+SELECT * FROM sequence_match_test2 ORDER BY date;
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 3) FROM
sequence_match_test2;
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 3) FROM
sequence_match_test2;
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_match_test2;
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_match_test2;
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 3, number = 4)
FROM sequence_match_test2;
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 3, number = 4)
FROM sequence_match_test2;
+
+--test reverse
+
+DROP TABLE IF EXISTS sequence_match_test3;
+
+CREATE TABLE sequence_match_test3(
+ `uid` int COMMENT 'user id',
+ `date` datetime COMMENT 'date time',
+ `number` int NULL COMMENT 'number'
+ )
+DUPLICATE KEY(uid)
+DISTRIBUTED BY HASH(uid) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO sequence_match_test3(uid, date, number) values (5, '2022-11-02
20:08:44', 3),
+ (4, '2022-11-02 19:05:04',
2),
+ (3, '2022-11-02 16:15:01',
1),
+ (2, '2022-11-02 13:28:02',
2),
+ (1, '2022-11-02 10:41:00',
1);
+
+SELECT * FROM sequence_match_test3 ORDER BY date;
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 3) FROM
sequence_match_test3;
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 3) FROM
sequence_match_test3;
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_match_test3;
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 2) FROM
sequence_match_test3;
+
+SELECT sequence_match('(?1)(?2)', date, number = 1, number = 3, number = 4)
FROM sequence_match_test3;
+SELECT sequence_count('(?1)(?2)', date, number = 1, number = 3, number = 4)
FROM sequence_match_test3;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
