MS SQL 2005: віконні функції
Іван Бодягін (Merle)
Введення
З огляду на те, що в наступній версії MS SQL Server,
вихід якої очікується в 2005 році, нововведень просто божевільна кількість,
слона доводиться їсти по частинах. Цей шматочок присвячений новій функціональності
званої «віконні функції» (Window Functions), також відомої широкої
громадськості під ім'ям «аналітичних», або OLAP-функцій.
Сама по собі ідея не нова. З подачі IBM, Oracle,
Informix і Compaq аналітичні функції були додані в ANSI SQL 99. В Oracle
підтримка подібної функціональності, зі своїми специфічними розширеннями,
з'явилася з версії 8i R2, в DB2 - з версії 7.1, та й наявні в T-SQL на
даний момент функції CUBE і ROLUP також можна віднести до аналітичних.
З одного боку, Microsoft і так поставляє разом c
SQL Server досить потужний механізм для аналізу даних - Analysis Services.
Частково з цієї причини Microsoft не поспішала підтримувати ініціативу введення деяких
OLAP-функцій в стандарт SQL.
Але з іншого боку, при необхідності деякого
аналізу даних не завжди є можливість і бажання піднімати ще один сервіс.
До того ж, строго кажучи, аналітичні функції не зовсім правильно відносити до
OLAP, вони не призначені для багатомірного аналізу даних, роботи з
гіперкуба і складними ієрархіями, ці функції здійснюють лише статистичний
аналіз даних у готовій вибіркою ... Тим не менш, це досить потужний і
зручний інструмент, і тому, врешті-решт, Microsoft також не залишилася в
стороні.
Спочатку кілька слів про те, що ж з себе
представляють і як працюють аналітичні функції в загальному вигляді. Одне з головних
відмінностей аналітичних функцій від звичайних, скалярних, полягає в тому, що
цей клас операторів працює з готовою вибіркою. Спочатку формується вибірка,
виконуються всі об'єднання, умови WHERE, GROUP BY, HAVING - все, крім
сортування, і тільки потім до практично готового набору даних застосовується
аналітична функція. Саме тому аналітичні функції можна вказувати
лише у списку вибірки або в умови сортування.
У загальному випадку принцип роботи аналітичних функцій
можна представити приблизно так. Припустимо, що у нас є
результуючий набір даних, отриманий вищеописаним способом - виконано все,
крім сортування. На кожен запис в цьому наборі накладається так зване
«Вікно», розміри і положення якого визначаються відповідно до деякого
аналітичним умовою (власне звідси і назва цього класу функцій --
«Віконні функції», window functions). У це віртуальне «вікно» потрапляють
кілька інших записів з того ж набору, то є ціла група записів. При
це «вікно» може бути сформована таким чином, що в нього потраплять зовсім не
сусідні запису, а практично довільні запису з набору, і навіть сама
поточний запис, на підставі якої формується «вікно», може в це «вікно», не
потрапити (надалі термін «вікно» буде використовуватися для позначення саме
такого набору записів). Коли «вікно» сформовано, аналітична функція
обчислює агреговане значення за записами, що входять до «вікно», і переходить до
наступного запису. Для цього запису формується нове «вікно», знову обчислюється
агреговане значення - і так для всіх записів у вибірці. При цьому розмір і
положення «вікна» від запису до запису може змінюватися, в таких випадках використовують
термін «ковзне вікно» (sliding window).
Спектр застосування аналітичних функцій достатньо
широкий - це різного роду розподілу (рангове (ranking), кумулятивний і
т.д.), процентірованіе (percentile), лінійна регресія, стандартні
відхилення, дисперсії, наростаючі підсумки, а також інша нижча математика і
вища бухгалтерія. :)
Строго кажучи, практично все, що можна отримати з
допомогою аналітичних функцій, можна отримати і без них, за допомогою звичайного
T-SQL, але це буде свідомо більш громіздким і часто не так ефективно.
Поточна реалізація
На даний момент в MS SQL Server 2005 реалізовано два
типу аналітичних функцій - агрегатні та функції ранжирування.
Агрегатні функції
Оскільки аналітична функція повертає
агрегований результат обробки записів, що потрапили в «вікно», то самі звичайні
агрегатні функції тепер мають можливість виступити в якості аналітичних.
Відмінність полягає в тому, що «звичайні» агрегати зменшують ступінь деталізації
результуючого набору, а в аналітичному варіанті ступінь деталізації не
зменшується. Це відноситься не тільки до агрегатів, але і до інших типів
аналітичних функцій. Розглянемо на прикладі.
Припустимо, у нас є таблиця з операціями клієнтів,
ID містить транзакції, ID клієнта і суму операції, всього 15 записів ...
CREATE TABLE sample (
ID_Trans int IDENTITY (1,1)
PRIMARY KEY,
ID_Customer int NOT NULL,
Amount int NOT NULL)
GO
INSERT INTO sample (ID_Customer, Amount)
SELECT 1, 100
union all SELECT 2, 100
union all SELECT 3, 100
union all SELECT 1, 110
union all SELECT 1, 120
union all SELECT 2, 200
union all SELECT 2, 220
union all SELECT 3, 300
union all SELECT 3, 330
union all SELECT 3, -100
union all SELECT 2, 400
union all SELECT 1, 101
union all SELECT 2, 202
union all SELECT 1, 100
union all SELECT 2, 200
Порівняємо результат виконання двох запитів. В одному
SUM виступає в якості звичайного агрегату:
SELECT ID_Customer, sum (Amount) FROM sample GROUP BY ID_Customer
--- Результат цілком
передбачуваний:
1 531
2 1322
3 630
А в іншому вже в якості аналітичної функції:
SELECT ID_Trans, ID_Customer,
sum (Amount) OVER (PARTITION
BY ID_Customer)
FROM sample
--- А тут одержимо
наступне:
4 1 531
5 1 531
1 1 531
12 1 531
14 1 531
15 2 1322
11 2 1322
13 2 1322
2 2 1322
6 2 1322
7 2 1322
8 3 630
9 3 630
10 3 630
3 3 630
Під час перегляду результатів другого запиту можна
помітити, що сервер не став лаятися на зазначення колонки ID_Trans у вибірці,
незважаючи на відсутність агрегує функції або угруповання по цій колонці.
Для «звичайних» агрегатів хоча б одне з цих умов обов'язково має
дотримуватися, оскільки в іншому випадку виникне неоднозначність-Но на
аналітичні агрегати вищеописане обмеження не поширюється, оскільки
ступінь деталізації не зменшується і, як наслідок, не виникає
неоднозначності. Що й можна спостерігати на прикладі другий запиту - результат
агрегує функції просто продублювали для кожного запису всередині групи,
оскільки результат агрегату для кожного запису всередині «вікна» збігається.
Саме час розібратися з синтаксисом - він досить
простий. Після функції вказується конструкція
OVER
([PARTITION BY , ... [n ]])
де - список полів, по
якими виробляється угруповання, при цьому використання аліасів або виразів
не допускається. Власне, таким чином і формується «вікно» для роботи
аналітичної функції. У «вікно» потрапляють всі записи, що згруповано за
зазначеної колонці. Ця група робить практично те ж саме, що й
оператор GROUP BY, але з парою відмінностей. По-перше, як уже говорилося, така
угруповання проводиться по вже сформованій вибірці, а по-друге, вона поширюється
тільки на той агрегат, після якого йде конструкція OVER (...), а не на всі
колонки. І якщо є необхідність використовувати дві аналітичні функції в
одному запиті, то для кожної функції конструкція OVER (...) вказується окремо.
Строго кажучи, результат запиту з аналітичної
сумою повністю аналогічний результату такого запиту, написаного в «старому
стилі »:
SELECT s.ID_Trans, s.ID_Customer, t.sum_amount
FROM sample s
INNER JOIN
(SELECT sum (Amount)
sum_amount, ID_Customer
FROM sample
GROUP BY ID_Customer
) t
ON s.ID_Customer =
t.ID_Customer
Більш того, плани обох запитів також абсолютно
ідентичні. Але, по-перше, запис із застосуванням аналітичних агрегатів виглядає
коротше і зрозуміліше, а по друге, не варто забувати, що ми маємо справу все ще не
з фінальною версією продукту і, можливо, до релізу сервер навчиться їх
оптимізувати.
В якості аналітичних функцій можуть також виступати
і власноруч написані агрегати.
Опції ранжирування
Крім звичайних агрегатів, для аналітичних запитів
вводяться функції ранжирування. Ці функції повертають ранг кожного запису всередині
«Вікна». У загальному випадку рангом є якесь число відображає положення або
«Вагу» записи щодо інших записів у тому ж наборі. Формується «вікно»
точно так само, як і у випадку агрегатних функцій - за допомогою угруповання.
Проте, оскільки результат роботи функцій ранжування залежить від порядку
обробки записів, то обов'язково повинен бути зазначений порядок записів всередині
«Вікна» за допомогою конструкції ORDER BY. Залежно від використовуваної функції
деякі записи можуть отримувати один і той же ранг. Опції ранжирування
є не детермінованими, тобто при одних і тих самих вхідних значеннях
вони можуть повертати різний результат.
На даний момент имееет 4 функції ранжирування,
розглянемо їх по порядку:
ROW_NUMBER ()
Збулася блакитна мрія спраглих нумерації записів на
сервер. :) Тепер така можливість з'явилася, однак це не основна
призначення даної функції ... Все-таки вона покликана нумерувати записи в зазначеному
порядку всередині «вікна». Але якщо в конструкції OVER опустити секцію PARTITION BY,
то за «вікно» буде прийнята вся вибірка - що дає можливість пронумерувати всі
записи в належному порядку, причому порядок нумерації може не збігатися з
порядком записів у результуючої вибіркою, тобто оператор ORDER BY всередині
OVER (...), який визначає порядок сортування записів всередині «вікна», і,
відповідно, порядок нумерації записів може не совпадасть з оператором
ORDER BY в конструкції SELECT, що визначає порядок видачі записів клієнта.
Нумерація завжди починається з одиниці.
RANK ()
Ця функція призначена для ранжирування записів
усередині «вікна», але знову-таки, якщо колонка для угруповання не задана явним
чином, то за «вікно» приймається вся вибірка. Рангом кожного запису є
кількість вже ранжируваних записів з більш високим рангом, ніж поточна, плюс
одиниця. Якщо зустрінуться кілька записів з однаковим значенням, за яким
проводиться ранжування, то цим записам буде присвоєно однаковий ранг.
Однак при цьому наступний запис з новим значенням отримає такий ранг, як
ніби-то попередні записи отримали свій унікальний номер, тобто утворюється
дірка.
Звучить заплутано ... :) Проте якщо по-простому, то це
та ж нумерація, що і в ROW_NUMBER (), яка починається з тієї ж одиниці.
Різниця в тому, що однакові записи отримують однаковий номер, а наступна
що відрізняється від них запис отримує такий номер, як якби ROW_NUMBER () і
використовувалася, і всі попередні записи отримали свої унікальні номери. Таким
чином, утворюється дірка в нумерації, що дорівнює кількості однакових записів
мінус одиниця.
DENSE_RANK ()
Ця функція виконує «щільне» ранжування, тобто
робить рівно те ж саме, що і попередня, але без «дірок» у нумерації.
NTILE ()
Ця функція дозволяє розділити запису всередині
«Вікна» на вказану кількість груп. Для кожного запису вона поверне номер
групи, до якої належить даний запис. Нумерація груп також починається з
одиниці. Якщо кількість записів у «вікні» не ділиться на кількість груп, то
вийде два типи груп з різною кількістю записів, що відрізняється на одиницю,
при цьому спочатку будуть виведені групи з великою кількістю записів, а потім --
з меншим.
Для демонстрації відмінностей функцій ранжирування можна виконати
наступний запит:
SELECT ID_Customer, Amount,
ROW_NUMBER () OVER (PARTITION BY
ID_Customer ORDER BY Amount DESC) N_Row,
RANK () OVER (PARTITION BY
ID_Customer ORDER BY Amount DESC) RANK,
DENSE_RANK () OVER (PARTITION BY
ID_Customer ORDER BY Amount DESC) DENSE_RANK,
NTILE (2) OVER (PARTITION BY
ID_Customer ORDER BY Amount DESC) NTILE
- виведемо тільки одну групу для економії місця
FROM sample WHERE ID_Customer = 2
ID_Cust Amnt N_Row RANK
D_RANK NTILE
----------------------------------------------- --------
2 400
1 1 1 1
2 220
2 2 2 1
2 202
3 3 3 2
2 200
4 4 4 2
2 200
5 4 4 3
2 100
6 6 5 4
Деякі приклади використання
Як вже говорилося, практично все, що можна зробити
за допомогою аналітичних функцій, можна зробити і без них, але з їх
використанням необхідного ефекту можна досягти простіше і, найчастіше, оптимальніше ...
Оскільки тепер з'явилася можливість нумерувати
запису у вибірці, можна скористатися цим для посторінкового видачі
результату. Запит буде виглядати приблизно так:
WITH Numbered
(
SELECT ROW_NUMBER () OVER (ORDER
BY name) N_Row, *
FROM sysobjects
)
SELECT * FROM Numbered WHERE N_Row between @ First AND @ Last
Як не дивно, цей запит буде виконуватися приблизно
в два рази швидше класичного:
EXECUTE ( 'SELECT * FROM
(SELECT TOP '+ @ Count +'
* FROM
(SELECT TOP '+ @ Last +'
*
FROM sysobjects ORDER BY
name ASC
) SO1
ORDER BY name DESC) SO2
ORDER BY name ')
Так що збулася ще одна мрія, про ефективну і
простий посторінкового вибіркою .. :)
Ще один приклад, де використання аналітичних функцій
може бути і зручним, і ефективним. Нерідко потрібно вивести, наприклад, два
найбільших замовлення для кожного клієнта. Може статися так, що замовлень з
максимальною сумою виявиться більше двох. Для випадку, коли замовлень повинно бути
саме два, запит може виглядати так:
WITH Ranked as
(
SELECT *,
Row_Number () OVER (PARTITION
BY ID_Customer
ORDER BY amount DESC)
[rank]
FROM sample
)
SELECT * FROM Ranked
WHERE [rank] <3
Такий запит на цих даних приблизно в 10 разів
ефективніше, ніж цей самий запит, виконаний в «старому стилі»:
SELECT *
FROM sample s1
WHERE ID_Trans in
(
SELECT top 2 ID_Trans
FROM sample s2
WHERE s1.ID_Customer =
s2.ID_Customer
ORDER BY amount DESC
)
Більше того, різниця у швидкості буде відчутно зростатиме з
збільшенням кількості даних в таблиці, оскільки в першому випадку алгоритм
досить простий - внутрішнім запитом нумеруються запису всередині груп,
практично за одну сортування, а потім фільтром в зовнішньому запиті відсікаються
всі зайві записи. У другому ж випадку, внутрішній підзапит виконується
заново, для кожного запису в таблиці. Все це дуже добре видно на планах
запитів. На другому плані кількість очікуваних виконань підзапит --
п'ятнадцять, тому що в тестовій табличці 15 записів.
План запиту з аналітичною функцією:
Операція Вартість Кількість
----------------------------------------------- -----------
| - Filter (WHERE: ([Expr1003] <(3))) 0.022873 1
| - Sequence
Project (...) 0.022866 1
| - Segment 0.022866 1
| - Segment 0.022866 1
| - Sort (ORDER
BY :(...)) 0.022864 1
| - Clustered Index Scan (...) 0.006423 1
План запиту без використання аналітичної функції:
Операція Вартість Кількість
----------------------------------------------- ------------
| - Nested Loops (Left Semi Join ...) 0.18998 1
| - Clustered Index
Scan (...) 0.00642 1
| - Filter (WHERE: (...)) 0.18350 15
| - Top (TOP
EXPRESSION: ((2))) 0.18348 15
| - Filter (WHERE: (...)) 0.18348 15
| - Sort (ORDER ([Amount]
DESC)) 0.18343 15
| - Clustered Index
Scan (...) 0.00665 15
Ложка дьогтю
Все це, звичайно, добре і чудово, але є
деякі негативні моменти, які зменшують радість від отримання нового
інструменту. Він, звичайно, хороший, але поки що ще дуже бідний і не розвинений. Чи не
вважаючи вбудованих агрегуються функцій, в SQL 2005 реалізовано всього 4
ранжирують функції, в той час як у ANSI SQL 2003 більше 30 різних типів аналітичних
функцій ...
Прикро й інше .. Як можна помітити, в синтаксисі для
аналітичних агрегатів відсутня можливість вказати сортування всередині
«Вікна». Для звичайних, вбудованих агрегатів це не має ніякого значення, але в
SQL Server 2005 з'явиться можливість писати свої власні агрегати на
CLR-сумісних мовами, якими, за бажання, можна було б розширити список
функцій, і ось для цих саморобних агрегуються функцій подібна можливість
могла б бути дуже корисною. Без можливості вказати порядок сортування
записів у «вікні» неможливо використовувати цілий клас агрегуються функцій,
залежних від порядку обробки даних. У принципі, ніщо не заважає в процесі
роботи власної агрегує функції складати дані в деяку колекцію,
сортувати їх там належним чином, а потім обрабативатать у потрібному порядку,
але, очевидно, це не ідеальне рішення, так як?? ріходітся виконувати роботу
сервера.
Найцікавіше, що при написанні користувача
агрегує функції можна вказати за допомогою спеціального атрибуту, чи залежить
результат від порядку обробки записів, але за відсутності можливості вказати
цей самий порядок обробки, даний атрибут даремний. Очевидно, ті хлопці,
які писали підтримку користувацьких агрегуються функцій, передбачили
можливість створення функцій, які залежать від порядку обробки даних, а ось у
тих, хто писав аналітичні функції, руки поки не дійшли. Дуже хочеться вірити,
що до релізу дійдуть ...
Так само навіває сумні думки дуже бідний механізм
вказівки «вікна» для аналітичної функції. На даний момент є тільки одна
спосіб задати це «вікно» - угруповання. Тобто, «вікно» можна встановити тільки з
допомогою вказівки колонки, однакові значення записів у якої є
ознакою приналежності до «вікна». Проте можливості вказівки «вікна» можуть
бути набагато ширше, але в поточній версії вся ця розкіш поки що
відсутня.
Список літератури
Для підготовки даної роботи були використані
матеріали з сайту http://www.rsdn.ru/
