OracleSQL.pdf

ORACLE

System Zarządzania Bazą

Danych Oracle

Oracle Data Warehouse

wersja 1.4 (aktualizacja 2007)

Politechnika Śląska 2005

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.

PROD_ID

CUST_ID

TIME_ID

CHANNEL_ID

PROMO_ID

QUANTITY_SOLD

AMOUNT_SOLD

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

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

suma

Southern

America

suma

Middle

East

Middle East

suma

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

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: