Опубликован: 20.12.2010 | Доступ: свободный | Студентов: 2381 / 140 | Оценка: 4.27 / 3.91 | Длительность: 39:39:00
ISBN: 978-5-9963-0353-3
Лекция 17:

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

< Лекция 16 || Лекция 17: 12345 || Лекция 18 >
Аннотация: В настоящей лекции рассматривается расширение диалектов SQL промышленных СУБД для аналитической обработки данных в хранилищах данных. Изучаются статистические функции, ранжирующие функции, оконные функции в диалекте Transact-SQL СУБД MS SQL Server 2008. Разбираются примеры использования, в том числе для формирования отчетов и построения гистограмм.

Цель лекции

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

  • что такое аналитические функции в SQL;
  • что такое статистические функции в Transact-SQL;
  • что такое ранжирующие функции в Transact-SQL;
  • что такое функции генерации отчетов в Transact-SQL;

и научитесь:

  • использовать предложение OVER ;
  • использовать статистические функции ;
  • использовать оконные функции ;
  • использовать ранжирующие функции;
  • применять предложение CASE для построения диаграмм.

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

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

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

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

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

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

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

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

Таблица 23.1. Аналитические функции и их использование
Тип Использование
Ранжирование Вычисление рангов, проценталей в результирующем множестве
Оконные функции Вычисление кумулятивных и скользящих средних: SUM, AVG, MIN, MAX, COUNT, и т.д.
Генерирование отчетов Формируют результирующие множества для построения отчетов. Работа с функциями: SUM, AVG, MIN, MAX, COUNT и т.д.
Статистические функции Вычисляют средние, дисперсии и т.д. по результирующему множеству

Для выполнения этих операций добавлено несколько элементов в обработку команд 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.
Таблица 23.2. Агрегатные и статистические функции
Функция Возвращаемое значение
AVG() Возвращает среднее арифметическое группы значений. Значения NULL не учитываются. Возвращаемый тип определяется типом вычисленного результата expression.
AVG ( [ ALL | DISTINCT ] expression )
CHECKSUM_AGG() Возвращает контрольную сумму значений в группе. Значения NULL не учитываются. Возвращает контрольную сумму всех значений expression как int.
CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )
COUNT(*) Возвращает количество элементов в группе. Функция COUNT всегда возвращает значение типа int.
COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )
Выражение может быть любого типа, за исключением text, image или ntext. Статистические функции и вложенные запросы запрещены
COUNT_BIG Возвращает количество элементов в группе. Функция COUNT_BIG всегда возвращает значение типа bigint.
COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * )
Выражение может быть любого типа. Статистические функции и вложенные запросы запрещены
GROUPING Указывает, является указанное выражение столбца в списке GROUP BY статистическим или нет. В результирующем наборе функция GROUPING возвращает 1 (статистическое выражение) или ноль (нестатистическое выражение). Функция GROUPING может использоваться только в предложениях SELECT <список выбора>, HAVING и ORDER BY, если указано предложение GROUP BY.
GROUPING ( <column_expression> )
MAX() Возвращает максимальное значение в выражении. Возвращает такое же значение, как и expression.
MAX ( [ ALL | DISTINCT ] expression )
Выражение может быть константой, именем столбца или функцией, а также любым сочетанием арифметических, побитовых и строковых операторов. Функцию MAX можно использовать с колонками типа numeric, character и datetime, но не с колонками типа bit. Статистические функции и вложенные запросы не применяются
MIN() Возвращает минимальное значение выражения. Возвращает такое же значение, как и expression.
MIN ( [ ALL | DISTINCT ] expression )
Выражение может быть константой, именем столбца или функцией, а также любым сочетанием арифметических, побитовых и строковых операторов. Функцию MAX можно использовать с колонками типа numeric, character и datetime, но не с колонками типа bit. Статистические функции и вложенные запросы не применяются.
SUM() Возвращает сумму всех (либо только уникальных) значений в выражении. Функция SUM может быть использована только для числовых колонок. Сумма всех значений выражения expression представлена в наиболее точном формате данных, используемом в выражении expression.
SUM ( [ ALL | DISTINCT ] expression )
Выражение может быть константой, столбцом или функцией, а также любым сочетанием арифметических, побитовых и строковых операторов. Аргумент expression является выражением относительно точных или приближенных данных любого числового типа, за исключением типа bit. Статистические функции и вложенные запросы не применяются
STDEV() Возвращает статистическое стандартное отклонение всех значений в указанном выражении. Возвращаемый тип - float.
STDEV ( [ ALL | DISTINCT ] expression )
STDEVP() Возвращает статистическое среднеквадратичное отклонение совокупности всех значений в указанном выражении. Возвращаемый тип - float.
STDEVP ( [ ALL | DISTINCT ] expression )
VAR() Возвращает статистическую дисперсию всех значений в указанном выражении. Возвращаемый тип - float.
VAR ( [ ALL | DISTINCT ] expression )
VARP() Возвращает статистическую дисперсию для заполнения всех значений в указанном выражении. Возвращаемый тип - float.
VARP ( [ ALL | DISTINCT ] expression )

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

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

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

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

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

Рис. 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
Таблица 23.3. Содержание таблицы фактов "Население" (Population)
Region State Population
Восточный округ МО 31878234
Южный округ МА 19128261
Северный округ КФ 18184774
Южный округ СР 14399985
Северный округ ПК 7987933
Западный округ ВО 7322870
Восточный округ КП 5337939
Центральный округ ТЛ 5358592
Западный округ ЧЕ 5071604
Центральный округ ФС 3300902

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

Вывод 1.

Region Minimum Maximum Average Variance
Восточный округ 5532939 31878234 18705586 347037284318512.5
Южный округ 14399985 19128261 16764123 11118296966088.0
Северный округ 7987933 18184774 13086353 51987783189640.5
Западный округ 5071604 7322870 6197237 25340993C1378.0
Центральный округ 3300902 5358692 4329797 2117249842050.0

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

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

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

Рис. 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.

Quota TotalSales Grouping
NULL 1533087.5999 0
250000.00 33461260.59 0
300000.00 9299677.9445 0
NULL 44294026.1344 1

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

Медианы

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

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

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

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

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

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

Рис. 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 в зависимости от знака.

< Лекция 16 || Лекция 17: 12345 || Лекция 18 >
Владислав Нагорный
Владислав Нагорный

Подскажите, пожалуйста, планируете ли вы возобновление программ высшего образования? Если да, есть ли какие-то примерные сроки?

Спасибо!

Лариса Парфенова
Лариса Парфенова

1) Можно ли экстерном получить второе высшее образование "Программная инженерия" ?

2) Трудоустраиваете ли Вы выпускников?

3) Можно ли с Вашим дипломом поступить в аспирантуру?