Сервер Oracle8i: ще SQL

Користувачі 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-системи мають в собі багато вбудованих готових засобів графічної видачі даних і спеціально розроблений інтерфейс для вказівки необхідних зрізів даних і згорток. Але це – ще й фактичну відсутність методології проектування, свій самостійний мова та окрема ціна.

Чи виправдана остання у вашому конкретному випадку, чи ні – вирішувати вам.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*