В настоящей лекции мы сконцентрируем внимание на тех возможностях, которые предоставляет SQL по аналитической обработке данных в ХД. В изложении материала и приводимых примерах будем придерживаться диалекта SQL СУБД Oracle 11g.
Расширение SQL аналитическими функциями тесно связано с практическими задачами бизнес-анализа - вычисление скользящего среднего, ранжирование выборки, и т. д. Решение таких задач в «стандартном» SQL требуют большого объема программирования. Это обстоятельство и привело разработчиков СУБД к реализации в SQL набора функции для решения задач подобного типа, которые получили название аналитических функций.
Аналитические функции подразделяются на следующие основные категории:
Ниже приведена краткая характеристика категорий аналитических функций и их наименования.
Порядок обработки (Processing Order). Запросы, использующие аналитические функции, обрабатываются в три стадии. Первая включает все соединения, WHERE, GROUP BY и HAVING. Вторая - применение аналитических функций к результирующему множеству. Третья - если есть предложение ORDER BY, то оно обрабатывается.Для выполнения этих операций добавлено несколько элементов в обработку команд SQL. Эти элементы встроены в SQL. Существует несколько новых понятий, используемых аналитическими функциями:
Синтаксис вызова аналитической функции имеет вид:
Имя_функции (список аргументов) OVER (определение фрагментации, определение упорядоченности, определение окна)
Ключевое слово OVER в SQL определяет функцию как аналитическую. Три конструкции после OVER определяют структуру данных, с которой будет работать аналитическая функция. Конструкция фрагментации определяет разбиение результирующего множества на секции (группы строк), к которым будет применяться аналитическая функция. Конструкция упорядочивания задает условия сортировки строк в секции. Конструкция окна задает параметры окна, в соответствии с которым просматриваются строки секций. Эти конструкции могут быть обязательными или необязательными в зависимости от типа функции.
Внимание! Если ключевое слово OVER для некоторых функций используется ключевое слово WITHIN GROUP, то функция становится агрегирующей.
Примеры использования аналитических функций к учебной базе данных HR заимствованы из документации Oracle 11g. При определении аналитических функций мы также будет следовать документации по SQL Oracle 11g. Методически изложение построено, как в Проектирование хранилищ данных для систем бизнес - аналитики: Учебное пособие /В.Е. Туманов — М.: Интернет-Университет Информационных Технологий: БИНОМ. Лаборатория знаний, 2010.
В предыдущем разделе были использованы некоторые термины математической статистики. Напомним определения этих терминов для использования в дальнейшем изложении. Материал этого раздела может быть опущен, если вы знакомы с понятиями математический статистики. Материал настоящего раздела заимствован из учебника Кремер Н.Ш. Теория вероятностей и математическая статистика: учебник для студентов вузов, обучающихся по экономическим специальностям/ Н.Ш. Кремер. - 3-е изд., перераб. и доп. - М.: ЮНИТИ-ДАНА, 2010.
Случайной величиной называется величина, которая в результате испытания из множества возможных своих значений принимает только одно, причём заранее неизвестно, какое именно. Случайные величины бывают дискретными и непрерывными. Дискретной случайной величиной называется случайная величина, которая может принимать конечное или счетное число различных значений. Непрерывной случайной величиной называется случайная величина, все возможные значения которой заполняют некоторый промежуток числовой прямой.
Генеральная совокупность, по определению, представляет все множество объектов, в отношении которого будут проводиться исследования. Предполагается, что объекты можно охарактеризовать как случайные величины.
Выборка – это ограниченная по численности группа объектов из генеральной совокупности для изучения ее свойств.
С каждым элементом выборки (i-ой реализации xi значения случайной величины X) можно связать относительную частоту как отношение его появления ni в ней к общему количеству элементов N. При увеличении количества элементов в выборке она стремиться к вероятности появления значения случайной величины (события):
Дискретную случайную величину можно полностью задать вероятностным рядом, указав вероятность рi для каждого значения хi.
Законом распределенияслучайной величины называют любое соотношение, устанавливающее связь между возможными значениями случайной величины и соответствующими им вероятностями. Вероятностный ряд является одним из видов законов распределения случайной величины.
Кумулятивная функция распределения определяет накопленные на конец каждого интервала частоты (вероятности), т.е. приближается в пределе к функции плотности распределения случайной величины.
Вместо полного определения случайной величины в виде законов распределения вероятностей в прикладных задачах ее часто определяют при помощи числовых характеристик — чисел, выражающих характерные особенности случайной величины, называемых моментами случайной величины.
Модой называется значение случайной величины, которое встречается наиболее часто. Моде соответствует наибольший подъем (вершина) графика распределения частот.
Медиана - это такое значение признака, которое делит упорядоченное (ранжированное) множество данных пополам так, что одна половина всех значений оказывается меньше медианы, а другая – больше.
Математическое ожидание (выборочное среднее) случайной величины (начальный момент первого порядка):
Для случайных величин математическое ожидание является теоретической величиной, к которой приближается среднее значение xi случайной величины Х при большом количестве испытаний.
Дисперсией (вторым центральным моментом) случайной величины называется математическое ожидание квадрата отклонения случайной величины от ее математического ожидания (выборочная дисперсия):
или, если известно истинное значение математического ожидания (дисперсия генеральной совокупности)
Дисперсия играет важную роль при статистических расчетах и является мерой рассеяния значений х около их математического ожидания. Корень квадратный из второго центрального момента (вычисленного по первой формуле) называется средним квадратичным отклонением (стандартным отклонением, или стандартом):
Во многих случаях, приступая к анализу имеющихся у нас данных, мы можем их только сравнить по принципу их упорядоченности, например, «больше-меньше», «лучше-хуже», «часто-реже» и т.д. В подобных случаях, можно отказаться от анализа конкретных значений данных и перейти к исследованию их взаимной упорядоченности. Для этого от исходных числовых данных осуществляют переход к их рангам.
Рангом наблюдения называют тот номер, который получит это наблюдение в упорядоченной совокупности всех данных — после их упорядочения по определенному правилу, а процедура перехода от совокупности наблюдений к последовательности их рангов называется ранжированием. Результат ранжирования называется ранжировкой.
Процентиль это такое значение измеренной величины, которое делит всю совокупность измерений так, что ниже этого значения находится заданный процент от всех ее значений, т.е. на уровне какого значения и ниже находится заданный процент значений. Обратный процентиль по значению измеренной величины определяет ее процентный ранг.
Ковариация определяется как мера линейной зависимости между двумя измеренными величинами. Для двух выборок случайных величин X и Y объемом N ее значение определяется формулой:
где
Если ковариация между X и Y равна нулю, то эти случайные величины независимы. Поскольку ковариацию можно выразить через дисперсию, то она имеет две формы – выборочную и для генеральной совокупности.
Корреляция - это мера зависимости между двумя и более случайными величинами. Данная зависимость выражается через коэффициент корреляции r. Коэффициент корреляции принимает значения от -1 до +1. Чем выше значение коэффициента корреляции, тем больше зависимость между величинами. Корреляция бывает выборочной и теоретической (для генеральной совокупности), в зависимости какая формула дисперсии используется. Формула для вычисления коэффициента корреляции:
Линейная регрессия можно рассматривать как математическую модель зависимости переменной от одной или нескольких других переменных (факторов, регрессоров, независимых переменных) с линейной функцией зависимости:
Коэффициентом детерминации называется квадрат коэффициента корреляции выборки. Коэффициент детерминации оценивает долю дисперсии (изменчивости) Y, которая объясняется с помощью X в простой линейной регрессионной модели. Коэффициенты линейного уравнения вычисляются методом наименьших квадратов по формулам:
Приведенных определений достаточно для понимания работы статистических функций в SQL Oracle 11g. Для статистических функций, описываемых в следующих разделах, будут приведены формулы, по которым выполняет расчет статистических показателй.
Окно — это набор строк, определяемый пользователем. Оконная функция вычисляет значение для каждой строки в результирующем наборе, полученном из окна. Они могут быть использованы только в предложениях SELECT и ORDER BY запроса. С помощью «окна» SQL обеспечивает доступ более чем к одной строке таблицы без самосоединения.
Предложение OVER определяет секционирование (разбиение на группы результирующего множества в соответствие с заданным критерием) и упорядочение набора строк, в частности, для применения соответствующей оконной функции.
Синтаксис:
Для ранжирующих оконных функций
< OVER_CLAUSE >::= OVER ( [PARTITION BY value_expression, … [n] ] <ORDER BY expression [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}]>)
Для агрегатных функций
< OVER_CLAUSE >::= OVER ( [PARTITION BY value_expression, … [n] ]
Опция PARTITION BY разделяет результирующий набор на секции. Аналитическая функция применяется к каждой секции отдельно, и вычисление начинается заново для каждой секции. Если это предложение опущено, функция интерпретирует все результирующее множество как одну группу.
Значение value_expression указывает столбец, по которому секционируется набор строк, произведенный соответствующим предложением FROM. Аргумент value_expression может ссылаться только на столбцы, доступные через предложение FROM. Аргумент value_expression не может ссылаться на выражения или псевдонимы в списке выбора. Выражение value_expression может быть выражением столбца, скалярным вложенным запросом, скалярной функцией или пользовательской переменной.
Предложение ORDER BY задает порядок строк в результирующем множестве для используемой функции. Если предложение ORDER BY используется в контексте ранжирующей оконной функции, оно может ссылаться только на столбцы, доступные через предложение FROM. Указывать положение имени или псевдонима столбца в списке выборки с помощью целого числа нельзя. Предложение ORDER BY не может использоваться со статистическими оконными функциями.
В одном запросе с одним предложением FROM может использоваться несколько статистических или оконных функций. Однако предложение OVER для каждой функции может использовать свое секционирование и упорядочение. Семантика NULL-значений оконных функций соответствует семантике NULL-значений агрегатных функций SQL.
Приведем простой пример, показывающий смысл предложения OVER. Пусть необходимо составить список количественного состава подразделений организации c указанием табельных номеров сотрудников в каждом подразделении. Тогда можно выполнить такой запрос к таблице EMPLOYEES схемы HR с использованием OVER.
SELECT employee_id, department_id, COUNT(*) OVER (PARTITION BY department_id) DEPT_COUNT FROM EMPLOYEES;
Будет получено следующее результирующе множество:
EMPLOEEY_ID DEPARTMENT_ID DEPT_COUNT 200 10 1 201 20 2 202 20 2 114 30 6 115 30 6 116 30 6 117 30 6 118 30 6 119 30 6 203 40 1 ….. 178 NULL 1
Запрос без использования OVER
SELECT employee_id, department_id, COUNT(*) DEPT_COUNT FROM EMPLOYEES GROUP BY department_id, employee_id ORDER BY department_id;
приведет к следующему результату:
EMPLOEEY_ID DEPARTMENT_ID DEPT_COUNT 200 10 1 201 20 1 202 20 1 114 30 1 115 30 1 116 30 1 117 30 1 118 30 1 119 30 1 203 40 1 ….. 178 NULL 1
Из примера видно, что предложение PARTITION BY разбивает результирующе множество на подмножества строк, соответствующие номеру подразделения. Функция COUNT(*) применяет к каждому из этих подмножеств.
Во втором случае предложение GROUP BY разбивает результирующее множество на подмножества, определяемое сочетанием значений колонок department_id и employee_id (причем колонку employee_id нельзя убрать, Oracle выдаст ошибку).
Рассмотрим на примере, как работают агрегатные функции SQL с использованием окон. Пусть требуется общую сумму выплаченных сотрудникам денег, среднюю зарплату, максимальную и минимальную зарплату по подразделениям. Следующий запрос к таблице EMPLOYEES схемы HR решает поставленную задачу.
SELECT department_id ,SUM(Salary) OVER(PARTITION BY department_id) AS "Итого" ,AVG(Salary) OVER(PARTITION BY department_id) AS "Среднее" ,COUNT(Salary) OVER(PARTITION BY department_id) AS "Кол-во" ,MIN(Salary) OVER(PARTITION BY department_id) AS "Min" ,MAX(Salary) OVER(PARTITION BY department_id) AS "Max" FROM EMPLOYEES;
Результат выполнения запроса (Фрагмент)
DEPARTMENT_ID Итого Среднее Кол-во Min Max 10 4400 4400 1 4400 4400 20 19000 9500 2 6000 13000 20 19000 9500 2 6000 13000 30 24900 4150 6 2500 11000 30 24900 4150 6 2500 11000 30 24900 4150 6 2500 11000 30 24900 4150 6 2500 11000 30 24900 4150 6 2500 11000 30 24900 4150 6 2500 11000 40 6500 6500 1 6500 6500 50 156400 3475,56 45 2100 8200 ….
Рассмотрим, как определятся окна для аналитических функций в SQL для команды SELECT. Результат выполнения команды SELECT – это таблица (результирующее множество), состоящее из строк и колонок. Окно является механизмом выделения группы строк в результирующем множестве для обработки их с использование определенной функции. Таким образом, окно задается на результирующем множестве, как группа строк.
При определении окна необходимо определить, как будет связано окно с результирующем множеством, т.е. каким образом будет создаваться группа строк, образующая окно. В диалекте Oracle SQL предлагается два основных способа создания окна: по диапазону (RANGE) значений данных или по смещению (ROWS) относительно текущей строки (по количеству строк).
Поскольку порядок строк в результирующем множестве при использовании функции имеет значение, то при использовании окон необходимо задавать предложение ORDER BY.
Синтаксис определения окна
{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 задают верхнюю и нижнюю границы агрегирования (то есть интервал строк, "окно" для агрегирования).
Ключевое слово RANGE указывает, что окно будет создано «по диапазону», а ключевое слово ROW – «по количеству строк». Для окон, задаваемых по количеству строк, используемые выражения могут быть любого типа и упорядочивать можно по любому количеству столбцов. Для окон, задаваемых по диапазону, используемые выражения должны быть числового типа.
Оператор UNBOUNDED PRECEDING определяет окно, которое начинается с первой строки текущей секции и заканчивается текущей обрабатываемой строкой.
Оператор UNBOUNDED FOLLOWING определяет окно, которое начинается на текущей строке и заканчивается на последней строке текущей секции.
Оператор CURRENT ROW окно, которое начинается и заканчивается текущей строкой.
Выражение PRECEDING определяет окно, которое начинается со строки, задаваемой значением <выражение>, до текущей (окно по количеству строк) или со строки, меньшей по значению столбца, упомянутого в предложении ORDER BY, но не более чем на значение числового выражения (окно по диапазону).
Выражение FOLLOWING задает окно, которое заканчивается (или начинается) со строки, задаваемой значением <выражение>, после текущей (окно по количеству строк) или со строки, большей по значению столбца, упомянутого в конструкции предложении ORDER BY, не более чем на значение числового выражения (окно по диапазону).
Начальную и конечную строку окна в конструкции BETWEEN можно задавать с использованием любой из перечисленных выше конструкций.
С конструкцией окна могут быть использованы функции SUM, AVG, MIN, MAX, COUNT, VARIANCE, STDDEV, FIRST_VAL, LAST_VALUE и другие статистические функции. Опция DISTINCT поддерживается только для MAX и MIN. Такие функции называют оконными функциями. Примеры использования окон в командах SELECT будут приводиться при подробном описании функций. Здесь приведем пример для создания отчета, показывающего сумму зарплат текущего и двух предыдущих сотрудников отдела. Запрос к таблице EMPLOYEES схемы HR может быть следующим:
SELECT department_id, last_name, salary, SUM(salary) OVER (PARTITION BY department_id ORDER BY last_name ROWS 2 PRECEDING) total FROM EMPLOYEES; ORDER BY department_id, ename
Фрагмент вывода
DEPARTMENT_ID LAST_NAME SALARY TOTAL 10 Whalen 4400 4400 20 Fay 6000 6000 20 Hartstein 13000 19000 30 Baida 2900 2900 30 Colmenares 2500 5400 30 Himuro 2600 8000 30 Khoo 3100 8200 30 Raphaely 11000 16700 30 Tobias 2800 16900 …..
Фраза ROWS 2 PRECEDING определяет окно, состоящее из трех строк.
Для получения официальных документов о завершении программы дополнительного профессионального образования (удостоверения о повышении квалификации, дипломов о профессиональной переподготовке и MBA) необходимо предоставить:
Внимание! Вы можете не заказывать доставку бумажной версии официального документы, а скачать его в электронном виде и распечатать самостоятельно. Информация о выданном документе в течение 1 месяца загружается в Федеральную информационную систему «Федеральный реестр сведений о документах об образовании и (или) о квалификации, документах об обучении» - ФИС ФРДО.
Доступ на новый сайт осуществляется с использованием адреса электронной почты, который был указан вами при регистрации на "старом". Мы постарались перенести все ваши данные с прежнего ресурса, однако не исключена вероятность потери части информации.
При возникновении проблемы со входом, воспользуйтесь функцией сброса пароля
Если вы обнаружите несоответствия, пожалуйста, сообщите нам.