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














































































T PAGE USER_NAME
22-06-08 10:30:00 login bart
22-06-08 10:31:00 search 1 bart
22-06-08 10:31:20 login homer
22-06-08 10:31:20 search 1 bart
22-06-08 10:31:30 search 1 bart
22-06-08 10:32:00 new doc bart
22-06-08 10:32:10 list doc homer
22-06-08 10:32:20 view doc homer
22-06-08 10:33:40 list doc homer
22-06-08 10:34:00 view doc homer
22-06-08 10:36:20 save doc bart
22-06-08 10:36:30 delete doc homer
22-06-08 10:36:30 list doc bart
22-06-08 10:37:00 logout homer
22-06-08 10:37:00 view doc bart
22-06-08 10:37:10 edit doc bart
22-06-08 10:38:30 save doc bart
22-06-08 10:38:45 logout bart

Рішення


Отже, необхідно дізнатися, як довго в середньому користувач затримується на сторінках. Не хотілося б вантажити клієнтську частину, тим більше, мета цієї статті як раз таки навчити уникати цього. Будемо використовувати аналітичну функцію LAG, для кращого розуміння розглянемо всі думки по кроках.

























Крок 0

Виберемо лог відвідувань для одного користувача “bart”
sql [1]
select
to_char(T, “HH24:MI:SS”) T,
PAGE,
USER_NAME
from APP_STAT
where USER_NAME = “bart”
order by T

Як бачимо, для того, щоб дізнатися кількість часу, який користувач проводить, наприклад, на сторінці “login”, потрібно з часу, який зазначено у другому рядку відняти час, який зазначено в першій:
22/06/2008 10:31:00 [Мінус] 22/06/2008 10:30:00 [Одно] 60 секунд
Маленьке зауваження для тих, хто не пам’ятає: в Oracle результат (date – date) – це кількість днів між датами. Число це може бути дробовим, так що легко дізнатися кількість хвилин або секунд, помноживши на відповідне число.
Першою моєю думкою зазвичай було завантажити ці дані на клієнта і обробити результат на Java (C #, C + +, [підставте сюди свій улюблений мова програмування]), але тепер я знаю, що існує така аналітична функція, як LAG


















































T PAGE USER_NAME
10:30:00 login bart
10:31:00 search 1 bart
10:31:20 search 1 bart
10:31:30 search 1 bart
10:32:00 new doc bart
10:36:20 save doc bart
10:36:30 list doc bart
10:37:00 view doc bart
10:37:10 edit doc bart
10:38:30 save doc bart
10:38:45 logout bart
Крок 1

Виберемо дані для користувача “bart” так, щоб побачити різницю в часі між поточною операцією в рядку логу і попередньої
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

Як бачите, в колонці DIFF виводиться різниця між часом в колонці T для поточного рядка і попередньої. Не вдаючись в подробиці, підемо далі, пояснення будуть пізніше.






























































T DIFF PAGE USER_NAME
10:30:00
login bart
10:31:00 60 search 1 bart
10:31:20 20 search 1 bart
10:31:30 10 search 1 bart
10:32:00 30 new doc bart
10:36:20 260 save doc bart
10:36:30 10 list doc bart
10:37:00 30 view doc bart
10:37:10 10 edit doc bart
10:38:30 80 save doc bart
10:38:45 15 logout bart
Крок 2

Ми вибрали за допомогою sql [2] список операцій для користувача і час, який минув між двома поспіль йдуть операціями. Але нас цікавлять всі користувачі (ми хочемо обчислити, скільки часу користувач витрачає між кліками). У другому кроці ми виберемо всі дані, не фільтруючи записи по користувачах.
Тут є певна проблема, так як у нас багато користувачів і коли “bart” думає, “homer” вже встиг щось зробити. Тобто послідовність дій і, відповідно, обчислення різниці, потрібно вести в контексті одного користувача. Якщо ми використовуємо sql [2] без фільтра, фактично ми отримаємо час, протягом якого приходили кліки, не важливо від кого. Нас це не влаштовує, тому пишемо так:
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

Тепер у нас є результуючий набір з часом в секундах, яке користувач думає між кліками. Обчислити середній час – дуже просто

































































































T DIFF PAGE USER_NAME
10:30:00
login bart
10:31:00 60 search 1 bart
10:31:20
login homer
10:31:20 20 search 1 bart
10:31:30 10 search 1 bart
10:32:00 30 new doc bart
10:32:10 50 list doc homer
10:32:20 10 view doc homer
10:33:40 80 list doc homer
10:34:00 20 view doc homer
10:36:20 260 save doc bart
10:36:30 10 list doc bart
10:36:30 150 delete doc homer
10:37:00 30 view doc bart
10:37:00 30 logout homer
10:37:10 10 edit doc bart
10:38:30 80 save doc bart
10:38:45 15 logout bart
Крок 3

Просто обчислюємо середнє значення:
sql [4]
select avg(DIFF) from (
    select
    (T – lag(T, 1, null)
        over (partition by USER_NAME order by T)) * (24 * 60 * 60) DIFF
    from APP_STAT
)

Зверніть увагу: аналітичні функції не можна використовувати в агрегації, так що ми використовували підзапит.
Отримали: 54.0625

Тепер прийшла пора розкрити магію. Як же все це працює:
Синтаксис функції 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], Тобто підрахувати різницю в часі. Візуально це виглядає так:



























