Transcript SQL Server Best Practices TechTalk

http://www.EkbIT.Pro
Высокопроизводительный код T-SQL.
Оконные функции
Алексей Князев
[email protected]
http://www.t-sql.ru
Содержание
Что такое оконные функции
Окна в SQL Server
Оптимизация оконных функций
Решения на основе T-SQL с использованием оконных
функций
• Заключение
• Вопросы?
•
•
•
•
2
SQL Server 2012
Что такое оконные функции
Оконные функции
• Встречаемые названия
– Оконные функции (Window Functions)
– Аналитические функции
– OLAP-функции
– OVER-функции
• Впервые появились
– Oracle i8 R2
– DB2 7.1
– SQL Server 2005
• Описаны впервые в стандарте ANSI SQL:1999 (OLAP
functions)
• ANSI SQL:2003 – более 30 аналитических функций
• ANSI SQL:2008
• ANSI SQL:2011
4
OVER()
• WiKi: Оконными (windows functions) называются
функции, которые применяются к наборам строк и
определяются посредством предложения OVER. В
основном они используются для аналитических задач,
позволяя вычислять нарастающие итоги и
скользящие средние, определять пробелы и островки
в данных, а также вычислять многие другие
вычисления.
• BOL: Окно — это определяемый пользователем набор
строк. Оконная функция вычисляет значение для
каждой строки в результирующем наборе,
полученном из окна.
5
Типы оконных функций
•
•
•
•
•
Агрегатные
Ранжирующие
Аналитические (или распределения)
Сдвига
…окна – это принцип, поэтому в следующих
редакциях стандарта могут появиться новые типы.
6
Элементы оконных функций
• Секционирование
• Упорядочение
• Кадрирование
7
SQL Server 2012
Окна в SQL Server
Окна в SQL Server
9
Оконные функции в SQL Server по типам
• Агрегатные
– SUM, COUNT, AVG, MAX, MIN, COUNT_BIG,
CHECKSUM_AGG, STDEV, STDEVP, VAR, VARP
• Ранжирующие
– ROW_NUMBER, NTILE, RANK, DENSE_RANK
• Аналитические (или распределения)
– PERCENTILE_CONT, PERCENTILE_DESC,
PERCENT_RANK, CUME_DISC
• Сдвига
– LAG, LEAD, FIRST_VALUE, LAST_VALUE
10
Элементы оконных функций
11
Кадрирование (
ROWS BETWEEN
UNBOUNDED PRECEDING |
<n> PRECEDING
|
<n> FOLLOWING
|
CURRENT ROW
AND
UNBOUNDED FOLLOWING|
<n> PRECEDING
|
<n> FOLLOWING
|
CURRENT ROW
12
Элементы оконных функций (пример)
13
Процесс выполнения
From
Where
Group By
Having
Select
Order By
14
Производительность оконных функций
15
Производительность оконных функций
Subquery
Window Function
10000
20000
30000
40000
50000
60000
70000
80000
90000
100000
16
Демонстрация
Окна в SQL Server
SQL Server 2012
Оптимизация оконных функций
Оптимизация
19
Демонстрация
Оптимизация оконных функций
SQL Server 2012
Решения на основе T-SQL с использованием оконных
функций
Задачи решаемы с помощью оконных функций
•
•
•
•
•
•
•
•
•
•
•
•
•
Последовательности
Разбиение на страницы
Устранение дубликатов
Сведение
Возвращение первых N-строк в каждой группе
Моды
Вычисление нарастающих итогов
Максимальное количество параллельных интервалов
Упаковка интервалов
Нахождение пробелов и островков
Медианы
Условные агрегаты
Сортировка иерархий
22
Демонстрация
Решения на основе T-SQL с использованием оконных
функций
Заключение
Заключение
•
•
•
•
•
Поддержка оконных функций в SQL описаны в стандарте SQL:1999. С
того времени в каждой новой редакции поддержка оконных функций
только укреплялась.
Оконные функции позволяют решать множество задач:
– Разбиение на страницы
– Устранение дубликатов
– Возвращение первых N-строк в каждой группе
– Вычисление нарастающих итогов
– Выполнение операций в интервалах
– Нахождение пробелов и островков
– Вычисление процентилей
– Вычисление режима распределения
– Иерархии сортировки
– Сведение
– Определение новизны
Запросы выглядят более компактно и более читабельно.
Улучшенная производительность для ряда запросов.
Оконные функции хорошо поддаются оптимизации.
25
Заключение
• Ещё много функций описанных в стандарте не
включены в SQL Server (NTH_VALUE, WINDOWS,
EXCLUDE, FILTER, поддержка RANGE реализована не
полностью и т.д.)
• Не поддерживается ключевое слово DISTINCT в
функциях агрегирования
• Требует определённого времени для изучения и
понимания как оконные функции работают, т.к. это
новая концепция, к которой надо привыкнуть.
• SQL Server 2014 CTP1 !? 
26
Q&A
Ваши вопросы
Ресурсы
• Books Online:
– Предложение OVER (Transact-SQL)
http://msdn.microsoft.com/ru-ru/library/ms189461.aspx
• Наш сайт
– http://www.EkbIT.Pro
• Мой блог
– http://www.t-sql.ru
• Книга Ицика Бен-Гана
28