Воскресенье
20.09.2020
07:05


Форма входа


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

Аналитические функции в 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
/

Пояснения.

  • Фактически проверяется не частота переключений журнальных файлов, а частота фиксации первого изменения в журнальных файлах. Это не совсем одно и то же, но, похоже, сильно коррелирующие события.
  • Результат получается в несколько проходов. Сначала для каждой записи проверяется средняя активность переключений в 10-минутные предшествующий и последующий интервалы. Затем выбираются записи, для которых средняя активность превышает порог :treshold = 30 в минуту. Затем размечаются точки перехода через порог, которые далее служат границами групп "повышенной" и "пониженной" активности. Потом интервалы с повышенной активностью выдаются на экран.

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

http://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

 

Категория: ORACLE, PL/SQL | Добавил: Akarak (26.10.2009)
Просмотров: 23175 | Комментарии: 4 | Рейтинг: 5.0/2
Всего комментариев: 4
0
3 Олег   [Материал]
Спасибо.
З.Ы. в фразе "и вот небольщой пример" ошибка.

0
4 Akarak   [Материал]
Спасибо. Исправил.

0
1 Доброжелатель   [Материал]
В заголовке не АНАЛИЧЕСКИЕ ФУНКЦИИ, А АНАЛИТИЧЕСКИЕ ФУНКЦИИ

0
2 Akarak   [Материал]
Спасибо! Поправил.

Имя *:
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