|
В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS" представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции. |
Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 7487 / 958 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Тема: Базы данных
Специальности: Администратор баз данных
Теги:
Лекция 14:
Некоторые примеры составления запросов. Дополнительные сведения об аналитических функциях
Разбиение данных на группы для вычислений
Аналитические функции агрегируют данные порциями (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 обязана присутствовать.
Ярослав Прозоров
Володимир Миколайчук
|
Помогите разобраться поетапно с логикой запроса
-------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')); И сколько строк он все таки вернет |