Аналітичні функції в Oracle, Oracle, Бази даних, статті

Зміст



Загальні положення


Загальна інформація


У версії СУБД Oracle 8.1.6 з’явився новий клас з 26 функцій, названих аналітичними, і отримали подальший розвиток у версії 9. Їх описи були створені спільними зусиллями фірм IBM, Informix, Oracle і Compaq шляхом розробки так званих “поліпшень” деяких конструкцій, що є в стандарті SQL1999.


На відміну від звичайних скалярних функцій аналітичні функції беруть аргументом SQL-таблицю, що представляє логічний проміжний результат обробки SQL-оператора, де використано звернення до такої функції, і повертають в якості свого результату зазвичай теж SQL-таблицю.


Цілі введення аналітичних функцій в Oracle


Технічна мета введення аналітичних функцій – дати лаконічну формулювання і збільшити швидкість виконання “аналітичних запитів” до БД, тобто запитів, які мають сенсом виявлення внутрішніх співвідношень і залежностей у даних. Більш точно, користування аналітичними функціями може дати наступні вигоди перед звичайними SQL-операторами:



Стратегічна мета введення в Oracle аналітичних функцій – дати базовий засіб для побудови ІС типу “складів даних” (data warehouse, DW), ІС “аналітичного характеру” (business intelligence systems, BI) або OLAP-систем. За уявленнями розробників, набір таких базових засобів крім аналітичних функцій формують ще й інші засоби Oracle, такі як



Класифікація видів аналітичних функцій в Oracle


Відповідно до класифікації з документації по Oracle, аналітичні функції можуть бути наступних видів:


(a) функції ранжирування
(b)
статистичні функції для плаваючого інтервалу
(c)
функції підрахунку часткою
(d) статистичні функції LAG / LEAD з запізнілих / випереджаючим аргументом
(e) статистичні функції (лінійна регресія і т. д.)


Основні технічні особливості


Місце вказівки аналітичних функцій в 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 статистичні (агрегатні, тобто узагальнюючі) функції COUNT, MIN, MAX, SUM, AVG та інші (“стандартні агрегатні функції “по документації). Приклади наводилися вище. Можна звернути увагу на те, що аналітичні функції зі статистичними агрегатами розумно обробляють NULL:


SELECT ename, hiredate, sal,
AVG(sal)
OVER (ORDER BY hiredate
RANGE BETWEEN UNBOUNDED PRECEDING AND INTERVAL “1” SECOND PRECEDING) avg_sal
FROM emp;


Нижче наводиться повний перелік аналітичних функцій у версії СУБД 9.2:








AVG *
CORR *
COVAR_POP *
COVAR_SAMP *
COUNT *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
 
LAST_VALUE *
LEAD
MAX *
MIN *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
 
REGR_ (вид_функции_линейной_регрессии) *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE
 

Зірочкою позначені функції, що допускають використання плаваючого інтервалу розрахунку.


Деякі з цих функцій розглядаються нижче.


Функції ранжирування


Функції ранжирування дозволяють “роздати” рядках “місця” в залежності від наявних в них значеннях. Деякі приклади:


SELECT ename, sal,
            ROW_NUMBER () OVER (ORDER BY sal DESC) AS salbacknumber,
            ROW_NUMBER () OVER (ORDER BY sal) AS salnumber,
            RANK() OVER (ORDER BY sal) AS salrank,
            DENSE_RANK() OVER (ORDER BY sal) AS saldenserank
FROM emp;


(Роздати співробітникам місця в порядку убування / зростання зарплат)


Функції підрахунку часткою


Функції підрахунку часткою дозволяють одній SQL-операцією отримати для кожного рядка її “вагу” в таблиці відповідно до її значеннями. Деякі приклади:


SELECT ename, sal, RATIO_TO_REPORT(sal) OVER () AS salshare FROM emp;


(Частки співробітників в загальній сумі зарплат)


Приклад видачі частки співробітників з меншою або рівною зарплатою, ніж у “поточного”:


SELECT job, ename, sal,
CUME_DIST() OVER (PARTITION BY job ORDER BY sal) AS cume_dist
FROM emp;


JOB                       ENAME                      SAL                            CUME_DIST
–                                                   –                     –










































































ANALYST SCOTT
3000

1
ANALYST FORD
3000

1
CLERK SMITH
800

.25
CLERK JAMES
950

.5
CLERK ADAMS
1100

.75
CLERK MILLER
1300

1
MANAGER CLARK
2450

.333333333
MANAGER BLAKE
2850

.666666667
MANAGER JONES
2975

1
PRESIDENT KING
5000

1
SALESMAN WARD
1250

.5
SALESMAN MARTIN
1250

.5
SALESMAN TURNER
1500

.75
SALESMAN ALLEN
1600

1

