Воскресенье
20.09.2020
08:15


Форма входа


Приветствую Вас Гость | RSS
Статьи по ORACLE, PL/SQL, Java и немного по SHELL (BASH),...
Главная Каталог статей Регистрация Вход
Главная » Статьи » ORACLE » ORACLE, PL/SQL

Аналитические функции в Oracle (Часть 2)

Основные технические особенности

Место указания аналитических функций в SQL-предложении

Аналитические функции принимают в качестве аргумента столбец промежуточного результата вычисления SQL-предложения и возвращают тоже столбец. Поэтому местом их использования в SQL-предложении могут быть только фразы ORDER BY и SELECT, выполняющие завершающую обработку логического промежуточного результата.

Сравнение с обычными функциями агрегирования

Многие аналитические функции действуют подобно обычным скалярным функциям агрегирования SUM, MAX и прочим, примененным к группам строк, сформированным с помощью GROUP BY. Однако обычные функции агрегирования уменьшают степень детализации, а аналитические функции нет. Поясняющий сравнительный пример:

SELECT deptno, job, SUM(sal) sum_sal
FROM emp
GROUP BY deptno, job;

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

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

DEPTNO             JOB                       SUM_SAL
----------                  ---------                         ----------

10

CLERK

1300
 <- - одна группа
10

MANAGER

2450
 <- - одна группа
10

PRESIDENT

5000
 <- - одна группа
20

CLERK

6000
 <- - одна группа
20

MANAGER

1900
20

PRESIDENT

2975
30

CLERK

950
30

MANAGER

2850
30

PRESIDENT

5600

9 rows selected.

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

ENAME                    DEPTNO           JOB                SUM_SAL
----------                        ----------             ---------                 ----------

MILLER
10
CLERK
1300 <- - одна группа
CLARK
10
MANAGER
2450 <- - еще одна группа
KING
10
PRESIDENT
5000 <- - еще одна группа
SCOTT
20
ANALYST
6000 <- - еще одна группа
FORD
20
ANALYST
6000  
SMITH
20
CLERK
1900 <- - еще одна группа
ADAMS
20
CLERK
1900  
JONES
20
MANAGER
2975 <- - еще одна группа
JAMES
30
CLERK
950 <- - еще одна группа
BLAKE
30
MANAGER
2850 <- - еще одна группа
ALLEN
30
SALESMAN
5600 <- - еще одна группа
MARTIN
30
SALESMAN
5600  
TURNER
30
SALESMAN
5600  
WARD
30
SALESMAN
5600  

14 rows selected.

Особенности обработки

Построим в SQL*Plus планы для двух запросов выше:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT deptno, job, SUM(sal) sum_sal
FROM emp
GROUP BY deptno, job;

SELECT empno, deptno, job,
SUM(sal) OVER (PARTITION BY deptno, job) sum_sal
FROM emp;

SET AUTOTRACE OFF

Обратим внимание на однопроходность и специальный шаг плана второго запроса (шаг WINDOW).

Разбиение данных на группы для вычислений

Аналитические функции агрегируют данные порциями (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 |
{UNBOUNDED | выражение 1}{PRECEDING | FOLLOWING}}
AND
{{UNBOUNDED FOLLOWING | CURRENT ROW |
{UNBOUNDED | выражение 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 в определении группы обязана присутствовать.

Формирование интервалов агрегирования "по строкам" и "по значениям"

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

Категория: ORACLE, PL/SQL | Добавил: Akarak (26.10.2009)
Просмотров: 17017 | Комментарии: 6 | Рейтинг: 5.0/3
Всего комментариев: 5
0
2 Jane_november   [Материал]
Подскажите, пожалуйста, где можно посмотреть какими значениями заполнена таблица EMP?

0
3 Akarak   [Материал]
ALTER SESSION SET nls_date_format='dd-mon-yy';
ALTER SESSION SET nls_date_language='american';
ALTER SESSION SET nls_territory='america';
DROP TABLE scott.emp;
DROP TABLE scott.dept;
DROP TABLE scott.salgrade;

CREATE TABLE scott.dept (
 deptno              NUMBER(2) NOT NULL,
 dname               CHAR(14),
 loc                 CHAR(13),
 CONSTRAINT dept_primary_key PRIMARY KEY (deptno));

INSERT INTO scott.dept VALUES (10,'accounting','new york');
INSERT INTO scott.dept VALUES (20,'research','dallas');
INSERT INTO scott.dept VALUES (30,'sales','chicago');
INSERT INTO scott.dept VALUES (40,'operations','boston');

CREATE TABLE scott.emp (
 empno               NUMBER(4) NOT NULL,
 ename               CHAR(10),
 job                 CHAR(9),
 mgr                 NUMBER(4) CONSTRAINT emp_self_key REFERENCES scott.emp (empno),
 hiredate            DATE,
 sal                 NUMBER(7,2),
 comm                NUMBER(7,2),
 deptno              NUMBER(2) NOT NULL,
 CONSTRAINT emp_foreign_key FOREIGN KEY (deptno) REFERENCES scott.dept (deptno),
 CONSTRAINT emp_primary_key PRIMARY KEY (empno));

INSERT INTO scott.emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO scott.emp VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO scott.emp VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO scott.emp VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO scott.emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO scott.emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO scott.emp VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO scott.emp VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO scott.emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO scott.emp VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO scott.emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO scott.emp VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO scott.emp VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO scott.emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);

CREATE TABLE salgrade (
 grade               NUMBER,
 losal               NUMBER,
 hisal               NUMBER);

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

0
4 Jane_november   [Материал]
Спасибо большое!

0
5 Jane_november   [Материал]
Ошибка в самом первом выводе с GROUP BY deptno, job. Для deptno = '20' должны быть строки:
для JOB = 'CLERK' SUM_SAL =  '1900',
для JOB = 'MANAGER' SUM_SAL = '2975'
и вместо 'PRESIDENT' JOB = 'ANALYST' c SUM_SAL = '6000'.

0
1 DWORD   [Материал]
спасибо, очень понятно описано и примеры хорошие.
ЗЫ Пеши истчо:)

Имя *:
Email *:
Код *:
Меню сайта

Категории раздела
ORACLE, PL/SQL [36]
Скрипты DDL [3]
Скрипты по управлению объектами
Встроенные функции SQL [2]
Встроенные функции SQL

Интересные сайты
  • sql.ru
  • all-oracle.ru
  • oranet.ru
  • oracloid.ru
  • forum.vingrad.ru

  • Статистика

    Онлайн всего: 1
    Гостей: 1
    Пользователей: 0

    Поиск

    Copyright MyCorp © 2020
    Используются технологии uCoz