|
Навигация
|
Главная » Sql Сервер Oracle8i: еще SQL, но уже OLAPИсточник: Interface Ltd Владимир Пржиялковский, преподаватель УКЦ Interface Ltd. Пользователи Oracle при слове "OLAP" чаще всего вспоминают Oracle Express. И не все знают, что для OLAP-работы вполне хватает и функциональности, присутствующей в Oracle8i. О чем конкретно идет речь, и как упомянутая функциональность соотносится с возможностями Oracle Express, говорится ниже.Туман вокруг OLAP Разработчики OLAP-продуктов изрядно потрудились не только над самими продуктами, но и над созданием терминологического тумана вокруг продуктов. Видимо ( sic ! ), в тумане легче продавать. В этой заметке не ставится попыткой разгребание авгиевых конюшен терминологии, но некоторые понятия нужно уточнить. С OLAP в сегодняшней торгово-популярной литературе связывается (по преимуществу) визуальный анализ данных, производимый в результате манипуляций над их базовым многомерно организованным представлением (над "многомерной БД"). Очевидно, что многомерный "гиперкуб" есть попытка реализации математического отношения, заданного на множестве имеющихся данных. Аналогичной попыткой реализации в реляционной теории является таблица. Реляционной таблице всегда однозначно соответствует гиперкуб, а гиперкубу - реляционная таблица. Для современных СУБД аналогичное утверждение верно только с оговорками ввиду невыполнения во всех из них некоторых важных требований реляционной теории. И, тем не менее, оно - ключ к пониманию общего и разного у "OLAP-систем" и "реляционных систем". Если мы хотим анализировать данные, получая "срезы" гиперкуба и свертки, то не обязательно прибегать к услугам многомерной БД. Функционально все то же самое можно делать и в более знакомой "реляционной" БД. Разница только в хранении данных и … в удобстве готовых для такой работы средств. Но последним в целом ряде случаев можно и слегка пожертвовать не конфликтуя с требованиями, предъявляемыми прикладной системе. В частности, если говорить об уровне работы с данными (и не касаться вопросов графического представления), OLAP-операции можно выполнять и в обычной БД под Oracle. В сервере 8i, однако, для этих целей добавлены некоторые расширения 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. Как выбрать нужную итоговую сумму Оба результата выше грешат тем, что выдают много разных итоговых сумм одновременно и не позволяют одним предложением отобрать сразу конкретный перечень нужных сверток. Для того, чтобы это сделать, в Oracle 8i добавлена возможность указания 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 и PL/SQL. С другой стороны, специализированные OLAP-системы имеют в себе много встроенных готовых средств графической выдачи данных и специально разработанный интерфейс для указания необходимых срезов данных и сверток. Но это - еще и фактическое отсутствие методологии проектирования, свой самостоятельный язык и отдельная цена. Оправдана последняя в вашем конкретном случае, или нет - решать вам. PerformancePoint Server. Sybase способствует ускорению внедрения Android организациями. Oracle Enterprise Manager 11g дополнен новыми революционными возможностями мониторинга работы пользователей. Symantec анонсировала Backup Exec 2010 V-Ray с функцией упреждающего восстановления сред VMware. Oracle анонсирует Oracle Exalytics Business Intelligence Machine. Главная » Sql |
© 2024 Team.Furia.Ru.
Частичное копирование материалов разрешено. |