Загадочные функции FIRST и LAST (не путать с FIRST_VALUE и LAST_VALUE)

Давно собирался посмотреть, зачем они нужны. Разобрался.
Весьма замысловатые функции. Полагаю, применяться будут редко.

Пишу для информации.

Первое:
Текст примера запроса, который содержится в документации не совсем точен, он не помогает понять, как это работает.
http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions065.htm
А именно, если исключить в первом запросе обе фразы
KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
то результат выполнения запроса не меняется.


Правильно так (здесь слово last исправлено на first):

select DEPARTMENT_ID

     , min (SALARY) keep (dense_rank first order by COMMISSION_PCT) "Worst"

     , max (SALARY) keep (dense_rank first order by COMMISSION_PCT) "Best"

from HR.EMPLOYEES

group by DEPARTMENT_ID

order by DEPARTMENT_ID, "Worst", "Best";

Для понимания текста ниже откройте в другом окне таблицу HR.EMPLOYEES и отсортируйте по DEPARTMENT_ID, COMMISSION_PCT, я не буду здесь приводить таблицу

select DEPARTMENT_ID, COMMISSION_PCT, SALARY

from HR.EMPLOYEES order by DEPARTMENT_ID, COMMISSION_PCT

Первый запрос из документации работает так:

1. Каждый департамент обсчитывается независимо, благодаря group by DEPARTMENT_ID
2. Здесь будем смотреть только департамент 80, поскольку только у него ненулевые COMMISSION_PCT
3. В пределах департамента 80 строки сортируются по COMMISSION_PCT. Определяется минимальное значение этого поля (иным словами применяется функция аналитическая функция DENSE_RANK). Это фраза dense_rank first order by COMMISSION_PCT
4. Для всех строк в группе DEPARTMENT_ID, которые обладают этим минимальным значением COMMISSION_PCT рассчитывается min (SALARY)
5. Рассчитанное значение распространяется на все строки группы DEPARTMENT_ID

Зеленым фоном выделил то, что делает фраза после KEEP

Что хотел сказать вендор разработкой этой функции, сказать трудно, пока вижу такие варианты:

Во первых, вместо SUM можно подставить любую агрегатную функцию.

Во вторых, подставив например COUNT и LAST получим количество людей в департаменте, которые обладают максимальной COMMISSION_PCT (премией с продаж) относительно общей численности департамента -- поделите / вычтите общий COUNT

Аналитическая форма работает схожим образом. GROUP BY не используется, а группировочное выражение пишется в фразу PARTITION BY