Аналитические функции Появление аналитических функций Аналитические функции

Аналитические функции

Появление аналитических функций Аналитические функции позволяют создавать запросы, которые могут определять внутренние соотношения между группированными данными. На обычном языке SQL легко создаются запросы, выводящие данные для отдельных объектов и их групп, выделяемых по значениям некоторых столбцов. Но, даже для получения общего итога в этом случае пришлось бы написать ещё один запрос, группирующий все объекты в одну группу, и соединить его с предыдущим запросом с помощью UNION. Ещё более сложны запросы, в которых создаются несколько группирований. Например, легко вывести данные о зарплате каждого сотрудника компании или средней зарплате по подразделениям одного уровня, например, отделам. Однако, запрос, который бы вычислял для каждого сотрудника соотношение между его собственной зарплатой и зарплатой средней по его подразделению уже требует создания коррелированного подзапроса. Планы исполнения у таких сложных подзапросов как правило, очень плохие.

Групповые функции с “плохим” планом SELECT deptno, SUM(sal) "Зарплата" FROM emp GROUP BY deptno UNION SELECT NULL, SUM(sal) "Зарплата" FROM emp;

Преимущества и возможности аналитических функций Преимущества: • Простая формулировка. Многие аналитические запросы к БД в обычном SQL сложно формулируются, а потому с трудом осмысливаются и плохо отлаживаются. • Снижение нагрузки на сеть. Вместо набора запросов в обычном SQL получается один запрос. По сети только отправляется запрос и получается окончательный результат. • Перенос вычислений на сервер. С использованием аналитических функций не нужны расчеты на клиенте; они полностью проводятся на сервере, ресурсы которого скорее всего более приспособлены для быстрой обработки больших объемов данных. • Лучшая эффективность обработки запросов. Аналитические функции реализуются алгоритмами вычисления, которые связаны со специальными планами обработки запросов, обеспечивающими большую скорость вычислений. Возможности аналитических функций: • Расширение возможностей GROUP BY • Динамические окна для вычислений • Анализ с опережающим/запаздывающим аргументом

Упрощённый синтаксис Синтаксис: <имя_функции>(аргументы) OVER ( [PARTITION BY [, …]] [ORDER BY [ASC|DESC] [NULLS FIRST|NULLS LAST]] ) Пример: SELECT deptno, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal) FROM EMP; Простейший пример: SELECT deptno, sal, SUM(sal) OVER () FROM EMP; Его результат Попробуйте исполнить предыдущий пример с пустым OVER (). Почему он не работает?

Два примера запросов (1|2) Запрос с агрегирующей функцией SUM(): SELECT deptno, job, SUM(sal) sum_sal FROM emp Одна группа GROUP BY deptno, job; Результат исполнения: Значения полей, не определяющих группу не доступны План исполнения:

Два примера запросов (2|2) SELECT ename, deptno, job, SUM(sal) OVER (PARTITION BY deptno, job) sum_sal FROM emp; Обратите внимание на то, что аналитическая функция SUM() имеет то же имя, что агрегирующая функция, но после неё обязательно записывается фраза OVER(), которая считается транслятором входящей в состав имени. Для того, чтобы при распечатке имена столбцов были осмысленными, псевдоним обязателен. Значения полей не определяющих группу доступны. Специальный шаг WINDOW

Отличия групповых и аналитических функций

Синтаксис аналитических функций Аналитические функции имеют общую форму: analytic_function([ arguments ]) OVER ([analytic_clause]) Имя аналит. функции Фраза аналит. функции Аргументы могут быть любого арифметического типа или любого типа, который может быть неявно преобразован в арифметический тип. Возвращаемый результат приводится к типу приоритетного аргумента, за исключением случаев, когда иное указывается для отдельных функций. analytic_clause: : = [ query_partition_clause ][ order_by_clause [ windowing_clause ] ] Предложение OVER ([analytic_clause]) указывает, что функция будет обрабатывать результирующее множество, возвращаемое запросом. То есть аналитические функции обрабатывают данные после выполнения FROM, WHERE, GROUP BY и HAVING предложений запроса.

