Аналитическая обработка данных в хранилищах данных (аналитические, статистические , ранжирования, оконные и функции для генерирования отчетов)

Привет, Вы узнаете о том , что такое аналитическая обработка данных, Разберем основные их виды и особенности использования. Еще будет много подробных примеров и описаний. Для того чтобы лучше понимать что такое аналитическая обработка данных, аналитическая обработка данных в хранилищах данных, аналитические функции, статистические функции, функции ранжирования, оконные функции, функции для генерирования отчетов , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.

SQL для анализа данных

В настоящей лекции мы сконцентрируем внимание на тех возможностях, которые предоставляет SQL по аналитической обработке данных в ХД. В приводимых примерах будем придерживаться диалекта Transact-SQL СУБД семейства MS SQL Server.

Отметим, что в целом SQL не имеет хорошей поддержки для решения аналитических задач. Однако встроенная поддержка аналитических функций в Transact-SQL и его ориентированность на работу с множествами делают его хорошим инструментом для выполнения таких вычислений над данными, хранящимися в ХД. аналитические функции и ориентированность на работу с множествами дают Transact-SQL определенные преимущества перед другими языками манипулирования данными.

Основные вычисления в системах бизнес-аналитики — вычисление скользящего среднего, ранжирование выборки, и т. д. — требуют большого объема программирования в стандартном SQL. Функции такого типа называются аналитическими функциями.

Аналитические функции подразделяются на следующие категории:

• статистические функции ;
• функции ранжирования ;
• оконные функции ;
• функции для генерирования отчетов .

В табл. 23.1 приведена краткая характеристика категорий аналитических функций.

Для выполнения этих операций добавлено несколько элементов в обработку команд SQL. Эти элементы встроены в SQL. Существует несколько новых понятий, используемых аналитическими функциями.

• Порядок обработки (Processing Order). Запросы, использующие аналитические функции, обрабатываются в три стадии. Первая включает все соединения, WHERE, GROUP BY и HAVING. Вторая — применение аналитических функций к результирующему множеству. Третья — если есть предложение ORDER BY, то оно обрабатывается.
• Секционирование результирующего множества (Result Set Partitions). Аналитические функции позволяют пользователю разделить результирующее множество запроса на группы строк, называемых секциями. Термин "секция" (partitions) используется в другом смысле, отличающемся по значению от того, что использовался при секционировании таблицы. Он применяется для обозначения групп, которые создаются после группировки предложением GROUP BY. Эти секции строятся в соответствии со значением колонки группировки.
• Окна (Window). Для каждой строки в секции вы можете определить скользящее окно данных (sliding window of data). Это окно определяет интервал строк, используемых для вычислений от текущей строки. Размер окна может быть задан либо как физическое число строк, либо как логическое число строк (условием). Окно имеет начальную строку (starting row) и конечную строку (ending row). В зависимости от определения окно может перемещаться по результирующему множеству в один или оба конца. Например, окно, определенное для функции кумулятивных сумм, будет иметь своей стартовой строкой первую строку секции, и ее конечная строка будет скользить от начальной точки через все к последней строке секции. В противоположность этому, окно, определенное для скользящего среднего, будет иметь и начальную, и конечную точки, скользящие так, чтобы захватывать постоянный физический или логический интервал.
• Текущая строка (Current Row). Каждое вычисление с аналитическими функциями основывается на текущей строке в секции. Текущая строка поддерживается как ссылка на строку (позицию) внутри окна. Например, вычисление центрированного скользящего среднего может потребовать определения окна с 5-ю строками, предшествующими текущей, и 6-ю следующими за ней. Окно будет иметь размер в 12 строк.

Агрегатные и статистические функции

В Transact-SQL поддерживаются девять различных агрегатных функций, которые необходимы при проведении статистических расчетов. Помимо агрегатных функций, которые уже упоминались в предыдущей лекции, — SUM(), MIN(), MAX(), COUNT() и AVG() — имеются еще четыре, непосредственно предназначенные для финансовых и статистических вычислений: STDEV(), STDEVP(), VAR(), VARP() (табл. 23.2).

Статистические функции выполняют вычисление на наборе значений и возвращают одиночное значение. За исключением функции COUNT, эти функции не учитывают значения NULL. Также они часто используются в выражении GROUP BY команды SELECT.

Эти функции могут использоваться в качестве выражений только в следующих случаях:

• список выбора инструкции SELECT (вложенный или внешний запрос);
• предложение COMPUTE или COMPUTE BY ;
• предложение HAVING.

AVG ( [ ALL | DISTINCT ] expression )

CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * )

GROUPING ( <column_expression> )

MAX ( [ ALL | DISTINCT ] expression )

MIN ( [ ALL | DISTINCT ] expression )

SUM ( [ ALL | DISTINCT ] expression )

STDEV ( [ ALL | DISTINCT ] expression )

STDEVP ( [ ALL | DISTINCT ] expression )

VAR ( [ ALL | DISTINCT ] expression )

VARP ( [ ALL | DISTINCT ] expression )

Указание ALL применяет функцию ко всем значениям. ALL является аргументом по умолчанию, а DISTINCT указывает, что рассматривается каждое уникальное значение.

Рассмотрим пример, в котором применяются встроенные агрегатные функции для вычисления основных статистических параметров.

Пример 23.1. Агрегатные и статистические функции

Пусть в ХД имеется таблица фактов "Население" ( Population ), содержание которой приведено в табл. 23.3, и таблица измерений "Район" ( Region ), физическая структура которых приведена на рис. 23.1.

Рис. 23.1. Физическая структура таблицы фактов "Население" (Population) и таблицы измерений "Район" (Region)

Оператор SELECT, приведенный ниже, иллюстрирует применение агрегатных и статистических функций.

SELECT Region. MIN(Population) AS Minimum, MAX(Populations)AS Maximum, 
AVG(Population) AS Average. VAR(Population) AS Variance
FROM Region 
GROUP BY Region 
ORDER BY Maximum DESC

Результат выполнения запроса приведен ниже.

Вывод 1.

Пример 23.2. Использование GROUPING

Предположим, что нам необходимо произвести статистическую обработку значений колонки "Объем продаж" (Sale), сгруппированных по колонке "Квота" (Quota) в таблице "Продажи" (Sales), структура которой показана на рис. 23.2. Функция GROUPING применяется к колонке Quota.

Рис. 23.2. Физическая структура таблицы "Продажи" (Sales)

Следующий запрос решает поставленную задачу:

SELECT Quota, SUM(Sale) 'TotalSales', GROUPING(Quota) AS 'Grouping'
FROM Sales
GROUP BY Quota WITH ROLLUP;
GO

В результирующем наборе показаны два значения NULL в колонке Quota. Первое значение NULL представляет группу значений NULL из этого столбца в таблице. Второе значение NULL находится в строке итогов, добавленной операцией ROLLUP. Строка итогов показывает суммы TotalSales для всех групп Quota и обозначается 1 в столбце Grouping.

Результат выполнения запроса приведен ниже.

Вывод 2.

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

Медианы

Задачи позиционирования записей или задачи нахождения записей на основании их физического расположения в выборке исторически были сложными для решения средствами SQL. Найти запись по значению в языках, ориентированных на работу с множествами, довольно просто; найти же запись по позиции является сложной задачей.

Задача нахождения медиан представляет собой задачу позиционирования записей. Если в выборке нечетное количество записей, значение медианы равно значению записи, расположенной точно в середине выборки. Выше и ниже этого значения существует одинаковое количество элементов. Если в выборке четное количество значений, значение медианы равно либо среднему двух центральных значений (в случае финансовых медиан), либо меньшему из них (в случае статистических медиан).

Задача позиционирования записей значительно упрощается, когда в таблице есть уникальный последовательный целый ключ (identity-колонка). Если это так, ключ становится виртуальным номером записи, и имеется возможность обращаться к записям в любой позиции таблицы как к массиву. За счет этого мы можем вычислять медианы практически мгновенно даже для выборок, состоящих из миллионов значений.

Пример. 16.3. Вычисление медианы.

Пусть в ХД имеется таблица фактов "Финансы" (Finance), содержащая несколько миллионов значений цен на товары. Физическая структура таблицы приведена на рис. 23.3. Колонка ID создана с квалификатором типа identity. Записи отсортированы по значению колонки "Цена" (Price) за счет создания кластеризованного индекса, для чего была добавлена колонка ID, т.е. записи были просто перенумерованы. Индекс на колонку "Цена" (Price) был удален, а на колонку ID — создан.

Рис. 23.3. Физическая структура таблицы фактов "Финансы" (Finance)

Следующий запрос вычисляет финансовую медиану:

SELECT AVG(Price) AS "Финансовая медиана" FROM finance 
WHERE ID BETWEEN MAX(ID) / 2 AND (MAX(ID) / 2) + SIGN(MAX{(ID) +1 % 2)

Функция SIGN() используется для того, чтобы сделать обработку четного и нечетного числа записей в одном предложении BETWEEN. Выражение с этой функцией добавляет к количеству записей в выборке 1, чтобы изменить его с четного на нечетное или наоборот, затем вычисляет остаток от деления на число 2, чтобы понять, четное или нечетное число, и возвращает 1 или 0 в зависимости от знака.

Оконные функции

Оконные функции определены в стандарте ISO SQL. Об этом говорит сайт https://intellect.icu . В СУБД семейства MS SQL Server предоставляются ранжирующие и статистические оконные функции. Окно — это набор строк, определяемый пользователем. Оконная функция вычисляет значение для каждой строки в результирующем наборе, полученном из окна.

Оконные функции могут использоваться для вычисления кумулятивных, скользящих и центрированных агрегатов. Они возвращают значение для каждой строки в таблице, которое зависит от других строк соответствующего окна. Они могут быть использованы только в предложениях SELECT и ORDER BY запроса. Как правило, оконные функции обеспечивают доступ к более чем одной строке таблицы без самосоединения.

Предложение OVER

Предложение OVER определяет секционирование и упорядочение набора строк до применения соответствующей оконной функции. В качестве оконных функций используются агрегатные и статистические ( SUM, AVG, MAX, MIN, COUNT ), ранжирующие функции. Каждая из ранжирующих функций ROW_NUMBER, DENSE_RANK, RANK и NTILE задействует предложение OVER (см. сл. раздел настоящей лекции).

Синтаксис:

• Для ранжирующих оконных функций

  < OVER_CLAUSE > ::= OVER ( [PARTITION BY value_expression, … [n] ] <ORDER BY expression>)
• Для агрегатных функций

  < 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 для каждой функции может применять свое секционирование и упорядочение. Предложение OVER не может работать со статистической функцией CHECKSUM.

Семантика NULL-значений оконных функций соответствует семантике NULL-значений агрегатных функций SQL.

Статистические оконные функции

Покажем на примере, как используются статистические оконные функции.

Пример. 16.4. Статистические оконные функции.

Пусть в ХД имеется таблица фактов "Позиции счетов" (OrderDetail), содержащая номер позиции (OrderID), идентификатор товара (ProductID), количество товара (OrderQt) и стоимость товара (Price). Физическая структура таблицы приведена на рис. 23.4.

Рис. 23.4. Физическая структура таблицы фактов "Финансы" (Finance)

Пусть необходимо для двух позиций счетов 43659 и 43664 посчитать для каждого проданного товара общее количество проданного товара, среднее количество каждого проданного товара, минимальное и максимальное количество проданного товара.

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

SELECT OrderID, ProductID, OrderQty
	,SUM(OrderQty) OVER(PARTITION BY OrderID) AS 'Итого'
	,AVG(OrderQty) OVER(PARTITION BY OrderID) AS 'Среднее' 
	,COUNT(OrderQty) OVER(PARTITION BY OrderID) AS 'Кол-во'
	,MIN(OrderQty) OVER(PARTITION BY OrderID) AS 'Min'
	,MAX(OrderQty) OVER(PARTITION BY OrderID) AS 'Max'
FROM OrderDetail
WHERE OrderID IN(43659,43664);
GO

Результат выполнения запроса приведен ниже.

Вывод 3.

Пусть руководство организации требует подсчитать процент проданного товара по позиции 43659. Следующий запрос с использованием оконных функций решает поставленную задачу.

SELECT OrderID, ProductID, OrderQty
	,SUM(OrderQty) OVER(PARTITION BY OrderID) AS 'Итого'
	,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY 	OrderID) *100 AS DECIMAL(5,2))AS 'Процент проданного товара'
FROM OrderDetail
WHERE OrderID = 43659;

Результат выполнения запроса приведен ниже.

Вывод 4.

Как видно из рассмотренных примеров, использование предложения OVER является более эффективным, чем использование вложенных запросов. Применение оконных ранжирующих функций будет рассмотрено в следующем разделе.

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

Функции ранжирования вычисляют ранг записи по отношению к другим записям в наборе данных, основываясь на значении набора метрик (measures). Они возвращают ранжирующее значение для каждой строки в секции. В зависимости от используемой функции значения некоторых строк могут совпадать.

Типы ранжирующих функций приведены в табл. 23.4.

Функции RANK и DENSE_RANK

Функции RANK и DENSE_RANK позволяют ранжировать итемы в группе, например, найти top 3 товаров, продаваемых в Калифорнии в последний год. Существуют две функции ранжирования с синтаксисом:

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

< partition_by_clause > ([PARTITION BY <value expression1> [, ...]]) делит результирующий набор, полученный с помощью предложения FROM, на секции, к которым применяется функция RANK.

< order_by_clause > ( ORDER BY <value expression2> [collate clause] [ASC|DESC] ) определяет порядок, в котором значения RANK применяются к строкам в секции. Целое значение не может представлять столбец < order_by_clause > при использовании его в ранжирующей функции.

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

Порядок сортировки, используемый для всего запроса, определяет порядок, в котором строки будут появляться в результирующем наборе.

DENSE_RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

< partition_by_clause > делит результирующий набор, полученный с помощью предложения FROM, на секции, к которым применяется функция DENSE_RANK.

< order_by_clause > определяет порядок, в котором значения DENSE_RANK применяются к строкам секции. Целое число не может представлять столбец в <order_by_clause>, который используется в ранжирующей функции.

Если две или более строки одной секции равны при ранжировании, каждой такой строке присваивается один и тот же ранг. Например, если двум лучшим продавцам соответствует одинаковое значение объема продаж, им обоим присваивается ранг 1. Менеджеру по продажам со следующим по величине значением объема продаж назначается ранг 2. Это на единицу больше, чем число разных рангов строк, расположенных перед этой строкой. Таким образом, между номерами, возвращаемыми функцией DENSE_RANK, нет промежутков, и они всегда имеют последовательные значения ранга.

Порядок сортировки строк в результате определяется порядком сортировки результата всего запроса. Из этого следует, что строка с рангом 1 не всегда является первой строкой в секции.

Таким образом, различие между RANK и DENSE_RANK состоит в том, что DENSE_RANK не оставляет промежутков в ранжируемой последовательности.

Функцию RANK рекомендуется применять в случаях, если:

• порядок сортировки для этих функций является принятым по умолчанию порядком сортировки - по возрастанию;
• выражения в предложении PARTITION BY делят результирующее множество запроса на группы, с которыми оперируют, функция RANK переопределяется при переходе к следующей группе. Фактически, <value expression> предложения PARTITION BY определяет границы действия функции;
• предложение PARTITION BY пропущено, тогда ранг вычисляется для всего результирующего множества запроса;
• предложение ORDER BY определяет метрики ( <value expressions> ), на которых вычисляется ранг, определяет порядок, в котором строки сортируются в каждой группе или секции. Когда данные сортируются в каждой секции, ранги даются для каждой строки, начиная с 1;
• <value expression2> может быть любым допустимым выражением, включающим ссылки на колонки, агрегаты или выражения, составленные из перечисленных элементов.

Рассмотрим ряд примеров применения ранжирующих функций.

Можно выполнять ранжирование по нескольким выражениям. Функции ранжирования необходимы для нахождения связи между значениями на множестве записей. Если первое выражение не может выявить связь, то второе используется для выявления связи и т.д.

Пример 23.5. Использование функции RANK().

Путь нужно ранжировать товар, основываясь на продажах в рублях в каждом регионе, разрывая связь с доходом. Физическая схема киоска данных приведена на рис. 23.5.

На схеме представлены таблица фактов "Продажи" (sale) с метриками "Количество" (s_amount) и "Прибыль" (s_profit); две таблицы измерений "Регион" (region) и "Товар" (product).

Следующий запрос решает поставленную задачу:

SELECT r_regionkey, p_productkey, s_amount, s_profit, 
  RANK() OVER (ORDER BY s_amount DESC, s_profit DESC) AS 'Ранг по востоку'
FROM region, product, sales
WHERE region.r_regionkey = sales.s_regionkey AND product.p_productkey = sales.s_productkey AND r_regionkey = 'Восток';

Рис. 23.5. Физическая схема киоска данных

Результат выполнения запроса приведен ниже.

Вывод 5.

Для жакетов и брюк колонка "Доход" (s_profit) показывает связь с колонкой "Количество" (s_amount). Но для свитеров и рубашек колонка "Доход" не может установить связь с колонкой "Количество". Следовательно, им присвоен один и тот же ранг.

Различие между RANK() и DENSE_RANK() может быть показано с помощью запроса к таблице фактов "Продажи":

SELECT р_productkey, SUM(s_amount) as 'Суммарное количество', 
  RANK() OVER (ORDER BY SUM(s_amount) DESC) AS 'rank_all', 
  DENSE_RANK() OVER (ORDER BY SUM(s_amount) DESC) AS 'rank_dense'
FROM sales
GROUP BY р_productkey;

Результат выполнения запроса приведен ниже.

Вывод 6.

Как видно по результирующему множеству, в случае функции DENSE_RANK() наибольшее значение ранга равно числу различных значение в наборе данных.

Функция RANK() может быть применена для операции в группах, т.е. ранг переустанавливается при изменении группы данных. Это важно для опции PARTITION BY. Группирующее выражение в PARTITION BY выделяет подклассы данных в наборе данных, с которыми работает RANK.

Пример 23.6. Необходимо ранжировать товары в каждом регионе по их продаже в рублях. Можно написать запрос для схемы на рис. 23.5, который ранжирует товары на основе их продаж в рублях в каждом регионе ( rank_of_product_per_region ) и во всех регионах ( rank_of_product_total ).

SELECT r_regionkey, p_productkey, SUM(s_amount) AS 'SUM_S_AMOUNT', 
RANK() OVER (PARTITION BY r_regionkey ORDER BY SUM(s_amount) DESC)
AS 'rank_of_product_per_region',
RANK() OVER (ORDER BY SUM(s_amount) DESC) AS 'rank_of_product_total'
FROM product, region, sales
WHERE region.r_regionkey = sales.r_regionkey AND product.p_productkey = sales.p_productkey
GROUP BY r_regionkey, p_productkey
ORDER BY r_regionkey;

Результат выполнения запроса приведен ниже.

Вывод 7.

Функция NTILE

Функция NTILE() делит упорядоченную секцию на указанное число групп, называемых бакетами ( buckets ), и назначает номер бакета каждой строке в секции. Для каждой строки функция NTILE() возвращает номер группы, которой принадлежит строка. NTILE() — очень полезная функция, поскольку дает возможность разделить набор данных на 40, 30 или другое число групп.

Бакеты вычисляются так, чтобы каждый из них имел одинаковое число строк, назначенное ему, или на одну больше. Например, если есть 100 строк в секции и находится функция NTILE для 4 бакетов, то 25 строк будет назначаться каждому бакету.

Если число строк в секции не делится нацело (на число бакетов), то число строк, назначаемое каждому бакету в начале, будет отличаться по крайней мере на 1. Например, если есть 103 строки в секции, к которой применяется функция NTILE(5), первые 21 строк будут назначены 1 бакету, следующие 21 — 2 бакету, следующие 21 — 3 бакету, следующие 20 — 4 бакету и последние 20 — 5 бакету.

Синтаксис:

NTILE (integer_expression)    OVER ( [ <partition_by_clause> ] < order_by_clause > )

integer_expression - положительное целое выражение-константа, указывающее количество групп, на которые необходимо разделить каждую секцию. Аргумент integer_expression может иметь тип int или bigint.

<partition_by_clause> делит результирующий набор, сформированный предложением FROM.

< order_by_clause > определяет порядок назначения значений функции NTILE строкам секции.

Аргумент integer_expression может ссылаться только на столбцы в предложении PARTITION BY и не может ссылаться на столбцы, перечисленные в текущем предложении FROM.

Пример 23.7. Использование функции NTILE()

Пусть нам нужно провести группировку проданных товаров по объему продаж, используя схему на рис. 23.5. Это можно сделать с помощью следующего запроса:

SELECT p_productkey, s_amount, 
   NTILE(4) (ORDER BY s_amount DESC AS '4_tile'
FROM product, sales
WHERE product.p_productkey = sales.p_productkey;

Результат выполнения запроса приведен ниже.

Вывод 8.

NTILE() является недетерминистической функцией. Равные значения могут распределяться различным бакетам (75 назначено бакетам 2 и 3); бакеты '1', '2' и '3' имеют по 3 элемента — на один больше, чем бакет '4'. В результирующем множестве "джинсы" можно было бы назначить бакету 2 (вместо 3) и "свитеры" — бакету 3 (вместо 2), потому что не существует упорядочивания по колонке p_productkey. Для того чтобы быть уверенным в детерминистическом результате, вы должны упорядочить результирующее множество по уникальному ключу.

Функция ROW_NUMBER

Функция ROW_NUMBER() назначает уникальный номер (последовательно, начиная с 1, в порядке, определенном ORDER BY ) каждой строке в секции.

Синтаксис:

ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

<partition_by_clause> делит результирующий набор, полученный по предложению FROM.

<order_by_clause> определяет порядок, в котором значение функции ROW_NUMBER назначается строкам в секции. Целое число не может представлять столбец, если аргумент <order_by_clause> используется в ранжирующей функции.

Предложение ORDER BY определяет последовательность, в которой строкам назначаются уникальные номера с помощью функции ROW_NUMBER в пределах указанной секции.

Пример 23.8. Использование функции ROW_NUMBER ()

Пусть нам нужно провести группировку проданных товаров по объему продаж, используя схему на рис. 23.5. Это можно сделать с помощью следующего запроса:

SELECT p_productkey, s_amount, 
     ROW_NUMBER() (ORDER BY s_amount DESC) AS srnum
FROM product, sales
WHERE product.p_productkey = sales.p_productkey;

Результат выполнения запроса приведен ниже.

Вывод 8.

Свитерам, джинсам и ремням (с s_amount = 75) назначаются различные номера строк (5, 6, 7).

Подобно функции NTILE(), функция ROW_NUMBER() является недетерминистической функцией, так что "свитеры" мог бы получить номер строки 7 (вместо 5), а "ремни" — 5 (вместо 7). Чтобы избежать подобных ситуаций, необходимо сортировать результирующее множество по уникальному ключу.

Функции, генерирующие отчеты

После того как запрос выполнен, значения агрегатов (типа количество строк в результирующем множестве или среднее значение в колонке) могут быть вычислены для секции и быть доступными для других отчетов. Агрегатные функции генерирования отчетов (Reporting aggregate functions) возвращают значения агрегатов для каждой строки в секции. К агрегатным функциям генерирования отчетов относятся функции SUM(), AVG(), MAX(), MIN(), COUNT(), использующее предложение OVER. Их поведение относительно NULL-значений такое же, как и в агрегатных функциях SQL.

В предыдущих разделах настоящей лекции мы уже обсуждали такое применение агрегатных функций. Поэтому в настоящем разделе приведем только несколько примеров.

Функции генерирования отчетов допустимы только для предложений SELECT. Основное их назначение состоит в способности выполнять многократный разбор блока данных результирующего множества запроса. Запросы типа "Подсчитать число продавцов, у которых уровень продаж больше на 10% от числа продаж по городу" не требуют соединений между отдельными блоками запроса.

Пример 23.9. Использование агрегатных функций для генерирования отчетов

Пусть нам нужно для каждого товара найти регион, в котором наблюдается максимальный уровень продаж каждого товара, используя схему на рис. 23.5. Это можно сделать с помощью следующего запроса:

SELECT s_productkey, s_regionkey, sum_s_amount
FROM
  (SELECT p_productkey, r_regionkey, SUM(s_amount) AS 'sum_s_amount',
        MAX(SUM(s_amount)) OVER 
           (PARTITION BY p_productkey) AS 'max_sum_s_amount' 
   FROM sales
   GROUP BY p_productkey, r_regionkey) 
WHERE sum_s_amount = max_sum_s_amount;

Данные внутреннего запроса к таблице фактов "Продажи" (sales), сгруппированные по колонкам p_productkey и p_regionkey, агрегируются для первых трех колонок, и функция MAX(SUM(s_amount)) возвращает результат.

Вывод 9.

Результат выполнения внешнего запроса приведен ниже.

Вывод 10.

Пример 23.10. Использование агрегирующих и ранжирующих функций для генерации отчета.

Более сложным является пример вычисления первых 10-ти (top 10) продаж той линейки товаров, которая имеет вклад более 10% в продажи товаров этой категории. Физическая схема для таблиц, используемых для решения этой задачи, приведена на рис. 23.6. Первая колонка является ключом для всех таблиц запроса.

Рис. 23.6. Физическая схема для задачи примера 16.10

CASE-выражения и создание гистограмм

Синтаксис предложения CASE и некоторые его применения были рассмотрены в "Проектирование и разработка процесса ETL" . С помощью предложения CASE можно легко найти среднюю зарплату всех сотрудников организации (если зарплата меньше 2000 руб., то она принимается равной 2000 руб.), как показано в запросе ниже:

SELECT AVG(CASE when e.sal > 2000 THEN e.sal ELSE 2000 end) FROM emps e;

Таблица "Сотрудники" (emps) включает в себя колонки "Табельный номер" (empid), "ФИО" (name), "Должность" (job) и "Зарплата" (sal), как показано на рис. 23.7.

Рис. 23.7. Физическая структура таблицы "Сотрудники"

Предложение CASE можно использовать при построении диаграмм для определенных пользователем бакетов (и по числу бакетов и по ширине каждого бакета). В первом примере ниже строится гистограмма итоговых сумм (totals) в нескольких колонках и выводится одной строкой. Во втором примере гистограмма показывается с колонкой метки, одной колонкой для итоговой суммы (totals) и выводится с помощью нескольких строк.

Пример 23.11. Построение гистограммы.

Пусть необходимо построить гистограмму распределения покупателей по возрастным группам для людей преклонного возраста. Возрастные группы определяются следующими условиями: 70-79, 80-89, 90-99, 100+.

Следующий запрос решает поставленную задачу (предполагается, что таблица "Покупатели" (customer) содержит колонку "Возраст" (age)):

SELECT
SUM(CASE WHEN age BETWEEN 70 AND 79 THEN 1 ELSE 0 END) as "70-79",
SUM(CASE WHEN age BETWEEN 80 AND 89 THEN 1 ELSE 0 END) as "80-89",
SUM(CASE WHEN age BETWEEN 90 AND 99 THEN 1 ELSE 0 END) as "90-99",
SUM(CASE WHEN age > 99 THEN 1 ELSE 0 END) as "100+"
FROM customer;

Результат выполнения внешнего запроса приведен ниже.

Вывод 11.

Следующий запрос решает ту же задачу, но выводит гистограмму в виде "столбика".

Пример 23.12.

SELECT
CASE WHEN age BETWEEN 70 AND 79 THEN '70-79'
   WHEN age BETWEEN 80 and 89 THEN '80-89'
   WHEN age BETWEEN 90 and 99 THEN '90-99'
   WHEN age > 99 THEN '100+' END) as age_group,
