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

Рисунок 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.

3. Выведите на экран 5 сотрудников с самой высокой зарплатой, выведите зарплату, которая меньше, чем у данного в рейтинге personA.

4. Повторите запрос с накопительным итогом зарплаты по отделам с сортировкой по номеру отдела, поясните результат.

5. Поясните отличие аналитической функции ROW_NUM от аналитической функции ORDER BY.

В запросах с использованием аналитического SQL есть возможность работать с окном данных и использовать записи из набора данных, которые являются следующими либо предыдущими по отношению к текущей записи.

Это сложная тема, поэтому я выделил ее в отдельный шаг.

Здесь PRECEDING и FOLLOWING задают верхнюю и нижнюю границы окна агрегирования (то есть определяют строки интервала для агрегирования).

В запросе используется конструкция окна ROWS BETWEEN N PRECEDING AND CURRENT ROW для вычисления среднего avg (sal), считаются 3 предыдущие строки перед текущей строкой.

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

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

В запросе используется конструкция окна ROWS BETWEEN N PRECEDING AND CURRENT ROW для вычисления среднего avg (sal), считаются все (UNBOUNDED) предыдущие строки перед текущей строкой.

В аналитическом SQL существуют аналитические функции, которые определяют значение n предыдущих или последующих строк по отношению к текущей строке в группе.

Разберем данные аналитические функции:

• LAG (f, n, m): f — имя поля, n — предыдущее значение в группе, m — значение по умолчанию;

• LEAD (f, n, m): f — имя поля, n — последующее значение в группе, m — значение по умолчанию;

•FIRST_VALUE (f): f — имя поля, первое значение в группе;

• LAST_VALUE (f): f — имя поля, последнее значение в группе.

Несколько примеров использования данных аналитических функций.

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

Выводится зарплата сотрудника, предыдущая зарплата по рейтингу LAG (SAL,1), следующая зарплата по рейтингу LEAD (SAL,1).

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

В функциях LAG и LEAD есть еще один важный параметр — это значение, которое будет возвращать функция, если результат будет равен NULL.

Пример такого запроса:

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

В данном случае пустые значения функций LAG, LEAD заменятся на 999999999.

Обратите внимание, что при использовании FIRST_VALUE, LAST_VALUE указывать сортировку не обязательно. Тогда данные функции будут работать как агрегатные функции MIN и MAX в рамках сегмента.

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

Можно ли использовать LAG, LEAD вне рамок сегмента?

Да, и эта возможность довольно часто используется на практике.

Пример:

Как найти в рамках нашего примера зарплату 2 за 3 строчки перед текущей в рамках рейтинга по величине зарплаты?

Второй параметр в LAG, LEAD обозначает количество строк перед или после текущей строки, за которой возвращается значение.