Где используются и как выполняются аналитические функции Аналитические функции можно использовать лишь в предложениях SELECT и ORDER BY. Аналитические функции не могут использоваться внутри фразы аналитической функции (analytic_clause). Аналитические функции не могут вкладываться. Аналитические функции выполняются после формирования набора выходных записей, за исключением заключительного ORDER BY предложения.

Фраза разбиения: query_partition_clause Для разбиения данных результирующего запроса на секции (группы) записей используется предложение PARTITION BY, содержащее одно или несколько выражений. Синтаксис фразы секционирования аналогичен синтаксису конструкции GROUP BY в обычных SQL-запросах: PARTITION BY выражение [, выражение] Если предложение PARTITION BY отсутствует, то все данные представляют одну секцию (группу). В запросе можно использовать несколько аналитических функций. Каждая из них может использовать своё разбиение данных результата на секции. В предложении PARTITION BY могут использоваться константы, столбцы таблиц, не аналитические функции, выражения, содержащие функции, или выражений, содержащие комбинацию из перечисленных элементов. Пример: SELECT ename, deptno, job, SUM(sal) OVER () sum_sal FROM emp; (единственной группой будет полный набор строк)

Фраза упорядочения order_by_clause (1/2) Первое представление. Определяет упорядочение данных внутри группы. Базовый синтаксис: ORDER BY <выражение>[ASC|DESC] [NULLS FIRST|NULLS LAST] где: – ASC|DESC определяет порядок сортировки. По умолчанию ASC. – оператор NULLS FIRST|NULLS LAST определяет позицию группы значений NULL в порядке. Если этот оператор отсутствует, то позиция значений NULL зависит от аргумента ASC или DESC. – По умолчанию NULL больше чем любое другое значение Присутствие ORDER BY влияет на порядок вычисления аналитических функций: • С ORDER BY множество строк, используемых при вычислении – текущая строка и все предшествующие строки в группе • Без ORDER BY при вычислении используются все строки группы

Фраза упорядочения: order_by_clause (2/2) Фраза order_by_clause упорядочивает данные внутри группы. В отсутствие конструкции ORDER BY среднее значение вычисляется по всей группе, и одно и то же значение выдается для каждой строки (функция используется как итоговая). Когда функция используется с конструкцией ORDER BY, то она применяется по текущей и всем предыдущим строкам (функция используется как оконная). Для всех аналитических функций за исключением PERCENTILE_CONT и PERCENTILE_DISC (которые допускают только один ключ сортировки), можно использовать ключ сортировки, состоящий из нескольких выражений. Для всех записей, имеющих одинаковые значения order_by_clause, аналитические функции возвращают один результат для всех записей. Фраза ORDER BY имеет следующий синтаксис: ORDER BY выражение [ASC | DESC] [NULLS FIRST | NULLS LAST] Необходимо учитывать, что строки будут упорядочены только в пределах групп. Конструкции NULLS FIRST и NULLS LAST указывает, где при упорядочении должны быть значения NULL — в начале или в конце.

Ограничения на фразу order_by_clause Внутри order_by_clause не допускается использование альтернативных имен столбцов и выражений предложения SELECT, а также использование номеров по порядку для колонок в предложении SELECT. Аналитические функции, использующие ключевое слово RANGE предложения windowing_clause (эта конструкция будет детально обсуждаться несколько позже) могут использовать составной ключ внутри ORDER BY предложения, если указывается одно из двух выражений внутри windowing_clause: • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW или сокращенная форма RANGE UNBOUNDED PRECEDING. • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING или сокращенная форма RANGE UNBOUNDED FOLLOWING. Аналитические функции всегда обрабатывают записи в порядке, указанном в order_by_clause функции. Однако сортировка данных запроса определяется предложением ORDER BY всего запроса, а не аналитических функций.

Пример с ORDER BY SELECT ename, deptno, job, SUM(sal) OVER (PARTITION BY deptno, job ORDER BY hiredate) sum_sal FROM emp; В новой группе начинаем новое упорядочение по job и новый счёт сумм

