Сервер Oracle8i: еще SQL, но уже OLAP
Владимир Пржиялковский 
Как выбрать нужную итоговую сумму
Как справиться с двусмысленными строками
Какую реализацию OLAP-работы выбрать
Пользователи Oracle при слове «OLAP» чаще всего вспоминают Oracle Express, но не все знают, что для OLAP-работы вполне хватает и функциональности, присутствующей в Oracle8i. Что конкретно имеется в виду и как упомянутая функциональность соотносится с возможностями Oracle Express, мы расскажем в этой статье.
Туман вокруг OLAP
Разработчики OLAP-продуктов изрядно потрудились не только над самими продуктами, но и над созданием терминологического тумана вокруг них. Видимо, в тумане легче продавать. В этой статье мы не будем пытаться очистить «авгиевы конюшни» терминологии, но некоторые понятия все-таки постараемся уточнить.
С OLAP в сегодняшней торгово-популярной литературе связывается преимущественно визуальный анализ данных, проводимый в результате манипуляций над их базовым многомерно организованным представлением, над так называемой многомерной базой данных. Очевидно, что многомерный «гиперкуб» есть попытка реализации математического отношения, заданного на множестве имеющихся данных. Аналогичной попыткой реализации в реляционной теории является таблица. Реляционной таблице всегда однозначно соответствует гиперкуб, а гиперкубу — реляционная таблица. Для современных СУБД аналогичное утверждение верно только с определенными оговорками — вследствие невыполнения в них некоторых важных требований реляционной теории. И тем не менее это утверждение — ключ к пониманию того, что есть общего у OLAP-систем и реляционных систем и чем они различаются. Если мы хотим анализировать данные, получая «срезы» гиперкуба и свертки, то необязательно прибегать к услугам многомерной БД. Функционально все то же самое можно делать и в более знакомой реляционной базе данных. Разница только в хранении данных и в удобстве имеющихся для такой работы средств. Но последним фактором в целом ряде случаев можно и пожертвовать, не конфликтуя с требованиями, предъявляемыми прикладной системе.
В частности, если говорить об уровне работы с данными и не касаться вопросов графического представления, OLAP-операции можно выполнять и в обычной базе данных Oracle. В сервере Oracle8i, однако, для этих целей добавлены некоторые расширения SQL, специально предназначенные для более удобного выполнения OLAP-операций и разработанные в соответствии с существующими международными предложениями по SQL, но… отдаляющими диалект SQL в Oracle еще дальше от принятых стандартов и от реляционной теории!
 |
