ORACLE

System Zarządzania Bazą

Danych Oracle

Oracle Data Warehouse

wersja 1.4 (aktualizacja 2007)

Politechnika Śląska 2005

2

Wstęp

Celem ćwiczenia jest zapoznanie się z wybranymi cechami SZBD Oracle, wspomagającymi
pracę systemów hurtowni danych. W szczególności ćwiczenie laboratoryjne pozwala
zapoznać się z rozszerzeniami języka SQL wprowadzonymi w SZBD Oracle:

• frazy: rollup, cube, grouping, funkcja grouping, grouping_id,

• funkcje analityczne,

• budowa modeli SQL dla zadań budżetowania, przewidywania trendów i zastosowań

statystycznych (fraza model),

Schemat testowy

Schemat SH przedstawia bazę danych (hurtownię danych) wspomagającą przeprowadzanie
analiz dotyczących wartości sprzedaży oraz poniesionych kosztów ze względu na:
okres sprzedaży, produkt, kanał dystrybucji, promocję oraz klienta który dokonał transakcji.
Główne tabele schematu SH:

• TIMES – wymiar czasu

• CHANNELS – wymiar kanału dystrybucji

• CUSTOMERS – wymiar klienta
• COUNTRIES – wymiar klienta, opis obszaru geograficznego

• PRODUCTS – wymiar produktu

• PROMOTIONS – wymiar promocji
• SALES – fakty o sprzedaży

• COSTS – fakty o kosztach

Opis tabeli można uzyskać m.in. stosując komendę

DESC <nazwa_tabeli>

.

W schematach użytkowników laboratoryjnych utworzono synonimy do odpowiednich tabel
schematu SH.

3

Z1 Identyfikacja sekcji laboratoryjnej – logowanie

1. Zapytać prowadzącego laboratorium o nazwę podsekcji, nazwę użytkownika

laboratoryjnego, hasło oraz opis bazy danych.

2. Ustalić identyfikator sekcji ID<data w formacie

rrmmdd>_<nr_sekcji>_<nr_podsekcji>, np. ID051231_1_3 – identyfikator ten
należy umieścić w sprawozdaniu

PROD_ID

CUST_ID

TIME_ID

CHANNEL_ID

PROMO_ID

QUANTITY_SOLD

AMOUNT_SOLD

4

Otworzyć SQL*Plus Worksheet lub SQL*Plus, zalogować się do bazy danych.

Z2 Raportowanie z wykorzystaniem konstrukcji standardu SQL

W zapytaniach należy użyć składni SQL:

select … from … where … group by …

Dla zadań Z2 oraz Z3 należy podać wyliczony, sumaryczny czas wykonania zapytań (należy
skorzystać z zapytania:

select to_char(sysdate, 'HH24:MI:SS') from dual

– bezpośrednio

przed i po wykonaniu zapytania).
Do formatowania wyniku należy użyć poleceń, typu:

•

COLUMN country_region FORMAT A12 --

•

COLUMN calendar_year FORMAT 9999

•

set pagesize 100

-- rozmiar strony

•

set linesize 200

-- długość linii

Polecenia formatowania wydaje się przed wykonaniem zapytania SQL.

Podać:

1. Liczbę rekordów w tabelach:

TIMES, CHANNELS, CUSTOMERS, COUNTRIES, PRODUCTS,

PROMOTIONS, SALES

(w tym przypadku czasów wykonania nie podawać)

2. Podać sumaryczną wartość sprzedaży, sumaryczną liczbę sprzedanych jednostek

osiągniętą w poszczególnych regionach i krajach (wyświetlić

country_iso_code

) –

wynik uporządkować malejąco wg wartości sprzedaży,

3. Podać zapytania pozwalające na pobranie danych do raportu podsumowującego

wartość sprzedaży na poziomach: regionu, podregionu, kraju, roku, kwartału dla
regionów

„Americas”

i

„Middle East”

i lat 2000-2001 (schemat raportu, wg

zamieszczonej tabeli, zawiera ona dane przykładowe). W raporcie należy podać
wszystkie sumy częściowe – od poziomu kraj-kwartał do sumy globalnej (wszystkie
regiony, we wszystkich latach) z użyciem frazy group by <lista kolumn>. Dodatkowo
podać całościowy czas odczytu danych do raportu.

rok

kwart

2000 2001

suma

region

podregion

kraj

Q1

