This is an automated email from the ASF dual-hosted git repository. kxiao pushed a commit to branch branch-2.0 in repository https://gitbox.apache.org/repos/asf/doris.git
commit 546e33d2a6530f0b4753e0261e9b91ff9dd5fbe2 Author: bobhan1 <[email protected]> AuthorDate: Fri Jul 21 13:57:27 2023 +0800 [Enhancement](window-funnel)add different modes for window_funnel() function (#20563) --- .../aggregate_function_window_funnel.h | 91 ++++++++++-- .../WINDOW-FUNCTION-WINDOW-FUNNEL.md | 6 +- .../WINDOW-FUNCTION-WINDOW-FUNNEL.md | 6 +- .../data/nereids_p0/aggregate/window_funnel.out | 15 ++ .../data/query_p0/aggregate/window_funnel.out | 15 ++ .../nereids_p0/aggregate/window_funnel.groovy | 161 ++++++++++++++++++++ .../suites/query_p0/aggregate/window_funnel.groovy | 162 +++++++++++++++++++++ 7 files changed, 440 insertions(+), 16 deletions(-) diff --git a/be/src/vec/aggregate_functions/aggregate_function_window_funnel.h b/be/src/vec/aggregate_functions/aggregate_function_window_funnel.h index 54ebfe7b28..422d8f1650 100644 --- a/be/src/vec/aggregate_functions/aggregate_function_window_funnel.h +++ b/be/src/vec/aggregate_functions/aggregate_function_window_funnel.h @@ -32,8 +32,10 @@ #include <utility> #include <vector> +#include "common/compiler_util.h" #include "util/binary_cast.hpp" #include "vec/aggregate_functions/aggregate_function.h" +#include "vec/columns/column_string.h" #include "vec/columns/column_vector.h" #include "vec/columns/columns_number.h" #include "vec/common/assert_cast.h" @@ -53,17 +55,35 @@ class IColumn; namespace doris::vectorized { +enum class WindowFunnelMode : Int64 { INVALID, DEFAULT, DEDUPLICATION, FIXED, INCREASE }; + +WindowFunnelMode string_to_window_funnel_mode(const String& string) { + if (string == "default") { + return WindowFunnelMode::DEFAULT; + } else if (string == "deduplication") { + return WindowFunnelMode::DEDUPLICATION; + } else if (string == "fixed") { + return WindowFunnelMode::FIXED; + } else if (string == "increase") { + return WindowFunnelMode::INCREASE; + } else { + return WindowFunnelMode::INVALID; + } +} + template <typename DateValueType, typename NativeType> struct WindowFunnelState { std::vector<std::pair<DateValueType, int>> events; int max_event_level; bool sorted; int64_t window; + WindowFunnelMode window_funnel_mode; WindowFunnelState() { sorted = true; max_event_level = 0; window = 0; + window_funnel_mode = WindowFunnelMode::INVALID; } void reset() { @@ -73,9 +93,12 @@ struct WindowFunnelState { events.shrink_to_fit(); } - void add(const DateValueType& timestamp, int event_idx, int event_num, int64_t win) { + void add(const DateValueType& timestamp, int event_idx, int event_num, int64_t win, + WindowFunnelMode mode) { window = win; max_event_level = event_num; + window_funnel_mode = mode; + if (sorted && events.size() > 0) { if (events.back().first == timestamp) { sorted = events.back().second <= event_idx; @@ -94,25 +117,58 @@ struct WindowFunnelState { } int get() const { - std::vector<std::optional<DateValueType>> events_timestamp(max_event_level); + if (max_event_level == 0) { + return 0; + } + std::vector<std::optional<std::pair<DateValueType, DateValueType>>> events_timestamp( + max_event_level); + bool is_first_set = false; for (int64_t i = 0; i < events.size(); i++) { const int& event_idx = events[i].second; const DateValueType& timestamp = events[i].first; if (event_idx == 0) { - events_timestamp[0] = timestamp; + events_timestamp[0] = {timestamp, timestamp}; + is_first_set = true; continue; } + if (window_funnel_mode == WindowFunnelMode::DEDUPLICATION && + events_timestamp[event_idx].has_value()) { + break; + } if (events_timestamp[event_idx - 1].has_value()) { - const DateValueType& first_timestamp = events_timestamp[event_idx - 1].value(); + const DateValueType& first_timestamp = + events_timestamp[event_idx - 1].value().first; DateValueType last_timestamp = first_timestamp; TimeInterval interval(SECOND, window, false); last_timestamp.template date_add_interval<SECOND>(interval); - if (timestamp <= last_timestamp) { - events_timestamp[event_idx] = first_timestamp; - if (event_idx + 1 == max_event_level) { - // Usually, max event level is small. - return max_event_level; + if (window_funnel_mode != WindowFunnelMode::INCREASE) { + if (timestamp <= last_timestamp) { + events_timestamp[event_idx] = {first_timestamp, timestamp}; + if (event_idx + 1 == max_event_level) { + // Usually, max event level is small. + return max_event_level; + } + } + } else { + if (timestamp <= last_timestamp && + events_timestamp[event_idx - 1].value().second < timestamp) { + if (!events_timestamp[event_idx].has_value() || + events_timestamp[event_idx].value().second > timestamp) { + events_timestamp[event_idx] = {first_timestamp, timestamp}; + } + if (event_idx + 1 == max_event_level) { + // Usually, max event level is small. + return max_event_level; + } + } + } + } else { + if (is_first_set && window_funnel_mode == WindowFunnelMode::FIXED) { + for (size_t i = 0; i < events_timestamp.size(); i++) { + if (!events_timestamp[i].has_value()) { + return i; + } } } } @@ -147,13 +203,17 @@ struct WindowFunnelState { std::inplace_merge(begin, middle, end); max_event_level = max_event_level > 0 ? max_event_level : other.max_event_level; window = window > 0 ? window : other.window; - + window_funnel_mode = window_funnel_mode == WindowFunnelMode::INVALID + ? other.window_funnel_mode + : window_funnel_mode; sorted = true; } void write(BufferWritable& out) const { write_var_int(max_event_level, out); write_var_int(window, out); + write_var_int(static_cast<std::underlying_type_t<WindowFunnelMode>>(window_funnel_mode), + out); write_var_int(events.size(), out); for (int64_t i = 0; i < events.size(); i++) { @@ -169,6 +229,9 @@ struct WindowFunnelState { read_var_int(event_level, in); max_event_level = (int)event_level; read_var_int(window, in); + int64_t mode; + read_var_int(mode, in); + window_funnel_mode = static_cast<WindowFunnelMode>(mode); int64_t size = 0; read_var_int(size, in); for (int64_t i = 0; i < size; i++) { @@ -178,7 +241,7 @@ struct WindowFunnelState { read_var_int(timestamp, in); read_var_int(event_idx, in); DateValueType time_value = binary_cast<NativeType, DateValueType>(timestamp); - add(time_value, (int)event_idx, max_event_level, window); + add(time_value, (int)event_idx, max_event_level, window, window_funnel_mode); } } }; @@ -204,8 +267,7 @@ public: Arena*) const override { const auto& window = assert_cast<const ColumnVector<Int64>&>(*columns[0]).get_data()[row_num]; - // TODO: handle mode in the future. - // be/src/olap/row_block2.cpp copy_data_to_column + StringRef mode = columns[1]->get_data_at(row_num); const auto& timestamp = assert_cast<const ColumnVector<NativeType>&>(*columns[2]).get_data()[row_num]; const int NON_EVENT_NUM = 3; @@ -215,7 +277,8 @@ public: if (is_set) { this->data(place).add( binary_cast<NativeType, DateValueType>(timestamp), i - NON_EVENT_NUM, - IAggregateFunction::get_argument_types().size() - NON_EVENT_NUM, window); + IAggregateFunction::get_argument_types().size() - NON_EVENT_NUM, window, + string_to_window_funnel_mode(mode.to_string())); } } } diff --git a/docs/en/docs/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-WINDOW-FUNNEL.md b/docs/en/docs/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-WINDOW-FUNNEL.md index 92a19d0644..fe2fcf4edf 100644 --- a/docs/en/docs/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-WINDOW-FUNNEL.md +++ b/docs/en/docs/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-WINDOW-FUNNEL.md @@ -17,7 +17,11 @@ Unless required by applicable law or agreed to in writing, software distributed Searches the longest event chain happened in order (event1, event2, ... , eventN) along the timestamp_column with length of window. - window is the length of time window in seconds. -- mode is reserved for future, not used for now. +- mode can be one of the followings: + - "default": Defualt mode. + - "deduplication": If the same event holds for the sequence of events, then such repeating event interrupts further processing. E.g. the array parameter is [event1='A', event2='B', event3='C', event4='D'], and the original event chain is "A-B-C-B-D". Since event B repeats, the filtered event chain can only be "A-B-C" and the max event level is 3. + - "fixed": Don't allow interventions of other events. E.g. the array parameter is [event1='A', event2='B', event3='C', event4='D'], and the original event chain is A->B->D->C, it stops finding A->B->C at the D and the max event level is 2. + - "increase": Apply conditions only to events with strictly increasing timestamps. - timestamp_column specifies column of DATETIME type, sliding time window works on it. - evnetN is boolean expression like eventID = 1004. 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 93567de318..21b073ab0f 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 @@ -17,7 +17,11 @@ Unless required by applicable law or agreed to in writing, software distributed 漏斗分析函数搜索滑动时间窗口内最大的发生的最大事件序列长度。 - window ：滑动时间窗口大小，单位为秒。 -- mode ：保留，目前只支持default。 +- mode ：模式，共有四种模式 + - "default": 默认模式。 + - "deduplication": 当某个事件重复发生时，这个重复发生的事件会阻止后续的处理过程。如，指定事件链为[event1='A', event2='B', event3='C', event4='D']，原始事件链为"A-B-C-B-D"。由于B事件重复，最终的结果事件链为A-B-C，最大长度为3。 + - "fixed": 不允许事件的顺序发生交错，即事件发生的顺序必须和指定的事件链顺序一致。如，指定事件链为[event1='A', event2='B', event3='C', event4='D']，原始事件链为"A-B-D-C"，则结果事件链为A-B，最大长度为2 + - "increase": 选中的事件的时间戳必须按照指定事件链严格递增。 - timestamp_column ：指定时间列，类型为DATETIME, 滑动窗口沿着此列工作。 - eventN ：表示事件的布尔表达式。 diff --git a/regression-test/data/nereids_p0/aggregate/window_funnel.out b/regression-test/data/nereids_p0/aggregate/window_funnel.out index b6db6f0046..3396dd90e8 100644 --- a/regression-test/data/nereids_p0/aggregate/window_funnel.out +++ b/regression-test/data/nereids_p0/aggregate/window_funnel.out @@ -11,3 +11,18 @@ -- !window_funnel -- 2 +-- !window_funnel_deduplication -- +2 + +-- !window_funnel_fixed -- +2 + +-- !window_funnel_fixed -- +1 + +-- !window_funnel_increase -- +4 + +-- !window_funnel_increase -- +2 + diff --git a/regression-test/data/query_p0/aggregate/window_funnel.out b/regression-test/data/query_p0/aggregate/window_funnel.out index b6db6f0046..3396dd90e8 100644 --- a/regression-test/data/query_p0/aggregate/window_funnel.out +++ b/regression-test/data/query_p0/aggregate/window_funnel.out @@ -11,3 +11,18 @@ -- !window_funnel -- 2 +-- !window_funnel_deduplication -- +2 + +-- !window_funnel_fixed -- +2 + +-- !window_funnel_fixed -- +1 + +-- !window_funnel_increase -- +4 + +-- !window_funnel_increase -- +2 + diff --git a/regression-test/suites/nereids_p0/aggregate/window_funnel.groovy b/regression-test/suites/nereids_p0/aggregate/window_funnel.groovy index 803c2ad1fe..c2ea9469b7 100644 --- a/regression-test/suites/nereids_p0/aggregate/window_funnel.groovy +++ b/regression-test/suites/nereids_p0/aggregate/window_funnel.groovy @@ -104,4 +104,165 @@ suite("window_funnel") { from ${tableName} t; """ sql """ DROP TABLE IF EXISTS ${tableName} """ + + sql """ DROP TABLE IF EXISTS ${tableName} """ + sql """ + CREATE TABLE IF NOT EXISTS ${tableName} ( + xwho varchar(50) NULL COMMENT 'xwho', + xwhen datetimev2(3) COMMENT 'xwhen', + xwhat int NULL COMMENT 'xwhat' + ) + DUPLICATE KEY(xwho) + DISTRIBUTED BY HASH(xwho) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ); + """ + sql "INSERT into ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 10:41:00.111111', 1)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:02.111111', 2)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:03.111111', 2)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 14:15:01.111111', 3)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 15:05:04.111111', 4)" + qt_window_funnel_deduplication """ + select + window_funnel( + 20000, + 'deduplication', + t.xwhen, + t.xwhat = 1, + t.xwhat = 2, + t.xwhat = 3, + t.xwhat = 4 + ) AS level + from ${tableName} t; + """ + sql """ DROP TABLE IF EXISTS ${tableName} """ + + sql """ DROP TABLE IF EXISTS ${tableName} """ + sql """ + CREATE TABLE IF NOT EXISTS ${tableName} ( + xwho varchar(50) NULL COMMENT 'xwho', + xwhen datetimev2(3) COMMENT 'xwhen', + xwhat int NULL COMMENT 'xwhat' + ) + DUPLICATE KEY(xwho) + DISTRIBUTED BY HASH(xwho) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ); + """ + sql "INSERT into ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 10:41:00.111111', 1)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:02.111111', 2)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 14:15:01.111111', 3)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 15:05:04.111111', 4)" + qt_window_funnel_fixed """ + select + window_funnel( + 20000, + 'fixed', + t.xwhen, + t.xwhat = 1, + t.xwhat = 2, + t.xwhat = 4, + t.xwhat = 3 + ) AS level + from ${tableName} t; + """ + sql """ DROP TABLE IF EXISTS ${tableName} """ + + sql """ DROP TABLE IF EXISTS ${tableName} """ + sql """ + CREATE TABLE IF NOT EXISTS ${tableName} ( + xwho varchar(50) NULL COMMENT 'xwho', + xwhen datetimev2(3) COMMENT 'xwhen', + xwhat int NULL COMMENT 'xwhat' + ) + DUPLICATE KEY(xwho) + DISTRIBUTED BY HASH(xwho) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ); + """ + sql "INSERT into ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 10:41:00.111111', 1)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:02.111111', 2)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 14:15:01.111111', 3)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 15:05:04.111111', 4)" + qt_window_funnel_fixed """ + select + window_funnel( + 20000, + 'fixed', + t.xwhen, + t.xwhat = 4, + t.xwhat = 3, + t.xwhat = 2, + t.xwhat = 1 + ) AS level + from ${tableName} t; + """ + sql """ DROP TABLE IF EXISTS ${tableName} """ + + sql """ DROP TABLE IF EXISTS ${tableName} """ + sql """ + CREATE TABLE IF NOT EXISTS ${tableName} ( + xwho varchar(50) NULL COMMENT 'xwho', + xwhen datetimev2(3) COMMENT 'xwhen', + xwhat int NULL COMMENT 'xwhat' + ) + DUPLICATE KEY(xwho) + DISTRIBUTED BY HASH(xwho) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ); + """ + sql "INSERT into ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 10:41:00.111111', 1)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:02.111111', 2)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:03.111111', 3)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 15:05:04.111111', 4)" + qt_window_funnel_increase """ + select + window_funnel( + 20000, + 'increase', + t.xwhen, + t.xwhat = 1, + t.xwhat = 2, + t.xwhat = 3, + t.xwhat = 4 + ) AS level + from ${tableName} t; + """ + sql """ DROP TABLE IF EXISTS ${tableName} """ + + sql """ DROP TABLE IF EXISTS ${tableName} """ + sql """ + CREATE TABLE IF NOT EXISTS ${tableName} ( + xwho varchar(50) NULL COMMENT 'xwho', + xwhen datetimev2(3) COMMENT 'xwhen', + xwhat int NULL COMMENT 'xwhat' + ) + DUPLICATE KEY(xwho) + DISTRIBUTED BY HASH(xwho) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ); + """ + sql "INSERT into ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 10:41:00.111111', 1)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:02.111111', 2)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:02.111111', 3)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 15:05:04.111111', 4)" + qt_window_funnel_increase """ + select + window_funnel( + 20000, + 'increase', + t.xwhen, + t.xwhat = 1, + t.xwhat = 2, + t.xwhat = 3, + t.xwhat = 4 + ) AS level + from ${tableName} t; + """ + sql """ DROP TABLE IF EXISTS ${tableName} """ } diff --git a/regression-test/suites/query_p0/aggregate/window_funnel.groovy b/regression-test/suites/query_p0/aggregate/window_funnel.groovy index 51a5446c20..83f39ae000 100644 --- a/regression-test/suites/query_p0/aggregate/window_funnel.groovy +++ b/regression-test/suites/query_p0/aggregate/window_funnel.groovy @@ -20,6 +20,7 @@ // and modified by Doris. suite("window_funnel") { + sql "SET enable_nereids_planner=false" def tableName = "windowfunnel_test" sql """ DROP TABLE IF EXISTS ${tableName} """ @@ -102,4 +103,165 @@ suite("window_funnel") { from ${tableName} t; """ sql """ DROP TABLE IF EXISTS ${tableName} """ + + sql """ DROP TABLE IF EXISTS ${tableName} """ + sql """ + CREATE TABLE IF NOT EXISTS ${tableName} ( + xwho varchar(50) NULL COMMENT 'xwho', + xwhen datetimev2(3) COMMENT 'xwhen', + xwhat int NULL COMMENT 'xwhat' + ) + DUPLICATE KEY(xwho) + DISTRIBUTED BY HASH(xwho) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ); + """ + sql "INSERT into ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 10:41:00.111111', 1)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:02.111111', 2)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:03.111111', 2)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 14:15:01.111111', 3)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 15:05:04.111111', 4)" + qt_window_funnel_deduplication """ + select + window_funnel( + 20000, + 'deduplication', + t.xwhen, + t.xwhat = 1, + t.xwhat = 2, + t.xwhat = 3, + t.xwhat = 4 + ) AS level + from ${tableName} t; + """ + sql """ DROP TABLE IF EXISTS ${tableName} """ + + sql """ DROP TABLE IF EXISTS ${tableName} """ + sql """ + CREATE TABLE IF NOT EXISTS ${tableName} ( + xwho varchar(50) NULL COMMENT 'xwho', + xwhen datetimev2(3) COMMENT 'xwhen', + xwhat int NULL COMMENT 'xwhat' + ) + DUPLICATE KEY(xwho) + DISTRIBUTED BY HASH(xwho) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ); + """ + sql "INSERT into ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 10:41:00.111111', 1)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:02.111111', 2)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 14:15:01.111111', 3)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 15:05:04.111111', 4)" + qt_window_funnel_fixed """ + select + window_funnel( + 20000, + 'fixed', + t.xwhen, + t.xwhat = 1, + t.xwhat = 2, + t.xwhat = 4, + t.xwhat = 3 + ) AS level + from ${tableName} t; + """ + sql """ DROP TABLE IF EXISTS ${tableName} """ + + sql """ DROP TABLE IF EXISTS ${tableName} """ + sql """ + CREATE TABLE IF NOT EXISTS ${tableName} ( + xwho varchar(50) NULL COMMENT 'xwho', + xwhen datetimev2(3) COMMENT 'xwhen', + xwhat int NULL COMMENT 'xwhat' + ) + DUPLICATE KEY(xwho) + DISTRIBUTED BY HASH(xwho) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ); + """ + sql "INSERT into ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 10:41:00.111111', 1)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:02.111111', 2)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 14:15:01.111111', 3)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 15:05:04.111111', 4)" + qt_window_funnel_fixed """ + select + window_funnel( + 20000, + 'fixed', + t.xwhen, + t.xwhat = 4, + t.xwhat = 3, + t.xwhat = 2, + t.xwhat = 1 + ) AS level + from ${tableName} t; + """ + sql """ DROP TABLE IF EXISTS ${tableName} """ + + sql """ DROP TABLE IF EXISTS ${tableName} """ + sql """ + CREATE TABLE IF NOT EXISTS ${tableName} ( + xwho varchar(50) NULL COMMENT 'xwho', + xwhen datetimev2(3) COMMENT 'xwhen', + xwhat int NULL COMMENT 'xwhat' + ) + DUPLICATE KEY(xwho) + DISTRIBUTED BY HASH(xwho) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ); + """ + sql "INSERT into ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 10:41:00.111111', 1)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:02.111111', 2)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:03.111111', 3)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 15:05:04.111111', 4)" + qt_window_funnel_increase """ + select + window_funnel( + 20000, + 'increase', + t.xwhen, + t.xwhat = 1, + t.xwhat = 2, + t.xwhat = 3, + t.xwhat = 4 + ) AS level + from ${tableName} t; + """ + sql """ DROP TABLE IF EXISTS ${tableName} """ + + sql """ DROP TABLE IF EXISTS ${tableName} """ + sql """ + CREATE TABLE IF NOT EXISTS ${tableName} ( + xwho varchar(50) NULL COMMENT 'xwho', + xwhen datetimev2(3) COMMENT 'xwhen', + xwhat int NULL COMMENT 'xwhat' + ) + DUPLICATE KEY(xwho) + DISTRIBUTED BY HASH(xwho) BUCKETS 3 + PROPERTIES ( + "replication_num" = "1" + ); + """ + sql "INSERT into ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 10:41:00.111111', 1)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:02.111111', 2)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 13:28:02.111111', 3)" + sql "INSERT INTO ${tableName} (xwho, xwhen, xwhat) VALUES('1', '2022-03-12 15:05:04.111111', 4)" + qt_window_funnel_increase """ + select + window_funnel( + 20000, + 'increase', + t.xwhen, + t.xwhat = 1, + t.xwhat = 2, + t.xwhat = 3, + t.xwhat = 4 + ) AS level + from ${tableName} t; + """ + sql """ DROP TABLE IF EXISTS ${tableName} """ } --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
