Есть ряд агрегатных функций, которые похожи на оконные, но таковыми не являются.
| Функция | Описание |
|---|---|
mode() WITHIN GROUP (ORDER BY выражение_сортировки) |
возвращает значение, наиболее часто встречающееся во входных данных (если одинаково часто встречаются несколько значений, произвольно выбирается первое из них) |
percentile_cont(дробь) WITHIN GROUP (ORDER BY выражение_сортировки) |
непрерывный процентиль: возвращает значение, соответствующее заданной дроби по порядку, интерполируя соседние входные значения, если необходимо |
percentile_disc(дробь) WITHIN GROUP (ORDER BY выражение_сортировки) |
дискретный процентиль: возвращает первое значение из входных данных, позиция которого по порядку равна или превосходит указанную дробь |
rank(аргументы) WITHIN GROUP (ORDER BY сортированные_аргументы) |
ранг гипотетической строки, с пропусками повторяющихся строк |
dense_rank(аргументы) WITHIN GROUP (ORDER BY сортированные_аргументы) |
ранг гипотетической строки, без пропусков |
percent_rank(аргументы) WITHIN GROUP (ORDER BY сортированные_аргументы) |
относительный ранг гипотетической строки, от 0 до 1 |
cume_dist(аргументы) WITHIN GROUP (ORDER BY сортированные_аргументы) |
относительный ранг гипотетической строки, от 1/N до 1 |
Если после агрегатной функции отсутствует OVER, то функция не является оконной. Значит значение будет вычисляться в процессе группировки строк. Агрегатные функции, после которых написано WITHIN GROUP, не являются исключением.
Что такое WITHIN GROUP?
В WITHIN GROUP в ORDER BY указывается значение, по которому будет сформирован набор данных с указанием сортировки. Сейчас разберем все на примере.
Мода
В повседневной жизни мы часто встречаемся с модой. Модная одежда, модная прическа, модный дизайн... Но что значит быть модным?
В математике для моды есть четкое определение. Модой ряда чисел называется число, которое встречается в данном ряду чаще других. Модой ряда 32, 26, 18, 26, 15, 21, 26 является число 26, встречается 3 раза.
Найдем самого модного руководителя, т.е. того, у кого больше всего подчиненных :)
Сначала посмотрим исходные данные:
SELECT e.manager_id,
count (*) AS cnt
FROM employee e
WHERE e.manager_id IS NOT NULL
GROUP BY e.manager_id
ORDER BY cnt DESC
| # | manager_id | cnt |
|---|---|---|
| 1 | 24 | 4 |
| 2 | 5 | 3 |
| 3 | 20 | 3 |
| 4 | 23 | 3 |
| 5 | 21 | 3 |
| 6 | 16 | 2 |
| ... | ... | ... |
Найдем "самого модного":
SELECT mode() WITHIN GROUP (ORDER BY e.manager_id)
FROM employee e
| # | mode |
|---|---|
| 1 | 24 |
P.S. В теме про оконные функции у нас нет цели разобрать все агрегатные функции. Целью данного задания было познакомиться с WITHIN GROUP. Остальные вышеперечисленные функции разберем в отдельной теме.