Разбираемся с упорядочениями Упорядочение в столбце running_total для единственной группы – всей таблицы SELECT ename, deptno, sal, SUM(sal) OVER (ORDER BY deptno, ename) running_total, SUM(sal) OVER (PARTITION BY deptno ORDER BY ename) department_total, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) seq FROM emp ORDER BY deptno, ename; Как всегда, задаёт упорядочение строк результата

Разбираемся с упорядочениями В первой сумме меняем столбцы deptno, ename местами, и вместо SUM(sal) OVER (ORDER BY deptno, ename) running_total, пишем SUM(sal) OVER (ORDER BY ename, deptno) running_total, и обнаруживаем непонятные результаты в столбце running_total. Это правильные результаты, но относятся они не к тому упорядочению, которое обозначено в двух первых столбцах. Проверьте это изменив общее упорядочение в последней стоке. Если набрать SUM(sal) OVER () running_total, то получим в каждой строке один и тот же результат 29025 – сумму по всей таблице.

Фраза окна: windowing_clause Фраза окна позволяет задать перемещающееся или жестко привязанное окно (набор, интервал) данных в пределах группы, с которым будет работать аналитическая функция. Синтаксис: {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}} Пример: конструкция диапазона RANGE UNBOUNDED PRECEDING означает: "применять аналитическую функцию к каждой строке данной группы, с первой по текущую".

Фраза окна: windowing_clause Стандартное окно, начинается с первой строки группы и продолжается до текущей строки. Очень важно: для использования окон необходимо задавать конструкцию ORDER BY. Фразы PRECEDING и FOLLOWING задают верхнюю и нижнюю границы агрегирования (то есть интервал строк, "окно" ). Возможны такие варианты окон: 1. Если нижняя граница окна фиксирована (совпадает с первой строкой упорядоченной некоторым образом группы строк), а верхняя граница ползет (совпадает с текущей строкой в этой группе), то получаем нарастающий итог (кумулятивный агрегат). Здесь и размер окна меняется (расширяется в одну сторону) и само окно движется. 2. Если нижняя и верхняя границы фиксированы (относительно текущей строки в этой группе, например, 1 строка до текущей и 2 строки после текущей), то получаем скользящий агрегат. Здесь размеры окна фиксированы , а само окно движется (скользит). 3. Частный случай 2. окно симметричное относительно текущей строки (например, 2 строки до текущей и 2 строки после текущей). Это тоже скользящий агрегат.

Пример вычислений с плавающим интервалом 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; Зарплата Ford’а Сумма для Ford’а и Scott’а Зарплата SMITH'а Сумма SMITH'а и ADAMS'а Зарплата ALLEN’а ALLEN+WARD+TURNER+ MARTIN

Интервалы агрегирования (1/3) Пример иллюстрирующий разницу между 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; В дальнейшем будем говорить об окнах диапазона и окнах строк. Окна диапазона это плавающие окна, для задания которых используется фразой ROWS, которая может применяться только к столбцам типа “дата” или “число”. Размер окна строк фиксированный, определяется заданием числа трок. Порядок строк в окне определяется фразой GROUP BY.

Интервалы агрегирования (2/3) JAMES и FORD поступили на работу одновременно, и с точки зрения интервала суммирования неразличимы. Поэтому суммирование "по значению" присвоило им один и тот же общий для "мини-группы", образованной этой парой, результат– максимальную сумму, которая при всех возможных порядках перечисления сотрудников внутри этой пары будет всегда одинакова. Суммирование "по строкам" (ROWS) поступило иначе: оно упорядочило сотрудников в "мини-группе", образованной равными датами (на самом деле чисто произвольно) и подсчитало суммы, как будто бы у этих сотрудников был задан порядок следования.

Интервалы агрегирования (3/3) План исполнения:

