Аналітичні функції в 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;

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*