Фукнкция lag_event возвращает значение из строки с указанным сдвигом, которое предшествует текущему значению строки.
Данные автоматически разделены (partition by) по экземплярам процесса. Строки заранее отсортированы по дате начала, потом по признаку сортировки (sorting) и потом по дате окончания на основании указанного маппинга в модели данных.
Значения NULL учитываются при работе функции.
lag_event( таблица.колонка [, сдвиг] )
Запрос:
select
case_id,
activity,
lag_event(event_log.activity) as "Предыдущий шаг"
from event_log
| case_id | activity |
|---|---|
| 1 | register request |
| 1 | examine thoroughly |
| 1 | check ticket |
| 1 | decide |
| 1 | reject request |
| 2 | register request |
| 2 | examine casually |
| 2 | check ticket |
| 2 | decide |
| 2 | reinitiate request |
| 2 | examine thoroughly |
| 2 | check ticket |
| 2 | pay compensation |
| case_id | activity | Предыдущий шаг |
|---|---|---|
| 1 | register request | |
| 1 | examine thoroughly | register request |
| 1 | check ticket | examine thoroughly |
| 1 | decide | check ticket |
| 1 | reject request | decide |
| 2 | register request | |
| 2 | examine casually | register request |
| 2 | check ticket | examine casually |
| 2 | decide | check ticket |
| 2 | reinitiate request | decide |
| 2 | examine thoroughly | reinitiate request |
| 2 | check ticket | examine thoroughly |
| 2 | pay compensation | check ticket |
Запрос:
select
case_id,
activity,
lag_event(event_log.activity, 2) as "Предыдущий шаг"
from event_log
from event_log
| case_id | activity |
|---|---|
| 1 | register request |
| 1 | examine thoroughly |
| 1 | check ticket |
| 1 | decide |
| 1 | reject request |
| 2 | register request |
| 2 | examine casually |
| 2 | check ticket |
| 2 | decide |
| 2 | reinitiate request |
| 2 | examine thoroughly |
| 2 | check ticket |
| 2 | pay compensation |
| case_id | activity | Предыдущий шаг |
|---|---|---|
| 1 | register request | |
| 1 | examine thoroughly | |
| 1 | check ticket | register request |
| 1 | decide | examine thoroughly |
| 1 | reject request | check ticket |
| 2 | register request | |
| 2 | examine casually | |
| 2 | check ticket | register request |
| 2 | decide | examine casually |
| 2 | reinitiate request | check ticket |
| 2 | examine thoroughly | decide |
| 2 | check ticket | reinitiate request |
| 2 | pay compensation | examine thoroughly |
Запрос:
select
activity as "Событие",
lag_event(event_log.activity) as "Предыдущее событие",
count(*) as "Кол-во переходов"
from event_log
group by activity, lag_event(event_log.activity)
order by count(*) desc
| case_id | activity |
|---|---|
| 1 | register request |
| 1 | check ticket |
| 1 | examine casually |
| 1 | decide |
| 1 | reinitiate request |
| 1 | check ticket |
| 1 | examine casually |
| 1 | decide |
| 1 | reinitiate request |
| 1 | examine thoroughly |
| 1 | check ticket |
| 1 | decide |
| 1 | reject request |
| Событие | Предыдущий событие | Кол-во переходов |
|---|---|---|
| reinitiate request | decide | 2 |
| examine casually | check ticket | 2 |
| decide | examine casually | 2 |
| reject request | decide | 1 |
| register request | 1 | |
| examine thoroughly | reinitiate request | 1 |
| decide | check ticket | 1 |
| check ticket | reinitiate request | 1 |
| check ticket | register request | 1 |
| check ticket | examine thoroughly | 1 |
Запрос:
select
user as "Исполнитель",
lag_event(event_log.user) as "Предыдущий исполнитель",
count(*) as "Кол-во переходов"
from event_log
group by user, lag_event(event_log.user)
order by count(*) desc
| case_id | user |
|---|---|
| 1 | Ellen |
| 1 | Pete |
| 1 | Mike |
| 1 | Sara |
| 1 | Sara |
| 1 | Ellen |
| 1 | Mike |
| 1 | Sara |
| 1 | Sara |
| 1 | Sean |
| 1 | Pete |
| 1 | Sara |
| 1 | Mike |
| Исполнитель | Предыдущий исполнитель | Кол-во переходов |
|---|---|---|
| Sara | Sara | 2 |
| Sara | Mike | 2 |
| Sean | Sara | 1 |
| Sara | Pete | 1 |
| Pete | Sean | 1 |
| Pete | Ellen | 1 |
| Mike | Sara | 1 |
| Mike | Pete | 1 |
| Mike | Ellen | 1 |
| Ellen | Sara | 1 |
| Ellen | 1 |
Запрос:
select
activity as "Событие",
lag_event(event_log.activity) as "Предыдущее событие",
avg(event_log.start_timestamp - lag_event(event_log.end_timestamp)) as "Ср. время между событиями"
from event_log
group by activity, lag_event(event_log.activity)
Или также можно взять поле interval_fact уже заранее рассчитанное системой с данной характеристикой. Оно считается автоматически в каждой из моделей.
select
activity as "Событие",
lag_event(event_log.activity) as "Предыдущее событие",
avg(interval_fact) as "Ср. интервал"
from event_log
group by activity, lag_event(event_log.activity)
Если объединить запросы, то будет видно, что данные одинаковые.
select
activity as "Событие",
lag_event(event_log.activity) as "Предыдущее событие",
avg(event_log.start_timestamp - lag_event(event_log.end_timestamp)) as "Ср. время между событиями",
avg(interval_fact) as "Ср. интервал"
from event_log
group by activity, lag_event(event_log.activity)
| case_id | activity | start_timestamp | end_timestamp |
|---|---|---|---|
| 1 | register request | 2010-12-30 11:32:00 | 2010-12-30 11:42:00 |
| 1 | check ticket | 2010-12-30 12:12:00 | 2010-12-30 12:55:00 |
| 1 | examine casually | 2010-12-30 14:16:00 | 2010-12-30 15:16:00 |
| 1 | decide | 2011-01-05 11:22:00 | 2011-01-05 11:32:00 |
| 1 | pay compensation | 2011-01-08 12:05:00 | 2011-02-08 12:05:00 |
| Событие | Предыдущее событие | Ср. время между событиями | Ср. интервал |
|---|---|---|---|
| register request | |||
| check ticket | register request | 1800 | 1800 |
| examine casually | check ticket | 4860 | 4860 |
| decide | examine casually | 504360 | 504360 |
| pay compensation | decide | 261180 | 261180 |