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

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 обозначает количество строк перед или после текущей строки, за которой возвращается значение.

В конструкции окна аналитической функции есть возможность оперировать интервалами времени. Приведите пример.

Это возможно с данными типа DATE или TIMESTAMP. Пример интервала за 20 дней назад от даты, соответствующей текущей строке запроса.

Можно ли использовать аналитический SQL, аналитические функции вместе с операциями группировки в запросе?

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

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