Новые функции OLAP в SQL-99

Функции OLAP в SQL-99 Поправки 1 имеют существенное значение в управлении практически всеми типами бизнес-активности. Эти функции облегчают, например, такие действия, как ранжирование, процентное увеличение, скользящие средние значения, и совокупные суммы. Функции OLAP - материал каждодневного бизнеса, важного для многих пользователей. И благодаря вкладу таких профессионалов, как IBM, Oracle, комитета ANSI, и наличием поддержки ряда других заинтересованных лиц, они доступны для нас как в стандарте, так и в быстро растущем ассортименте аналитических приложений.

Ричард Винтер, Intelligent Enterprise

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

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

Ладно, не волнуйтесь. Подпишите с Клубом Волос пакт о ненападении. ANSI принял набор функции оперативной аналитической обработки (OLAP) как поправку к SQL-99. Эти функции поддерживают подобные вычисления наряду со многими другими, чье использование было либо невозможным, либо уж очень кривым в классическом SQL.

IBM и Oracle совместно предложили эти полезнейшие расширения в начале 1999 года. Благодаря ANSI, его необыкновенно быстрым (и достойным всяческой похвалы) действиям, они уже часть стандарта. Замечательно также, что IBM внедрил отдельные части этих спецификаций в DB2 UDB 6.2, которая была коммерчески доступна в некоторых редакциях уже с середины 1999 года. Oracle8i версии 2 и DB2 UDB 7.1, обе выпущенные в конце 1999 года, уже содержат полноценные реализации этих спецификаций. Также существенным является то, что другие поставщики достаточно рано включились в этот в процесс - например такие производители инструментальных средств как Brio, MicroStrategy, Cognos, и среди них Informix. Поразительно, что IBM и Oracle сумели на время отложить конкурентную борьбу, чтобы совместными усилиями запустить этот проект, завершение которого является большим подарком как для пользователей, так и целого ряда производителей ПО.

Эта впечатляющая разработка стала результатом работы целой группы, включающей людей различных компаний, но доктор Hamid Pirahesh научно-исследовательской лаборатории Almaden компании IBM сыграл особенно важную роль. Наряду с тем, что Pirahesh осуществил известное техническое влияние на концепции, он также взял на себя инициативу в организации тесного сотрудничества между компаниями, которое закончилось быстрой стандартизацией и коммерческим волощением. После того, как его группа разработчиков исследовала тему около года и придумала подход к расширению SQL в этой области, он обратился в Oracle. После разговора компании узнали, что каждый независимо друг от друга сделал весьма существенную работу. С помощью Andy Witkowski, играющим основную роль в Oracle, приблизительно за два месяца эти две компании выработали коллективное предложение по стандартам. Затем они попросили высказаться ведущих поставщиков инструментальных средств разработки и уже затем вышли с предложением к комитету ANSI. Поскольку работа делалась в течение года, все большее количество поставщиков предоставило им свою поддержку и таких квалифицированных профессионалов для работы в комитетах ANSI, как Jim Melton и других, результатом чего стало ускоренное усовершенствование и быстрое принятие.

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

Я воспроизвел нижеследующий пример, также как и многое другое из технической информации, содержащейся в этой статье, из документа ANSI "Введение в Функции OLAP", написанного Fred Zemke, Krishna Kulkarni, Andy Witkowski, и Bob Lyle. Я немного модифицировал примеры для соответствия моему собственному стилю представления. Выводы и мнения, естественно, являются моими собственными.

Пример запроса

Предположим для примера, что коммерческая организация имеет данные по продажам на протяжении нескольких лет со строками для каждого региона продаж в течение каждого месяца. Примерами регионов являются северо-восток, северо-запад, и центральный север. Месяцы представлены состоящим из четырех цифр годом, за которым следует две цифры месяца. (199806 является июнем 1998 года). Так что строка в истории продаж выглядела бы примерно так: северо-восток, 199806, 25, означая что 25 единиц товара было продано в северо-восточном регионе в июне 1998 года.

Теперь предположим, что вы хотите знать, для каждого месяца и региона, объем продаж в течение месяца и средние продажи в течение трехмесячного периода, заканчивающегося тем же месяцем. Например, для северо-востока в июне 1998, вы хотите посмотреть объем продаж за июнь и средние продажи в течение периода апрель-июнь (так называемое скользящее среднее значение).

Больно смотреть на запрос SQL, требуемый для выведения этого результата. Но с новыми функциями OLAP в SQL-99, вы можете выразить это следующим образом:

SELECT Sh.Region, Sh.Month, Sh.Sales,
AVG (Sh.Sales)
OVER (PARTITION BY Sh.Region ORDER BY Sh.Month ASC ROWS 2 PRECEDING )
AS Moving_average
FROM Sales_history AS Sh
ORDER BY Sh.Month ASC;