Интервалы времени в датах (1/2) Для интервалов (окон), упорядоченных внутри по значению ("логическом", 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;

Интервалы времени в датах (2/2) Другая формулировка позволяющая использовать для числа месяцев обычное числовое выражение: SELECT ename, hiredate, sal, AVG(sal) OVER (ORDER BY hiredate RANGE BETWEEN NUMTOYMINTERVAL(6, 'MONTH') PRECEDING AND CURRENT ROW) avg_sal FROM emp;

Классификация видов аналитических функций в Oracle 1. 2. 3. 4. Функции ранжирования (позволяющие строить запросы типа "первых N" ) Статистические функции для плавающего интервала (оконные функции). Итоговые функции. Похожи на оконные, но итоговые функции работают со всеми строками секции или группы. Ключевое отличие итоговой функции от оконной — отсутствие конструкции ORDER BY в операторе OVER. При отсутствии конструкции ORDER BY функция применяется к каждой строке группы. При наличии конструкции ORDER BY функция применяется к окну. Статистические функции, такие как VAR_POP, VAR_SAMP, STDEVJPOP, набор функций линейной регрессии и т. п. Эти функции позволяют вычислять значения статистических показателей для любой неупорядоченной секции.

I. Функции ранжирования Вычисляют значение ранга строки по RANK() вычисляет относительный отношению к другим строкам из той ранг каждой строки. DENSE_RANK вычисляет плотный же группы относительный ранг без промежут. Функции ранжирования: ков – RANK() и DENSE_RANK() – CUME_DIST(), PERCENT_RANK() и RATIO_TO_REPORT – NTILE() – ROW_NUMBER() Пример: SELECT sal, RANK() OVER (ORDER BY sal) AS rank, DENSE_RANK() OVER (ORDER BY sal) AS dense_rank FROM emp WHERE deptno IN (10, 30);

Промежуточное группирование (1/2) Одним запросом вычислить сумму зарплат по отдела и должностям, по всем отделам и по всем должностям. SELECT DECODE (GROUPING(dname), 1, 'All Depts', dname) AS dname, DECODE(GROUPING(job), 1, 'All Jobs', job) AS job, COUNT(*) "Total empl", sum(sal), RANK() OVER (PARTITION BY GROUPING(dname), GROUPING(job) ORDER BY sum(sal) DESC) AS rnk FROM emp JOIN dept USING (deptno) GROUP BY CUBE(dname, job); Подробности после изучения CUBE() !!

Промежуточное группирование (2/2)

Top-N значений Вывести двух самых высокооплачиваемых сотрудников: SELECT ename, sal, rownum RANK FROM (SELECT * FROM EMP ORDER BY sal DESC) WHERE ROWNUM <= 2; ENAME -----KING SCOTT SAL RANK -----------5000 1 3000 2 ENAME SELECT * FROM -----(SELECT ename, sal, KING FORD (SELECT COUNT(sal) SCOTT FROM emp e 1 WHERE (e 1. sal > e 0. sal))+1 AS rank FROM emp e 0 ORDER BY rank) WHERE rank <= 2; SAL RANK -----------5000 1 3000 2 SELECT ename, sal, rank FROM (SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) AS rank FROM emp) WHERE rank <= 2; ENAME -----KING SCOTT FORD SAL RANK -----------5000 1 3000 2 Одно значение (Ford 3000) потеряно С подзапросом С функцией RANK()

Функции подсчета долей RATIO_TO_REPORT(), CUME_DIST() и PERCENT_RANK() Функции подсчета долей позволяют в одном запросе получить для каждой строки ее "вес" в таблице в соответствии с ее значениями. Некоторые примеры: 1. Доли сотрудников в общей сумме зарплат SELECT ename, sal, RATIO_TO_REPORT(sal) OVER () AS salshare FROM emp; 2. Часть сотрудников, имеющая зарплату равную или меньшую, чем у текущего сотрудника SELECT job, ename, sal, CUME_DIST() OVER (PARTITION BY job ORDER BY sal) AS cume_dist FROM emp; 3. Ранжируем рез-т примера 2 по доле сотрудников в группе с помощью функции 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; Результаты запроса на следующем слайде

Функция подсчета долей 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; Ранги вычисляются с квантом 1 / (число_значений_в_группе - 1)