COUNT(*) as age_count
FROM customer
GROUP BY 
CASE WHEN age BETWEEN 70 AND 79 THEN '70-79'
   WHEN age BETWEEN 80 and 89 THEN '80-89'
   WHEN age BETWEEN 90 and 99 THEN '90-99'
   WHEN age > 99 THEN '100+' END);

Результат выполнения внешнего запроса приведен ниже.

Вывод 12.

Статистические функции в других диалектах SQL

В заключение нужно отметить, что некоторые СУБД имеют в своих диалектах SQL боле широкий набор статистических функций, оконных функций, функций ранжирования и функций генерирования отчетов. Например, в СУБД Oracle 11g имеется набор функций для построения линейной регрессии.

Приведем в качестве примера краткий обзор функций построения линейной регрессии для СУБД семейства Oracle.

Функции регрессий устанавливают соответствие по обычному методу наименьших квадратов (ordinary-least-squares) — линию регрессии для набора пар чисел. Данные функции применяются к набору пар of (e1, e2) после проверки всех пар на равенство нулевому значению (либо e1, либо e2)l. e1 — значение зависимой переменной ("y-значение"), а e2 — значение независимой переменной ("x-значение"). Оба выражения должны быть числовыми. Функции регрессии вычисляются за один проход по всем данным.

