Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 6784 / 673 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 14:

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

< Лекция 13 || Лекция 14: 12345
Разбиение данных на группы для вычислений

Аналитические функции агрегируют данные порциями (partitions; разделами, группами), количество и размер которых можно регулировать специальной синтаксической конструкцией. Ниже она указана на примере агрегирующей функции SUM:

SUM ( выражение1 ) 
OVER (
 [PARTITION BY выражение2 [, выражение3 [, …]]] 
)

Примеры использования такой конструкции приведены и выше, и ниже по тексту.

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

Если PARTITION BY не указано, то в качестве единственной группы для вычислений будет взят полный набор строк:

SELECT ename, deptno, job, 
       SUM ( sal ) OVER ( ) sum_sal
FROM emp
;

Результат последнего запроса:

ENAME          DEPTNO JOB          SUM_SAL
---------- ---------- --------- ----------
SMITH              20 CLERK          29025   ← единственная группа,
ALLEN              30 SALESMAN       29025        и сумма на всех одна
WARD               30 SALESMAN       29025
JONES              20 MANAGER        29025
MARTIN             30 SALESMAN       29025
BLAKE              30 MANAGER        29025
CLARK              10 MANAGER        29025
SCOTT              20 ANALYST        29025
KING               10 PRESIDENT      29025
TURNER             30 SALESMAN       29025
ADAMS              20 CLERK          29025
JAMES              30 CLERK          29025
FORD               20 ANALYST        29025
MILLER             10 CLERK          29025
14 rows selected.
Упорядочение в границах отдельной группы

С помощью синтаксической конструкции ORDER BY строки в группах вычислений можно упорядочивать. Синтаксис иллюстрируется на примере агрегирующей функции SUM:

SUM ( выражение1 ) 
OVER (
 [PARTITION …] 
 ORDER BY выражение2 [,…] [{ ASC|DESC }] [{ NULLS FIRST|NULLS LAST }]
)

Правила работы ORDER BY — как в обычных SQL-операторах.

Пример:

SELECT
  ename
, deptno
, job
, SUM ( sal ) 
  OVER ( PARTITION BY deptno, job ORDER BY hiredate )
  sum_sal
FROM emp
;
ENAME          DEPTNO JOB          SUM_SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
FORD               20 ANALYST         3000   ← порядок и сумма изменились
SCOTT              20 ANALYST         6000
SMITH              20 CLERK            800   ← порядок и сумма изменились
ADAMS              20 CLERK           1900
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850
ALLEN              30 SALESMAN        1600   ← порядок и сумма изменились
WARD               30 SALESMAN        2850
TURNER             30 SALESMAN        4350
MARTIN             30 SALESMAN        5600
14 rows selected.

В группах из более одной строки появился заданный порядок. Природа изменения поля SUM_SAL в пределах групп из нескольких строк станет ясна из следующего раздела.

Выполнение вычислений для строк в группе по плавающему окну (интервалу)

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

{ ROWS | RANGE } { { UNBOUNDED | выражение} PRECEDING | CURRENT ROW }
{ ROWS | RANGE } 
BETWEEN
{ UNBOUNDED PRECEDING
| CURRENT ROW
| выражение1 { PRECEDING | FOLLOWING }
} 
AND 
{ UNBOUNDED FOLLOWING 
| CURRENT ROW 
| выражение2 { PRECEDING | FOLLOWING }
} 

Фразы PRECEDING и FOLLOWING задают верхнюю и нижнюю границы агрегирования (то есть интервал строк, "окно" для агрегирования).

Вот поясняющий пример, воспроизводящий результат из предыдущего раздела:

SELECT
  ename
, deptno
, job
, SUM ( sal )
  OVER (
    PARTITION BY deptno, job
    ORDER BY hiredate 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) sum_sal
FROM emp
;
ENAME          DEPTNO JOB          SUM_SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
FORD               20 ANALYST         3000  ← зарплата FORD'а
SCOTT              20 ANALYST         6000  ← сумма FORD'а и SCOTT'а
SMITH              20 CLERK            800  ← зарплата SMITH'а
ADAMS              20 CLERK           1900  ← сумма SMITH'а и ADAMS'а
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850
ALLEN              30 SALESMAN        1600  ← зарплата ALLEN'а
WARD               30 SALESMAN        2850  ← сумма ALLEN'а и WARD'а
TURNER             30 SALESMAN        4350  ← ALLEN+WARD+TURNER
MARTIN             30 SALESMAN        5600  ← ALLEN+WARD+TURNER+MARTIN
14 rows selected.

Здесь в пределах каждой группы (использована фраза PARTITION BY) сотрудники упорядочиваются по времени найма на работу (фраза ORDER BY), и для каждого в группе вычисляется сумма зарплат: его и всех его предшественников (фраза ROWS BETWEEN формулирует "окошко суммирования" от первого в группе до текущего рассматриваемого).

Обратите внимание, что плавающий интервал задается в терминах упорядоченных строк (ROWS) или значений (RANGE); ввиду этого фраза ORDER BY в определении группы в конструкции OVER обязана присутствовать.

< Лекция 13 || Лекция 14: 12345
Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Ладисьяно Писеев
Ладисьяно Писеев
Россия