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

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

Введение

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

• Предложение SELECT select_list [ INTO new_table ] определяет список имен колонок таблиц БД, представлений или производных полей.
• Предложение FROM table_source определяет список имен таблиц и представлений БД, участвующих в выборке.
• Предложение WHERE search_condition определяет предикат отбора строк в выборку.
• Предложение GROUP BY group_by_expression определяет условия группировки строк в выборке.
• Предложение HAVING search_condition, которое условия поиска на разбиении результирующего множества предложения GROUP BY.
• Предложение ORDER BY order_expression [ ASC | DESC ] определяет правила упорядочивания строк в выборке.

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

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

увеличить изображение
Рис. 22.1. Схема "звезда" для анализа сбыта продукции торговой организации

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

• таблица изменений "Товар" (Product), описание которой приведено в табл. 22.1;
• таблица измерений "Время" (Time), описание которой приведено в табл. 22.2;
• таблица измерений "Покупатель" (Customer), описание которой приведено в табл. 22.3;
• таблица измерения "Регион" (Region), описание которой приведено в табл. 22.4;
• таблица фактов "Данные по продажам" (Sales), описание которой приведено в табл. 22.5.

Таким образом, получаем одну таблицу фактов и четыре таблицы измерений. Рассмотрим, как конструируется запрос на 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 для ХД, которое предназначено для анализа движения товаров через магазин.

Рис. 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. Об этом говорит сайт https://intellect.icu . Обычно расширение таких возможностей SQL выполняется в следующих направлениях:

• расширение возможностей аналитической обработки в рамках предложения GROUP BY оператора SELECT — предложения ROLLUP и CUBE ;
• новые семейства встроенных функций SQL для аналитической обработки данных;
• добавление встроенных функций для линейных регрессионных моделей данных;
• добавление CASE-выражения для поддержки ветвления обработки в SELECT.

Предложения 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).

• Показать итоговые продажи всего товара по возрастанию уровня агрегации измерения "География": от области к стране, к региону по возрастанию: от области к региону, к стране за период от 1998 г. до 1999 г.
• Создать перекрестный отчет (cross-tabular) операций организации, показывающий расширение территории торговых операций в Южной Америке за 2004-2008 гг. Включить в него все возможные промежуточные суммы.
• Показать список 10 самых крупных продаж (top 10 sales) в Азии в соответствии с прибылью от продаж за 2008 год для автомобилей и ранжировать их по комиссионным.

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

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

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

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

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

• упрощение программирования за счет сокращения кода SQL;
• ускорение обработки запросов;
• сокращение объема процесса загрузки клиентов и сетевого трафика за счет перемещения процесса агрегации данных на серверы БД;
• использование кеширования для агрегатов в случае однотипных запросов.

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

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

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

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

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

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

• обычные строки агрегации, которые выдаются предложением GROUP BY без использования ROLLUP ;
• частичные суммы 1-го уровня, агрегированные для "Отдела продаж" (Department) для каждой комбинации измерений "Время" (Time) и "Регион" (Region);
• частичные суммы 2-го уровня, агрегированные для измерений "Регион" (Region) и "Отдела продаж" (Department) для каждого значения измерения "Время" (Time);
• строку с итоговой суммой.

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

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

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

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

• обычные строки агрегации, которые выдаются предложением GROUP BY без использования ROLLUP ;
• частичную сумму 1-го уровня агрегации по "Отделам продаж" (Department) для каждой комбинации "Время" (Time) и "Регион" (Region);
• частичную сумму 2-го уровня агрегации по измерениям "Регион" (Region) и "Отдел продаж" (Department) для каждого значения измерения "Время" (Time);
• нет строки с итоговой суммой.

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

• в измерениях с иерархической структурой, таких как "время" или "географическое расположение": ROLLUP(y, m, day) или ROLLUP(country, state, city).
• для быстрой генерации отчетов с суммарными данными.

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

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

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

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

Использование CUBE для вычисления частичных сумм аналогично использованию предложения ROLLUP для вычисления частичных сумм, в котором можно ограничить использование некоторых измерений. В этом случае вычисления всех возможных комбинаций ограничиваются указанными в списке группировки измерениями.

Синтаксис:

GROUP BY expr1, CUBE(expr2, expr3);

В результате выполнения этой команды будет вычислено 4 частичные суммы:

• (expr1, expr2, expr3)
• (expr1, expr2)
• (expr1, expr3)
• (expr1)

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

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

Вывод 4. Использование CUBE для вычислений частичных сумм

Без использования предложения CUBE для n -мерного куба потребуется 2n команд SELECT с UNION ALL.

Предложение CUBE целесообразно использовать при решении задач создания перекрестных отчетов.

Квалификатор DISTINCT имеет ошибочную семантику в предложениях ROLLUP и CUBE. Не рекомендуется применять квалификатор DISTINCT в комбинации с этими предложениями.

Функция GROUPING

Две проблемы возникают при использовании ROLLUP и CUBE. Первая: как можно программно определить, какие строки результирующего множества являются частичными суммами, и как найти точный уровень агрегации данной частичной суммы? Часто необходимо использовать частичные суммы для вычислений процентных отношений между суммами, поэтому нужен простой способ находить частичные суммы. Вторая проблема: что произойдет, если результат запроса содержит и NULL-значение хранимых строк, и псевдо-NULL-значения, созданные ROLLUP или CUBE? Как различить их в результирующем множестве?

Для решения этой задачи предназначена функция GROUPING. Это статистическая функция, выдающая дополнительный столбец, который содержит значение 1, если строка добавлена с помощью оператора CUBE или ROLLUP, или значение 0 — в ином случае.

Синтаксис (указывается в списке предложения SELECT ):

SELECT ...  [GROUPING(column_name)...]  ... 
  GROUP BY ...    {CUBE | ROLLUP}  (column_name)

Пример 22.7. Использование функции GROUPING для создания колонок-масок в результирующем множестве.

SELECT Time, Region, Department, SUM(Profit) AS Profit,
  GROUPING (Time) as T, 
  GROUPING (Region) as R, 
  GROUPING (Department) as D
  FROM Sales
GROUP BY ROLLUP (Time, Region, Department);

Вывод 5. Использование функции GROUPING

Как видно из примера, маска "0 0 0" — агрегированная строка из таблицы, "0 0 1" — первый уровень агрегации, "0 1 1" — второй уровень агрегации, "1 1 1" — итоговая сумма.

Пример 22.8. Предположим, что оператор SELECT выдает следующее результирующее множество, созданное выражением CUBE.

Вывод 6.

В результирующем множестве 4 различных строки с NULL-значениями для колонок "Время" (Time) и "Регион" (Region). Некоторые из этих NULL-значений должны представлять агрегаты CUBE, а некоторые — агрегаты NULL-значений из базы данных. Как отличить в отчете агрегатные NULL-значения, построенные предложением CUBE, от хранимых в БД NULL-значений?

Использование GROUPING-функции в комбинации с CASE -выражением и функцией преобразования типов данных CAST (expression AS data_type [ (length ) ]) позволяет решить эту проблему.

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

Синтаксис:

CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

или

CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

Теперь можно преобразовать отчет из примера 22.8 таким образом, чтобы выделить агрегаты предложения CUBE, как показано ниже.

Пример 22.9. Разграничение агрегатных и хранимых NULL-значений.

SELECT 
CASE WHEN GROUPING(Time) = 1 THEN 'All Times' ELSE CAST(Time as CHAR(20)) END AS Time,
CASE WHEN GROUPING(Region) = 1 THEN 'All Regions' ELSE Region END AS Region, SUM(Profit) AS Profit
FROM Sales
GROUP BY CUBE(Time, Region);

Вывод 7. Разграничение агрегатных и хранимых NULL-значений

Первая колонка есть "Время" (Time), определенное выражением

CASE WHEN GROUPING(Time) = 1 THEN 'All Times' ELSE CAST(Time as CHAR(20)) END AS Time,

Значение Time определяется выражением CASE, содержащим функцию GROUPING. Функция GROUPING возвращает 1, если строка есть агрегат предложений ROLLUP или CUBE, иначе — 0. CASE работает с результатом функции GROUPING. Оно возвращает текст "All Times", если это 1, и значение колонки "Время" (time) из БД, если это 0. Значениями из базы данных будут либо фактическое значение, такое как 2007, или сохраняемое NULL-значение. Функция CAST используется для согласования типов, поскольку в результирующем множестве все колонки должны быть одного типа. Вторая колонка спецификации, показывающая значения колонки "Регион" (Region), обрабатывается аналогичным образом.

Функция GROUPING полезна не только для идентификации NULL-значений, она также может помочь отсортировать строки частичных сумм и отфильтровать результирующее множество. В примере ниже выбирается подмножество частичных сумм, созданное CUBE. Предложение HAVING ограничивает колонки, которые используются в функции GROUPING.

