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

Звездочкой помечены функции, допускающие использование плавающего интервала расчета.

Некоторые из этих функций рассматриваются ниже.

Функции ранжирования

Функции ранжирования позволяют "раздать" строкам "места" в зависимости от имеющихся в них значениях. Некоторые примеры:

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
--------- -                     ---------                              ----------                     ----------

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
--------- ---------- ---------- ---------- ----------

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 в ITShop.ru
• Курсы по продуктам Oracle
• Подписаться на рассылку "СУБД Oracle с нуля"