Технология Клиент-Сервер 2000'2
	Home Поиск Издания Контакты k-press.ru

Продолжается подписка на наши издания! Вы не забыли подписаться?

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

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

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

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

IBM и Oracle совместно предложили ввести такое расширение в начале 1999 года. Необычайно быстро для ANSI это расширение было введено в стандарт. Примечательно, что IBM реализовала часть этой спецификации в DB2 UDB 6.2, поступившей в продажу в середине 1999 года. Вышедшие в конце 1999 года Oracle8i version 2 и DB2 UDB 7.1 содержат уже расширенную спецификацию. Стоит упомянуть, что другие разработчики также не остались в стороне, в их числе Brio, MicroStrategy, Cognos, и, кстати, Informix. Самое же странное, что IBM и Oracle сумели временно забыть о конкуренции и затеять совместное мероприятие, в целом выгодное для ряда производителей, да и для пользователей тоже.

Следующий пример, как и большая часть технической информации в этой статье, взят из документа ANSI “Introduction to OLAP Functions”.

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

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

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

Достаточно трудно придумать 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-функция. Конструкция в скобках определяет «окно» данных, к которым применяется AVG.

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

Конструкция WINDOW определяет разделенный набор строк, к которым применяется агрегатная функция. Концептуально эта конструкция говорит взять набор строк из истории продаж и:

1. разделить их по регионам;
2. сортировать их по месяцам;
3. сгруппировать каждую запись с двумя предыдущими записями этого региона.

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

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

Итак, конструкция WINDOW определила 120 групп записей, и по каждой группе посчитано скользящее среднее.

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

Новая конструкция WINDOW состоит из трех основных частей: разделение (partitioning), сортировка (ordering) и агрегирование (aggregation).

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

Таблица 1. Результаты запроса

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

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

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

•  две предшествующие записи или

•  любая строка, содержащая месяц не более, чем на два месяца раньше.

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

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

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

Выводы

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

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

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

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

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

Copyright © 1994-2016 ООО "К-Пресс"