14 rows selected.


(Видно, що три чверті клерків мають зарплату, менше ніж ADAMS).


Проранжувати цю видачу за часткою співробітників в групі можна функцією PERCENT_RANK:


SELECT job, ename, sal,
CUME_DIST() OVER (PARTITION BY job ORDER BY sal) AS cume_dist,
PERCENT_RANK() OVER (PARTITION BY job ORDER BY sal) AS pct_rank
FROM emp;


JOB ENAME SAL CUME_DIST PCT_RANK
– – – –
























































































ANALYST SCOTT
3000

1

0
ANALYST FORD
3000

1

0
CLERK SMITH
800

.25

0
CLERK JAMES
950

.5

.333333333
CLERK ADAMS
1100

.75

.666666667
CLERK MILLER
1300

1

1
MANAGER CLARK
2450

.333333333

0
MANAGER BLAKE
2850

.666666667

.5
MANAGER JONES
2975

1

1
PRESIDENT KING
5000

1

0
SALESMAN WARD
1250

.5

0
SALESMAN MARTIN
1250

.5

0
SALESMAN TURNER
1500

.75

.666666667
SALESMAN ALLEN
1600

1

1

14 rows selected.


Процентний ранг відраховується від 0 і змінюється до 1.


Деякі життєві приклади аналітичних запитів


Для типів сегментів, більше за інших витрачають дисковий простір, видати головних користувачів, відповідальних за такий витрата


Побудувати такий запит на основі таблиці SYS.DBA_SEGMENTS, можна покроково.


Крок 1. Видати типи сегментів в БД, загальний обсяг пам’яті на диску для кожного типу і частку числа типів з рівним або меншим загальним об’ємом пам’яті:


SELECT segment_type,
      SUM(bytes) bytes,
      CUME_DIST() OVER (ORDER BY SUM(bytes)) bytes_percentile
      FROM sys.dba_segments
      GROUP BY segment_type;


Крок 2. Відібрати 40% “найбільш марнотратних” по дискової пам’яті типів:


SELECT *
FROM

(SELECT segment_type,
SUM(bytes) bytes,
CUME_DIST() OVER (ORDER BY SUM(bytes)) bytes_percentile
FROM sys.dba_segments
GROUP BY segment_type)
WHERE bytes_percentile >= 0.5;


Крок 3. Відібрати користувачів, які займають перші п’ять місць за витрачання пам’яті серед “найбільш марнотратних” типів сегментів:


SELECT *
FROM
(
SELECT owner,
        SUM(bytes) bytes,
        RANK() OVER(ORDER BY SUM(bytes) DESC) bytes_rank
FROM sys.dba_segments
WHERE segment_type IN

      (SELECT segment_type
        FROM
           (SELECT segment_type,
                SUM(bytes) bytes,
                CUME_DIST() OVER (ORDER BY SUM(bytes)) bytes_percentile
                FROM sys.dba_segments
                GROUP BY segment_type)
        WHERE bytes_percentile >= 0.5)
GROUP BY owner
)
WHERE bytes_rank <=5

/


Видати список періодів найбільш активного перемикання журнальних файлів БД


Список перемикань журнальних файлів зберігається в динамічній таблиці v $ loghist. Нижче наводиться один з варіантів запиту.


var treshold number
exec :treshold := 30
alter session set nls_date_format=”MON-DD HH24:MI:SS”;


SELECT
start_time,
end_time,
ROUND((end_time – start_time)*24*60, 2) delta_min,
switches,
switches / ((end_time – start_time)*24*60) per_minute
FROM
(
SELECT
MIN(time_stamp) start_time,
MAX(time_stamp) end_time,
count (*) switches
FROM
(
SELECT time_stamp, freq10, more,
SUM(ABS(indicator)) OVER (ORDER BY time_stamp) part
FROM
(
SELECT time_stamp, freq10,
SIGN(freq10 – :treshold – 0.5) more,
SIGN(freq10 – :treshold – 0.5) – LAG(SIGN(freq10 – :treshold – 0.5), 1)
OVER (ORDER BY time_stamp) indicator
FROM
(
SELECT first_time time_stamp,
GREATEST(
COUNT(*)
OVER (ORDER BY first_time
RANGE BETWEEN CURRENT ROW AND INTERVAL “10” MINUTE FOLLOWING)
,
COUNT(*)
OVER (ORDER BY first_time
RANGE BETWEEN INTERVAL “10” MINUTE PRECEDING AND CURRENT ROW)
) freq10
FROM v$loghist
) /* frequency table */
) /* frequency treshold overcome table */
) /* transient partitioned table */
WHERE more > 0
GROUP BY part
)
WHERE (end_time – start_time)*24*60 > 0
/


Пояснення



Посилання по темі

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


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

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

Ваш отзыв

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

*

*