Перейти к содержимому
Последнее обновление

SEQUENCE_COUNT

Описание​

Подсчитывает количество цепочек событий, соответствующих шаблону. Функция ищет непересекающиеся цепочки событий. Поиск следующей цепочки начинается после нахождения соответствия для текущей цепочки.

ВНИМАНИЕ!

События, произошедшие в одну и ту же секунду, могут располагаться в последовательности в неопределённом порядке, что влияет на результат.

Синтаксис​

SEQUENCE_COUNT(<pattern>, <timestamp>, <cond_1> [, <cond_2>, ..., <cond_n>]);

Параметры​

ПараметрОписание
<pattern>Строка шаблона, см. Синтаксис шаблона ниже. Поддерживает тип String.
<timestamp>Столбец, содержащий данные о времени. Поддерживает типы Date, DateTime, TimestampNs.
<cond_n>Условия, описывающие цепочку событий. Поддерживает тип Bool. Можно передать до 32 аргументов условий. Функция учитывает только события, описанные в этих условиях. Если последовательность содержит данные, не описанные ни в одном условии, функция пропускает их.

Синтаксис шаблона

  • (?N) — соответствует аргументу условия в позиции N. Условия нумеруются в диапазоне [1, 32]. Например, (?1) соответствует аргументу, переданному в параметр cond_1.

  • .* — соответствует любому количеству событий. Для подсчёта этого элемента шаблона аргументы условий не требуются.

  • (?t operator value) — задаёт интервал в секундах, который должен разделять два события.

  • Обозначим через t разность в секундах между двумя моментами времени. Например, шаблон (?1)(?t>1800)(?2) соответствует событиям, между которыми прошло более 1800 секунд. Шаблон (?1)(?t>10000)(?2) соответствует событиям, между которыми прошло более 10000 секунд. Между этими событиями может находиться произвольное количество любых событий. Можно использовать операторы >=, >, <, <=, ==.

Возвращаемое значение​

Количество непересекающихся цепочек событий, соответствующих шаблону. Если в группе нет допустимых данных, возвращает 0.

Примеры​

Примеры совпадений

-- Create sample table
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 AUTO
PROPERTIES ( 
    "replication_num" = "1"
); 

-- Insert sample data
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); 

-- Query example
SELECT
    SEQUENCE_COUNT('(?1)(?2)', date, number = 1, number = 3) as c1,
    SEQUENCE_COUNT('(?1)(?2)', date, number = 1, number = 2) as c2,
    SEQUENCE_COUNT('(?1)(?t>=3600)(?2)', date, number = 1, number = 2) as c3
FROM sequence_count_test1;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 |    2 |    2 |
+------+------+------+

Примеры без совпадений

-- Create sample table
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 AUTO
PROPERTIES ( 
    "replication_num" = "1"
); 

-- Insert sample data
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);

-- Query example
SELECT
    SEQUENCE_COUNT('(?1)(?2)', date, number = 1, number = 2) as c1,
    SEQUENCE_COUNT('(?1)(?2).*', date, number = 1, number = 2) as c2,
    SEQUENCE_COUNT('(?1)(?t>3600)(?2)', date, number = 1, number = 7) as c3
FROM sequence_count_test2;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    0 |    0 |    0 |
+------+------+------+

Особые случаи

-- Create sample table
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 AUTO
PROPERTIES ( 
    "replication_num" = "1"
); 

-- Insert sample data
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);

-- Query example
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 между ними, поскольку они не описаны как события. Чтобы при поиске цепочки событий из примера учитывать такое значение, для него нужно задать условие.

Теперь выполните этот запрос:

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, функция засчитала бы совпадение с шаблоном.

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 |
+------------------------------------------------------------------------------+
Report issue