Функции c within group в СУБД Oracle

Функции c within group бывают агрегатные и аналитические, а объединяет их то, что для вычисления результата они нуждаются в упорядоченной последовательности входных значений. Именно упорядочивание входных значений и задается с помощью within group (order by ...).

В СУБД Oracle версий 11 и 12 имеются следующие функции c within group:

Синтаксис	Назначение
listagg(expr[, delimiter]) within group (order by ...) [over (partition by ...)]	Упорядочивает и конкатенирует в строку значения expr, опционально разделяя их delimiter.
-- агрегатная rank(expr,...) within group (order by ...) -- аналитическая rank() over ([partition by ...] order by ...)	Вычисляет ранг значения (число) в группе значений. В качестве аналитической функции ранжирует значения в диапазоне с пропусками.
-- агрегатная dense_rank(expr,...) within group (order by ...) -- аналитическая dense_rank() over ([partition by ...] order by ...)	Вычисляет ранг значения (число) в группе значений. В качестве аналитической функции ранжирует значения от 1 до N, без пропусков, где N - количество уникальных значений в группе.
-- агрегатная percent_rank(expr,...) within group (order by ...) -- аналитическая percent_rank() over ([partition by ...] order by ...)	Вычисляет процентный ранг значения (число между 0 и 1) в группе значений.
percentile_cont(expr) within group (order by ...) [over (partition by ...)]	Вычисляет процентиль в группе значений, представляющих распределение непрерывной величины. Возвращается интерполированное значение.
percentile_disc(expr) within group (order by ...) [over (partition by ...)]	Вычисляет процентиль в группе значений, представляющих распределение дискретной величины. Возвращается значение из группы.
-- агрегатная cume_dist(expr,...) within group (order by ...) -- аналитическая cume_dist() over ([partition by ...] order by ...)	Вычисляет функцию кумулятивного распределения вероятности для значения в группе значений.

Как видим, функции rank, dense_rank, percent_rank и cume_dist имеют аналитический синтаксис без within group. Но при этом упорядочивание входных значений в части over (order by ...) является для них обязательным: то есть, требование упорядочивания последовательности значений на входе по-прежнему выполняется. Причем ни одна из этих функций не может содержать в части over спецификацию окна (windowing clause), которая могла бы повлиять на состав входной последовательности; таким образом, для этих функций окна зафиксированы.

Как over, так и within group имеют дело только со строками, выбранными основным запросом. При этом order by в over или в within group никак не зависит от order by основного запроса.

Приведу примеры использования функции dense_rank:

SQL> -- аналитическая функция
SQL> select
  2      username,
  3      dense_rank()
  4          over (order by username) d_rank
  5  from all_users
  6  order by 1 desc
  7  ;

USERNAME                           D_RANK
------------------------------ ----------
XS$NULL                                17
XDB                                    16
SYSTEM                                 15
SYS                                    14
OUTLN                                  13
ORDS_PUBLIC_USER                       12
ORDS_METADATA                          11
MDSYS                                  10
HR                                      9
HI0001                                  8
FLOWS_FILES                             7
CTXSYS                                  6
APEX_040000                             5
APEX_PUBLIC_USER                        4
ANONYMOUS                               3
AI                                      2
ABC                                     1
17 rows selected

SQL> -- агрегатная функция
SQL> select
  2      dense_rank('Ulysses')
  3          within group (order by username) d_rank
  4  from all_users
  5  ;

    D_RANK
----------
        16

Больше примеров работы с функциями rank и dense_rank можно найти в статье RANK, DENSE_RANK, FIRST и LAST в Oracle 11g.

Обратите внимание, что в аналитических версиях тех функций, где часть within group является обязательной, нет возможности задать order by в аналитической части over; если бы это было возможно, условия упорядочивания могли бы противоречить друг другу. Это функции listagg, percentile_cont и percentile_disc.

Вот примеры использования функции listagg для агрегирования имен пользователей из вью all_users в список имен, разделенных запятой:

SQL> -- агрегатная функция
SQL> select
  2      listagg(username, ',')
  3          within group (order by username)
  4  from all_users
  5  ;

LISTAGG(USERNAME,',')WITHINGRO
--------------------------------------------------------------------------------------------------------------------------------------------
ABC,AI,ANONYMOUS,APEX_040000,APEX_PUBLIC_USER,CTXSYS,FLOWS_FILES,HI0001,HR,MDSYS,ORDS_METADATA,ORDS_PUBLIC_USER,OUTLN,SYS,SYSTEM,XDB,XS$NULL

SQL> select
  2      listagg(username, ',')
  3          within group (order by username)
  4  from all_users
  5  where length(username) <= 5
  6  ;

LISTAGG(USERNAME,',')WITHINGRO
--------------------------------------------------------------------------------
ABC,AI,HR,MDSYS,OUTLN,SYS,XDB

SQL> -- аналитическая функция
SQL> select
  2      username,
  3      listagg(username, ',')
  4          within group (order by username)
  5          over (partition by substr(username, 1, 1))
  6  from all_users
  7  ;

USERNAME             LISTAGG(USERNAME,',')WITHINGRO
-------------------- --------------------------------------------------------------------------------
ABC                  ABC,AI,ANONYMOUS,APEX_040000,APEX_PUBLIC_USER
AI                   ABC,AI,ANONYMOUS,APEX_040000,APEX_PUBLIC_USER
ANONYMOUS            ABC,AI,ANONYMOUS,APEX_040000,APEX_PUBLIC_USER
APEX_040000          ABC,AI,ANONYMOUS,APEX_040000,APEX_PUBLIC_USER
APEX_PUBLIC_USER     ABC,AI,ANONYMOUS,APEX_040000,APEX_PUBLIC_USER
CTXSYS               CTXSYS
FLOWS_FILES          FLOWS_FILES
HI0001               HI0001,HR
HR                   HI0001,HR
MDSYS                MDSYS
ORDS_METADATA        ORDS_METADATA,ORDS_PUBLIC_USER,OUTLN
ORDS_PUBLIC_USER     ORDS_METADATA,ORDS_PUBLIC_USER,OUTLN
OUTLN                ORDS_METADATA,ORDS_PUBLIC_USER,OUTLN
SYS                  SYS,SYSTEM
SYSTEM               SYS,SYSTEM
XDB                  XDB,XS$NULL
XS$NULL              XDB,XS$NULL
17 rows selected

Примеры работы с функциями percentile_cont, percentile_disc и cume_dist встречаются в моей статье Статистические функции в Oracle 11g, часть II.

Подводя итог сказанному: в части within group выполняется упорядочивание входных значений для функции, агрегатной или аналитической. Если функция с within group аналитическая, то в части over нельзя задать еще одно (потенциально противоречивое) условие order by и нельзя изменить спецификацию окна. Строки для упорядочивания в within group (как и для обработки в аналитической части over) поступают из основного запроса.