Функция NTILE() Справка: квантили Квантилем называется точка на числовой прямой, которая делит совокупность исходных наблюдений на две части с указанными пропорциями частей. Запись Кр читается: квантиль уровня (порядка) p. Показатель p лежит в диапазоне от 0 до 1. Частными случаями квантиля являются: медиана, квартили, децили и процентили. Медиана характеризуется одним квантилем К 0, 5 и делит всю совокупность наблюдений на две равные части. Квартиль делит исходную совокупность на две части, каждая из которых пропорциональна четвертым частям. Всего три квартиля: К 0, 25, К 0, 5 и К 0, 75 (они же Q 1, Q 2, Q 3). Дециль делит всю совокупность на десятые доли. Всего девять децилей D 1, … D 9. Существует ещё 99 процентилей P 1, … P 99.

Функция NTILE() Пример: Разделить сотрудников из таблицы EMP на четыре группы (количество элементов в каждом блоке заранее не известно) select ntile(4) over (order by empno) grp, empno, ename from emp; NTILE(4) разбивает упорядоченное множество на четыре сегмента. Если же количество записей не делится на 4 нацело, то записи «остатка» распределяются в доступные блоки, начиная с первого. В примере блоки 1 и 2 включают по 4 строки, блоки 3 и 4 – по три.

Функция ROW_NUMBER() (1/2) ROW_NUMBER нумерует строки, возвращаемые запросом. С ее помощью можно выполнить упорядочение строк более сложное чем то, которое дает ORDER BY. Можно: • задать свою нумерацию строк, отличающуюся от порядка сортировки строк результата; • создать "несквозную" нумерацию, свою в каждой выделенной группе строк; • использовать одновременно несколько способов нумерации, поскольку создаваемая нумерация не зависит от сортировки строк запроса.

Функция ROW_NUMBER() (2/2) Своя нумерация, отличная от упорядочения результата: SELECT job, sal, row_number() over(ORDER BY empno) num FROM emp ORDER BY job, sal; Пронумеровать сотрудников отдельно в каждом отделе: SELECT job, sal, deptno, row_number() over(partition BY deptno ORDER BY job, sal) num FROM emp ORDER BY job, sal;

II. Оконные функции Вспомните введённые ранее понятия окна диапазонов и окна строк. Ключевое отличие двух классов аналитических функций -- итоговых от оконных - отсутствие конструкции ORDER BY в операторе OVER. При отсутствии конструкции ORDER BY функция применяется к каждой строке группы. При наличии конструкции ORDER BY функция применяется к окну в целом. Окна диапазона Если задано, например, "range 5 preceding", то это означает, что будет создано перемещающееся окно, включающее предыдущие строки группы, отстоящие от текущей строки не более чем на 5 строк. Вспомним, что создания такого окна используется фраза RANGE применимая только к выражениям типов дата или число. Пример: Найти все строки, у которых значение в поле HIREDATE больше значения HIREDATE в текущей строке, но не более чем на 100 дней.

Пример оконной функции select ename, sal, hiredate -100 windowtop, first_value(ename) over (order by hiredate asc range 100 preceding) ename_prec, first_value(hiredate) over (order by hiredate asc range 100 preceding) hiredate_prec from emp Начало окна order by hiredate asc; окно Аналитическая функция FIRST_VALUE возвращает вычисленное значение для первой строки окна, позволяя понять, где начинается окно. Замените ASC на DESC. Что изменилось?

III. Итоговые функции

Rollup -- расширение фразы Group by Фраза Group by позволяет разбить промежуточный итог на группы по значениям одного или многих столбцов: SELECT deptno, job, SUM(sal) FROM emp GROUP BY deptno, job ORDER BY 1, 2 Получаем один уровень группировки. Для получения итогов по отделам и общего итога необходимо написать ещё два запроса и соединить их с помощью UNION. А можно использовать опцию ROLLUP в GROUP BY:

