Выборка данных. Фраза SELECT предложения SELECT

Аналитические функции

Аналитические функции — это нескалярные функции (за исключением аналитических статистических, скалярных по результату), которые в отличие от стандартных агрегатных могут употребляться только во фразах SELECT и ORDER BY, так как применяются к уже отобранному результату (см. выше схему выполнения предложения SELECT). Свое название получили по той причине, что позволяют средствами SQL (в Oracle) строить запросы, анализирующие данные в БД. Являются вариацией "оконных функций", вошедших в SQL:2003; другая вариация реализована фирмой IBM в DB2.

Функции этой категории иногда называют "функциями OLAP" ввиду того, что они хорошо подходят для систем типа OLAP (On-Line Analytical Processing), аналитических систем и "аналитических баз данных".

В Oracle они могут быть следующих видов:

1. функции ранжирования;
2. статистические функции для плавающего интервала;
3. функции подсчета долей;
4. статистические функции LAG/LEAD с запаздывающим/опережающим аргументом;
5. статистические функции (линейная регрессия и т. д.).

Далее по очереди приводятся примеры употребления аналитических функций каждого из этих видов.

"Раздать сотрудникам места по мере убывания или возрастания их зарплат":

SELECT 
  ename
, sal
, ROW_NUMBER ( ) OVER ( ORDER BY sal DESC ) AS row_number_desc
, ROW_NUMBER ( ) OVER ( ORDER BY sal )      AS row_number_asc
, RANK ( )       OVER ( ORDER BY sal )      AS rank
, DENSE_RANK ( ) OVER ( ORDER BY sal )      AS dense_rank 
FROM emp
;

Ответ:

ENAME        SAL ROW_NUMBER_DESC ROW_NUMBER_ASC       RANK DENSE_RANK
-------- ------- --------------- -------------- ---------- ----------
SMITH        800              14              1          1          1
JAMES        950              13              2          2          2
ADAMS       1100              12              3          3          3
MARTIN      1250              11              4          4          4
WARD        1250              10              5          4          4
MILLER      1300               9              6          6          5
TURNER      1500               8              7          7          6
ALLEN       1600               7              8          8          7
CLARK       2450               6              9          9          8
BLAKE       2850               5             10         10          9
JONES       2975               4             11         11         10
SCOTT       3000               3             12         12         11
FORD        3000               2             13         12         11
KING        5000               1             14         14         12

Как видно, разница в поведении проявляется на данных, где критерий определения места оказывается одинаковым у нескольких сотрудников. ROW_NUMBER в таких случаях места раздает случайно. Например, в версии 9 СУБД на этот запрос выдавала Скотту и Форду второе и третье места, а Мартину и Варду — десятое и одиннадцатое. Функции же RANK и DENSE_RANK на одинаковом показателе критерия присваивают строкам одно и то же место с той разницей, что в случае DENSE_RANK следующее по величине критерия место выдается по порядку, а в случае RANK — с пропуском за счет возникших повторений.

"'Растущий итог' выплат на зарплату по мере приема сотрудников на работу":

SELECT 
  ename
, sal
, SUM ( sal ) OVER
  (
   ORDER BY hiredate 
   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
  ) AS sum_over_range
FROM emp
;

Ответ:

ENAME             SAL SUM_OVER_RANGE
---------- ---------- --------------
SMITH             800            800
ALLEN            1600           2400
WARD             1250           3650
JONES            2975           6625
BLAKE            2850           9475
CLARK            2450          11925
TURNER           1500          13425
MARTIN           1250          14675
KING             5000          19675
JAMES             950          23625
FORD             3000          23625
MILLER           1300          24925
SCOTT            3000          27925
ADAMS            1100          29025

Заметьте, что Джеймс и Форд поступили на работу одновременно, и поэтому значение общей суммы зарплат у них одинаковое. В то же время смысл такого суммирования не совсем ясен. Более понятен запрос, где вместо слова RANGE указать ROWS. Если это сделать, Джеймс и Форд "получат" разные суммы, но снова в случайном порядке (значение HIREDATE в качестве критерия упорядочения у них одинаковое).

