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

匹配例子

  1. DROP TABLE IF EXISTS sequence_count_test1;
  3. CREATE TABLE sequence_count_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_count_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_count_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_count('(?1)(?2)', date, number = 1, number = 3) FROM sequence_count_test1;
  34. +----------------------------------------------------------------+
  35. | sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 3) |
  36. +----------------------------------------------------------------+
  37. |                                                              1 |
  38. +----------------------------------------------------------------+
  40. SELECT sequence_count('(?1)(?2)', date, number = 1, number = 2) FROM sequence_count_test1;
  42. +----------------------------------------------------------------+
  43. | sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 2) |
  44. +----------------------------------------------------------------+
  45. |                                                              2 |
  46. +----------------------------------------------------------------+
  48. SELECT sequence_count('(?1)(?t>=3600)(?2)', date, number = 1, number = 2) FROM sequence_count_test1;
  50. +---------------------------------------------------------------------------+
  51. | sequence_count('(?1)(?t>=3600)(?2)', `date`, `number` = 1, `number` = 2) |
  52. +---------------------------------------------------------------------------+
  53. |                                                                         2 |
  54. +---------------------------------------------------------------------------+

不匹配例子

  1. DROP TABLE IF EXISTS sequence_count_test2;
  3. CREATE TABLE sequence_count_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_count_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_count_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_count('(?1)(?2)', date, number = 1, number = 2) FROM sequence_count_test2;
  34. +----------------------------------------------------------------+
  35. | sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 2) |
  36. +----------------------------------------------------------------+
  37. |                                                              0 |
  38. +----------------------------------------------------------------+
  40. SELECT sequence_count('(?1)(?2).*', date, number = 1, number = 2) FROM sequence_count_test2;
  42. +------------------------------------------------------------------+
  43. | sequence_count('(?1)(?2).*', `date`, `number` = 1, `number` = 2) |
  44. +------------------------------------------------------------------+
  45. |                                                                0 |
  46. +------------------------------------------------------------------+
  48. SELECT sequence_count('(?1)(?t>3600)(?2)', date, number = 1, number = 7) FROM sequence_count_test2;
  50. +--------------------------------------------------------------------------+
  51. | sequence_count('(?1)(?t>3600)(?2)', `date`, `number` = 1, `number` = 7) |
  52. +--------------------------------------------------------------------------+
  53. |                                                                        0 |
  54. +--------------------------------------------------------------------------+

特殊例子

  1. DROP TABLE IF EXISTS sequence_count_test3;
  3. CREATE TABLE sequence_count_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_count_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_count_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_count('(?1)(?2)', date, number = 1, number = 5) FROM sequence_count_test3;
  3. +----------------------------------------------------------------+
  4. | sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 5) |
  5. +----------------------------------------------------------------+
  6. |                                                              1 |
  7. +----------------------------------------------------------------+

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

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

  1. SELECT sequence_count('(?1)(?2)', date, number = 1, number = 5, number = 4) FROM sequence_count_test3;
  3. +------------------------------------------------------------------------------+
  4. | sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 5, `number` = 4) |
  5. +------------------------------------------------------------------------------+
  6. |                                                                            0 |
  7. +------------------------------------------------------------------------------+

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

  1. SELECT sequence_count('(?1)(?2)', date, number = 1, number = 5, number = 6) FROM sequence_count_test3;
  3. +------------------------------------------------------------------------------+
  4. | sequence_count('(?1)(?2)', `date`, `number` = 1, `number` = 5, `number` = 6) |
  5. +------------------------------------------------------------------------------+
  6. |                                                                            1 |
  7. +------------------------------------------------------------------------------+

keywords

SEQUENCE_COUNT