Крок 0

Це все дані з таблиці логу
select
to_char(T, “HH24:MI:SS”) T,
PAGE,
USER_NAME USR
from APP_STAT
order by T asc















































































T PAGE USER
10:30:00 login bart
10:31:00 search 1 bart
10:31:20 login homer
10:31:20 search 1 bart
10:31:30 search 1 bart
10:32:00 new doc bart
10:32:10 list doc homer
10:32:20 view doc homer
10:33:40 list doc homer
10:34:00 view doc homer
10:36:20 save doc bart
10:36:30 delete doc homer
10:36:30 list doc bart
10:37:00 logout homer
10:37:00 view doc bart
10:37:10 edit doc bart
10:38:30 save doc bart
10:38:45 logout bart
Крок 1

Розділимо дані для кожного користувача
select
to_char(T, “HH24:MI:SS”) T,
PAGE,
USER_NAME USR
from APP_STAT
where USER_NAME = “bart”
order by T asc

select
to_char(T, “HH24:MI:SS”) T,
PAGE,
USER_NAME USR
from APP_STAT
where USER_NAME = “homer”
order by T asc

bart:


















































T PAGE USR
10:30:00 login bart
10:31:00 search 1 bart
10:31:20 search 1 bart
10:31:30 search 1 bart
10:32:00 new doc bart
10:36:20 save doc bart
10:36:30 list doc bart
10:37:00 view doc bart
10:37:10 edit doc bart
10:38:30 save doc bart
10:38:45 logout bart

homer:


































T PAGE USR
10:31:20 login homer
10:32:10 list doc homer
10:32:20 view doc homer
10:33:40 list doc homer
10:34:00 view doc homer
10:36:30 delete doc homer
10:37:00 logout homer
Крок 2

Підрахуємо час, який користувач витрачав на обдумування “між кліками”

Ви вже знаєте, що це можна зробити за допомогою функції LAG:


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

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 = “homer”
order by T

bart:






























































T DIFF PAGE USER_NAME
10:30:00
login bart
10:31:00 60 search 1 bart
10:31:20 20 search 1 bart
10:31:30 10 search 1 bart
10:32:00 30 new doc bart
10:36:20 260 save doc bart
10:36:30 10 list doc bart
10:37:00 30 view doc bart
10:37:10 10 edit doc bart
10:38:30 80 save doc bart
10:38:45 15 logout bart

homer:










































T DIFF PAGE USER_NAME
10:31:20
login homer
10:32:10 50 list doc homer
10:32:20 10 view doc homer
10:33:40 80 list doc homer
10:34:00 20 view doc homer
10:36:30 150 delete doc homer
10:37:00 30 logout homer
Крок 3
Ну, а тепер зіллємо результати в одну таблицю
Як це зробити за допомогою LAG в одному запиті – нижче.


































































































T DIFF PAGE USER_NAME
10:30:00
login bart
10:31:00 60 search 1 bart
10:31:20
login homer
10:31:20 20 search 1 bart
10:31:30 10 search 1 bart
10:32:00 30 new doc bart
10:32:10 50 list doc homer
10:32:20 10 view doc homer
10:33:40 80 list doc homer
10:34:00 20 view doc homer
10:36:20 260 save doc bart
10:36:30 10 list doc bart
10:36:30 150 delete doc homer
10:37:00 30 view doc bart
10:37:00 30 logout homer
10:37:10 10 edit doc bart
10:38:30 80 save doc bart
10:38:45 15 logout bart

Ці всі операції проводяться за допомогою одного 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

Отримаємо правильний результат:
































































































T DIFF PAGE USER_NAME
10:30:00
login bart
10:31:00 60 search 1 bart
10:31:20 20 search 1 bart
10:31:30 10 search 1 bart
10:32:00 30 new doc bart
10:36:20 260 save doc bart
10:36:30 10 list doc bart
10:37:00 30 view doc bart
10:37:10 10 edit doc bart
10:38:30 80 save doc bart
10:38:45 15 logout bart
10:31:20
login homer
10:32:10 50 list doc homer
10:32:20 10 view doc homer
10:33:40 80 list doc homer
10:34:00 20 view doc homer
10:36:30 150 delete doc homer
10:37:00 30 logout homer

Висновок

У висновку зазначу, що аналітичних функцій – чимало і вони надають різні можливості. У цій статті не описувалися так звані “вікна” (window), які поряд з “розділом” використовуються для визначення групи рядків, над яким потрібно проводити обчислення.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*