"Доли зарплаты сотрудников в общей сумме зарплат":

SELECT
  ename
, sal
, RATIO_TO_REPORT ( sal ) OVER ( ) AS ratio_to_report 
FROM emp
;

Ответ:

ENAME             SAL RATIO_TO_REPORT
---------- ---------- ---------------
SMITH             800      .027562446
ALLEN            1600      .055124892
WARD             1250      .043066322
JONES            2975      .102497847
MARTIN           1250      .043066322
BLAKE            2850      .098191214
CLARK            2450      .084409991
SCOTT            3000      .103359173
KING             5000      .172265289
TURNER           1500      .051679587
ADAMS            1100      .037898363
JAMES             950      .032730405
FORD             3000      .103359173
MILLER           1300      .044788975

"Изменение зарплаты сотрудника по отношению к предшественнику по мере приема на работу":

SELECT 
  ename
, sal
, sal - LAG ( sal, 1 ) OVER ( ORDER BY hiredate ) delta 
FROM emp
;

Ответ:

ENAME             SAL      DELTA
---------- ---------- ----------
SMITH             800           
ALLEN            1600        800
WARD             1250       -350
JONES            2975       1725
BLAKE            2850       -125
CLARK            2450       -400
TURNER           1500       -950
MARTIN           1250       -250
KING             5000       3750
JAMES             950      -4050
FORD             3000       2050
MILLER           1300      -1700
SCOTT            3000       1700
ADAMS            1100      -1900

Обратите внимание на разумное поведение аналитических функций (вообще) на границах упорядоченных множеств данных (строка со Смитом). Неприятность в другом: NULL, который порождает Oracle в своем ответе, имеет здесь смысл "значение неприменимо", а не "неизвестно", как хотелось бы (обсуждение разницы приводилось выше).

"Три из имеющихся видов регрессии для оценки взаимозависимости значений в столбцах":

SELECT 
  REGR_SLOPE ( sal, comm ) AS slope
, REGR_AVGX  ( sal, comm ) AS avgsal
, REGR_AVGY  ( sal, comm ) AS avgcomm
FROM emp
;

Ответ:

     SLOPE     AVGSAL    AVGCOMM
---------- ---------- ----------
-.20642202        550       1400

Названия функций для имеющихся прочих видов регрессии приведены в документации по Oracle. Обратите внимание на вероятную формальность этого запроса, если не предположить, что связь между зарплатой и комиссионными в жизни вдруг существует, в результате чего запрос приобретает смысл. Функции регрессии — единственные, требующие в качестве аргументов два столбца.

Выражение типа "ссылка на курсор"

Во фразе SELECT (а также в качестве аргумента функции — в составе любого выражения) можно использовать выражение типа "ссылка на курсор". Оно строится с помощью функции CURSOR, аргументом которой передается другое предложение SELECT, и возвращает скаляр — ссылку на курсор. Хотя в SQL*Plus эту функцию и разрешено задействовать, основное применение ей — в программной обработке результатов предложения SELECT. Пример в SQL*Plus:

SELECT
  dname
, CURSOR ( SELECT ename FROM emp WHERE emp.deptno = dept.deptno ) 
FROM dept;

В качестве элемента более общего выражения курсорное выражение может войти только будучи предъявленным как аргумент функции; в примере ниже это вымышленная "табличная" функция JOBSEMPS:

SELECT 
  TABLE ( jobsemps ( CURSOR ( SELECT * FROM emp ) ) ) AS "Nested Table:"
FROM dual;

Такая техника позволяет передавать подпрограмме для обработки нефиксированный по количеству (но фиксированный по структуре) массив строк.

В SQL фирмы Oracle тип ссылки на курсор отсутствует. Он имеется только в PL/SQL.