Q2 Q3 Q4

suma

Q1

Q2 Q3 Q4

suma

Americas

Northern

America

US 1 1 1 1 4

1 1 1 1 4 8

CA

1

1

1

1

4

1

1

1

1

4

8

suma

2 2 2 2

8

2 2 2 2

8

16

Southern

America

AR

1

1

1

1

4

1

1

1

1

4

8

suma

1 1 1 1

4

1 1 1 1

4

8

suma

3

3

3

3

12

3

3

3

3

12

24

Middle

East

Middle

East SA 1 1 1 1 4

1 1 1 1 4 8

suma

1

1

1

1

4

1

1

1

1

4

8

suma

1 1 1 1

4

1 1 1 1

4

8

suma

4 4 4 4

16

4 4 4 4

16

32

5

Z3 Raportowanie z wykorzystaniem fraz rollup, cube

Frazy cube, rollup, grouping sets umożliwiają rozszerzoną specyfikację poziomów
grupowania frazy group by standardu SQL. Zapytania wykorzystujące frazy cube, rollup,
grouping sets dostarczają pojedynczego zbioru odpowiedzi, który odpowiada połączeniu
(UNION ALL) danych pogrupowanych w różny sposób. Fraza rollup wylicza wartości
podanych funkcji agregujących na różnych poziomach grupowania – od najniższego do
najwyższego. Fraza cube rozszerza działanie frazy rollup na wszystkie możliwe kombinacje
poziomów agregacji. Funkcja grouping pozwala odróżnić informacje zwracane dzięki
zastosowaniu fraz cube i rollup (dodatkowe podsumowania, grouping zwraca 1) od danych
zwracanych dzięki zastosowaniu frazy group by (dane zwracane przez zapytanie standardu
SQL, grouping zwraca 0). Dzięki zastosowaniu funkcji grouping możliwe jest również
rozróżnienie wartości NULL zwracanych przez frazy cube i rollup (co wskazuje, że mamy do
czynienia z określonym poziomem grupowania) od wartości NULL, które charakteryzują
grupy danych. Funkcja grouping może być także wykorzystana w celu filtrowania
otrzymanych rezultatów. W celu uniknięcia konieczności specyfikowania użycia funkcji
grouping dla każdej z kolumn grupowania można wykorzystać funkcję
grouping_id(<lista_kolumn_grupowania>) zwracającą pojedynczą wartość (liczbę) której
reprezentacja bitowa określa jednoznacznie poziom grupowania.
Wyrażenie grouping sets umożliwia jawną specyfikację żądanych poziomów agregacji,
eliminując przetwarzanie pozostałych, zbędnych poziomów (co może mieć miejsce przy
zastosowaniu frazy cube).

Składnia

SELECT … [GROUPING( <kolumna_grupowania> )…]…

GROUP BY ROLLUP( <lista_kolumn_grupowania> )

SELECT … [GROUPING( <kolumna_grupowania> )…]…

GROUP BY CUBE( <lista_kolumn_grupowania> )

np.,

SELECT … GROUPING(channel_desc) AS Ch, GROUPING(calendar_month_desc) AS Mo,
GROUPING(country_iso_code) AS Co

…
GROUP BY ROLLUP(channels.channel_desc, calendar_month_desc,
countries.country_iso_code)

SELECT … GROUPING(channel_desc) AS Ch, GROUPING(calendar_month_desc) AS Mo,

GROUPING(country_iso_code) AS Co

…
GROUP BY CUBE(channels.channel_desc, calendar_month_desc,
countries.country_iso_code)

Fraza:

CUBE(a, b, c)

odpowiada wyrażeniu:

GROUPING SETS ((a, b, c), (a, b), (a, c), (b, c), (a), (b), (c), ())

1. Podać zapytanie(a) SQL odczytujące dane do raportu podsumowującego sprzedaż

(zadanie Z2.3) z wykorzystaniem frazy:

a. group by rollup(…),
b. group by cube (…),
c. group by grouping sets(…)
Dla każdego zapytania podać:

• 20 pierwszych rekordów uzyskanych w wyniku realizacji zapytania,

• całkowitą liczbę zwróconych rekordów

6

• czas wykonania zapytania (zapytań) i porównać z sumarycznym czasem

wykonania zapytania Z2.3.

