Максим Чалышев - Oracle SQL. 100 шагов от новичка до профессионала

Выполним сначала следующий запрос:

Рисунок 163. Демонстрация работы аналитических функций

Колонка NUM, полученная с помощью аналитической функции ROW_NUMBER () и аналитического подзапроса, возвращает номер строки в наборе данных, разбитом по колонке OTD, отсортированном по SAL.

Преобразуем запрос, чтобы вывести только первых трех сотрудников.

Рисунок 164. Демонстрация работы аналитических функций

Еще один пример: данные делятся по наименованию отдела OTD, а сортировка данных в рамках сегмента по имени — NAME.

По наименованию (PARTITION по отделу), сортировка по NAME.

Рисунок 165. Демонстрация работы аналитических функций: запрос к PERSONA

Запросы с накопительным итогом

Накопление суммы в рамках отдела, накопление прибыли в рамках заданного месяца, накопление бонусов в рамках карты с сортировкой по дате — все это запросы с накопительным итогом.

Запросы с накопительным итогом пишутся аналогично запросам, которые мы только что проходили, различаются только функции. В данном случае рассмотрим аналитическую функцию SUM.

Накопительный итог по зарплате.

В данном запросе в колонке Num формируется накопительный итог по зарплате — аналитическая функция SUM (SAL), в рамках отдела с сортировкой по зарплате OVER (PARTITION BY OTD ORDER BY SAL).

Рисунок 166. Демонстрация работы аналитических функций: запрос к PERSONA

Накопительный итог по зарплате, но с сортировкой по NAME.

В данном запросе в колонке Num формируется накопительный итог по зарплате — аналитическая функция sum (sal), в рамках отдела с сортировкой по колонке name — OVER (PARTITION BY otd ORDER BY name).

Рисунок 167. Демонстрация работы аналитических функций: запрос к PERSONA

Попробуем найти среднее значение по зарплате с накоплением в рамках отдела, используем avg (sal).

Рисунок 168. Демонстрация работы аналитических функций: запрос к PERSONA

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

Для правильной работы запроса из первого примера лучше использовать специальную функцию, которая называется RANK ().

Более корректно: используем rank ().

Рисунок 169. Демонстрация работы аналитических функций: запрос к PERSONA

Посмотрите, как изменились результаты: теперь, когда у людей в рамках отдела одинаковая зарплата, им присваивается одинаковый рейтинг — Макс, Макс и Макс из отдела 30.

Пример демонстрирует отличие rank () от row_number ().

Чем отличаются аналитические функции AVG и SUM от соответствующих агрегатных функций, используемых при простой группировке?

Они имеют совершенно разное назначение: благодаря конструкции OVER в запросе СУБД понимает, что в запросе используется аналитический SQL, тогда будет возвращен накопительный итог, иначе функция будет работать как обычная агрегатная функция.

В чем отличие аналитической функции RANK (f) от функции DENSe_RANK (f)?

RANK (f) считает абсолютный ранг записи в рамках сегмента — отдела в нашем примере, DENSe_RANK вычисляет относительный ранг, с учетом изменения результата.

Посмотрите следующий пример, чтобы лучше понять, как работает RANK ().

Рисунок 170. Демонстрация работы аналитических функций: запрос к PERSONA

В данном случае RANK Тани = 3, а dense_RANK Тани = 8.

Можно ли в примере в запросах с рейтингом не использовать сортировку ORDER BY?

Нет, в таком случае данный запрос будет бессмысленным, поскольку параметр, по какому признаку сортировать данные в рейтинге, есть основа самого рейтинга.

Что если в запросе с накопительным итогом не использовать ORDER BY?

Тогда функция вернет окончательный итог без учета накоплений.

Пример запроса:

Рисунок 171. Демонстрация работы аналитических функций: запрос к PERSONA, первые N

Что означает в синтаксисе аналитических запросов NULLS FIRST/NULLS LAST?

Это параметры значат, что если аналитическая функция встречает в запросе пустое значение, то помещает их в конец списка NULLS LAST или в начало списка NULLS FIRST.

1. Разберите запросы из тестовых примеров.

2. Выведите на экран 5 сотрудников с самой высокой зарплатой personA.