in regression-test/suites/nereids_p0/aggregate/window_funnel.groovy [364:741]
CREATE TABLE windowfunnel_test (
`xwho` varchar(50) NULL COMMENT 'xwho',
`xwhen` datetime COMMENT 'xwhen',
`xwhat` int NULL COMMENT 'xwhat'
)
DUPLICATE KEY(xwho)
DISTRIBUTED BY HASH(xwho) BUCKETS 3
PROPERTIES (
"replication_num" = "1"
);
"""
sql """
INSERT into windowfunnel_test (xwho, xwhen, xwhat) values ('1', '2022-03-12 10:41:00', 1),
('1', '2022-03-12 13:28:02', 2),
('1', '2022-03-12 16:15:01', 3),
('1', '2022-03-12 19:05:04', 4);
"""
qt_window_funnel_neq """
select window_funnel(3600 * 24, 'default', t.xwhen, t.xwhat = 1, t.xwhat != 2,t.xwhat=3 ) AS level from windowfunnel_test t;
"""
sql """ DROP TABLE IF EXISTS windowfunnel_test """
sql """
CREATE TABLE windowfunnel_test(
user_id BIGINT,
event_name VARCHAR(64),
event_timestamp datetime,
phone_brand varchar(64),
tab_num int
) distributed by hash(user_id) buckets 3 properties("replication_num"="1");
"""
sql """
INSERT INTO windowfunnel_test VALUES
(100123, '登录', '2022-05-14 10:01:00', 'HONOR', 1),
(100123, '访问', '2022-05-14 10:02:00', 'HONOR', 2),
(100123, '下单', '2022-05-14 10:04:00', "HONOR", 3),
(100123, '付款', '2022-05-14 10:10:00', 'HONOR', 4),
(100125, '登录', '2022-05-15 11:00:00', 'XIAOMI', 1),
(100125, '访问', '2022-05-15 11:01:00', 'XIAOMI', 2),
(100125, '下单', '2022-05-15 11:02:00', 'XIAOMI', 6),
(100126, '登录', '2022-05-15 12:00:00', 'IPHONE', 1),
(100126, '访问', '2022-05-15 12:01:00', 'HONOR', 2),
(100127, '登录', '2022-05-15 11:30:00', 'VIVO', 1),
(100127, '访问', '2022-05-15 11:31:00', 'VIVO', 5);
"""
// test default mode
qt_window_funnel_default0 """
SELECT
user_id,
window_funnel(3600 * 3, "default", event_timestamp, event_name = '登录', event_name = '访问', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id
"""
// in 5 minutes
qt_window_funnel_default1 """
SELECT
user_id,
window_funnel(300, "default", event_timestamp, event_name = '登录', event_name = '访问', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id
"""
// in 30 seconds
qt_window_funnel_default2 """
SELECT
user_id,
window_funnel(30, "default", event_timestamp, event_name = '登录', event_name = '访问', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id
"""
qt_window_funnel_default3 """
SELECT
user_id,
window_funnel(3600000000, "default", event_timestamp, event_name = '登录', event_name = '登录',event_name = '访问', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id
"""
qt_window_funnel_default4 """
SELECT
user_id,
window_funnel(3600000000, "default", event_timestamp, event_name = '登录', event_name = '访问',event_name = '访问', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id
"""
qt_window_funnel_default5 """
SELECT
user_id,
window_funnel(3600000000, "default", event_timestamp, event_name = '登录', event_name = '登录', event_name = '登录', event_name = '登录', event_name = '登录',event_name = '登录', event_name = '登录') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id
"""
// complicate expressions
qt_window_funnel_default6 """
SELECT
user_id,
window_funnel(3600000000, "default", event_timestamp, event_name = '登录', event_name != '登陆', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id;
"""
qt_window_funnel_default7 """
SELECT
user_id,
window_funnel(3600000000, "default", event_timestamp, event_name = '登录', event_name != '访问', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id;
"""
qt_window_funnel_default8 """
SELECT
user_id,
window_funnel(3600000000, "default", event_timestamp,
event_name = '登录' AND phone_brand in ('HONOR', 'XIAOMI', 'VIVO') AND tab_num not in (4, 5),
event_name = '访问' AND tab_num not in (4, 5),
event_name = '下单' AND tab_num not in (6, 7),
event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id;
"""
sql """ DROP TABLE IF EXISTS windowfunnel_test """
sql """
CREATE TABLE windowfunnel_test(
user_id BIGINT,
event_name VARCHAR(64),
event_timestamp datetime,
phone_brand varchar(64),
tab_num int
) distributed by hash(user_id) buckets 3 properties("replication_num"="1");
"""
// test multiple matched event list, output the longest match
sql """
INSERT INTO windowfunnel_test VALUES
(100123, '登录', '2022-05-14 10:01:00', 'HONOR', 1),
(100123, '访问', '2022-05-14 10:02:00', 'HONOR', 2),
(100123, '下单', '2022-05-14 10:04:00', "HONOR", 3),
(100125, '登录', '2022-05-15 11:00:00', 'XIAOMI', 1),
(100125, '访问', '2022-05-15 11:01:00', 'XIAOMI', 2),
(100125, '下单', '2022-05-15 11:02:00', 'XIAOMI', 6),
(100126, '登录', '2022-05-15 12:00:00', 'IPHONE', 1),
(100126, '访问', '2022-05-15 12:01:00', 'HONOR', 2),
(100127, '登录', '2022-05-15 11:30:00', 'VIVO', 1),
(100127, '访问', '2022-05-15 11:31:00', 'VIVO', 5),
(100123, '登录', '2022-05-14 13:01:00', 'HONOR', 1),
(100123, '访问', '2022-05-14 13:02:00', 'HONOR', 2),
(100123, '下单', '2022-05-14 13:04:00', "HONOR", 3),
(100123, '付款', '2022-05-14 13:10:00', 'HONOR', 4),
(100126, '登录', '2022-05-15 14:00:00', 'IPHONE', 1),
(100126, '访问', '2022-05-15 14:01:00', 'HONOR', 2),
(100126, '下单', '2022-05-15 14:02:00', 'HONOR', 3),
(100126, '付款', '2022-05-15 14:03:00', 'HONOR', 4);
"""
qt_window_funnel_default9 """
SELECT
user_id,
window_funnel(3600, "default", event_timestamp, event_name = '登录', event_name = '访问', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id
"""
// test deduplication mode
sql """ DROP TABLE IF EXISTS windowfunnel_test """
sql """
CREATE TABLE windowfunnel_test(
user_id BIGINT,
event_name VARCHAR(64),
event_timestamp datetime,
phone_brand varchar(64),
tab_num int
) distributed by hash(user_id) buckets 3 properties("replication_num"="1");
"""
sql """
INSERT INTO windowfunnel_test VALUES
(100123, '登录', '2022-05-14 10:01:00', 'HONOR', 1),
(100123, '访问', '2022-05-14 10:02:00', 'HONOR', 2),
(100123, '下单', '2022-05-14 10:04:00', "HONOR", 4),
(100123, '登录', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '登录1', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '登录2', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '登录3', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '登录4', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '登录5', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '付款', '2022-05-14 10:10:00', 'HONOR', 4),
(100125, '登录', '2022-05-15 11:00:00', 'XIAOMI', 1),
(100125, '访问', '2022-05-15 11:01:00', 'XIAOMI', 2),
(100125, '下单', '2022-05-15 11:02:00', 'XIAOMI', 6),
(100126, '登录', '2022-05-15 12:00:00', 'IPHONE', 1),
(100126, '访问', '2022-05-15 12:01:00', 'HONOR', 2),
(100127, '登录', '2022-05-15 11:30:00', 'VIVO', 1),
(100127, '访问', '2022-05-15 11:31:00', 'VIVO', 5);
"""
qt_window_funnel_deduplication0 """
SELECT
user_id,
window_funnel(3600, "deduplication", event_timestamp, event_name = '登录', event_name = '访问', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id
"""
sql """ truncate table windowfunnel_test; """
sql """
INSERT INTO windowfunnel_test VALUES
(100123, '登录', '2022-05-14 10:01:00', 'HONOR', 1),
(100123, '访问', '2022-05-14 10:02:00', 'HONOR', 2),
(100123, '下单', '2022-05-14 10:04:00', "HONOR", 4),
(100123, '登录1', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '登录2', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '登录3', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '登录4', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '登录5', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '访问', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '付款', '2022-05-14 10:10:00', 'HONOR', 4),
(100125, '登录', '2022-05-15 11:00:00', 'XIAOMI', 1),
(100125, '访问', '2022-05-15 11:01:00', 'XIAOMI', 2),
(100125, '下单', '2022-05-15 11:02:00', 'XIAOMI', 6),
(100126, '登录', '2022-05-15 12:00:00', 'IPHONE', 1),
(100126, '访问', '2022-05-15 12:01:00', 'HONOR', 2),
(100127, '登录', '2022-05-15 11:30:00', 'VIVO', 1),
(100127, '访问', '2022-05-15 11:31:00', 'VIVO', 5);
"""
qt_window_funnel_deduplication1 """
SELECT
user_id,
window_funnel(3600, "deduplication", event_timestamp, event_name = '登录', event_name = '访问', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id
"""
sql """ truncate table windowfunnel_test; """
sql """
INSERT INTO windowfunnel_test VALUES
(100123, '登录', '2022-05-14 10:01:00', 'HONOR', 1),
(100123, '访问', '2022-05-14 10:02:00', 'HONOR', 2),
(100123, '下单', '2022-05-14 10:04:00', "HONOR", 4),
(100123, '登录1', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '登录2', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '登录3', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '登录4', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '登录5', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '下单', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '付款', '2022-05-14 10:10:00', 'HONOR', 4),
(100125, '登录', '2022-05-15 11:00:00', 'XIAOMI', 1),
(100125, '访问', '2022-05-15 11:01:00', 'XIAOMI', 2),
(100125, '下单', '2022-05-15 11:02:00', 'XIAOMI', 6),
(100126, '登录', '2022-05-15 12:00:00', 'IPHONE', 1),
(100126, '访问', '2022-05-15 12:01:00', 'HONOR', 2),
(100127, '登录', '2022-05-15 11:30:00', 'VIVO', 1),
(100127, '访问', '2022-05-15 11:31:00', 'VIVO', 5);
"""
qt_window_funnel_deduplication2 """
SELECT
user_id,
window_funnel(3600, "deduplication", event_timestamp, event_name = '登录', event_name = '访问', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id
"""
// test fixed mode
sql """ truncate table windowfunnel_test; """
sql """
INSERT INTO windowfunnel_test VALUES
(100123, '登录', '2022-05-14 10:01:00', 'HONOR', 1),
(100123, '访问', '2022-05-14 10:02:00', 'HONOR', 2),
(100123, '登录', '2022-05-14 10:03:00', 'HONOR', 3),
(100123, '下单', '2022-05-14 10:04:00', "HONOR", 4),
(100123, '付款', '2022-05-14 10:10:00', 'HONOR', 4),
(100125, '登录', '2022-05-15 11:00:00', 'XIAOMI', 1),
(100125, '访问', '2022-05-15 11:01:00', 'XIAOMI', 2),
(100125, '下单', '2022-05-15 11:02:00', 'XIAOMI', 6),
(100126, '登录', '2022-05-15 12:00:00', 'IPHONE', 1),
(100126, '访问', '2022-05-15 12:01:00', 'HONOR', 2),
(100127, '登录', '2022-05-15 11:30:00', 'VIVO', 1),
(100127, '访问', '2022-05-15 11:31:00', 'VIVO', 5);
"""
qt_window_funnel_fixed0 """
SELECT
user_id,
window_funnel(3600, "fixed", event_timestamp, event_name = '登录', event_name = '访问', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id
"""
sql """ DROP TABLE IF EXISTS windowfunnel_test """
sql """
CREATE TABLE windowfunnel_test(
user_id BIGINT,
event_name VARCHAR(64),
event_timestamp datetime,
phone_brand varchar(64),
tab_num int
) distributed by hash(event_timestamp) buckets 3 properties("replication_num"="1");
"""
sql """
INSERT INTO windowfunnel_test VALUES
(100123, '登录', '2022-05-14 10:01:00', 'HONOR', 1),
(100123, '访问', '2022-05-14 10:02:00', 'HONOR', 2),
(100123, '登录2', '2022-05-14 10:03:00', 'HONOR', 3),
(100123, '下单', '2022-05-14 10:04:00', "HONOR", 4),
(100123, '付款', '2022-05-14 10:10:00', 'HONOR', 4),
(100125, '登录', '2022-05-15 11:00:00', 'XIAOMI', 1),
(100125, '访问', '2022-05-15 11:01:00', 'XIAOMI', 2),
(100125, '下单', '2022-05-15 11:02:00', 'XIAOMI', 6),
(100126, '登录', '2022-05-15 12:00:00', 'IPHONE', 1),
(100126, '访问', '2022-05-15 12:01:00', 'HONOR', 2),
(100127, '登录', '2022-05-15 11:30:00', 'VIVO', 1),
(100127, '访问', '2022-05-15 11:31:00', 'VIVO', 5);
"""
qt_window_funnel_fixed1 """
SELECT
user_id,
window_funnel(3600, "fixed", event_timestamp, event_name = '登录', event_name = '访问', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id
"""
// test increase mode
sql """ DROP TABLE IF EXISTS windowfunnel_test """
sql """
CREATE TABLE windowfunnel_test(
user_id BIGINT,
event_name VARCHAR(64),
event_timestamp datetime,
phone_brand varchar(64),
tab_num int
) distributed by hash(user_id) buckets 3 properties("replication_num"="1");
"""
sql """
INSERT INTO windowfunnel_test VALUES
(100123, '登录', '2022-05-14 10:01:00', 'HONOR', 1),
(100123, '访问', '2022-05-14 10:02:00', 'HONOR', 2),
(100123, '下单', '2022-05-14 10:04:00', "HONOR", 4),
(100123, '付款', '2022-05-14 10:04:00', 'HONOR', 4),
(100125, '登录', '2022-05-15 11:00:00', 'XIAOMI', 1),
(100125, '访问', '2022-05-15 11:01:00', 'XIAOMI', 2),
(100125, '下单', '2022-05-15 11:02:00', 'XIAOMI', 6),
(100126, '登录', '2022-05-15 12:00:00', 'IPHONE', 1),
(100126, '访问', '2022-05-15 12:01:00', 'HONOR', 2),
(100127, '登录', '2022-05-15 11:30:00', 'VIVO', 1),
(100127, '访问', '2022-05-15 11:31:00', 'VIVO', 5);
"""
qt_window_funnel_increase0 """
SELECT
user_id,
window_funnel(3600, "increase", event_timestamp, event_name = '登录', event_name = '访问', event_name = '下单', event_name = '付款') AS level
FROM windowfunnel_test
GROUP BY user_id
order BY user_id
"""
sql """ DROP TABLE IF EXISTS windowfunnel_test_1 """
sql """
CREATE TABLE windowfunnel_test_1 (
`xwho` varchar(50) NULL COMMENT 'xwho',
`xwhen` datetime(3) COMMENT 'xwhen',
`xwhat` int NULL COMMENT 'xwhat'
) DUPLICATE KEY(xwho)
DISTRIBUTED BY HASH(xwho) BUCKETS 3
PROPERTIES ("replication_num" = "1");
"""
sql """ INSERT INTO windowfunnel_test_1 (xwho, xwhen, xwhat) VALUES
('1', '2022-03-12 10:41:00.111', 1),
('1', '2022-03-12 10:41:00.888', 2),
('1', '2022-03-12 13:28:02.177', 3),
('1', '2022-03-12 16:15:01.444', 4); """
explain {
sql("""select window_funnel(3600 * 3, 'deduplication', t.xwhen, t.xwhat = 1, t.xwhat = 2 ) AS level from windowfunnel_test_1 t;""")
notContains("cast")
}