|
В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS" представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции. |
Опубликован: 28.12.2011 | Уровень: для всех | Доступ: свободно
Лекция 14:
Некоторые примеры составления запросов. Дополнительные сведения об аналитических функциях
Формирование интервалов агрегирования "по строкам" и "по значениям"
Разницу между ROWS и RANGE (определяющими, как говорится в документации, "физические" и "логические" интервалы-окна) удобно продемонстрировать следующим примером:
SELECT ename, hiredate, sal , SUM ( sal ) OVER ( ORDER BY hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) rows_sal , SUM ( sal ) OVER ( ORDER BY hiredate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) range_sal FROM emp; ENAME HIREDATE SAL ROWS_SAL RANGE_SAL ---------- --------- ---------- ---------- ---------- SMITH 17-DEC-80 800 800 800 ALLEN 20-FEB-81 1600 2400 2400 WARD 22-FEB-81 1250 3650 3650 JONES 02-APR-81 2975 6625 6625 BLAKE 01-MAY-81 2850 9475 9475 CLARK 09-JUN-81 2450 11925 11925 TURNER 08-SEP-81 1500 13425 13425 MARTIN 28-SEP-81 1250 14675 14675 KING 17-NOV-81 5000 19675 19675 JAMES 03-DEC-81 950 20625 23625 FORD 03-DEC-81 3000 23625 23625 MILLER 23-JAN-82 1300 24925 24925 SCOTT 19-APR-87 3000 27925 27925 ADAMS 23-MAY-87 1100 29025 29025 14 rows selected.
JAMES и FORD поступили на работу одновременно и с точки зрения интервала суммирования неразличимы. Поэтому суммирование "по значению" присвоило им один и тот же общий для "мини-группы", образованной этой парой, результат — максимальную сумму, которая при всех возможных порядках перечисления сотрудников внутри этой пары будет всегда одинакова. Суммирование "по строкам" (ROWS) поступило иначе: оно упорядочило сотрудников в "мини-группе", образованной равными датами (на самом деле чисто произвольно), и подсчитало суммы, как будто бы у этих сотрудников был задан порядок следования.
Функции FIRST_VALUE и LAST_VALUE для интервалов агрегирования
Эти функции позволяют для каждой строки выдать первое значение ее окна и последнее.
Пример:
SELECT ename , hiredate , sal , FIRST_VALUE ( sal ) OVER ( ORDER BY hiredate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) first_rows , LAST_VALUE ( sal ) OVER ( ORDER BY hiredate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) last_rows , FIRST_VALUE ( sal ) OVER ( ORDER BY hiredate RANGE BETWEEN 2 PRECEDING AND CURRENT ROW ) first_range , LAST_VALUE ( sal ) OVER ( ORDER BY hiredate RANGE BETWEEN 2 PRECEDING AND CURRENT ROW ) last_range FROM emp ; ENAME HIREDATE SAL FIRST_ROWS LAST_ROWS FIRST_RANGE LAST_RANGE -------- --------- ------ ---------- --------- ----------- ---------- SMITH 17-DEC-80 800 800 800 800 800 ALLEN 20-FEB-81 1600 800 1600 1600 1600 WARD 22-FEB-81 1250 800 1250 1600 1250 JONES 02-APR-81 2975 1600 2975 2975 2975 BLAKE 01-MAY-81 2850 1250 2850 2850 2850 CLARK 09-JUN-81 2450 2975 2450 2450 2450 TURNER 08-SEP-81 1500 2850 1500 1500 1500 MARTIN 28-SEP-81 1250 2450 1250 1250 1250 KING 17-NOV-81 5000 1500 5000 5000 5000 JAMES 03-DEC-81 950 1250 950 950 3000 FORD 03-DEC-81 3000 5000 3000 950 3000 MILLER 23-JAN-82 1300 950 1300 1300 1300 SCOTT 19-APR-87 3000 3000 3000 3000 3000 ADAMS 23-MAY-87 1100 1300 1100 1100 1100 14 rows selected.
Интервалы времени
Для интервалов (окон), упорядоченных внутри по значению ("логическому", RANGE) в случае, если это значение имеет тип "дата", границы интервала можно указывать выражением над датой, а не конкретными значениями из строк. Примеры таких выражений:
INTERVAL 'число' {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}
NUMTODSINTERVAL(число, '{DAY | HOUR | MINUTE | SECOND}')
NUMTOYMINTERVAL(число, '{YEAR | MONTH}')
Пример выдачи зарплат сотрудников и средних зарплат за последние полгода на момент приема нового сотрудника:
SELECT ename , hiredate , sal , AVG ( sal ) OVER ( ORDER BY hiredate RANGE BETWEEN INTERVAL '6' MONTH PRECEDING AND CURRENT ROW ) avg_sal FROM emp ; ENAME HIREDATE SAL AVG_SAL ---------- --------- ---------- ---------- SMITH 17-DEC-80 800 800 ALLEN 20-FEB-81 1600 1200 WARD 22-FEB-81 1250 1216.66667 JONES 02-APR-81 2975 1656.25 BLAKE 01-MAY-81 2850 1895 CLARK 09-JUN-81 2450 1987.5 TURNER 08-SEP-81 1500 2443.75 MARTIN 28-SEP-81 1250 2205 KING 17-NOV-81 5000 2550 JAMES 03-DEC-81 950 2358.33333 FORD 03-DEC-81 3000 2358.33333 MILLER 23-JAN-82 1300 2166.66667 SCOTT 19-APR-87 3000 3000 ADAMS 23-MAY-87 1100 2050 14 rows selected.
Вот другая запись для того же запроса, но позволяющая использовать для количества месяцев обычное числовое выражение:
SELECT
ename
, hiredate
, sal
, AVG ( sal ) OVER (
ORDER BY hiredate
RANGE BETWEEN NUMTOYMINTERVAL ( 6, 'MONTH' ) PRECEDING
AND CURRENT ROW
) avg_sal
FROM emp
;
Ярослав Прозоров
Володимир Миколайчук
|
Помогите разобраться поетапно с логикой запроса
-------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')); И сколько строк он все таки вернет |
Жанбек Сарсенов
| Россия, Москва, Московский Государственный Университет имени Ломоносова М.В., 2002 |