SQL: Аналитические функции

Аналитические функции на примере Oracle, функции LAG.
Прочитав этот материал вы поймете, как работают аналитические функции в Oracle. Я рассмотрю только одну функцию, но принцип действия у них один.

Постановка задачи

Будем рассматривать на простом примере:
Есть таблица APP_STAT, которая содержит данные о совершаемой операции. Каждая строка содержит время, описание операции и имя пользователя, который эту операцию выполнил. Своеобразный log.
Нам нужно узнать, сколько пользователь "думает" между кликами и вычислить среднее время, которое каждый пользователь тратит на "обдумывание".

Создадим таблицу:

create table "APP_STAT"
(
    "T" date not null,
    "PAGE" varchar2(50 char) not null,
    "USER_NAME" varchar2(50 char) not null
);

Соответственно, T - время, в которое выполнялась операция; PAGE - название страницы/операции, которую совершил пользователь; USER_NAME - имя пользователя.

Заполним ее данными:

alter session set NLS_DATE_FORMAT='DD-MM-YY HH24:MI:SS';
insert into APP_STAT (T, PAGE, USER_NAME)
select '22-06-08 10:30:00','login','bart' from dual union
select '22-06-08 10:31:00','search 1','bart' from dual union
select '22-06-08 10:31:20','search 1','bart' from dual union
select '22-06-08 10:31:20','login','homer' from dual union
select '22-06-08 10:31:30','search 1','bart' from dual union
select '22-06-08 10:32:00','new doc','bart' from dual union
select '22-06-08 10:32:10','list doc','homer' from dual union
select '22-06-08 10:32:20','view doc','homer' from dual union
select '22-06-08 10:33:40','list doc','homer' from dual union
select '22-06-08 10:34:00','view doc','homer' from dual union
select '22-06-08 10:36:20','save doc','bart' from dual union
select '22-06-08 10:36:30','delete doc','homer' from dual union
select '22-06-08 10:36:30','list doc','bart' from dual union
select '22-06-08 10:37:00','logout','homer' from dual union
select '22-06-08 10:37:00','view doc','bart' from dual union
select '22-06-08 10:37:10','edit doc','bart' from dual union
select '22-06-08 10:38:30','save doc','bart' from dual union
select '22-06-08 10:38:45','logout','bart' from dual;

Посмотрим, что мы сделали:

select * from APP_STAT
order by T asc

Решение

Итак, необходимо узнать, как долго в среднем пользователь задерживается на страницах. Не хотелось бы грузить клиентскую часть, тем более, цель этой статьи как раз таки научить избегать этого. Будем использовать аналитическую функцию LAG, для лучшего понимания рассмотрим все мысли по шагам.


Теперь пришла пора раскрыть магию. Как же все это работает:
Синтаксис функции LAG:

LAG (выражение, [смещение,] [значение-по-умолчанию]) over ([partition by раздел] выражение-сортировки)

Что же она возвращает? Все очень просто: значение [выражения] для строки, которая находится выше текущей на [смещение] строчек, как если бы все строки сортировались по [выражение-сортировки]. При этом весь набор строк разбивается на независимые наборы так, что значения в столбцах [раздел] для одного набора - одинаковые. Каждый набор обрабатывается отдельно.

sql [2]

select

to_char(T, 'HH24:MI:SS') T,
(T - lag(T, 1, null) over (order by T)) * (24 * 60 * 60) DIFF,
PAGE,
USER_NAME
from APP_STAT
where USER_NAME = 'bart'
order by T

В sql [2] мы использовали простой вариант без использования раздела (partition). На самом деле раздел есть, но он является полным результирующим набором (т.е. все строки из таблицы APP_STAT, где пользователь == 'bart').

Кроме того, мы использовали сортировку. Для чего она? Как Вы знаете, база данных вольна вернуть строки в любом порядке, а нам необходимо вычислять разницу между текущей операцией в логе и предыдущей. Этот порядок можно получить, отсортировав результат по времени (поле T). Для этого и нужна сортировка, чтобы база данных поняла, что такое "предыдущая строка".

Итак, в sql [2] функция LAG возвращает:

Значение поля T из предыдущей строки (смещение 1) или null (значение-по-умолчанию), если предыдущей строки не существует. При этом сортировка выполняется по полю T.

sql [3]

select

to_char(T, 'HH24:MI:SS') T,
(T - lag(T, 1, null) over (partition by USER_NAME order by T)) * (24 * 60 * 60) DIFF,
PAGE,
USER_NAME
from APP_STAT
order by T

В sql [3] мы использовали дополнительное выражение раздела (partition). Мотивация разделения на раздел с точки зрения логики тут такова: мы хотим понять, сколько думал отдельный пользователь, а не как часто на сервер приходил запрос. Для этого разности во времени нужно считать в контексте каждого отдельного пользователя. Как получить это? Ответ напрашивается сам с собой: нужно рассматривать выборку строк по каждому отдельному пользователю. Для этого и был придуман параметр [partition by раздел]. Мы знаем, что нам нужно разделить полученный набор так, чтобы каждая группа строк содержала упоминание только одного пользователя, затем мы хотим применить ту же операцию, что и в sql [2], то есть подсчитать разность во времени. Визуально это выглядит так:

Эти все операции производятся с помощью одного SQL:

select
T,
(T - lag(T, 1, null)
  over (partition by USER_NAME order by T))
  * (24 * 60 * 60) DIFF,
PAGE,
USER_NAME
from APP_STAT
order by T

Выражение 'partition by USER_NAME' как раз и выполнило разделение таблицы из Шага 0 так, что мы получили две таблицы из Шага 1. Далее данные обрабатывались отдельно как в Шаге 2, строки склеились и результат вернулся отсортированным по полю T так, как записано в основном запросе. В результате вы получим такую-же таблицу, как и в результате Шага 3.

Обратите внимание, что финальная сортировка не влияет на вычисление функции LAG, так как в качестве параметра ей также передаются правила, по которому нужно отсортировывать каждый набор. Другими словами, финальная сортировка выполняется после вычисления в аналитических функциях. Например, если мы хотим посмотреть, как вел себя каждый пользователь, мы можем поменять сортировку в основном запросе:

select
T,
(T - lag(T, 1, null)
  over (partition by USER_NAME order by T))
  * (24 * 60 * 60) DIFF,
PAGE,
USER_NAME
from APP_STAT
order by USER_NAME

Получим правильный результат:

Заключение

В заключении отмечу, что аналитических функций - немало и они предоставляют различные возможности. В этой статье не описывались так называемые "окна" (window), которые наряду с "разделом" используются для определения группы строк, над которым нужно производить вычисления.