Опция ROLLUP в GROUP BY SELECT deptno, job, SUM(sal) FROM emp GROUP BY ROLLUP(deptno, job) ORDER BY 1, 2 И теперь у нас к прежним данным добавлены итоги по отделам и общий итог. Итог отд. 10 Итог отд. 20 ПРАВИЛО: Если в ROLLUP входят n столбцов, то выполняется n+1 уровней обобщений. Итог отд. 30 Общий итог

Варьируем ROLLUP SELECT deptno, job, SUM(sal) FROM emp GROUP BY ROLLUP(deptno), job ORDER BY 1, 2; группировка по deptno, job Уровней обобщения два, для обобщения использована группировка по job Контрольный пример: SELECT job, deptno, SUM(sal) FROM emp GROUP BY ROLLUP('A'), job, deptno ORDER BY 1, 2

Опция CUBE в GROUP BY Заменяем ROLLUP на CUBE: SELECT deptno, job, SUM(sal) FROM emp GROUP BY CUBE(deptno, job) ORDER BY 1, 2; Как это работает? SELECT deptno, job, SUM(sal) FROM emp GROUP BY CUBE('A'), deptno, job ORDER BY 1, 2; CUBE добавил суммы по второму столбцу как с ROLLUP

Функция GROUPING() SELECT deptno, job, SUM(sal), grouping(deptno) as dept_group, grouping(job) as job_group FROM emp GROUP BY ROLLUP(deptno, job) ORDER BY 1, 2; Нет job Нет deptno Нет job

DECODE() и GROUPING() SELECT DECODE(grouping(deptno), 1, 'Total sum', deptno) as dept_group, DECODE(grouping(job), 1, 'Total per dept', job) as job_group, deptno, job, SUM(sal) Сопоставьте! FROM emp GROUP BY ROLLUP(deptno, job) ORDER BY 1, 2; Задание: Измените запрос так, чтобы в первой строке в Job_group не появлялась ненужная запись Total per dept, а в остальных указывался номер отдела

Функция GROUPING_ID (1/2) C версии 9 можно использовать ещё функцию GROUPING_ID, возвращающую тот же результат, что и GROUPING, но в виде битового вектора. Применяется только в запросах с расширениями GROUP BY, то есть ROLLUP, CUBE и GROUPING. Позволяет избежать множественного группирования. Пример: SELECT GROUPING_ID ( mgr, job ), mgr, job, SUM ( sal ), COUNT ( * ) FROM emp GROUP BY ROLLUP ( mgr, job );

Функция GROUPING_ID (2/2)

GROUPING SETS Первый пример: select deptno, job, count(*) as staff_quantity from emp group by grouping sets (deptno, job); Оказывается, Group By Rollup (a, b, c) ≡ Group by grouping sets (a, b, c), (a, b), (a)(). Проверьте это на запросе: select deptno, job, count(*) as staff_quantity from emp group by rollup (deptno, job)

GROUPING SETS и CUBE Group By Cube == Group by grouping sets (a, b, c)(a, b)(b, a)(b, c)(a)(b)(c)() select dept, job_title, count(*) as staff_quantity from emp_data group by cube (dept, job_title) ;

Конструкция MODEL появилась в версии Oracle 10 g. MODEL позволяет представлять результат запроса в виде многомерного куба и задавать выражения для расчета его произвольных ячеек.

dimension by и measures Фраза dimension by определяет размерности гиперкуба. Фраза measures задаёт измеряемую величину. Простейший пример: всего лишь выделение двух столбцов empno –как dimension, ename – как measure; действия над данными (rules) не предусмотрены. select empno, ename from emp t model dimension by (empno) measures (ename) rules () order by empno;

Ограничения на использование алиасов Нельзя использовать алиасы из SELECT условия: select empno as id, ename from emp model dimension by (id) measures (ename) rules () order by empno; ORA-00904: "ID": invalid identifier Но в dimension by использовать алиасы можно: select id, ename from emp model dimension by (empno * 10 as id) measures (ename) rules () order by id;

