SQL в хранилищах данных: агрегация и суммирование

Цель лекции

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

и научитесь:

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

Введение

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

Единственным средством общения и администраторов БД, и проектировщиков, и разработчиков, и пользователей с реляционной БД является структурированный язык запросов SQL (Structured Query Language). SQL есть полнофункциональный язык манипулирования данными в реляционных БД. В настоящее время он является общепризнанным, стандартным интерфейсом для реляционных БД, таких как Oracle, Sybase, DB/2, MS SQL Server и ряда других (стандарты ANSI и ISO).

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

Теоретические основы SQL были заложены в известной статье [Кодд], положившей начало развитию теории реляционных БД. Первая практическая реализации была выполнена в исследовательских лабораториях фирмы IBM Chamberlin D.D. и Royce R.F. Промышленное применение SQL было впервые реализовано в СУБД Ingres. Одной из первых промышленных реляционных СУБД является Oracle. По сути дела, реляционная СУБД – это программное обеспечение, которое управляет работой реляционной БД.

Первый международный стандарт языка SQL был принят в 1989 г. (SQL-89). В конце 1992 г. был принят новый международный стандарт SQL-92. В настоящее время большинство производителей реляционных СУБД используют его в качестве базового. Однако работы по стандартизации языка SQL далеки от завершения, и уже разработан проект стандарта SQL-99, который вводит в обиход языка понятие объекта и разрешает на него ссылаться в операторах SQL. В исходном варианте SQL не было команд управления потоком данных, они появились в принятом стандарте ISO/IEC 9075-5: 1996 дополнительной части SQL.

Каждой конкретной СУБД соответствует своя собственная реализация SQL, в целом поддерживающая определенный стандарт, но имеющая свои особенности. Такие реализации называются диалектами. Так, стандарт ISO/IEC 9075-5 предусматривает объекты, называемые постоянно хранимыми модулями, или PSM-модулями (persistent stored modules). В СУБД Oracle расширение PL/SQL является аналогом указанного выше расширения стандарта.

Далее в примерах будет использоваться диалект SQL (Transact-SQL) СУБД MS SQL Server 2005/2008.

Оператор SELECT и схема "звезда"

Синтаксис оператора SELECT имеет следующий вид:

<SELECT statement> ::=  
    [WITH <common_table_expression> [,...n]]
    <query_expression> 
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] } 
  [ ,...n ] ] 
    [ COMPUTE 
  { { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ] 
  [ BY expression [ ,...n ] ] 
    ] 
    [ <FOR Clause>] 
    [ OPTION ( <query_hint> [ ,...n ] ) ] 
<query_expression> ::= 
    { <query_specification> | ( <query_expression> ) } 
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }
        <query_specification> | ( <query_expression> ) [...n ] ] 
<query_specification> ::= 
SELECT [ ALL | DISTINCT ] 
    [TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
    < select_list > 
    [ INTO new_table ] 
    [ FROM { <table_source> } [ ,...n ] ] 
    [ WHERE <search_condition> ] 
    [ <GROUP BY> ] 
    [ HAVING < search_condition > ]

Оператор SELECT предназначен для выборки строк из БД и позволяет построить выборки из одной или нескольких строк или колонок из одной или нескольких таблиц БД. Мы не будем детально разбирать синтаксис оператора SELECT. Результат выполнения оператора SELECT, т.е. построенная выборка, называется результирующим множеством, или результирующим набором (result set).

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

Операторы UNION, EXCEPT и INTERSECT используются для построения комбинации операторов SELECT или сравнения результатов их выполнения в рамках одного результирующего множества.

Теперь перейдем к примерам конструирования запросов к схеме типа "звезда". Рассмотрим схему "звезда" ХД, предназначенного для анализа сбыта продукции торговой организации (рис 22.1).

ХД предназначено для анализа системы сбыта продукции организации и включает, помимо времени, следующие объекты:

Таким образом, получаем одну таблицу фактов и четыре таблицы измерений. Рассмотрим, как конструируется запрос на SQL к схеме типа "звезда". В типовом запросе к такой схеме указываются детальные сведения о точках этой схемы (т.е. измерение), а затем данные, соответствующие этим точкам, обобщаются.

Рассмотрим пример запроса к схеме, приведенной на рис 22.1.

Пример 22.1. Пусть требуется просмотреть данные о продажах товара с идентификационным номером 33 за месяцы с мая по август текущего года по региону "Москва" с идентификационным номером 81. Тогда запрос может выглядеть следующим образом:

SELECT SUM(sales_dollar_amount* sales_quantity_sold), time_month, region_name
FROM Sales, Time, Region
WHERE Sales.region_id = Region.region_id
   AND Sales.time_id = Time_time_id
   AND Sales.product_id = 33
   AND Sales.region_id = 81
   AND Time.time_month    BETWEEN 'Май' AND 'Август'
   AND Time.time_year = 2009  
GROUP BY time_month, region_name

Изменяя данные о регионе, месяцах и товаре, при помощи вышеприведенного запроса можно выявить тенденции изменения данных о продажах. Для схемы "звезда" характерно использование односторонних эквисоединений таблиц измерений и таблицы фактов. Таблицы измерений не соединяются друг с другом.

Метрика типа "Объем продаж", рассмотренная в предыдущем примере, является аддитивным фактом. Рассмотрим теперь, как конструировать запросы для полуаддитивных фактов.

Метрика "Остаток на складе" является типичным примером полуаддитивного факта. Допустим, что в магазине на складе в конце каждого месяца рассчитывается остаток по каждому товару. Рассмотрим схему типа "звезда" на рис 22.2 для ХД, которое предназначено для анализа движения товаров через магазин.

В схеме представлено три таблицы измерений: "Месяц" (Data_month), "Магазин" (Store), "Товары" (Products) и таблица фактов "Остаток на складе" (Quantity_on_hand_fact). Описание таблиц приведено в табл. 22.6 ниже.

Метрика "Остаток на складе" является аддитивной по измерениям "Товары" (Products) и "Магазин" (Store), но не является аддитивной по измерению "Месяц" (Data_month). Рассмотрим, как можно получить итоговое количество товаров на складе в магазине в любой момент времени, используя измерения, для которых полуаддитивный факт является аддитивным.

Пример 22.2. Пусть нам необходимо просуммировать остатки товара "Подушка" на складе магазинов за январь 2009 года с учетом месторасположения последних, т.е. определить, сколько нереализованных подушек было в сети магазинов торговой организации в январе 2009 года. Сделаем это за счет соединения таблицы фактов с измерением "Магазин", как показано ниже.

SELECT Store.store_location, SUM(Quantity_on_hand_fact.Quantity_on_hand)
FROM Store, Quantity_on_hand_fact, Products, Data_month
WHERE Store.store_id = Quantity_on_hand_fact.store_id
AND Quantity_on_hand_fact.month_id = Data_month.month_id
AND Products.product_id = Quantity_on_hand_fact.product_id
AND Data_month.data_month = 'Январь'
AND Data_month.data_year = 2009
AND Products.product_name ='Подушка'
GROUP BY Store.store_location

Аналогично можно суммировать метрику "Остаток на складе" по измерению "Товары", чтобы получать количество нереализованных товаров, сгруппированных по категориям товара.

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

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

Предложение GROUP BY определяет, каким образом строить разбиение исходного множества на подмножества в соответствии с заданным критерием. Полученное разбиение представляет исходное отношение как объединение конечного числа непересекающихся подмножеств. В частности, агрегатные функции применяются последовательно к каждому подмножеству в отдельности, а результат функции выводится в результирующем отношении.

Кроме функции SUM(), к агрегатным функциям относятся функции: AVG() – вычисляет среднее значение, MIN() – вычисляет минимальное значение, MAX() – вычисляет максимальное значение, COUNT() – вычисляет количество итемов в результирующем множестве (или элементе разбиения результирующего множества), и ряд других, предусмотренных реализацией SQL в конкретной СУБД.

Предложение GROUP BY ведет себя как проекция с производными колонками. Оно разбивает значения в заданных колонках на подмножества в соответствии со списком колонок группировки. Эти подмножества характеризуются одинаковыми значениями из списка проекции. Затем осуществляется проекция на эти атрибуты, причем при этом вычисляется какая-либо производная колонка и помещается в дополнительную колонку результирующей таблицы.

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

Можно задавать условия выборки на результаты выполнения предложения GROUP BY, для того чтобы исключить определенные кортежи из построенного разбиения. Для этого предназначено специальное предложение HAVING, которое задает условие выборки к атрибутам перегруппированного, а не исходного отношения. Основное отличие в действии условия выборки предложения HAVING в сравнении с аналогичным условием выборки предложения WHERE состоит в том, что первое выбирает подмножества из разбиения целиком в зависимости от его агрегируемых свойств, в то время как последнее просматривает содержимое каждого из этих подмножеств построчно, не учитывая полученное разбиение. Иногда наблюдается более быстрое выполнение команды SELECT с использованием предложения HAVING, чем с предложением WHERE.

Расширение оператора SELECT для обработки данных

Расширения для оператора SELECT в реляционных СУБД

Производители промышленных реляционных СУБД стремятся расширить возможности аналитической обработки данных в своих диалектах SQL. Обычно расширение таких возможностей SQL выполняется в следующих направлениях:

Предложения CUBE и ROLLUP делают выполнение запросов и построение отчетов проще в среде ХД. Предложение ROLLUP создает промежуточные суммы (subtotals) в соответствии с возрастающим уровнем агрегации, от наиболее детализированных уровней представления данных к более обобщенным суммам. Предложение CUBE является расширением, подобным предложению ROLLUP, позволяющим в одной команде вычислить все возможные комбинации промежуточных сумм. Предложение CUBE может генерировать информацию, необходимую для перекрестных отчетов (cross-tabulation reports), в одном запросе.

Аналитические функции увеличивают потенциал SQL в области статистической обработки данных результирующих множеств запросов. Функции ранжирования включают в себя вычисление кумулятивных распределений, процентных рангов (percent rank) и разбиений на заданное число групп (N-tiles). Вычисления в плавающих окнах (moving window) позволяют работать с кумулятивными агрегатами (moving and cumulative aggregations), такими как суммы и средние величины.

Другие расширения SQL включают в себя семейство функций для вычисления регрессий и CASE-выражения. Функции вычисления регрессий включают и полный набор вычислений для линейной регрессии. CASE-выражения обеспечивают реализацию логики if – then.

Расширение SQL для агрегации данных

Многомерный анализ данных

Одной из ключевых концепций систем поддержки принятия решений (DSS) и информационных систем руководителя (EIS) является многомерный анализ — анализ объекта во всех необходимых комбинациях измерений. Термин "Измерение" (dimension) применяется для обозначения любой категории, используемой для спецификации запроса. Примерами измерений в ХД чаще всего выступают "Время", "География", "Товар", "Подразделение" и "Канал распределения". События или объекты, связанные с конкретными значениями измерений, принято называть фактами. Примерами фактов могут служить "Продажи", "Прибыль", "Количество клиентов", "Объем продукции".

Типичными примерами вопросов в многомерном анализе являются такие, которые мы будем называть многомерными запросами (MDQ).

Во всех перечисленных вопросах используется несколько измерений. Во многих MDQ требуется агрегировать данные по времени, географии или финансам и сравнивать полученные наборы данных.

Для визуализации данных, которые имеют несколько измерений, аналитики используют аналогию с кубом данных (data cube), т.е. часть многомерного пространства, в котором факты сохраняются на пересечении n-измерений. Например, куб может хранить данные о продажах, организованные в трех измерениях — "Товар", "Рынок сбыта" и "Время".

Вы можете разворачивать (делать сечения) данные (slices of data) из куба. Это соответствует перекрестному отчету, показанному в табл. 22.7. Например, региональный менеджер может изучать данные, сравнивая сечения куба по различным рынкам. Менеджер по товарам может сравнивать сечения куба по различным продуктам.

Ответы на MDQ часто требуют доступа к большому количеству данных, агрегации этих данных по уровням иерархии измерений, вычислении частичных сумм по измерениям. Таким образом, аналитические задачи требуют эффективной и удобной агрегации данных.

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

Для иллюстрации расширений SQL в настоящей лекции мы взяли гипотетическое ХД организации, которая продает и сдает напрокат видеокассеты. В ХД сохраняется информация о действиях организации в нескольких регионах, отлеживаются продажи и прибыль с продаж. Данные сохраняются в трех измерениях – "Время" (Time), "Отдел продаж" (Department) и "Регион" (Region). Временной период данных составляет период от 2000 года до 2009 года. В компании имеется два типа отделов продаж – "Отдел розничных продаж" (Video Sales) и "Отдел видеопроката" (Video Rentals). Регион включает три направления: "Центральный" (Central), "Восточный" (East) и "Западный" (West). Таблица фактов "Продажи" (Sales) содержит данные о продажах и прокате видеопродукции компании за 2000-2009 гг. Схема "звезда" для рассматриваемого ХД приведена на рис 22.3, а описание полей таблиц измерений и таблицы фактов приведено в табл. 22.7.

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

Обратим внимание на то, что в этом небольшом отчете генерируются пять частичных сумм и итоговые суммы. Частичные суммы являются скрытыми числами, которые должны быть вычислены для отчета в запросе, использующем агрегатную функцию SUM() и предложение GROUP BY.

Рассмотрим теперь подробнее расширения оператора SELECT, которые упрощают конструирование запросов для построения отчетов, аналогичных приведенному в табл. 22.7.

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

Предложение ROLLUP позволяет в команде SELECT вычислять многоуровневые частичные суммы для специфицированных групп измерений. Также вычисляется итоговая сумма. Предложение ROLLUP является простым расширением предложения GROUP BY, поэтому синтаксис для его применения прост. Использование предложения ROLLUP очень эффективно.

Синтаксис:

SELECT ... GROUP BY ROLLUP(grouping_column_reference_list)

Действия ROLLUP являются следующими: создаются частичные суммы для каждого из раскрываемых уровней от наиболее низкого уровня иерархии к более высокому уровню и вычисляется итоговая сумма в соответствии с указанным списком колонок в предложении ROLLUP. Предложение ROLLUP рассматривает свои аргументы как упорядоченный список колонок группировки. Сначала вычисляется стандартное агрегатное значение, указанное в предложении GROUP BY. Затем создаются частичные суммы для уровней атрибутов из списка группировки GROUP BY в порядке возрастания их значений, справа налево по списку колонок группировки. И окончательно создается итоговая сумма (grand total).

Предложение ROLLUP создает частичные суммы для n+1 уровней, где n есть число колонок группировки. Например, если в запросе указан ROLLUP на колонки группировки измерений "Время" (Time), "Регион" (Region) и "Отдел продаж" (Department) ( n=3 ), то результирующее множество (result set) будет включать в себя строки для 4-х уровней агрегации.

Рассмотрим примеры.

Пример 22.3. Пусть руководству компании требуется отчет о прибыли по всем регионам по всем отделам продаж за 2007-08 гг. Предложение схемы ХД может выглядеть следующим образом.

SELECT Time, Region, Department, SUM(Profit) AS Profit 
FROM sales
   GROUP BY ROLLUP(Time, Region, Department);

Вывод 1: Агрегирование в ROLLUP для трех измерений

Как видно из примера выше, запрос возвращает следующий набор строк:

Заметим, что NULL-значения показываются только для ясности. В действительности при выводе будут показаны пробелы.

NULL-значения, возвращаемые в результате выполнения предложений ROLLUP и CUBE, не всегда могут толковаться в общепринятом смысле, как неопределенные значения. NULL-значения могут указывать, что строка содержит частичную сумму. Например, первое NULL-значение в Выводе 1 появляется в колонке "Отдел продаж" (Department). Это NULL-значение означает, что строка есть частичная сумма для всех отделов продаж для Центрального региона за 2007 год.

Использование ROLLUP для вычисления частичных сумм

Можно использовать предложение ROLLUP только для вычисления некоторых частичных сумм. Такие команды с применением ROLLUP используют синтаксис как показано ниже:

GROUP BY expr1, ROLLUP(expr2, expr3);

В этом случае предложение ROLLUP создает частичные суммы для (2+1=3) уровней агрегации (aggregation levels), т.е. для уровней (expr1, expr2, expr3), (expr1, expr2) и (expr1). Итоговая сумма (grand total) не создается.

Пример 22.4. Пусть руководству компании требуется отчет о прибыли по всем регионам по всем отделам продаж за 2007-2008 гг. без итоговой суммы прибыли. Предложение схемы ХД может выглядеть следующим образом:

SELECT Time, Region, Department, SUM(Profit) AS Profit FROM sales
   GROUP BY Time, ROLLUP (Region, Department);

Вывод 2. Использование предложения ROLLUP для вывода частичных сумм

Как видно, запрос возвращает следующее множество строк:

Можно вычислить частичные суммы без использования предложения ROLLUP следующим образом:

SELECT Time, Region, Department, SUM(Profit)
 FROM Sales
 GROUP BY Time, Region, Department
UNION  ALL
 SELECT Time, Region, '' , SUM(Profit)
 FROM Sales
 GROUP BY Time, Region
UNION ALL
 SELECT Time, '', '', SUM(Profit)
 FROM Sales
 GROUP BY Time
UNION ALL
 SELECT '', '', '', SUM(Profit)
 FROM Sales;

Как видно из примера выше, для этого требуется для n измерений n+1 SELECT с UNION ALL.

ROLLUP-предложение целесообразно использовать для задач, в которых вычисляются промежуточные или частичные суммы:

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

Частичные суммы, генерируемые предложением ROLLUP, представляют только часть возможных комбинаций частичных сумм в измерениях. Например, в перекрестном отчете (см. табл. 22.1) итоги работы отделов продаж по регионам (279,000 и 319,000) не могут быть вычислены в предложении ROLLUP(Time, Region, Department). Для этого нужно изменить порядок колонок группировки в предложении ROLLUP: ROLLUP(Time, Department, Region). Простой способ генерации полного набора частичных сумм для перекрестных отчетов состоит в использовании расширения CUBE предложения GROUP BY.

Предложение CUBE позволяет команде SELECT вычислить частичные суммы для всех возможных комбинаций групп измерений. Оно также вычисляет итоговую сумму. Подобно ROLLUP, предложение CUBE является расширением предложения GROUP BY.

Синтаксис:

SELECT ...  GROUP BY
  CUBE (grouping_column_reference_list)

Из примера ниже видно, что CUBE берет указанный набор колонок группировки и создает частичные суммы для всех возможных комбинаций значений этих колонок. С точки зрения многомерного анализа, предложение CUBE генерирует все частичные суммы, которые могут быть вычислены для куба данных с указанными измерениями. Если указывается CUBE(Time, Region, Department), то результирующее множество запроса будет включать все значения, которые входят в аналогичную конструкцию ROLLUP, плюс набор дополнительных комбинаций.

Пример 22.5. Пусть руководству компании требуется перекрестный отчет о прибыли по всем регионам по всем отделам продаж за 2007-2008 гг. Предложение схемы ХД может выглядеть следующим образом:

SELECT Time, Region, Department, SUM(Profit) AS Profit 
FROM sales
   GROUP BY CUBE(Time, Region, Department);

Вывод 3. Выполнение CUBE с агрегацией по трем измерениям