Ранжирующие функции SQL

1.

Курс «Хранилища данных»
Тема: Ранжирующие функции SQL
Барабанщиков
Игорь Витальевич

2.

Аналитические функции SQL
• Запросы систем поддержки принятия
решений (DSS, Decision Support System)
отличаются от запросов в системах
оперативной обработки транзакций (OLTP,
Online Transaction Processing).
• Эти запросы используются руководителями и
аналитиками для отслеживания тенденций,
выявления отклонений, прогнозирования.
2

3.

Примеры бизнес-запросов
• Найти десять лучших продавцов в каждом
регионе за прошлый год.
• Найти всех клиентов, годовой объем
заказов которых превысил 20% от общего
объема продаж их географического региона.
• Определить какой регион больше всего
пострадал от поквартального спада продаж.
• Найти товары, продаваемые лучше и хуже
других для каждого квартала прошлого года
по регионам.
3

4.

Проблемы бизнес-запросов
Эти запросы сложно записать на SQL по
следующим причинам:
• Они могут требовать разные уровни
обобщения для одних и тех же данных.
• Они могут включать сравнения данных
внутри одной таблицы.
• Они могут требовать проведение
дополнительной фильтрации после
проведения сортировки результирующего
множества (определение 10 лучших и
худших).
4

5.

Возможное решение
• Можно было бы получить желаемые
результаты, используя такие возможности
SQL, как:
- самообъединения
- встроенные представления
- пользовательские функции
• Полученные таким способ запросы было
бы тяжело понять, а выполнялись бы они
неприемлемо долго.
• Выход – использовать аналитические
функции SQL.
5

6.

Применение аналитических функций
• Аналитические функции могут
использоваться только в инструкции
SELECT.
• Аналитические функции выполняются
только после того, как вычислены
инструкции FROM, WHERE, GROUP BY и
HAVING.
• После выполнения аналитических
функций выполняется инструкция ORDER
BY, упорядочивающая итоговый результат.
6

7.

Ранжирующие функции
Позволяют определить эффективность
некоторой бизнес-сущности по сравнению с
такими же, как она (одноранговыми),
объектами:
• Определение наиболее используемых активов
• Определение продуктов. Хуже всего
продающихся в регионе.
• Нахождение лучших торговых представителей.
Для решения этих задач можно использовать
функции RANK, DENSE_RANK и ROW_NUMBER.
7

8.

Функции RANK,
DENSE_RANK и ROW_NUMBER
• Генерируют целое значение от 1 до N для
каждой строки, где N меньше или равно
количеству строк результирующего
множества.
• Отличие значений, возвращаемых этими
функциями, обусловлено тем, как каждая
из них обрабатывает равные значения.
8

9.

Отличия ранжирующих функций
• ROW_NUMBER – возвращает уникальное число для
каждой строки, начиная с 1. Для строк, имеющих
повторяющиеся значения, числа присваиваются
произвольным образом.
• DENSE_RANK – присваивает уникальный номер
каждой строке, начиная с 1, за исключением
строк, имеющих одинаковые значения; таким
строкам присваиваются одинаковые ранги.
• RANK - присваивает уникальный номер каждой
строке, начиная с 1, за исключением строк,
имеющих одинаковые значения; таким строкам
присваиваются одинаковые ранги, и в
последовательности присваиваемых значений
возникает промежуток.
9

10.

Пример
SELECT region_id, cust_nbr,
SUM(sales) cust_sales,
RANK() OVER (ORDER BY SUM(sales) DESC) rs,
DENSE_RANK() OVER (ORDER BY SUM(sales) DESC) ds,
ROW_NUMBER() OVER (ORDER BY SUM(sales) DESC) rn
FROM orders
WHERE year = 2001
GROUP BY region_id, cust_nbr
ORDER BY 6
10

11.

Результат запроса
Region_id
Cust_nbr
sales
Rank
Dense_Rank
Row_Number
9
25
1000
1
1
1
8
17
950
2
2
2
7
19
880
3
3
3
10
14
880
3
3
4
11
15
880
3
3
5
7
14
850
6
4
6
10
12
820
7
5
7
9
16
790
8
6
8
8
12
740
9
7
9
10
11
710
10
8
10
7
15
695
11
9
11
11

12.

Заключение
Применение
ранжирующих
функций SQL
позволяет
определить
эффективность
некоторой бизнессущности по
сравнению с такими
же, как она
(одноранговыми),
объектами.
12