Здесь, AVG (Sh.Sales) OVER (PARTITION BY& ) - функция OLAP. Конструкция внутри круглых скобок определяет окно данных (WINDOW), к которым применяется AVG .
Любая функция агрегирования SQL могла бы использоваться вместо примененной здесь AVG. Таким образом, дозволяется использовать SUM, COUNT, и другие разнообразные функции агрегирования.
Конструкция WINDOW определяет разбитый на разделы набор строк, к которым применяется функция совокупного агрегирования. По идее конструкция WINDOW говорит, что приняла набор строк Sales_History и:

Разбила по регионам
Упорядочила по месяцам
Сгруппировала каждую строку с двумя предшествующими строками для того же самого региона.

Таким образом, сначала берутся все строки для северо-восточного региона и сводятся вместе для формирования разбиения. Если бы были данные по продажам для периода 1990-1999 года, в разделе было бы 120 строк, одна для каждого месяца этого периода. После того, как упорядочение закончено, даты этих строки начались бы с января 1990 года и до конца декабря 1999 года. Пока все это должно звучать знакомо.

Третий шаг, тем не менее, представляет собой незнакомую концепцию SQL. Эго действие заключается в замене этих 120 строк при выводе запроса на 120 групп строк. Каждая из этих групп включает одну из первоначальных строк плюс две ей предшествующих. Таким образом, группа, соответствующая марту 1990 года включает строки для января и февраля.
( Обратите внимание, что эти "шаги", которые я здесь описываю, не обязательно выполняются процессором базы данных. Скорее, запрос обрабатывается таким образом, чтобы получить тот же самый результат, как будто они были им выполнены.)

Далее, конструкция WINDOW определяет 120 групп строк, и среднее вычисляется по каждой из этих групп. Это среднее выводится вместе с каждой строкой результата запроса как элемент названный Moving_Average.

Конструкция WINDOW

Новейшая конструкция WINDOW имеет три главных части: выделение разделов, упорядочение, и агрегирующее групирование.

Набор имен столбцов определяет разбиение на разделы, которое применяется к строкам, генерированным предшествующими операторами FROM, WHERE, GROUP BY, и HAVING. Если никакое разбиение не определено, полный набор строк составляет единственный раздел, и агрегирующая функция обращается каждый раз к целому набору. Хотя разбиение на разделы и напоминает GROUP BY, однако это не то же самое. GROUP BY сворачивает строки раздела в единственную строку. Разбиение на разделы в пределах WINDOW просто организовывает строки в группы без сворачивания.

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

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

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

Две предшествующих строки или
Любая строка, содержащая месяц не менее чем два месяца ранее.

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

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

Я мог бы рассказать гораздо больше о функциях OLAP. Документ ANSI, представляющий эти концепции, занимает 38 страниц. Само предложение ANSI о спецификациях занимает уже 60 страниц. Так что, это не простое небольшое изменение - это мощное агрегирование (простите за каламбур) возможностей. И для того, чтобы полностью понять их и их отношение к другим концепциям SQL, нужно достаточно потрудиться.

Выводы

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

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

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

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

Norm Sun, VLDB инженер в MicroStrategy, приветствует появление функций OLAP. Вычисления, которые иначе были бы выполнены аналитическим процессором MicroStrategy, теперь могут быть проведены значительно более эффективно внутри самой базы данных. В принципе, процессор MicroStrategy должен генерировать и обрабатывать меньшее количество кода из-за функций, обрабатываемых самим процессором базы данных. Кроме того, некоторые вычисления, которые были бы многопроходными во внешнем аналитическом процессоре, теперь могут быть выполнены за один проход внутри базы данных. Sun отмечает, что было особенно полезным, когда IBM и Oracle, при формулировке их начальной инициативы, достаточно рано прислушались к рекомендациям MicroStrategy и других компаний-производителей.

Поскольку эти функции теперь стандартны, они гораздо более экономичны в эксплуатации для поставщиков аналитических приложений. Таким образом, поставщик может генерировать код SQL, использующий функции OLAP, ожидая что ведущие производители баз данных, а в конечном счете практически все производители СУБД, обработают этот код - и сделают это в соответствии со стандартом.

Возможно наиболее важный из всех пунктов - это то, что функции OLAP в SQL-99 Поправки 1 имеют существенное значение в управлении практически всеми типами бизнес-активности. Эти функции облегчают, например, такие действия, как ранжирование, процентное увеличение, скользящие средние значения, и совокупные суммы. Функции OLAP - материал каждодневного бизнеса, важного для многих пользователей. И благодаря вкладу таких профессионалов, как IBM, Oracle, комитета ANSI, и наличием поддержки ряда других заинтересованных лиц, они доступны для нас как в стандарте, так и в быстро растущем ассортименте аналитических приложений.

Ричард Винтер - специалист в технологии больших баз данных и их реализации, кроме того, президент Winter Corp., находящейся в Бостоне (США).