聚合函数 - SEQUENCE_MATCH - 《Apache Doris v2.0 中文文档》

SEQUENCE-MATCH
- Description
- example
- keywords

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

匹配例子

1. DROP TABLE IF EXISTS sequence_match_test1;

3. CREATE TABLE sequence_match_test1(
4. `uid` int COMMENT 'user id',
5. `date` datetime COMMENT 'date time',
6. `number` int NULL COMMENT 'number'
7. )
8. DUPLICATE KEY(uid)
9. DISTRIBUTED BY HASH(uid) BUCKETS 3
10. PROPERTIES (
11. "replication_num" = "1"
12. );

14. INSERT INTO sequence_match_test1(uid, date, number) values (1, '2022-11-02 10:41:00', 1),
15. (2, '2022-11-02 13:28:02', 2),
16. (3, '2022-11-02 16:15:01', 1),
17. (4, '2022-11-02 19:05:04', 2),
18. (5, '2022-11-02 20:08:44', 3);

20. SELECT * FROM sequence_match_test1 ORDER BY date;

22. +------+---------------------+--------+
23. | uid  | date                | number |
24. +------+---------------------+--------+
25. |    1 | 2022-11-02 10:41:00 |      1 |
26. |    2 | 2022-11-02 13:28:02 |      2 |
27. |    3 | 2022-11-02 16:15:01 |      1 |
28. |    4 | 2022-11-02 19:05:04 |      2 |
29. |    5 | 2022-11-02 20:08:44 |      3 |
30. +------+---------------------+--------+

32. SELECT sequence_match('(?1)(?2)', date, number = 1, number = 3) FROM sequence_match_test1;

34. +----------------------------------------------------------------+
35. | sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 3) |
36. +----------------------------------------------------------------+
37. |                                                              1 |
38. +----------------------------------------------------------------+

40. SELECT sequence_match('(?1)(?2)', date, number = 1, number = 2) FROM sequence_match_test1;

42. +----------------------------------------------------------------+
43. | sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 2) |
44. +----------------------------------------------------------------+
45. |                                                              1 |
46. +----------------------------------------------------------------+

48. SELECT sequence_match('(?1)(?t>=3600)(?2)', date, number = 1, number = 2) FROM sequence_match_test1;

50. +---------------------------------------------------------------------------+
51. | sequence_match('(?1)(?t>=3600)(?2)', `date`, `number` = 1, `number` = 2) |
52. +---------------------------------------------------------------------------+
53. |                                                                         1 |
54. +---------------------------------------------------------------------------+

不匹配例子

1. DROP TABLE IF EXISTS sequence_match_test2;

3. CREATE TABLE sequence_match_test2(
4. `uid` int COMMENT 'user id',
5. `date` datetime COMMENT 'date time',
6. `number` int NULL COMMENT 'number'
7. )
8. DUPLICATE KEY(uid)
9. DISTRIBUTED BY HASH(uid) BUCKETS 3
10. PROPERTIES (
11. "replication_num" = "1"
12. );

14. INSERT INTO sequence_match_test2(uid, date, number) values (1, '2022-11-02 10:41:00', 1),
15. (2, '2022-11-02 11:41:00', 7),
16. (3, '2022-11-02 16:15:01', 3),
17. (4, '2022-11-02 19:05:04', 4),
18. (5, '2022-11-02 21:24:12', 5);

20. SELECT * FROM sequence_match_test2 ORDER BY date;

22. +------+---------------------+--------+
23. | uid  | date                | number |
24. +------+---------------------+--------+
25. |    1 | 2022-11-02 10:41:00 |      1 |
26. |    2 | 2022-11-02 11:41:00 |      7 |
27. |    3 | 2022-11-02 16:15:01 |      3 |
28. |    4 | 2022-11-02 19:05:04 |      4 |
29. |    5 | 2022-11-02 21:24:12 |      5 |
30. +------+---------------------+--------+

32. SELECT sequence_match('(?1)(?2)', date, number = 1, number = 2) FROM sequence_match_test2;

34. +----------------------------------------------------------------+
35. | sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 2) |
36. +----------------------------------------------------------------+
37. |                                                              0 |
38. +----------------------------------------------------------------+

40. SELECT sequence_match('(?1)(?2).*', date, number = 1, number = 2) FROM sequence_match_test2;

42. +------------------------------------------------------------------+
43. | sequence_match('(?1)(?2).*', `date`, `number` = 1, `number` = 2) |
44. +------------------------------------------------------------------+
45. |                                                                0 |
46. +------------------------------------------------------------------+

48. SELECT sequence_match('(?1)(?t>3600)(?2)', date, number = 1, number = 7) FROM sequence_match_test2;

50. +--------------------------------------------------------------------------+
51. | sequence_match('(?1)(?t>3600)(?2)', `date`, `number` = 1, `number` = 7) |
52. +--------------------------------------------------------------------------+
53. |                                                                        0 |
54. +--------------------------------------------------------------------------+

特殊例子

1. DROP TABLE IF EXISTS sequence_match_test3;

3. CREATE TABLE sequence_match_test3(
4. `uid` int COMMENT 'user id',
5. `date` datetime COMMENT 'date time',
6. `number` int NULL COMMENT 'number'
7. )
8. DUPLICATE KEY(uid)
9. DISTRIBUTED BY HASH(uid) BUCKETS 3
10. PROPERTIES (
11. "replication_num" = "1"
12. );

14. INSERT INTO sequence_match_test3(uid, date, number) values (1, '2022-11-02 10:41:00', 1),
15. (2, '2022-11-02 11:41:00', 7),
16. (3, '2022-11-02 16:15:01', 3),
17. (4, '2022-11-02 19:05:04', 4),
18. (5, '2022-11-02 21:24:12', 5);

20. SELECT * FROM sequence_match_test3 ORDER BY date;

22. +------+---------------------+--------+
23. | uid  | date                | number |
24. +------+---------------------+--------+
25. |    1 | 2022-11-02 10:41:00 |      1 |
26. |    2 | 2022-11-02 11:41:00 |      7 |
27. |    3 | 2022-11-02 16:15:01 |      3 |
28. |    4 | 2022-11-02 19:05:04 |      4 |
29. |    5 | 2022-11-02 21:24:12 |      5 |
30. +------+---------------------+--------+

Perform the query:

1. SELECT sequence_match('(?1)(?2)', date, number = 1, number = 5) FROM sequence_match_test3;

3. +----------------------------------------------------------------+
4. | sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 5) |
5. +----------------------------------------------------------------+
6. |                                                              1 |
7. +----------------------------------------------------------------+

上面为一个非常简单的匹配例子，该函数找到了数字5跟随数字1的事件链。它跳过了它们之间的数字7，3，4，因为该数字没有被描述为事件。如果我们想在搜索示例中给出的事件链时考虑这个数字，我们应该为它创建一个条件。

现在，考虑如下执行语句：

1. SELECT sequence_match('(?1)(?2)', date, number = 1, number = 5, number = 4) FROM sequence_match_test3;

3. +------------------------------------------------------------------------------+
4. | sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 5, `number` = 4) |
5. +------------------------------------------------------------------------------+
6. |                                                                            0 |
7. +------------------------------------------------------------------------------+

您可能对这个结果有些许疑惑，在这种情况下，函数找不到与模式匹配的事件链，因为数字4的事件发生在1和5之间。如果在相同的情况下，我们检查了数字6的条件，则序列将与模式匹配。

1. SELECT sequence_match('(?1)(?2)', date, number = 1, number = 5, number = 6) FROM sequence_match_test3;

3. +------------------------------------------------------------------------------+
4. | sequence_match('(?1)(?2)', `date`, `number` = 1, `number` = 5, `number` = 6) |
5. +------------------------------------------------------------------------------+
6. |                                                                            1 |
7. +------------------------------------------------------------------------------+

keywords

SEQUENCE_MATCH