Sprawozdanie z zadania laboratoryjnego
Temat: Zapytania do bazy danych i zapoznanie się z
rozszerzeniami języka Oracle SQL
Prowadzący: mgr inż. Andrzej Rozmus
Wykonujący: Bujalski Paweł, Kowalewski Damian, Kowalik Adrian,
Kurek Janusz
Grupa szkoleniowa: I7E1S1
Logowanie do systemu i edycja ustawień
Do bazy danych zalogowaliśmy się przy użyciu edytora SQL*Plus na konto sh@orcl przy
użyciu hasła oki320. Logowanie przebiegło pomyślnie, w związku z czym nie było konieczne
dokonywanie modyfikacji w pliku tsnames.ora. Po zalogowaniu użyliśmy poleceń do
modyfikacji formatu wyświetlanych wyników:
column wartosc format 999,999,999.99;
Polecenie to powoduje, ze wartości kolumny „wartosc” wyświetlane są w formacie, gdzie
separatorem jest przecinek, a część ułamkowa wyświetlana jest po kropce i składa się z
dwóch liczb.
set pagesize 1000;
Dzięki temu poleceniu można ustawić długość strony, a dokładniej liczbę wierszy jakie
zostaną wyświetlone. Gdy wartość ta nie zostanie ustawiona, wówczas zapytanie posiadające
np. 100 rekordów będzie wyświetlane w „paczkach” po np. 10 wierszy, a każda z nich
rozpoczynać będzie się od nazw kolumn.
set linesize 1000;
Polecenie to działa tak jak powyższe, z tym, ze określa liczbę znaków jakie można zmieścić w
jednej linii. Tutaj także ustawiliśmy wartość 1000, aby zapewnić prawidłowe wyświetlanie
wyników zapytania. Gdyby długość linii nie została ustawiona, wówczas wyniki zapytania
byłby „zawijane”, co utrudnia ich odczytywanie.
set timing on;
Użycie tego polecenia spowoduje, że po wyświetleniu wyników każdego zapytania pojawi się
informacja dotycząca długości czasu wykonywanego zapytania.
Liczba rekordów w poszczególnych tabelach.
Liczbę rekordów danej tabeli można pobrać za pomocą następującego zapytania SQL:
SELECT count(*) [AS alias] FROM tabela;
Słowem-kluczem jest tutaj funkcja count(*), która zwraca liczbę wszystkich rekordów
zapisanych w tabeli tabela. Opcjonalnie, wyświetlonemu wynikowi można nadać alias, który
będzie wyświetlany na ekranie. W przypadku, gdy alias ten nie zostanie ustawiony, zostanie
wyświetlony napis count(*).
• Liczba rekordów tabeli TIMES:
SELECT count(*) AS liczba FROM times;
Wynik powyższego zapytania:
LICZBA
----------
1826
Elapsed: 00:00:00.00
• Liczba rekordów tabeli CHANNELS:
SELECT count(*) AS liczba FROM channels;
LICZBA
----------
5
Elapsed: 00:00:00.00
• Liczba rekordów tabeli CUSTOMERS:
SELECT count(*) AS liczba FROM customers;
Wynik powyższego zapytania:
LICZBA
----------
55500
Elapsed: 00:00:00.01
• Liczba rekordów tabeli COUNTRIES:
SELECT count(*) AS liczba FROM countries;
LICZBA
----------
23
Elapsed: 00:00:00.00
• Liczba rekordów tabeli PRODUCTS:
SELECT count(*) AS liczba FROM products;
Wynik powyższego zapytania:
LICZBA
----------
72
Elapsed: 00:00:00.00
• Liczba rekordów tabeli PROMOTIONS:
SELECT count(*) AS liczba FROM promotions;
LICZBA
----------
503
Elapsed: 00:00:00.00
• Liczba rekordów tabeli SALES:
SELECT count(*) AS liczba FROM sales;
Wynik powyższego zapytania:
LICZBA
----------
918843
Elapsed: 00:00:00.00
Sumaryczna wartość sprzedaży na poziomach kanału sprzedaży, miesiąca i kraju przyjmując
kanały sprzedaży 'Direct Sales', 'Internet', miesiące wrzesień oraz październik roku 2000 oraz
jako kraje Stany Zjednoczone oraz Wielką Brytanię.
Zapytanie rozpoczyna się od wybrania nazwy kraju z tabeli countries, kanału z tabeli
channels, miesiąca z tabeli Times oraz liczby sprzedanych produktów z tabeli sales.
Następnie następuje zdefiniowanie warunków, które określają, jakie dane mają zostać
pobrane z bazy danych. Warunki te określają, że interesujące nas kanały są kanałami Direct
Sales oraz Internet, miesiące to wrzesień oraz październik 2000 roku (odpowiednio 2000-09 i
2000-10 oraz ), a kraje to United States of America oraz United Kingdom. Pobrane dane
muszą zostać pogrupowane, tak aby wyświetlona została prawidłowa suma transakcji.
Grupowane są one za pomocą klauzuli GROUP BY według kraju, kanału, a następnie
miesiąca. Zapis taki oznacza, że zostaną wyświetlone sumy poszczególnych kanałów
dystrybucji dla każdego miesiąca w każdym kraju. Wyrażenie dodatkowo posortowane
zostało malejąco według sumy, nazwy kanału, numeru miesiąca oraz nazwy kraju. Całe
zapytanie przedstawione jest poniżej:
SELECT
co.country_name
AS
kraj,
ch.channel_desc
AS
kanal,
t.calendar_month_desc AS miesiac, SUM(s.amount_sold) AS suma
FROM sales s, customers cu, times t, channels ch, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND s.channel_id = ch.channel_id
AND cu.country_id = co.country_id
AND ch.channel_desc IN ('Direct Sales','Internet')
AND t.calendar_month_desc IN ('2000-09', '2000-10')
AND (co.country_name='United States of America' OR co.country_name='United
Kingdom')
GROUP BY co.country_name, ch.channel_desc, t.calendar_month_desc
ORDER BY suma DESC, kanal DESC, miesiac DESC, kraj DESC;
Po wykonaniu tego zapytania wyświetlone zostały następujące wyniki:
KRAJ KANAL MIESIAC SUMA
---------------------------------------- -------------------- -------- ---------------
United States of America Direct Sales 2000-10 682,296.59
United States of America Direct Sales 2000-09 638,200.81
United States of America Internet 2000-10 137,054.29
United States of America Internet 2000-09 124,223.75
United Kingdom Direct Sales 2000-10 91,925.43
United Kingdom Direct Sales 2000-09 85,222.92
United Kingdom Internet 2000-09 16,569.36
United Kingdom Internet 2000-10 14,539.14
8 rows selected.
Elapsed: 00:00:00.14
Sumaryczna wartość sprzedaży oraz sumaryczna liczba sprzedanych jednostek osiągniętych w
poszczególnych regionach i krajach (wyświetlić country_iso_code) – wynik uporządkować
malejąco wg wartości sprzedaży.
W zapytaniu tym należy wybrać region oraz kod ISO z tabeli countries, a także łączną liczbę
oraz wartość sprzedanych produktów z tabeli sales. Z racji tego, że należy wyświetlić dane
dla wszystkich krajów z osobna, nie trzeba formułować dodatkowych warunków zapytania.
Warunki ograniczają się do „nakierowania” do poszczególnych tabel przy użyciu
identyfikatorów kraju oraz klienta. Sumaryczna wartość sprzedaży oraz sprzedanych
produktów ma być pogrupowana według regionu oraz kodu ISO państwa, a wyniki
posortowane zgodnie z założeniami zadania. Zapytanie przedstawia się następująco:
SELECT co.country_region AS region, co.country_iso_code AS kod,
SUM(s.quantity_sold) AS liczba, SUM(s.amount_sold) AS wartosc
FROM sales s, customers cu, countries co
WHERE s.cust_id = cu.cust_id
AND cu.country_id = co.country_id
GROUP BY co.country_region, co.country_iso_code
ORDER BY wartosc DESC, liczba DESC, region DESC, kod DESC;
Wyniki po wykonaniu zapytania prezentują się następująco:
REGION KO LICZBA WARTOSC
-------------------- -- ---------- ---------------
Americas US 526212 52,910,773.15
Europe DE 81978 9,210,129.22
Asia JP 60183 7,207,880.09
Europe GB 58638 6,393,762.94
Europe IT 42570 4,854,505.28
Oceania AU 33685 3,962,293.15
Europe FR 33078 3,776,270.13
Asia SG 25253 3,063,093.70
Americas CA 22858 2,686,510.09
Europe ES 17136 2,090,863.44
Europe DK 16651 1,977,764.79
Americas BR 180 36,051.88
Americas AR 202 14,646.64
Europe PL 18 8,447.14
Europe TR 168 7,836.62
Asia CN 19 3,828.19
Middle East SA 7 903.83
Oceania NZ 7 270.93
18 rows selected.
Elapsed: 00:00:01.20
Wartość sprzedaży na poziomach: regionu, podregionu, kraju, roku, kwartału dla regionów
„Americas” i „Middle East” i lat 2000-2001. W zestawieniu należy podać wszystkie sumy
częściowe – od poziomu kraj-kwartał do sumy globalnej (wszystkie regiony, we wszystkich
latach)
• group by <lista kolumn>
Zgodnie z wytycznymi w poleceniu w zapytaniu tym wybrane zostały kolumny region,
podregion oraz kraj z tabeli countries, rok i kwartał z tabeli times, a także suma sprzedaży, na
podstawie tabeli sales. Ograniczenia, jakie zostały tu uwzględnione dotyczą poszczególnych
kwartałów, począwszy od kwartału pierwszego roku 2000, a skończywszy na kwartale
ostatnim roku 2001. Ponadto regiony ograniczone zostały tylko do dwóch: Americas oraz
Middle East. Dane te zostały pogrupowane najpierw według regionu, podregionu, kraju, roku
oraz kwartału, co oznacza, że zsumowane zostały wartości sprzedaży dla każdego kraju, w
każdym kwartale wspomnianych wcześniej lat. Niestety sama klauzula GROUP BY, nie
pozwala na obliczanie sum częściowych, np. dla poszczególnych kwartałów, podregionów,
regionów, itp. Zapytanie przedstawione zostało poniżej:
SELECT co.country_region AS region, co.country_subregion AS podregion,
co.country_name AS kraj, t.calendar_year AS rok, t.calendar_quarter_desc AS
kwartal, SUM(s.amount_sold) AS wartosc
FROM sales s, customers cu, times t, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND cu.country_id = co.country_id
AND t.calendar_quarter_desc BETWEEN '2000-01' AND '2001-04'
AND (co.country_region='Americas' OR co.country_region='Middle East')
GROUP BY co.country_region, co.country_subregion, co.country_name,
t.calendar_year, t.calendar_quarter_desc
ORDER BY region, podregion, kraj, rok ASC, kwartal ASC, wartosc DESC;
Wyniki powyższego zapytania:
REGION PODREGION KRAJ ROK KWARTAL WARTOSC
-------------------- ------------------------------ ---------------------------------------- ---------- ------- ---------------
Americas Northern America Canada 2000 2000-01 150,519.09
Americas Northern America Canada 2000 2000-02 154,946.75
Americas Northern America Canada 2000 2000-03 167,878.76
Americas Northern America Canada 2000 2000-04 177,642.49
Americas Northern America Canada 2001 2001-01 169,569.41
Americas Northern America Canada 2001 2001-02 208,044.87
Americas Northern America Canada 2001 2001-03 240,496.05
Americas Northern America Canada 2001 2001-04 241,107.88
Americas Northern America United States of America 2000 2000-01 3,293,493.49
Americas Northern America United States of America 2000 2000-02 2,929,280.87
Americas Northern America United States of America 2000 2000-03 3,387,621.66
Americas Northern America United States of America 2000 2000-04 3,510,198.19
Americas Northern America United States of America 2001 2001-01 3,613,827.97
Americas Northern America United States of America 2001 2001-02 3,698,537.18
Americas Northern America United States of America 2001 2001-03 3,795,742.62
Americas Northern America United States of America 2001 2001-04 3,953,149.85
Americas Southern America Argentina 2000 2000-01 1,386.85
Americas Southern America Argentina 2000 2000-02 1,255.26
Americas Southern America Argentina 2000 2000-03 721.87
Americas Southern America Argentina 2001 2001-02 297.52
Americas Southern America Argentina 2001 2001-04 405.17
Americas Southern America Brazil 2000 2000-01 1,530.91
Americas Southern America Brazil 2000 2000-02 129.77
Americas Southern America Brazil 2000 2000-03 2,195.83
Americas Southern America Brazil 2000 2000-04 9,522.53
Americas Southern America Brazil 2001 2001-01 1,671.36
Americas Southern America Brazil 2001 2001-02 432.63
Americas Southern America Brazil 2001 2001-03 352.31
Americas Southern America Brazil 2001 2001-04 2,741.59
Middle East Middle East Saudi Arabia 2000 2000-01 274.94
Middle East Middle East Saudi Arabia 2001 2001-01 628.89
31 rows selected.
Elapsed: 00:00:00.75
Jak widać powyżej wyświetlone zostały sumy sprzedaży dla każdego kraju w każdym
kwartale.
• group by rollup(…)
Zapytanie to zostało skonstruowane analogicznie jak powyższe, z jedną zasadniczą różnicą: w
klauzuli GROUP BY występuje funkcja ROLLUP(), która grupuje dane, w taki sposób, że
pokazuje sumy częściowe najpierw dla każdego kraju w każdym kwartale, następnie sumę dla
całego roku kalendarzowego (2000 i 2001), później całkowitą sumę sprzedaży na poziomie
kraju, w dalszej kolejności na poziomie podregionu, kończąc na regionie. Na końcu zbioru
danych wyświetlona zostaje suma globalna. Zapytanie to przedstawia się następująco:
SELECT co.country_region AS region, co.country_subregion AS podregion,
co.country_name AS kraj, t.calendar_year AS rok, t.calendar_quarter_desc AS
kwartal, SUM(s.amount_sold) AS wartosc
FROM sales s, customers cu, times t, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND cu.country_id = co.country_id
AND t.calendar_quarter_desc BETWEEN '2000-01' AND '2001-04'
AND (co.country_region='Americas' OR co.country_region='Middle East')
GROUP BY ROLLUP (co.country_region, co.country_subregion, co.country_name,
t.calendar_year, t.calendar_quarter_desc)
ORDER BY region, podregion, kraj, rok ASC, kwartal ASC, wartosc DESC;
REGION PODREGION KRAJ ROK KWARTAL WARTOSC
-------------------- ------------------------------ ---------------------------------------- ---------- ------- ---------------
Americas Northern America Canada 2000 2000-01 150,519.09
Americas Northern America Canada 2000 2000-02 154,946.75
Americas Northern America Canada 2000 2000-03 167,878.76
Americas Northern America Canada 2000 2000-04 177,642.49
Americas Northern America Canada 2000 650,987.09
Americas Northern America Canada 2001 2001-01 169,569.41
Americas Northern America Canada 2001 2001-02 208,044.87
Americas Northern America Canada 2001 2001-03 240,496.05
Americas Northern America Canada 2001 2001-04 241,107.88
Americas Northern America Canada 2001 859,218.21
Americas Northern America Canada
1,510,205.30
Americas Northern America United States of America 2000 2000-01 3,293,493.49
Americas Northern America United States of America 2000 2000-02 2,929,280.87
Americas Northern America United States of America 2000 2000-03 3,387,621.66
Americas Northern America United States of America 2000 2000-04 3,510,198.19
Americas Northern America United States of America 2000 13,120,594.21
Americas Northern America United States of America 2001 2001-01 3,613,827.97
Americas Northern America United States of America 2001 2001-02 3,698,537.18
Americas Northern America United States of America 2001 2001-03 3,795,742.62
Americas Northern America United States of America 2001 2001-04 3,953,149.85
Americas Northern America United States of America 2001 15,061,257.62
Americas Northern America United States of America
28,181,851.83
Americas Northern America
29,692,057.13
Americas Southern America Argentina 2000 2000-01 1,386.85
Americas Southern America Argentina 2000 2000-02 1,255.26
Americas Southern America Argentina 2000 2000-03 721.87
Americas Southern America Argentina 2000 3,363.98
Americas Southern America Argentina 2001 2001-02 297.52
Americas Southern America Argentina 2001 2001-04 405.17
Americas Southern America Argentina 2001 702.69
Americas Southern America Argentina
4,066.67
Americas Southern America Brazil 2000 2000-01 1,530.91
Americas Southern America Brazil 2000 2000-02 129.77
Americas Southern America Brazil 2000 2000-03 2,195.83
Americas Southern America Brazil 2000 2000-04 9,522.53
Americas Southern America Brazil 2000 13,379.04
Americas Southern America Brazil 2001 2001-01 1,671.36
Americas Southern America Brazil 2001 2001-02 432.63
Americas Southern America Brazil 2001 2001-03 352.31
Americas Southern America Brazil 2001 2001-04 2,741.59
Americas Southern America Brazil 2001 5,197.89
Americas Southern America Brazil
18,576.93
Americas Southern America
22,643.60
Americas
29,714,700.73
Middle East Middle East Saudi Arabia 2000 2000-01 274.94
Middle East Middle East Saudi Arabia 2000 274.94
Middle East Middle East Saudi Arabia 2001 2001-01 628.89
Middle East Middle East Saudi Arabia 2001 628.89
Middle East Middle East Saudi Arabia 903.83
Middle East Middle East
903.83
Middle East
903.83
29,715,604.56
52 rows selected.
Elapsed: 00:00:00.81
• group by cube(…)
Podobnie jak dwa poprzednie zapytania, to także narzucone ma ograniczenia na lata, kwartały
oraz regiony. W tym przypadku również zmianie uległa klauzula GROUP BY uzupełniona o
funkcję CUBE(). Grupy jakie zostały tworzone, to podsumowania dla każdych grup, np. suma
sprzedaży na poziomie danego regionu, na poziomie każdego kwartału, roku, kraju, itp.
Możliwe jest wyświetlenie tutaj globalnej sumy sprzedaży. Poniżej znajduje się kompletne
zapytanie:
SELECT co.country_region AS region, co.country_subregion AS podregion,
co.country_name AS kraj, t.calendar_year AS rok, t.calendar_quarter_desc AS
kwartal, SUM(s.amount_sold) AS wartosc
FROM sales s, customers cu, times t, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND cu.country_id = co.country_id
AND t.calendar_quarter_desc BETWEEN '2000-01' AND '2001-04'
AND (co.country_region='Americas' OR co.country_region='Middle East')
GROUP BY CUBE (co.country_region, co.country_subregion, co.country_name,
t.calendar_year, t.calendar_quarter_desc)
ORDER BY region, podregion, kraj, rok ASC, kwartal ASC, wartosc DESC;
A oto jego wyniki:
REGION PODREGION KRAJ ROK KWARTAL WARTOSC
-------------------- ------------------------------ ---------------------------------------- ---------- ------- ---------------
Americas Northern America Canada 2000 2000-01 150,519.09
Americas Northern America Canada 2000 2000-02 154,946.75
Americas Northern America Canada 2000 2000-03 167,878.76
Americas Northern America Canada 2000 2000-04 177,642.49
Americas Northern America Canada 2000 650,987.09
Americas Northern America Canada 2001 2001-01 169,569.41
Americas Northern America Canada 2001 2001-02 208,044.87
Americas Northern America Canada 2001 2001-03 240,496.05
Americas Northern America Canada 2001 2001-04 241,107.88
Americas Northern America Canada 2001 859,218.21
Americas Northern America Canada 2000-01 150,519.09
Americas Northern America Canada 2000-02 154,946.75
Americas Northern America Canada 2000-03 167,878.76
Americas Northern America Canada 2000-04 177,642.49
Americas Northern America Canada 2001-01 169,569.41
Americas Northern America Canada 2001-02 208,044.87
Americas Northern America Canada 2001-03 240,496.05
Americas Northern America Canada 2001-04 241,107.88
Americas Northern America Canada 1,510,205.30
Americas Northern America United States of America 2000 2000-01 3,293,493.49
Americas Northern America United States of America 2000 2000-02 2,929,280.87
Americas Northern America United States of America 2000 2000-03 3,387,621.66
Americas Northern America United States of America 2000 2000-04 3,510,198.19
Americas Northern America United States of America 2000 13,120,594.21
Americas Northern America United States of America 2001 2001-01 3,613,827.97
Americas Northern America United States of America 2001 2001-02 3,698,537.18
Americas Northern America United States of America 2001 2001-03 3,795,742.62
Americas Northern America United States of America 2001 2001-04 3,953,149.85
Americas Northern America United States of America 2001 15,061,257.62
Americas Northern America United States of America 2000-01 3,293,493.49
Americas Northern America United States of America 2000-02 2,929,280.87
Americas Northern America United States of America 2000-03 3,387,621.66
Americas Northern America United States of America 2000-04 3,510,198.19
Americas Northern America United States of America 2001-01 3,613,827.97
Americas Northern America United States of America 2001-02 3,698,537.18
Americas Northern America United States of America 2001-03 3,795,742.62
Americas Northern America United States of America 2001-04 3,953,149.85
Americas Northern America United States of America 28,181,851.83
Americas Northern America 2000 2000-01 3,444,012.58
Americas Northern America 2000 2000-02 3,084,227.62
Americas Northern America 2000 2000-03 3,555,500.42
Americas Northern America 2000 2000-04 3,687,840.68
Americas Northern America 2000 13,771,581.30
Americas Northern America 2001 2001-01 3,783,397.38
Americas Northern America 2001 2001-02 3,906,582.05
Americas Northern America 2001 2001-03 4,036,238.67
Americas Northern America 2001 2001-04 4,194,257.73
Americas Northern America 2001 15,920,475.83
Americas Northern America 2000-01 3,444,012.58
Americas Northern America 2000-02 3,084,227.62
Americas Northern America 2000-03 3,555,500.42
Americas Northern America 2000-04 3,687,840.68
Americas Northern America 2001-01 3,783,397.38
Americas Northern America 2001-02 3,906,582.05
Americas Northern America 2001-03 4,036,238.67
Americas Northern America 2001-04 4,194,257.73
Americas Northern America 29,692,057.13
Americas Southern America Argentina 2000 2000-01 1,386.85
Americas Southern America Argentina 2000 2000-02 1,255.26
Americas Southern America Argentina 2000 2000-03 721.87
Americas Southern America Argentina 2000 3,363.98
Americas Southern America Argentina 2001 2001-02 297.52
Americas Southern America Argentina 2001 2001-04 405.17
Americas Southern America Argentina 2001 702.69
Americas Southern America Argentina 2000-01 1,386.85
Americas Southern America Argentina 2000-02 1,255.26
Americas Southern America Argentina 2000-03 721.87
Americas Southern America Argentina 2001-02 297.52
Americas Southern America Argentina 2001-04 405.17
Americas Southern America Argentina 4,066.67
Americas Southern America Brazil 2000 2000-01 1,530.91
Americas Southern America Brazil 2000 2000-02 129.77
Americas Southern America Brazil 2000 2000-03 2,195.83
Americas Southern America Brazil 2000 2000-04 9,522.53
Americas Southern America Brazil 2000 13,379.04
Americas Southern America Brazil 2001 2001-01 1,671.36
Americas Southern America Brazil 2001 2001-02 432.63
Americas Southern America Brazil 2001 2001-03 352.31
Americas Southern America Brazil 2001 2001-04 2,741.59
Americas Southern America Brazil 2001 5,197.89
Americas Southern America Brazil 2000-01 1,530.91
Americas Southern America Brazil 2000-02 129.77
Americas Southern America Brazil 2000-03 2,195.83
Americas Southern America Brazil 2000-04 9,522.53
Americas Southern America Brazil 2001-01 1,671.36
Americas Southern America Brazil 2001-02 432.63
Americas Southern America Brazil 2001-03 352.31
Americas Southern America Brazil 2001-04 2,741.59
Americas Southern America Brazil 18,576.93
Americas Southern America 2000 2000-01 2,917.76
Americas Southern America 2000 2000-02 1,385.03
Americas Southern America 2000 2000-03 2,917.70
Americas Southern America 2000 2000-04 9,522.53
Americas Southern America 2000 16,743.02
Americas Southern America 2001 2001-01 1,671.36
Americas Southern America 2001 2001-02 730.15
Americas Southern America 2001 2001-03 352.31
Americas Southern America 2001 2001-04 3,146.76
Americas Southern America 2001 5,900.58
Americas Southern America 2000-01 2,917.76
Americas Southern America 2000-02 1,385.03
Americas Southern America 2000-03 2,917.70
Americas Southern America 2000-04 9,522.53
Americas Southern America 2001-01 1,671.36
Americas Southern America 2001-02 730.15
Americas Southern America 2001-03 352.31
Americas Southern America 2001-04 3,146.76
Americas Southern America 22,643.60
Americas Argentina 2000 2000-01 1,386.85
Americas Argentina 2000 2000-02 1,255.26
Americas Argentina 2000 2000-03 721.87
Americas Argentina 2000 3,363.98
Americas Argentina 2001 2001-02 297.52
Americas Argentina 2001 2001-04 405.17
Americas Argentina 2001 702.69
Americas Argentina 2000-01 1,386.85
Americas Argentina 2000-02 1,255.26
Americas Argentina 2000-03 721.87
Americas Argentina 2001-02 297.52
Americas Argentina 2001-04 405.17
Americas Argentina 4,066.67
Americas Brazil 2000 2000-01 1,530.91
Americas Brazil 2000 2000-02 129.77
Americas Brazil 2000 2000-03 2,195.83
Americas Brazil 2000 2000-04 9,522.53
Americas Brazil 2000 13,379.04
Americas Brazil 2001 2001-01 1,671.36
Americas Brazil 2001 2001-02 432.63
Americas Brazil 2001 2001-03 352.31
Americas Brazil 2001 2001-04 2,741.59
Americas Brazil 2001 5,197.89
Americas Brazil 2000-01 1,530.91
Americas Brazil 2000-02 129.77
Americas Brazil 2000-03 2,195.83
Americas Brazil 2000-04 9,522.53
Americas Brazil 2001-01 1,671.36
Americas Brazil 2001-02 432.63
Americas Brazil 2001-03 352.31
Americas Brazil 2001-04 2,741.59
Americas Brazil 18,576.93
Americas Canada 2000 2000-01 150,519.09
Americas Canada 2000 2000-02 154,946.75
Americas Canada 2000 2000-03 167,878.76
Americas Canada 2000 2000-04 177,642.49
Americas Canada 2000 650,987.09
Americas Canada 2001 2001-01 169,569.41
Americas Canada 2001 2001-02 208,044.87
Americas Canada 2001 2001-03 240,496.05
Americas Canada 2001 2001-04 241,107.88
Americas Canada 2001 859,218.21
Americas Canada 2000-01 150,519.09
Americas Canada 2000-02 154,946.75
Americas Canada 2000-03 167,878.76
Americas Canada 2000-04 177,642.49
Americas Canada 2001-01 169,569.41
Americas Canada 2001-02 208,044.87
Americas Canada 2001-03 240,496.05
Americas Canada 2001-04 241,107.88
Americas Canada 1,510,205.30
Americas United States of America 2000 2000-01 3,293,493.49
Americas United States of America 2000 2000-02 2,929,280.87
Americas United States of America 2000 2000-03 3,387,621.66
Americas United States of America 2000 2000-04 3,510,198.19
Americas United States of America 2000 13,120,594.21
Americas United States of America 2001 2001-01 3,613,827.97
Americas United States of America 2001 2001-02 3,698,537.18
Americas United States of America 2001 2001-03 3,795,742.62
Americas United States of America 2001 2001-04 3,953,149.85
Americas United States of America 2001 15,061,257.62
Americas United States of America 2000-01 3,293,493.49
Americas United States of America 2000-02 2,929,280.87
Americas United States of America 2000-03 3,387,621.66
Americas United States of America 2000-04 3,510,198.19
Americas United States of America 2001-01 3,613,827.97
Americas United States of America 2001-02 3,698,537.18
Americas United States of America 2001-03 3,795,742.62
Americas United States of America 2001-04 3,953,149.85
Americas United States of America 28,181,851.83
Americas 2000 2000-01 3,446,930.34
Americas 2000 2000-02 3,085,612.65
Americas 2000 2000-03 3,558,418.12
Americas 2000 2000-04 3,697,363.21
Americas 2000 13,788,324.32
Americas 2001 2001-01 3,785,068.74
Americas 2001 2001-02 3,907,312.20
Americas 2001 2001-03 4,036,590.98
Americas 2001 2001-04 4,197,404.49
Americas 2001 15,926,376.41
Americas 2000-01 3,446,930.34
Americas 2000-02 3,085,612.65
Americas 2000-03 3,558,418.12
Americas 2000-04 3,697,363.21
Americas 2001-01 3,785,068.74
Americas 2001-02 3,907,312.20
Americas 2001-03 4,036,590.98
Americas 2001-04 4,197,404.49
Americas 29,714,700.73
Middle East Middle East Saudi Arabia 2000 2000-01 274.94
Middle East Middle East Saudi Arabia 2000 274.94
Middle East Middle East Saudi Arabia 2001 2001-01 628.89
Middle East Middle East Saudi Arabia 2001 628.89
Middle East Middle East Saudi Arabia 2000-01 274.94
Middle East Middle East Saudi Arabia 2001-01 628.89
Middle East Middle East Saudi Arabia 903.83
Middle East Middle East 2000 2000-01 274.94
Middle East Middle East 2000 274.94
Middle East Middle East 2001 2001-01 628.89
Middle East Middle East 2001 628.89
Middle East Middle East 2000-01 274.94
Middle East Middle East 2001-01 628.89
Middle East Middle East 903.83
Middle East Saudi Arabia 2000 2000-01 274.94
Middle East Saudi Arabia 2000 274.94
Middle East Saudi Arabia 2001 2001-01 628.89
Middle East Saudi Arabia 2001 628.89
Middle East Saudi Arabia 2000-01 274.94
Middle East Saudi Arabia 2001-01 628.89
Middle East Saudi Arabia 903.83
Middle East 2000 2000-01 274.94
Middle East 2000 274.94
Middle East 2001 2001-01 628.89
Middle East 2001 628.89
Middle East 2000-01 274.94
Middle East 2001-01 628.89
Middle East 903.83
Middle East Saudi Arabia 2000 2000-01 274.94
Middle East Saudi Arabia 2000 274.94
Middle East Saudi Arabia 2001 2001-01 628.89
Middle East Saudi Arabia 2001 628.89
Middle East Saudi Arabia 2000-01 274.94
Middle East Saudi Arabia 2001-01 628.89
Middle East Saudi Arabia 903.83
Middle East 2000 2000-01 274.94
Middle East 2000 274.94
Middle East 2001 2001-01 628.89
Middle East 2001 628.89
Middle East 2000-01 274.94
Middle East 2001-01 628.89
Middle East 903.83
Northern America Canada 2000 2000-01 150,519.09
Northern America Canada 2000 2000-02 154,946.75
Northern America Canada 2000 2000-03 167,878.76
Northern America Canada 2000 2000-04 177,642.49
Northern America Canada 2000 650,987.09
Northern America Canada 2001 2001-01 169,569.41
Northern America Canada 2001 2001-02 208,044.87
Northern America Canada 2001 2001-03 240,496.05
Northern America Canada 2001 2001-04 241,107.88
Northern America Canada 2001 859,218.21
Northern America Canada 2000-01 150,519.09
Northern America Canada 2000-02 154,946.75
Northern America Canada 2000-03 167,878.76
Northern America Canada 2000-04 177,642.49
Northern America Canada 2001-01 169,569.41
Northern America Canada 2001-02 208,044.87
Northern America Canada 2001-03 240,496.05
Northern America Canada 2001-04 241,107.88
Northern America Canada 1,510,205.30
Northern America United States of America 2000 2000-01 3,293,493.49
Northern America United States of America 2000 2000-02 2,929,280.87
Northern America United States of America 2000 2000-03 3,387,621.66
Northern America United States of America 2000 2000-04 3,510,198.19
Northern America United States of America 2000 13,120,594.21
Northern America United States of America 2001 2001-01 3,613,827.97
Northern America United States of America 2001 2001-02 3,698,537.18
Northern America United States of America 2001 2001-03 3,795,742.62
Northern America United States of America 2001 2001-04 3,953,149.85
Northern America United States of America 2001 15,061,257.62
Northern America United States of America 2000-01 3,293,493.49
Northern America United States of America 2000-02 2,929,280.87
Northern America United States of America 2000-03 3,387,621.66
Northern America United States of America 2000-04 3,510,198.19
Northern America United States of America 2001-01 3,613,827.97
Northern America United States of America 2001-02 3,698,537.18
Northern America United States of America 2001-03 3,795,742.62
Northern America United States of America 2001-04 3,953,149.85
Northern America United States of America 28,181,851.83
Northern America 2000 2000-01 3,444,012.58
Northern America 2000 2000-02 3,084,227.62
Northern America 2000 2000-03 3,555,500.42
Northern America 2000 2000-04 3,687,840.68
Northern America 2000 13,771,581.30
Northern America 2001 2001-01 3,783,397.38
Northern America 2001 2001-02 3,906,582.05
Northern America 2001 2001-03 4,036,238.67
Northern America 2001 2001-04 4,194,257.73
Northern America 2001 15,920,475.83
Northern America 2000-01 3,444,012.58
Northern America 2000-02 3,084,227.62
Northern America 2000-03 3,555,500.42
Northern America 2000-04 3,687,840.68
Northern America 2001-01 3,783,397.38
Northern America 2001-02 3,906,582.05
Northern America 2001-03 4,036,238.67
Northern America 2001-04 4,194,257.73
Northern America 29,692,057.13
Southern America Argentina 2000 2000-01 1,386.85
Southern America Argentina 2000 2000-02 1,255.26
Southern America Argentina 2000 2000-03 721.87
Southern America Argentina 2000 3,363.98
Southern America Argentina 2001 2001-02 297.52
Southern America Argentina 2001 2001-04 405.17
Southern America Argentina 2001 702.69
Southern America Argentina 2000-01 1,386.85
Southern America Argentina 2000-02 1,255.26
Southern America Argentina 2000-03 721.87
Southern America Argentina 2001-02 297.52
Southern America Argentina 2001-04 405.17
Southern America Argentina 4,066.67
Southern America Brazil 2000 2000-01 1,530.91
Southern America Brazil 2000 2000-02 129.77
Southern America Brazil 2000 2000-03 2,195.83
Southern America Brazil 2000 2000-04 9,522.53
Southern America Brazil 2000 13,379.04
Southern America Brazil 2001 2001-01 1,671.36
Southern America Brazil 2001 2001-02 432.63
Southern America Brazil 2001 2001-03 352.31
Southern America Brazil 2001 2001-04 2,741.59
Southern America Brazil 2001 5,197.89
Southern America Brazil 2000-01 1,530.91
Southern America Brazil 2000-02 129.77
Southern America Brazil 2000-03 2,195.83
Southern America Brazil 2000-04 9,522.53
Southern America Brazil 2001-01 1,671.36
Southern America Brazil 2001-02 432.63
Southern America Brazil 2001-03 352.31
Southern America Brazil 2001-04 2,741.59
Southern America Brazil 18,576.93
Southern America 2000 2000-01 2,917.76
Southern America 2000 2000-02 1,385.03
Southern America 2000 2000-03 2,917.70
Southern America 2000 2000-04 9,522.53
Southern America 2000 16,743.02
Southern America 2001 2001-01 1,671.36
Southern America 2001 2001-02 730.15
Southern America 2001 2001-03 352.31
Southern America 2001 2001-04 3,146.76
Southern America 2001 5,900.58
Southern America 2000-01 2,917.76
Southern America 2000-02 1,385.03
Southern America 2000-03 2,917.70
Southern America 2000-04 9,522.53
Southern America 2001-01 1,671.36
Southern America 2001-02 730.15
Southern America 2001-03 352.31
Southern America 2001-04 3,146.76
Southern America 22,643.60
Argentina 2000 2000-01 1,386.85
Argentina 2000 2000-02 1,255.26
Argentina 2000 2000-03 721.87
Argentina 2000 3,363.98
Argentina 2001 2001-04 405.17
Argentina 2001 702.69
Argentina 2000-01 1,386.85
Argentina 2000-02 1,255.26
Argentina 2000-03 721.87
Argentina 2001-02 297.52
Argentina 2001-04 405.17
Argentina 4,066.67
Brazil 2000 2000-01 1,530.91
Brazil 2000 2000-02 129.77
Brazil 2000 2000-03 2,195.83
Brazil 2000 2000-04 9,522.53
Brazil 2000 13,379.04
Brazil 2001 2001-01 1,671.36
Brazil 2001 2001-02 432.63
Brazil 2001 2001-03 352.31
Brazil 2001 2001-04 2,741.59
Brazil 2001 5,197.89
Brazil 2000-01 1,530.91
Brazil 2000-02 129.77
Brazil 2000-03 2,195.83
Brazil 2000-04 9,522.53
Brazil 2001-01 1,671.36
Brazil 2001-02 432.63
Brazil 2001-03 352.31
Brazil 2001-04 2,741.59
Brazil 18,576.93
Canada 2000 2000-01 150,519.09
Canada 2000 2000-02 154,946.75
Canada 2000 2000-03 167,878.76
Canada 2000 2000-04 177,642.49
Canada 2000 650,987.09
Canada 2001 2001-01 169,569.41
Canada 2001 2001-02 208,044.87
Canada 2001 2001-03 240,496.05
Canada 2001 2001-04 241,107.88
Canada 2001 859,218.21
Canada 2000-01 150,519.09
Canada 2000-02 154,946.75
Canada 2000-03 167,878.76
Canada 2000-04 177,642.49
Canada 2001-01 169,569.41
Canada 2001-02 208,044.87
Canada 2001-03 240,496.05
Canada 2001-04 241,107.88
Canada 1,510,205.30
Saudi Arabia 2000 2000-01 274.94
Saudi Arabia 2000 274.94
Saudi Arabia 2001 2001-01 628.89
Saudi Arabia 2001 628.89
Saudi Arabia 2000-01 274.94
Saudi Arabia 2001-01 628.89
Saudi Arabia 903.83
United States of America 2000 2000-01 3,293,493.49
United States of America 2000 2000-02 2,929,280.87
United States of America 2000 2000-03 3,387,621.66
United States of America 2000 2000-04 3,510,198.19
United States of America 2000 13,120,594.21
United States of America 2001 2001-01 3,613,827.97
United States of America 2001 2001-02 3,698,537.18
United States of America 2001 2001-03 3,795,742.62
United States of America 2001 2001-04 3,953,149.85
United States of America 2001 15,061,257.62
United States of America 2000-01 3,293,493.49
United States of America 2000-02 2,929,280.87
United States of America 2000-03 3,387,621.66
United States of America 2000-04 3,510,198.19
United States of America 2001-01 3,613,827.97
United States of America 2001-02 3,698,537.18
United States of America 2001-03 3,795,742.62
United States of America 2001-04 3,953,149.85
United States of America 28,181,851.83
2000 2000-01 3,447,205.28
2000 2000-02 3,085,612.65
2000 2000-03 3,558,418.12
2000 13,788,599.26
2001 2001-01 3,785,697.63
2001 2001-02 3,907,312.20
2001 2001-03 4,036,590.98
2001 2001-04 4,197,404.49
2001 15,927,005.30
2000-01 3,447,205.28
2000-02 3,085,612.65
2000-03 3,558,418.12
2000-04 3,697,363.21
2001-01 3,785,697.63
2001-02 3,907,312.20
2001-03 4,036,590.98
2001-04 4,197,404.49
29,715,604.56
443 rows selected.
Elapsed: 00:00:01.17
• group by grouping sets(…)
W tym przypadku występuje tutaj klauzula GROUPING SETS, która obejmuje pożądane
grupy danych i powoduje, że wyświetlone zostaną sumy częściowe interesujących nas
zbiorów. Działanie funkcji GROUPING SETS opisana została w dalszej części tego
sprawozdania.
SELECT co.country_region AS region, co.country_subregion AS podregion,
co.country_name AS kraj, t.calendar_year AS rok, t.calendar_quarter_desc AS
kwartal, SUM(s.amount_sold) AS wartosc
FROM sales s, customers cu, times t, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND cu.country_id = co.country_id
AND t.calendar_quarter_desc BETWEEN '2000-01' AND '2001-04'
AND (co.country_region='Americas' OR co.country_region='Middle East')
GROUP BY GROUPING SETS ( (co.country_region, co.country_subregion,
co.country_name, t.calendar_year, t.calendar_quarter_desc),
(co.country_region, co.country_subregion,
co.country_name, t.calendar_year),
(co.country_region, co.country_subregion,
co.country_name),
(co.country_region, co.country_subregion),
(co.country_region), ())
ORDER BY region, podregion, kraj, rok ASC, kwartal ASC, wartosc DESC;
REGION PODREGION KRAJ ROK KWARTAL WARTOSC
-------------------- ------------------------------ ---------------------------------------- ---------- ------- ---------------
Americas Northern America Canada 2000 2000-01 150,519.09
Americas Northern America Canada 2000 2000-02 154,946.75
Americas Northern America Canada 2000 2000-03 167,878.76
Americas Northern America Canada 2000 2000-04 177,642.49
Americas Northern America Canada 2000 650,987.09
Americas Northern America Canada 2001 2001-01 169,569.41
Americas Northern America Canada 2001 2001-02 208,044.87
Americas Northern America Canada 2001 2001-03 240,496.05
Americas Northern America Canada 2001 2001-04 241,107.88
Americas Northern America Canada 2001 859,218.21
Americas Northern America Canada 1,510,205.30
Americas Northern America United States of America 2000 2000-01 3,293,493.49
Americas Northern America United States of America 2000 2000-02 2,929,280.87
Americas Northern America United States of America 2000 2000-03 3,387,621.66
Americas Northern America United States of America 2000 2000-04 3,510,198.19
Americas Northern America United States of America 2000 13,120,594.21
Americas Northern America United States of America 2001 2001-01 3,613,827.97
Americas Northern America United States of America 2001 2001-02 3,698,537.18
Americas Northern America United States of America 2001 2001-03 3,795,742.62
Americas Northern America United States of America 2001 2001-04 3,953,149.85
Americas Northern America United States of America 2001 15,061,257.62
Americas Northern America United States of America 28,181,851.83
Americas Northern America 29,692,057.13
Americas Southern America Argentina 2000 2000-01 1,386.85
Americas Southern America Argentina 2000 2000-02 1,255.26
Americas Southern America Argentina 2000 2000-03 721.87
Americas Southern America Argentina 2000 3,363.98
Americas Southern America Argentina 2001 2001-02 297.52
Americas Southern America Argentina 2001 2001-04 405.17
Americas Southern America Argentina 2001 702.69
Americas Southern America Argentina 4,066.67
Americas Southern America Brazil 2000 2000-01 1,530.91
Americas Southern America Brazil 2000 2000-02 129.77
Americas Southern America Brazil 2000 2000-03 2,195.83
Americas Southern America Brazil 2000 2000-04 9,522.53
Americas Southern America Brazil 2000 13,379.04
Americas Southern America Brazil 2001 2001-01 1,671.36
Americas Southern America Brazil 2001 2001-02 432.63
Americas Southern America Brazil 2001 2001-03 352.31
Americas Southern America Brazil 2001 2001-04 2,741.59
Americas Southern America Brazil 2001 5,197.89
Americas Southern America Brazil 18,576.93
Americas Southern America 22,643.60
Americas 29,714,700.73
Middle East Middle East Saudi Arabia 2000 2000-01 274.94
Middle East Middle East Saudi Arabia 2000 274.94
Middle East Middle East Saudi Arabia 2001 2001-01 628.89
Middle East Middle East Saudi Arabia 2001 628.89
Middle East Middle East Saudi Arabia 903.83
Middle East Middle East 903.83
Middle East 903.83
29,715,604.56
52 rows selected.
Elapsed: 00:00:00.87
Wariant (group by cube (…)) zapytania z poprzedniego punktu rozwinięty o funkcje grouping
i grouping_id (dla wszystkich kolumn wymienionych we frazie cube). Wyniki posortowane w
ten sposób, aby wszystkie podsumowania znalazły się na początku zwracanego zbioru danych
– rozpoczynając od sumy globalnej.
W tym zapytaniu zostały dodane funkcje GROUPING(<nazwa kolumny>), które wskazują,
czy kolumna znajdująca się w wyrażeniu w klauzuli GROUP BY jest zagregowana, czy nie.
W przypadku, gdy jest – przyjmuje wartość 1, w przeciwnym przypadku wartość 0. Na
przykład, gdy zostanie wyświetlona suma globalna, wówczas wartość wszystkich funkcji
GROUPING() będzie miała wartość 1. Gdy wyświetlona zostanie suma dla danego regionu,
wartość 1 będą miały grupy z wyjątkiem gr_reg. W zapytaniu tym została użyta także funkcja
GROUPING_ID(). Funkcja ta wylicza poziom zgrupowania kolumn, które są wymienione
jako jej argumenty. Argumentami z kolei mogą być kolumny, które zostały wymienione we
frazie SELECT. W poniższym zapytaniu argumentami funkcji GROUPING_ID() są wszystkie
wymienione wcześniej kolumny. Wartość tej funkcji, dla sumy globalnej wynosi 31, co równe
jest binarnej liczbie 11111, z kolei ta wartość, to wartości funkcji GROUPING() dla kolejnych
kolumn. Zapytanie to znajduje się poniżej:
SELECT co.country_region AS region, co.country_subregion AS podregion,
co.country_name AS kraj, t.calendar_year AS rok, t.calendar_quarter_desc AS
kwartal, SUM(s.amount_sold) AS wartosc, GROUPING(co.country_region) AS
gr_reg, GROUPING(co.country_subregion) AS gr_pod, GROUPING(co.country_name)
AS gr_kra, GROUPING(t.calendar_year) AS gr_yea,
GROUPING(t.calendar_quarter_desc) AS gr_qua, GROUPING_ID(co.country_region,
co.country_subregion, co.country_name, t.calendar_year,
t.calendar_quarter_desc) AS grupa
FROM sales s, customers cu, times t, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND cu.country_id = co.country_id
AND t.calendar_quarter_desc BETWEEN '2000-01' AND '2001-04'
AND (co.country_region='Americas' OR co.country_region='Middle East')
GROUP BY CUBE (co.country_region, co.country_subregion, co.country_name,
t.calendar_year, t.calendar_quarter_desc)
ORDER BY wartosc DESC, region, podregion, kraj, rok ASC, kwartal ASC;
Ze względu na dużą objętość wyników nie zostały one wklejony, gdyż niemożliwe byłoby ich
odczytanie.
Pozycja pod względem wartości sprzedaży regionów w latach 2000-2001 w rozbiciu na
miesiące używając funkcji rank().
W zapytaniu tym użyta została funkcja RANK(), następnie słowo kluczowe OVER(), która
określa według jakich kolumn ma nastąpić rankingowanie zbiorów danych. Zapytanie to
wygląda tak:
SELECT co.country_region AS region, t.calendar_month_desc AS miesiac,
SUM(s.amount_sold) AS wartosc, RANK() OVER (ORDER BY SUM(s.amount_sold)) AS
ranking
FROM sales s, customers cu, times t, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND cu.country_id = co.country_id
AND t.calendar_month_desc BETWEEN '2000-01' AND '2001-12'
GROUP BY co.country_region, t.calendar_month_desc
ORDER BY region, miesiac ASC, wartosc DESC;
A oto jego wyniki (posortowane według nazwy regionu, a następnie miesiąca oraz wartości):
REGION MIESIAC WARTOSC RANKING
-------------------- -------- --------------- ----------
Americas 2000-01 1,113,992.28 81
Americas 2000-02 1,222,251.17 87
Americas 2000-03 1,110,686.89 80
Americas 2000-04 989,603.67 76
Americas 2000-05 1,096,197.00 78
Americas 2000-06 999,811.98 77
Americas 2000-07 1,099,297.42 79
Americas 2000-08 1,260,177.57 89
Americas 2000-09 1,198,943.13 82
Americas 2000-10 1,288,681.44 90
Americas 2000-11 1,209,177.14 84
Americas 2000-12 1,199,504.63 83
Americas 2001-01 1,223,237.13 88
Americas 2001-02 1,209,622.46 85
Americas 2001-03 1,352,209.15 94
Americas 2001-04 1,350,042.87 93
Americas 2001-05 1,216,028.15 86
Americas 2001-06 1,341,241.18 92
Americas 2001-07 1,311,855.41 91
Americas 2001-08 1,354,501.35 95
Americas 2001-09 1,370,234.22 96
Americas 2001-10 1,385,435.69 97
Americas 2001-11 1,395,874.47 98
Americas 2001-12 1,416,094.33 99
Asia 2000-02 220,390.75 40
Asia 2000-03 172,668.11 28
Asia 2000-04 184,116.91 33
Asia 2000-05 174,076.70 29
Asia 2000-06 176,445.88 30
Asia 2000-07 187,503.41 34
Asia 2000-08 220,817.21 41
Asia 2000-09 217,119.89 37
Asia 2000-10 179,578.48 31
Asia 2000-11 219,182.03 39
Asia 2000-12 183,467.57 32
Asia 2001-01 192,958.21 35
Asia 2001-02 254,581.48 44
Asia 2001-03 240,356.82 42
Asia 2001-04 271,883.23 48
Asia 2001-05 217,677.67 38
Asia 2001-06 271,705.21 47
Asia 2001-07 254,909.68 45
Asia 2001-08 258,338.83 46
Asia 2001-09 246,513.64 43
Asia 2001-10 279,787.78 50
Asia 2001-11 302,374.05 51
Asia 2001-12 276,825.56 49
Europe 2000-01 600,888.64 63
Europe 2000-02 604,846.20 64
Europe 2000-03 513,397.81 53
Europe 2000-04 537,634.13 57
Europe 2000-05 536,305.46 56
Europe 2000-06 493,090.70 52
Europe 2000-07 530,908.95 55
Europe 2000-08 549,126.94 58
Europe 2000-09 619,753.81 66
Europe 2000-10 600,119.67 62
Europe 2000-11 556,016.14 59
Europe 2000-12 595,412.53 60
Europe 2001-01 598,266.86 61
Europe 2001-02 530,357.67 54
Europe 2001-03 613,499.23 65
Europe 2001-04 675,569.91 69
Europe 2001-05 646,282.75 67
Europe 2001-06 661,219.38 68
Europe 2001-07 691,215.91 72
Europe 2001-08 717,719.05 74
Europe 2001-09 691,131.37 71
Europe 2001-10 689,433.98 70
Europe 2001-11 697,456.89 73
Europe 2001-12 764,520.95 75
Middle East 2000-01 61.98 1
Middle East 2000-02 212.96 2
Middle East 2001-01 628.89 3
Oceania 2000-01 83,687.83 16
Oceania 2000-02 70,917.89 8
Oceania 2000-04 54,155.41 4
Oceania 2000-05 67,913.69 7
Oceania 2000-06 62,379.39 5
Oceania 2000-07 79,053.04 12
Oceania 2000-08 82,134.07 14
Oceania 2000-09 76,403.85 10
Oceania 2000-10 96,232.62 21
Oceania 2000-11 75,968.46 9
Oceania 2000-12 84,306.21 17
Oceania 2001-01 98,618.04 23
Oceania 2001-02 108,562.93 25
Oceania 2001-03 124,198.57 27
Oceania 2001-04 82,461.22 15
Oceania 2001-05 107,033.54 24
Oceania 2001-06 81,323.28 13
Oceania 2001-07 89,864.35 19
Oceania 2001-08 97,856.05 22
Oceania 2001-09 111,858.77 26
Oceania 2001-10 94,510.46 20
Oceania 2001-11 78,982.09 11
Oceania 2001-12 89,601.27 18
99 rows selected.
Elapsed: 00:00:01.23
Rozszerzenia języka SQL w systemie ORACLE
• rollup
Użycie tej funkcji pozwala na wyliczenie dodatkowych podsumowań częściowych i
ogólnych. Służy ona do konstruowania pół-kostek danych. Dla n kolumn grupujących funkcja
ta tworzy n+1 podsumowań. Dodatkowe podsumowania wyznaczone przez ROLLUP
wyliczane są przez eliminowanie kolejno kolumn grupujących począwszy od ostatniej, a
skończywszy na pierwszej.
Przykładowe użycie:
SELECT a, b, c, SUM (s)
FROM t
GROUP BY ROLLUP (a,b,c)
Zapytanie to spowoduje, że wypisane zostaną sumy częściowe dla unikalnych kombinacji
grup (a, b, c), (a,b) oraz (a). Ponadto wyliczana zostaje suma całkowita.
Funkcja ta może być operacją częściową, czyli może obejmować tylko część kolumn
występujących w klauzuli GROUP BY, np.:
SELECT a, b, c, SUM (s)
FROM t
GROUP BY a, b, ROLLUP (c)
To zapytanie z kolei wyznaczy podsumowania (a, b, c) oraz (a,b)
• cube
Funkcja ta tworzy podsumowania dla wszystkich możliwych kombinacji grupowanych
kolumn. Generuje ona podsumowania częściowe i ogólne tabeli faktów dla wszystkich
możliwych wymiarów. Dla n kolumn grupujących funkcja ta tworzy 2^n podsumowań.
Przykładowe użycie:
SELECT a, b, c, SUM (s)
FROM t
GROUP BY CUBE (a,b,c)
Zapytanie to spowoduje wypisanie sumy globalnej oraz sum częściowych dla unikalnych
kombinacji grup (a, b, c), (a, b), (a, c), (b, c), (a), (b) oraz (c).
Podobnie jak w przypadku funkcji ROLLUP tutaj również istnieje możliwość tworzenia
zestawień częściowych, np.:
SELECT a, b, c, SUM (s)
FROM t
GROUP BY a, CUBE (b, c)
To zapytanie spowoduje wyznaczenie następujących podsumowań: (a, b, c), (a, b), (a, c) oraz
(a).
• grouping sets
Operacja ta pozwala na jednoznaczne wskazanie tych podsumowań, które chcemy uzyskać,
gdyż zarówno funkcje CUBE oraz ROLLUP może generować także takie podsumowania,
które są zbędne.
Przykładowe użycie:
SELECT a, b, c, SUM (s)
FROM t
GROUP BY GROUPING SETS ( (a, b, c), (a, b), (a, c) )
Zapytanie to spowoduje, że wyświetlone zostaną podsumowania tylko dla grup (a, b, c), (a, b)
oraz (a, c).
• rank
Funkcja ta pozwala na wyświetlenie pozycji w rankingu określonego zbioru danych pod
względem np. sumy lub średniej jakichś wartości.
Przykładowe użycie:
SELECT a, b, c, SUM(s), RANK() OVER (ORDER BY SUM(s))
FROM t
GROUP BY a, b, c
Zapytanie to spowoduje, że obok pogrupowanych danych, wyświetlonych wraz z ich sumą
zostanie wypisany pozycja w rankingu, na jakiej znajduje się dana grupa biorąc pod uwagę
sumę posortowaną malejąco (domyślnie).
Różnica między pierwszym zapytaniem, a drugim.
Pierwsze z wymienionych w treści zadania zapytań prezentuje następujące wyniki:
CHANNEL_DESC CALENDAR COUNTRY_NAME SPRZEDAZ
-------------------- -------- ---------------------------------------- --------------
Internet 2000-09 United Kingdom 16,569
Internet 2000-09 United States of America 124,224
Internet 2000-09 140,793
Internet 2000-10 United Kingdom 14,539
Internet 2000-10 United States of America 137,054
Internet 2000-10 151,593
Internet 292,387
Direct Sales 2000-09 United Kingdom 85,223
Direct Sales 2000-09 United States of America 638,201
Direct Sales 2000-09 723,424
Direct Sales 2000-10 United Kingdom 91,925
Direct Sales 2000-10 United States of America 682,297
Direct Sales 2000-10 774,222
Direct Sales 1,497,646
1,790,032
Jak widać zsumowane zostały wartości sprzedaży dla każdego kraju, w każdym miesiącu, dla
każdego kanału. Ponadto wypisane są sumy częściowe dla każdego kanału w każdym
miesiącu, dla każdego kanału z osobna oraz suma globalna. Po wykonaniu drugiego zapytania
wyświetlone zostaną takie samy wyniki, z jedną różnicą: nie zostanie wypisana suma
globalna. Dzieje się tak dlatego, że fraza GROUP BY zawiera kanał dystrybucji, a następnie
użyta jest funkcja ROLLUP(), której argumentami są miesiąc oraz nazwa kraju. I właśnie
wypisywane są tylko sumy częściowe dla tych kolumn, które są argumentami tych funkcji.
W zadaniu tych zapoznaliśmy się bardzo dokładnie z rozszerzeniami języka Oracle SQL,
takimi jak funkcje grupujące ROLLUP, CUBE, GROUPING SETS, GROUPING,
GROUPING_ID oraz RANK.
Funkcje te różnią się od siebie dość znacząco i pozwalają na uzyskiwanie różnych zbiorów
danych, dzięki czemu można uzyskiwać praktycznie wszystkie rodzaje interesujących nas
danych, w przeciwieństwie do języka SQL wykorzystywanego w systemach MySQL, czy
PostgreSQL, które powyższych rodzajów operacji nie zawierają.
Ćwiczenie to było również bardzo dobrym przypomnieniem języka SQL poprzez budowanie
złożonych zapytań, które były wymagane w początkowej części całego zadania.
Wszystkie pojedyncze zadania zostały przez nas zrealizowane, w związku z czym cel zadania
uważamy za osiągnięty.