DIMENSION BY должен дать адрес ячейки В противном случае возникает ошибка: select job, ename from emp model dimension by (job) measures (ename) rules () order by job; ORA-32638: Non unique addressing in MODEL dimensions Но можно использовать UNIQUE SINGLE REFERENCE select job, ename from emp model UNIQUE SINGLE REFERENCE dimension by (job) measures (ename) rules () order by job; Как это скажется на адресации формул?

MEASURES определяет столбцы используемые для доступа к данным. Количество столбцов не менее одного. Можно записывать константы и выражения. Пример: select empno, ename, sal, date_now from emp model dimension by (empno) measures (ename, sal * 100 as sal, sysdate as date_now) rules () order by empno;

Ограничения на DIMENSION и MEASURES (1/2) 1. Нельзя обращаться к столбцу отсутствующему в dimension by и measures 2. Нельзя использовать столбец и в dimension by и measures. Пример: select empno, ename from emp model dimension by (empno) measures (empno, ename) rules () order by empno; ORA-00957: duplicate column name 3. Нельзя использовать NULL и столбцы нулевой длины. Примеры: select empno, ename from emp model dimension by (empno, null as test) measures (ename) rules () order by empno; ORA-01723: zero-length columns are not allowed

Ограничения на DIMENSION и MEASURES (2/2) Пример к п. 3: select empno, ename from emp model dimension by (empno) measures (ename, '' as test) rules () order by empno; ORA-01723: zero-length columns are not allowed 4. С NULL следует использовать CAST. Пример: select empno, ename, test from emp model dimension by (empno) measures (ename, CAST( NULL AS VARCHAR 2(10) ) as test) rules () order by empno; Напоминание: функция CAST преобразует данные к указанному типу

Правила RULES (1/3) Фраза rules описывает формулы для расчёта значений measures. Из предыдущих примеров видно, что список формул в rules может быть пустым. Формулы позволяют изменять любые значения measures. Пример одномерного массива: select empno, job, ename from emp model dimension by (empno) measures (job, ename) rules( ename[7839] ='Ну-ну!', ename[empno < 7788] = 'Тыбы', ename[empno BETWEEN 7900 and 7902 ] = 'И эти' ) order by empno; Обратите внимание, ename[7839] указывает адрес ячейки в одномерном массиве. В остальных правилах задаются диапазоны.

Правила RULES (2/3) Структура правил предыдущего примера: 1. ename[7839] называется “cell reference” и определяет значение ename, для которого ключ из dimension by, то есть empno равен 7839. Для присваивания используется название “cell assignment”. 2. Часть условия заключенная в квадратные скобки [7839], или [empno < 7788] называется “dimension reference” и может содержать как константы, так и различные условия. Для присваивания (cell asignment) можно использовать ключевое слово ANY – любое значение. Пример: select empno, job, ename from emp model dimension by (empno) measures (job, ename) rules ( ename[any] = 'Имя' ) order by empno;

Правила RULES (2/3) Можно использовать вложенные cell references, но только для определения одной ячейки: select empno, job, ename from emp model dimension by(empno) measures(job, ename, empno as empno 2) rules (ename[(empno 2[7900] + 2) ] = null ) order by empno;

Правила RULES (3/3) using_equals_null from (select 'A' id from dual union all Если значение dimension is null, select null from dual) то оно будет попадать под model следующие условия • dimension by( id ) [any] • measures ( [group_1 is any] • 'N' as using_any , [null] • 'N' as using_is_any , [group_1 is null] 'N' as using_null , 'N' as using_is_null , Условие типа “[group_1 = null]” не 'N' as using_equals_null ) сработает. rules ( using_any [ ANY ] = 'Y' , using_is_any [ id IS ANY ] = 'Y' , using_null [ NULL ] = 'Y' , using_is_null [ id IS NULL ] = 'Y' , using_equals_null[ id = NULL ] = 'Y' );

