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

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

< Лекция 13 || Лекция 14: 12345
Формирование интервалов агрегирования "по строкам" и "по значениям"

Разницу между 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
;
< Лекция 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'));

И сколько строк он все таки вернет