Аналітичні функції в Oracle (Частина 3), Інші СУБД, Бази даних, статті

Види аналіческіх функцій


В якості базової в аналітичній функції можуть бути зазначені традиційні для 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
/


Пояснення.



Взято отсоюда


www.interface.ru/fset.asp?Url=/oracle/anal-itiv.htm&anchor=01


і ось небольщой приклад


WITH tabl AS (


SELECT “перший день” den, “1первая тиждень” ned FROM dual UNION ALL


SELECT “другий день” den, “1первая тиждень” ned FROM dual UNION ALL


SELECT “третій день” den, “1первая тиждень” ned FROM dual UNION ALL


SELECT “перший день” den, “2вторая тиждень” ned FROM dual UNION ALL


SELECT “другий день” den, “2вторая тиждень” ned FROM dual UNION ALL


SELECT “третій день” den, “2вторая тиждень” ned FROM dual UNION ALL


SELECT “перший день” den, “3третья тиждень” ned FROM dual UNION ALL


SELECT “другий день” den, “3третья тиждень” ned FROM dual UNION ALL


SELECT “третій день” den, “3третья тиждень” ned FROM dual


) SELECT dense_rank() over (ORDER BY ned) dr,row_number() over ( PARTITION BY ned ORDER BY ned) rn,


den, ned


FROM tabl

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


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

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

Ваш отзыв

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

*

*