|
Демонстрационный пример
Рассмотрим следующий пример базы данных. Пусть в прикладной системе собирается информация о посещениях Web-узла. Регистрируется число посещений за декаду с указанием имени домена посетителя и вида браузера. Данные попадают в следующую таблицу:
CREATE TABLE visits (time DATE, idomain VARCHAR2(15), browser VARCHAR2(15), hits NUMBER);
Заполним ее данными:
INSERT INTO visits VALUES (’10-sep-2000',’com’,’Netscape’, 3792); INSERT INTO visits VALUES (’10-sep-2000',’ru’,’Netscape’, NULL); INSERT INTO visits VALUES (’10-sep-2000',’com’,’IE’, 9567); INSERT INTO visits VALUES (’10-sep-2000',’ru’,’IE’, 3095); INSERT INTO visits VALUES (’20-sep-2000',’com’,’Netscape’, 2642); INSERT INTO visits VALUES (’20-sep-2000',’ru’,’Netscape’, 1639); INSERT INTO visits VALUES (’20-sep-2000',’com’,’IE’, 8045); INSERT INTO visits VALUES (’20-sep-2000',’ru’,’IE’, 3085);
Значение NULL во второй строке оставлено специально, чтобы показать, что рассматриваемые ниже операции «правильно» обрабатывают NULL-значения, не требуя специальных указаний.
На основе этой таблицы можно построить гиперкуб с тремя измерениями и полем hits в качестве «факта».
|
|
Указания ROLLUP и CUBE
Данные можно просмотреть посредством обычного предложения SELECT, но это не будет «аналитической обработкой». Для нее нужно уметь удобно выдавать итоговые результаты (свертки) по разным позициям. Далее в тексте будет фигурировать итоговая сумма, однако синтаксически все это можно делать с использованием других функций, допускаемых для применения с GROUP BY, например COUNT, AVG, MIN или VARIANCE.
В этих целях можно использовать указание GROUP BY ROLLUP:
SELECT time, idomain, browser, SUM(hits) AS total FROM visits GROUP BY ROLLUP(time, idomain, browser); TIME IDOMAIN BROWSER TOTAL ———— ————— ————— ———— 10-SEP-00 com IE 9567 10-SEP-00 com Netscape 3792 10-SEP-00 com 13359 10-SEP-00 ru IE 3095 10-SEP-00 ru Netscape 10-SEP-00 ru 3095 10-SEP-00 16454 20-SEP-00 com IE 8045 20-SEP-00 com Netscape 2642 20-SEP-00 com 10687 20-SEP-00 ru IE 3085 20-SEP-00 ru Netscape 1639 20-SEP-00 ru 4724 20-SEP-00 15411 31865 15 rows selected.
В целом понятно, о чем говорит этот результат: в первых трех столбцах, там где значение отсутствует, — итог. Однако здесь есть одно неудобство: чтобы посмотреть итоговые суммы по другим критериям, необходимо выдать аналогичный запрос с перестановкой имен полей во фразе GROUP BY ROLLUP. Можно справиться с этим в рамках одного запроса, указав вместо ROLLUP слово CUBE (то есть выдать все возможные свертки по гиперкубу):
SELECT time, idomain, browser, SUM(hits) AS total FROM visits GROUP BY CUBE(time, idomain, browser) TIME IDOMAIN BROWSER TOTAL ———— ————— ————— ———— 10-SEP-00 com IE 9567 10-SEP-00 com Netscape 3792 10-SEP-00 com 13359 10-SEP-00 ru IE 3095 10-SEP-00 ru Netscape 10-SEP-00 ru 3095 10-SEP-00 IE 12662 10-SEP-00 Netscape 3792 10-SEP-00 16454 20-SEP-00 com IE 8045 20-SEP-00 com Netscape 2642 20-SEP-00 com 10687 20-SEP-00 ru IE 3085 20-SEP-00 ru Netscape 1639 20-SEP-00 ru 4724 20-SEP-00 IE 11130 20-SEP-00 Netscape 4281 20-SEP-00 15411 com IE 17612 com Netscape 6434 com 24046 ru IE 6180 ru Netscape 1639 ru 7819 IE 23792 Netscape 8073 31865 27 rows selected.
Это более общий результат, из которого будут браться все прочие результаты, получаемые с помощью ROLLUP.
|
|
Как выбрать нужную итоговую сумму
Оба полученных результата имеют один минус — выдают много разных итоговых сумм одновременно и не позволяют одним предложением отобрать сразу конкретный перечень нужных сверток. Для того чтобы это сделать, в Oracle8i добавлена возможность указания GROUPING:
SELECT time, idomain, browser, SUM(hits) AS total, GROUPING (time) AS t, GROUPING (idomain) AS d, GROUPING (browser) AS b FROM visits GROUP BY ROLLUP (time, idomain, browser); TIME IDOMAIN BROWSER TOTAL T D B ———— ————— ————— ———— ———— ———— ———— 10-SEP-00 com IE 9567 0 0 0 10-SEP-00 com Netscape 3792 0 0 0 10-SEP-00 com 13359 0 0 1 10-SEP-00 ru IE 3095 0 0 0 10-SEP-00 ru Netscape 0 0 0 10-SEP-00 ru 3095 0 0 1 10-SEP-00 16454 0 1 1 20-SEP-00 com IE 8045 0 0 0 20-SEP-00 com Netscape 2642 0 0 0 20-SEP-00 com 10687 0 0 1 20-SEP-00 ru IE 3085 0 0 0 20-SEP-00 ru Netscape 1639 0 0 0 20-SEP-00 ru 4724 0 0 1 20-SEP-00 15411 0 1 1 31865 1 1 1 15 rows selected.
Для CUBE это указание работает аналогично. Функция GROUPING возвращает 1 в тех случаях, когда ROLLUP или CUBE в GROUP BY выдают NULL в качестве признака того, что по этому полю подсчитана итоговая сумма. Фирма Oracle предлагает теперь воспользоваться отбором по маске полей T, D и B, но, к сожалению, в моей версии 8.1.6 для NT этот отбор не работает так, как заявлено в документации. Предложение HAVING вообще не воспринимает имен столбцов, присвоенных в теле запроса, например имени D. Вместо этого следует явно указать, к примеру, GROUPING (browser) = 1. Остается только надеяться на исправление этой ошибки в следующих версиях.
|
|
Как справиться с двусмысленными строками
NULL как отсутствующее значение и NULL как результат вычисления свертки могут вызвать путаницу, так как в выдаваемом ответе неразличимы. Предположим, что наше приложение знакомо только с браузерами Navigator/Communicator и IE ничего не знает об Opera, из-за чего добавляет в таблицу такую сроку:
INSERT INTO visits VALUES (’10-sep-2000', ‘com’, NULL, 3095); 1 row created. Теперь сделаем запрос: SELECT time, browser, SUM(hits) AS hits FROM visits GROUP BY CUBE(time, browser) TIME BROWSER HITS ———— ——————— ———— 10-SEP-00 IE 12662 10-SEP-00 Netscape 3792 10-SEP-00 3095 10-SEP-00 19549 20-SEP-00 IE 11130 20-SEP-00 Netscape 4281 20-SEP-00 15411 IE 23792 Netscape 8073 3095 34960 11 rows selected.
Что появляется в третьей и четвертой сверху, а также в двух последних строках — свертка или отсутствующие значения? И в каких полях что?
Чтобы в этом разобраться, можно снова воспользоваться функцией GROUPING в сочетании с DECODE:
SELECT DECODE(GROUPING(time), 1, ‘All Times’, 0, time) AS time, DECODE(GROUPING(browser), 1, ‘All Browsers’, 0, browser) AS browser, SUM(hits) AS hits FROM visits GROUP BY CUBE(time, browser); TIME BROWSER HITS ———— ——————— ———— 10-SEP-00 IE 12662 10-SEP-00 Netscape 3792 10-SEP-00 3095 10-SEP-00 All Browsers 19549 20-SEP-00 IE 11130 20-SEP-00 Netscape 4281 20-SEP-00 All Browsers 15411 All Times IE 23792 All Times Netscape 8073 All Times 3095 All Times All Browsers 34960 11 rows selected.
Попытка отобрать напрямую из результата нужные подстроки при работе с версией 8.1.6 мне также удалась только после повторения во фразе HAVING полной формулировки формируемого столбца.
|
|
Какую реализацию OLAP-работы выбрать
Из сказанного видно, что сервер 8i совершенно бесплатно предлагает специально оптимизированные (что нужно отметить особо) возможности для получения сверток по таблицам типа «гиперкуб». Неприятно, конечно, что в реализации ROLLUP и CUBE в существующей версии есть недоработки (кроме вышеуказанных можно еще назвать непонимание новых конструкций некоторыми продуктами третьих фирм, разработанными для Oracle). Однако, хотя и не очень изящно, эти недочеты часто можно обойти. А вот проблемы перенесения данных из операционной базы данных в аналитическую здесь вообще не существует. Программировать анализ данных можно с помощью знакомых языков программирования Oracle — его диалекта SQL, используемого Oracle, и языка PL/SQL.
Кроме того, специализированные OLAP-системы имеют множество встроенных готовых средств графического отображения данных и специально разработанный интерфейс для указания необходимых срезов данных и сверток. Но их применение — это еще и фактическое отсутствие методологии проектирования, свой самостоятельный язык и отдельная цена.
Оправдана ли последняя в вашем конкретном случае или нет — решать вам.
Автору будет интересно узнать ваше мнение о статье по e-mail: mail@interface.ru.
КомпьютерПресс 7'2001
