Очень часто при выборке данных появляется задача определения значения первой записи в заданной подгруппе для его дальнейшего использования, например, в качестве параметра. Или еще один распространенный случай: выбрать первую запись из отсортированной выборки. Эту задачу можно решить с помощью псевдостолбца ROWNUM, но тогда потребуется использование вложенного подзапроса, т.к. сначала необходимо выбираемые данные отсортировать. В таких случаях наиболее эффективным решением будет использование групповых функций FIRST и LAST.

Функции FIRST и LAST позволяют ранжировать записи выборки и выполнять дальнейшие вычисления над записями только наибольшего или наименьшего ранга. Данные функции аналогичны друг другу с той лишь разницей, что обрабатывается первая или последняя запись выборки соответственно. Общий вид использования на примере функции FIRST:

aggregate_function KEEP (DENSE_RANK {FIRST | LAST}
ORDER BY expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]])+
[OVER query_partitioning_clause]

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

Групповые функции, допустимые к использованию с FIRST и LAST: MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV. Они производят вычисления над набором значений, получивших ранг “первый” (в случае FIRST) или “последний” (в случае LAST). Если заданный ранг получило только одно значение, вычисления производятся над множеством из одного элемента.

Элементы вызова

KEEP – ключевое слово, выполняющая семантическую задачу. Показывает, что групповая функция будет работать только с записями наибольшего/наименьшего ранга.

DENSE_RANK FIRST/DENSE_RANK LAST – определяет ранг записей (“первый”/”последний”), которые будут переданы групповой функции для вычислений.

OVER – аналитический режим. В этом случае, в качестве обрабатывающей групповой функции используется аналитическая функция, а в конструкции OVER допустима единственная конструкция PARTITION BY.

Пример использования в режиме группировки

Возьмем стандартный пример БД отдела кадров предприятия (схема HR) и рассмотрим применение FIRST/LAST на примере выборки минимальной зарплаты сотрудников с наименьшими комиссионными и максимальной зарплаты сотрудников с наибольшими комиссионными по каждому отделу.

SELECT
    department_id,
    MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
    MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
FROM employees
GROUP BY department_id
ORDER BY department_id, "Worst", "Best";

Пример работы в аналитическом режиме

Теперь выполним расчет предыдущего примера, но сформируем информацию для каждого сотрудника по отделам.

SELECT
last_name,
department_id,
salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) OVER (PARTITION BY department_id) "Best"
FROM employees
ORDER BY department_id, salary;