T-SQL, Сортування ієрархій
Уявіть, що вам потрібно надати інформацію певної ієрархії у відсортованому вигляді. Потрібно розмістити батьківські елементи перед нащадками. Також необхідний контроль за порядком розміщення елементів, що є одному рівні. Для створення тестових даних скористаємося наступним кодом, який створює та наповнює даними таблицю на ім'я dbo.Employees (не потрібно її плутати з існуючою таблицею HR.Employees, де зберігаються інші дані):
Припустимо, що співробітників треба представити в ієрархічному порядку - менеджер перед підлеглими, а також розмістити співробітників у порядку empname. Для вирішення цього завдання можна використовувати два інструменти: функцію ROW_NUMBER та рекурсивний CTE-вираз. Спочатку визначаємо звичайне CTE на ім'я EmpsRN, де обчислюється атрибут на ім'я n, що представляє номер рядка з секціюванням по mgrid і впорядкуванням по empname і empid (empid додається за необхідністю для детермінізму):
Потім визначається рекурсивне CTE на ім'я EmpsPath, яке ітеративно перераховує співробітників за одним рівнем за раз, починаючи з кореня (виконавчого директора) і далі за ієрархічною структурою організації. Можна побудувати бінарний шлях для кожного співробітника, який починається з порожнього шляху докорінно і на кожному рівні підпорядкування конкатенується шлях керівника з бінарною формою n (номером рядка). Зверніть увагу, що для мінімізації розміру шляху потрібно стільки байт, щоб охопити максимальну кількість прямих підлеглих, які є в одного менеджера. Наприклад, якщо число прямих підлеглих не перевищує 255, достатньо одного байти, два байти підтримують до 32767 прямих підлеглих і т.д. Припустимо, у нашому випадку потрібно два байти. Можна також обчислити рівень співробітника вдереві (відстань від кореня), призначивши кореню значення 0 і кожному рівні додаючи 1. Ось код, який обчислює як шлях сортування, і рівень:
Потрібно ще забезпечити, щоб співробітники відображалися в правильному порядку, для чого потрібно виконати впорядкування sortpath. Можна також організувати відступи, що відображають рівень співробітника в ієрархії, реплікуючи рядок n разів. Ось код закінченого рішення:
Подивіться, як у результаті менеджер завжди розташовується перед підлеглими, а співробітники на одному рівні впорядковуються за empname:
Якщо на одному рівні потрібен інший порядок, припустимо заробітну плату, просто змініть відповідним чином пропозицію впорядкування вікна у функції ROW_NUMBER:
Ось результат виконання цього запиту:
Мене не перестає захоплювати краса віконних функцій. Їх розробили для того, щоб усунути ряд недоліків традиційніших конструкцій SQL, і вони добре піддаються оптимізації. У цьому посібнику ви мали можливість неодноразово переконатися в тому, що за допомогою віконних функцій можна елегантно та ефективно вирішити величезну кількість завдань. Я сподіваюся, що викладений матеріал стане лише початком, і ви знайдете власні цікаві та винахідливі способи використання віконних функцій.
Стандарт SQL надає величезне значення віконним функцій, і тому в ньому з'являється все більше функцій та їх можливостей. У Microsoft витратили значні зусилля на реалізацію раніше відсутньої підтримки віконних функцій у SQL Server 2012, і я думаю, що це дозволить набагато ефективніше вирішувати багато завдань. Я дуже сподіваюся, що в компанії продовжать роботу над реалізацією стандарту і в нових версіях SQL Server з'явиться підтримка інших віконних функцій.