Перечень функций линейной регрессии дан в табл. 23.5.

Пример 23.13. Вычисления линейной регрессии.

В примере вычисляется регрессия по методу наименьших квадратов, которая показывает премиальные сотрудников как линейную функцию их зарплаты. Величины SLOPE, ICPT, RSQR — наклон, пересечение и коэффициент детерминации регрессии, соответственно. Значения AVGSAL и AVGBONUS есть средняя зарплата и средний размер премий сотрудников, соответственно, а целочисленная величина CNT есть число сотрудников в отделе, для которого вычисления проводятся.

Рис. 23.8. Физическая схема таблицы "Сотрудники" (Employee)

Пусть имеется таблица "Сотрудники" (Employee) с колонками "Табельный номер" (EMPNO), "ФИО" (NAME), "Отдел" (DEPT), "Зарплата" (SALARY), "Премии" (BONUS) ( рис. 23.8). Таблица содержит восемь сотрудников (табл. 23.6).

Следующий запрос вычисляет параметры линейной регрессии.

SELECT  REGR_SLOPE(BONUS, SALARY) SLOPE,
        REGR_INTERCEPT(BONUS, SALARY) ICPT,
        REGR_R2(BONUS, SALARY) RSQR,
        REGR_COUNT(BONUS, SALARY) COUNT,
        REGR_AVGX(BONUS, SALARY) AVGSAL,
        REGR_AVGY(BONUS, SALARY) AVGBONUS,
        REGR_SXX(BONUS, SALARY) SXX,
        REGR_SXY(BONUS, SALARY) SXY,
        REGR_SYY(BONUS, SALARY) SXY
FROM employee
GROUP BY dept;

Результаты выполнения запроса приведены ниже.

Вывод 13.

Резюме

В настоящей лекции мы изучили ряд функций SQL, используемых при аналитической обработке данных в ХД и киосках данных. Это статистические функции, ранжирующие функции и оконные функции.

Статистические функции выполняют вычисление на наборе значений и возвращают одиночное значение. Они, за исключением функции COUNT, не учитывают значения NULL. Также они часто используются в выражении GROUP BY команды SELECT.

Функции ранжирования вычисляют ранг записи по отношению к другим записям в наборе данных, основываясь на значении набора метрик таблиц фактов ХД. Они возвращают ранжирующее значение для каждой строки в секции. В зависимости от используемой функции значения некоторых строк могут совпадать.

Оконные функции могут использоваться для вычисления кумулятивных, скользящих и центрированных агрегатов. Они возвращают значение для каждой строки в таблице, которое зависит от других строк соответствующего окна. Они могут быть использованы только в предложениях SELECT и ORDER BY запроса. Как правило, оконные функции обеспечивают доступ более чем к одной строке таблицы без самосоединения.

Был также рассмотрен ряд примеров применения вышеперечисленных функций для генерирования отчетных данных, а в сочетании с CASE-выражением — для построения гистограмм.

Проектировщику ХД очень полезно знать возможности аналитической обработки данных в SQL для более качественного проектирования схем данных ХД и определения гранулированности таблиц фактов.

Вау!! 😲 Ты еще не читал? Это зря!

• хранилища данных
• оконные функции

Исследование, описанное в статье про аналитическая обработка данных, подчеркивает ее значимость в современном мире. Надеюсь, что теперь ты понял что такое аналитическая обработка данных, аналитическая обработка данных в хранилищах данных, аналитические функции, статистические функции, функции ранжирования, оконные функции, функции для генерирования отчетов и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL