SQL в хранилищах данных: аналитическая обработка данных

Цель лекции

Изучив материал настоящей лекции, вы будете знать:

и научитесь:

Литература: [38], [42].

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

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

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

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

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

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

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

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

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

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

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

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. Агрегатные и статистические функции

Пусть в ХД имеется таблица фактов "Население" ( .

Оператор 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.

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

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 — создан.

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

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. В СУБД семейства 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.

Пусть необходимо для двух позиций счетов 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 рекомендуется применять в случаях, если:

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

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

Пример 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 = 'Восток';

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

Вывод 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.

, который ранжирует товары на основе их продаж в рублях в каждом регионе ( 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. Для того чтобы быть уверенным в детерминистическом результате, вы должны упорядочить результирующее множество по уникальному ключу.