2. Rozwinąć zapytanie Z3.1.b o funkcje grouping i grouping_id (dla wszystkich kolumn

wymienionych we frazie cube). Posortować wyniki w ten sposób, aby wszystkie
podsumowania znalazły się na początku zwracanego zbioru danych – rozpoczynając
od sumy globalnej. Podać czas wykonania zapytania, 20 pierwszych rekordów wyniku
oraz całkowitą liczbę uzyskanych rekordów.

Z4 Raportowanie z wykorzystaniem funkcji analitycznych SQL

Funkcje analityczne umożliwiają wyznaczenie m.in.

• pozycji (rankingów), kwantyli, udziałów procentowych

• wartości (średnich, sum) kroczących
• analizy szeregów czasowych

• znajdowanie pierwszych i ostatnich wartości w uporządkowanych grupach

• statystyk regresji liniowej

Przykład. RANK i DENSE_RANK umożliwiają wyznaczenie pozycji elementu w grupie.
Wskazanie frazy zapytania partycjonującego dane powoduje wyznaczenie rankingu dla
każdego zdefiniowanego podzbioru danych.

RANK ( ) OVER ( [<fraza zapytania partycjonującego dane>] order by … )

DENSE_RANK ( ) OVER ( [<fraza zapytania partycjonującego dane>] order by… )

np.,

RANK() OVER (ORDER BY SUM(amount_sold))