Пример 22.10.

SELECT Time, Region, Department, SUM(Profit) AS Profit, 
  GROUPING (Time) AS T,
  GROUPING (Region) AS R, 
  GROUPING (Department) AS D
  FROM Sales
  GROUP BY CUBE (Time, Region, Department)
  HAVING (GROUPING(Department)=1 AND GROUPING(Region)=1 AND GROUPING(Time)=1) 
  OR (GROUPING(Region)=1 AND (GROUPING(Department)=1)
  OR (GROUPING(Time)=1 AND GROUPING(department)=1);

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

Управление иерархией в предложениях ROLLUP и CUBE

Предложения ROLLUP и CUBE работают независимо от какой-либо иерархии данных в БД. Их вычисления основываются на колонках, которые появляются в списке оператора оператора SELECT. Такой подход позволяет использовать CUBE и ROLLUP и для обработки иерархий. Простой способ управления иерархией измерения — использовать предложение ROLLUP и точно указать уровни иерархии для выделенной колонки. В примере ниже показано, как месяцы сворачиваются в кварталы, а кварталы — в год. При этом предполагается, что схема ХД учебного примера модифицирована, как показано на рис. 22.4.

Пример 22.11.

SELECT Year, Quarter, Month, SUM(Profit) AS Profit 
FROM sales
   GROUP BY ROLLUP(Year, Quarter, Month)
HAVING Year = 2008;

Рис. 22.4. Модифицированная схема ХД учебного примера

Вывод 9. Пример ROLLUP с использованием уровней иерархии измерения "Время" (Time)

Year	Quarter	Month	Profit
2008	Первый	Январь	55,00
2008	Первый	Февраль	64,00
2008	Первый	Март	71,00
2008	Первый	NULL	190,00
2008	Второй	Апрель	75,00
2008	Второй	Май	86,00
2008	Второй	Июнь	88,00
2008	Второй	NULL	249,00
2008	Третий	Июль	91,00
2008	Третий	Август	87,00
2008	Третий	Сентябрь	101,00
2008	Третий	NULL	279,00
2008	Четвертый	Октябрь	109,00
2008	Четвертый	Ноябрь	114,00
2008	Четвертый	Декабрь	133,00
2008	Четвертый	NULL	356,00
2008	NULL	NULL	1074,00

Обратим внимание на некоторые особенности использования предложений ROLLUP и CUBE.

Предложения CUBE и ROLLUP не ограничивают мощность колонки (Column Capacity) предложения GROUP BY. Предложение GROUP BY, с расширениями или без них, может работать с 255 колонками. Однако число комбинаций, которое создает предложение CUBE, может быть очень велико. Для 20-ти колонок предложением CUBE будут создано 220 комбинаций в результирующем множестве.

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

Предложение ORDER BY команды SELECT не влияет на использование предложений ROLLUP и CUBE. Предикат в предложении ORDER BY применяется ко всем строкам результирующего множества.

Резюме

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

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

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

В заключение приведем подробный синтаксис предложения GROUP BY диалекта SQL СУБД семейства MS SQL Server.

Синтаксис, совместимый со стандартом ISO/IEC 9075-5:

GROUP BY <group by spec>

<group by spec> ::=
    <group by item> [ ,...n ]

<group by item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>
    | <grouping sets spec>
    | <grand total>

<simple group by item> ::=
    <column_expression>

<rollup spec> ::=
    ROLLUP ( <composite element list> )

<cube spec> ::=
    CUBE ( <composite element list> )

<composite element list> ::=
    <composite element> [ ,...n ]

<composite element> ::=
    <simple group by item>
    | ( <simple group by item list> )

<simple group by item list> ::=
    <simple group by item> [ ,...n ]

<grouping sets spec> ::=
    GROUPING SETS ( <grouping set list> )

<grouping set list> ::=
    <grouping set> [ ,...n ]

<grouping set> ::=
    <grand total>
    | <grouping set item>
    | ( <grouping set item list> )

<empty group> ::=
        ( )

<grouping set item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>

<grouping set item list> ::=
    <grouping set item> [ ,...n ]

Листинг .

Синтаксис, не предусмотренный стандартом ISO/IEC 9075-5:

[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
    [ WITH { CUBE | ROLLUP } ] 
]
<column_expression>

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

• Агрегирование
• Индексирование
• Партицирование
• Денормализация

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