Циклы с dimension references Без циклов: select deptno, dname, loc from dept model dimension by(deptno) measures(dname, loc) rules( loc[20] = 'SPB', loc[30] = 'SPB', loc[40] = 'SPB' ) order by deptno; Результат: (1/2) С циклом: select deptno, dname, loc from dept model dimension by(deptno) measures(dname, loc) rules( loc [FOR deptno FROM 20 TO 40 INCREMENT 10] = 'SPB' ) order by deptno; Вариант цикла: [FOR deptno in (20, 30, 40) ] = 'SPB'

Циклы с dimension references Ещё пример: with t as (select 'W-'||rownum as id, ename, job from emp) select * from t model dimension by(id) measures(ename, job) rules( job [FOR id LIKE 'W-%' FROM 2 TO 14 INCREMENT 2 ] = null ) (2/2)

Разрешение дубликатов ключей select * from emp model dimension by (job) measures (ename, sal) rules () order by job; select * from emp model UNIQUE SINGLE REFERENCE dimension by (job) measures (ename, sal) rules () order by job; ORA-32638: Non unique addressing in MODEL dimensions Значение ANY указывает на весь куб. Например, rules (sal[any]=9999)

Ограничение на UNIQUE SINGLE REFERENCE Ограничение: нельзя в правой части правила (rule) указывать ссылку на неуникальную ячейку: select * from emp model UNIQUE SINGLE REFERENCE dimension by (job) measures (ename, sal) rules (sal[any]= sal['MANAGER']) order by job; ORA-32638: Non unique addressing in MODEL dimensions

Функция CV() Функцию CV() дает доступ к текущему значению dimension select * from emp model dimension by (empno) measures (job, ename, 0 sub_empno) rules ( sub_empno[any] = cv(empno) * 10 ) order by empno; Попробуйте вариант: measures (job, ename, sub_empno)

Использование функции CV() Можно использовать функцию CV() без аргумента для возврата текущего значения dimension относительно позиции в cell reference. select * from emp model dimension by (empno, job) measures (ename, empno as sub_empno) rules (sub_empno[any, any] = sub_empno[cv(), cv()]*10) order by empno; В CV() нельзя использовать значение measures: select * from emp model dimension by (empno) measures (job, ename, empno as sub_empno) rules ( sub_empno[any] = cv(sub_empno) * 10 ) order by empno; ORA-00904: : invalid identifier

Использование функции CV() Вне dimension references нельзя использовать функцию CV() без аргумента: select * from emp model dimension by (empno) measures (job, ename, 0 sub_empno) rules ( sub_empno[any] = cv() * 10 ) order by empno; ORA-32611: incorrect use of MODEL CV operator

PARTITON BY Позволяет ускорить обработку данных на многопроцессорных системах и в некоторых случаях сокращает код в dimension references. select * from emp model partition by (deptno) dimension by (job, ename) measures (sal, sal as new_sal) rules ( new_sal['CLERK', any] = sal[cv(), cv()] * 2, new_sal['MANAGER', any] = sal[cv(), cv()] * 3, new_sal['SALESMAN', any] = sal[cv(), cv()] * 4 ) order by deptno, job, ename;

Использование процедурных свойств MODEL select job , substr( str, 2 ) as str from emp t model return updated rows partition by (job) dimension by (row_number() over (partition by job order by job) as position) measures (cast(sal as varchar 2(200)) as str)rules upsert iterate( 4 )until ( presentv(str[iteration_number+2], 1, 0) = 0 ) ( str[0] = str[0] || ', ' || str[iteration_number+1] )order by job;

Функция объединения строк LISTAGG как агрегатная функция (c версии 11. 2) LISTAGG как агрегатная функция обрабатывает и возвращает данные для каждой группы определенной в GROUP BY : select deptno, listagg( ename, '; ' ) within group (order by ename) enames from emp group by deptno order by deptno; DEPTNO ENAMES --------------10 CLARK; KING; MILLER 20 ADAMS; FORD; JONES; SCOTT; SMITH 30 ALLEN; BLAKE; JAMES; MARTIN; TURNER; WARD

Функция объединения строк LISTAGG как аналитическая функция Аналитическая ф. LISTAGG обрабатывает данные разбитые на блоки, задаваемые одним или несколькими выражениями query_ partition_clause.