RANK() OVER (PARTITION BY calendar_month_desc ORDER BY SUM(amount_sold)

Funkcje kroczącego okna pozwalają na m.in. na wyznaczenie wartości skumulowanych
i średnich kroczących.

{SUM|AVG|MAX|MIN|COUNT|STDDEV|VARIANCE|FIRST_VALUE|LAST_VALUE}
({<wyrażenie_1> | *}) OVER
([PARTITION BY wyrażenie_2[,...])
ORDER BY wyrażenie_3 [<fraza_sortowania>]
[ASC| DESC] [NULLS FIRST | NULLS LAST] [,...]
{ROWS | RANGE} {BETWEEN
{UNBOUNDED PRECEDING | CURRENT ROW | wyrażenie {PRECEDING | FOLLOWING}} AND
{ UNBOUNDED FOLLOWING | CURRENT ROW | wyrażenie { PRECEDING | FOLLOWING } }

| { UNBOUNDED PRECEDING | CURRENT ROW | wyrażenie PRECEDING}}

gdzie
<wyrażenie_1> – argumenty funkcji analitycznej (wartości numeryczne),
<wyrażenie_2> – określa sposób partycjonowania danych,
<wyrażenie_3> – określa sposób sortowania danych
np.,

SUM(SUM(amount_sold)) OVER (PARTITION BY c.cust_id ORDER BY c.cust_id,
t.calendar_quarter_desc ROWS UNBOUNDED PRECEDING)

Jako argument funkcji analitycznej okna kroczącego należy podać funkcję agregującą
(tak jak w podanym przykładzie).

ROWS i RANGE definiują okno (zbiór danych) dla każdego z rekordów, dla którego liczona
jest funkcja analityczna:

7

• ROWS – określa okno poprzez podanie fizycznej liczby rekordów

• RANGE – określa okno poprzez podanie logicznego zakresu

Dla wyrażenie długości okna można użyć nst. konstrukcji:

• RANGE 10 PRECEDING
• ROWS 2 PRECEDING

• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (forma

skrócona RANGE UNBOUNDED PRECEDING)

• RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING (forma

skrócona RANGE UNBOUNDED FOLLOWING)

• RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY

FOLLOWING

1. Podać zapytanie tworzące ranking krajów według sprzedaży całkowitej (użyć RANK)

– miejsce I dla kraju o największej wartości sprzedaży

2. Podać zapytanie tworzące ranking krajów z regionu „Europe” w poszczególnych

latach według sprzedaży całkowitej (użyć RANK).

3. Napisać zapytanie pokazujące sumy wartości sprzedaży w USA kwartalnie

i narastająco kwartalnie w danym roku:

Rok

Kwartał

Suma

Narastająco

2000

1

9

9

2 20 29

3

10

39

4

3

42

2001

1

8

8

2 4

12

… … …

4. Napisać zapytanie pokazujące sumy wartości sprzedaży w USA kwartalnie oraz

3-kwartalną średnią kroczącą:

Rok

Kwartał

Suma

Średnia 3 kw

2000

1

9

9

2 20 14,5

3

10

13

4

3

11

2001

1

8

7

2 4

5

… … …

8

Z5 Raportowanie z wykorzystaniem modeli SQL

Fraza model wzbogaca SQL o właściwości charakterystyczne dla arkuszy kalkulacyjnych.
Fraza ta, na podstawie wyników zapytania, umożliwia tworzenie wielowymiarowych tabel, do
których możliwe jest zastosowanie formuł (zwanych regułami) wyznaczających żądane
wartości.
Wielowymiarowa tabela definiowana we frazie model powstaje w wyniku przypisania
elementów wyniku zapytania do jednej z 3 grup: partycji, wymiarów lub miar. Partycja
definiuje logiczne podzbiory danych (podobnie jak w zapytaniach wykorzystujących funkcje
analityczne). Reguły frazy model są stosowane do każdej z partycji niezależnie. Wymiary
definiują wielowymiarową tabelę i służą do identyfikacji komórek w ramach partycji.
Domyślnie, kombinacja wszystkich wymiarów powinna wskazywać pojedynczą komórkę
partycji. Miary przechowują wartości podlegające przetwarzaniu.
Fraza model umożliwia specyfikację reguł przetwarzania (modyfikowania, tworzenia
nowych) wartości miar określonych poprzez kolumny partycji i wymiarów.

Schemat przetwarzania

Zapytanie:

select country, product, year, sum(sales) as sales,…..

Definicja modelu – specyfikacja grup modelu dla elementów wyniku

• country – partycja
• product, year – wymiary

• sales – miara

Definicja modelu – specyfikacja reguł:

sales[prod1, 2006] = sales[prod1, 2004] + sales[prod1, 2005]

sales[prod2, 2006] = sales[prod2, 2004] + sales[prod2, 2005]

Końcowy wynik zapytania, przy zastosowaniu reguł modelu:

Country

Product

Year

Sales

Partycja

Wymiar

Wymiar

Miara

Poland Prod1 2004 1

Podstawowe wyniki zapytania

Poland Prod1 2005 2
Poland Prod2 2004 3
Poland Prod2 2005 4

Canada

Prod1

2004

5

Canada

Prod1

2005

6

Canada

Prod2

2004

7

Canada

Prod2

2005

8

Poland Prod1 2006 3

Wynik zastosowania reguł

Poland Prod2 2006 7

Canada Prod1 2006 11
Canada Prod2 2006 15

Fraza model umożliwia:

• adresowanie komórek z użyciem wartości wymiarów, np.: sales[product='Prod1',

year=2004] (można również użyć sales['Prod1', 2004]),

9

• specyfikację reguł operujących na danych, np.: sales[product='Prod1', year=2006] =

MAX(sales) [product='Prod1', year between 2003 and 2005]

• modyfikację i/lub tworzenie nowych wartości miar (opcja może być specyfikowana

dla każdej z reguł)

o UPSERT – opcja domyślna, tworzy wartości komórek, które nie istnieją w

danych wejściowych a modyfikuje wartości komórek istniejących,

o UPSERT ALL – opcja UPSERT dla szerszego zakresu reguł
o UPDATE – tylko modyfikacja komórek istniejących

• stosowanie znaków wieloznacznych w odwoływaniu się do wymiarów:

o ANY, IS ANY oznacza dowolną wartość wymiaru, np.:

sales[ANY, 2006] =

sales['Prod1', 2005]

• dostęp do wartości wymiaru z użyciem funkcji CV() – funkcję można użyć po prawej

stronie reguły w celu uzyskania dostępu do wartości wymiaru komórki opisywanej po
lewej stronie reguły; funkcja umożliwia tworzenie reguł o wyższym stopniu ogólności,
np. 3 reguły

sales[product='Prod1', year=2006] = 1.2 * sales['Prod1', 2005]
sales[product='Prod2', year=2006] = 1.2 * sales['Prod2', 2005]
sales[product='Prod3', year=2006] = 1.2 * sales['Prod3', 2005]

można zastąpić regułą:

sales[product in ('Prod1','Prod3','Prod3'), year=2006] = 1.2 *
sales[CV(product), 2005]

• określenie kolejności przetwarzania danych – w przypadku modyfikacji wartości może

okazać się konieczne wymuszenie szczególnego porządku przetwarzania wartości
wymiaru – należy użyć opcji ORDER BY, np.

sales[product IS ANY, year BETWEEN 2000 AND 2003] ORDER BY year = 1.05

* sales[CV(product), CV(year)-1]

co zapewnia dostęp do wartości lat w porządku chronologicznym

• określenie automatycznego uporządkowania reguł od siebie zależnych (RULES

AUTOMATIC ORDER) – w przypadku następujących reguł, dwie ostatnie reguły
będą przetworzone przed pierwszą:

{sales[product='Prod1', year=2001] = sales[product='Prod1', year=2000]
+ sales[product='Prod1', year=1999]
sales[product='Prod1', year=2000] = 50000,

sales[product='Prod1', year=1999] = 40000}

• iteracyjne przetwarzanie reguł – do czasu osiągnięcia warunku końcowego, np.:

MODEL DIMENSION BY (…) MEASURES (…)
RULES ITERATE (<liczba_iteracji>) (<reguła>)

oprócz ITERATE możliwe jest użycie frazy UNTIL,

• odwołanie się (tylko do odczytu) do innych modeli referencyjnych.

Składnia

MODEL
[<globalne opcje referencji>]
[<modele referencyjne>]
[MAIN <nazwa-główna>]
[PARTITION BY (<kolumny>)]

DIMENSION BY (<kolumny >)
MEASURES (<kolumny >)
[<opcje referencji>]
[RULES] <opcje reguł>
(<reguła>, <reguła>,.., <reguła>)

<globalne opcje referencji> ::= <opcje referencji> <ret-opt>

<ret-opt> ::= RETURN {ALL|UPDATED} ROWS

<opcje referencji> ::=
[IGNORE NAV | [KEEP NAV]

10

[UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]

<opcje reguł> ::=
[UPDATE | UPSERT | UPSERT ALL]
[AUTOMATIC ORDER | SEQUENTIAL ORDER]

[ITERATE (<liczba>) [UNTIL <warunek]]

<modele referencyjne> ::= REFERENCE ON <ref-name> ON (<zapytanie>)
DIMENSION BY (<kolumny>) MEASURES (<kolumny >) <opcje referencji>

np.

SELECT SUBSTR(country,1,20) country, SUBSTR(product,1,15) product, year,
sales
FROM sales_view
WHERE country in ('Italy', 'Japan')

MODEL
RETURN UPDATED ROWS
MAIN simple_model
PARTITION BY (country)

DIMENSION BY (product, year)
MEASURES (sales)
RULES
(sales['Bounce', 2001] = 1000,
sales['Bounce', 2002] = sales['Bounce', 2001] + sales['Bounce', 2000],
sales['Y Box', 2002] = sales['Y Box', 2001])

ORDER BY country, product, year;

gdzie:

CREATE VIEW sales_view AS
SELECT country_name country, prod_name product, calendar_year year,
SUM(amount_sold) sales, COUNT(amount_sold) cnt,
MAX(calendar_year) KEEP (DENSE_RANK FIRST ORDER BY SUM(amount_sold) DESC)
OVER (PARTITION BY country_name, prod_name) best_year,

MAX(calendar_year) KEEP (DENSE_RANK LAST ORDER BY SUM(amount_sold) DESC)
OVER (PARTITION BY country_name, prod_name) worst_year
FROM sales, times, customers, countries, products
WHERE sales.time_id = times.time_id AND sales.prod_id = products.prod_id AND
sales.cust_id =customers.cust_id AND
customers.country_id=countries.country_id
GROUP BY country_name, prod_name, calendar_year;

1. Utworzyć perspektywę SALES_VIEW (wg podanej powyżej definicji).
2. W oparciu o SALES_VIEW utworzyć jeden model o następującej charakterystyce:

a. model skoryguje, że po roku 1999 wartość sprzedaży podkładki pod mysz

(‘Mouse Pad’) w Polsce wynosiła 5,

b. model zakłada, że wartość sprzedaży produktu '128MB Memory Card' w roku

2006 w USA i Wielkiej Brytanii wzrośnie o 15% w stosunku do średniej
sprzedaży w latach 1999-2005,

c. model zakłada, że wartość sprzedaży produktu 'Mouse Pad' w roku 2006 w

Polsce wzrośnie o 5% w stosunku do maksymalnej wartości dotychczas
zarejestrowanej sprzedaży tego produktu,

d. model wypisze przewidywaną wartość wszystkich produktów, których

sprzedaż prognozuje się w roku 2006