27.02.2016 0 min to read

Групповые функции FIRST и LAST

Category : Статьи

Очень часто при выборке данных появляется задача определения значения первой записи в заданной подгруппе для его дальнейшего использования, например, в качестве параметра. Или еще один распространенный случай: выбрать первую запись из отсортированной выборки. Эту задачу можно решить с помощью псевдостолбца 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";

 

DEPARTMENT_ID Worst Best
10
4400
4400
20
6000
13000
30
2500
11000
40
6500
6500
50
2100
8200
60
4200
9000
70
10000
10000
80
6100
14000
90
17000
24000
100
6900
12000
110
8300
12000
7000
7000

 

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

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

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;

 

LAST_NAME DEPARTMENT_ID SALARY Worst Best
Whalen
10
4400
4400
4400
Fay
20
6000
6000
13000
Hartstein
20
13000
6000
13000
Colmenares
30
2500
2500
11000
Himuro
30
2600
2500
11000
Tobias
30
2800
2500
11000
De Haan
90
17000
17000
24000
King
90
24000
17000
24000
Popp
100
6900
6900
12000
Sciarra
100
7700
6900
12000
Urman
100
7800
6900
12000
Chen
100
8200
6900
12000
Faviet
100
9000
6900
12000
Greenberg
100
12000
6900
12000
Gietz
110
8300
8300
12000
Higgins
110
12000
8300
12000
Grant
7000
7000
7000

 

Tags: ,