TRANSFORM SUM(Wartosc)
SELECT Wiersze
FROM Tabela
GROUP BY Wiersze
PIVOT Kolumny

Środowisko

Do realizacji omawianych w tym e‑materiale zapytań możemy użyć programu MS Access lub LibreOffice Base.

W programie MS Access wykonywanie poleceń SQL możliwe jest po otwarciu bazy, wybraniu wstążki Tworzenie oraz ikony Projekt kwerendy. Następnie należy zamknąć okno Pokazywanie tabeli i kliknąć ikonę SQL.

W programie LibreOffice Base po otworzeniu bazy, klikamy ikonę Kwerendy, następnie wybieramy Utwórz kwerendę SQL. Po wpisaniu kodu SQL możemy uruchomić kwerendę za pomocą ikony lub używając polecenia Wykonaj kwerendę z menu Edycja. Można również użyć klawisza F5. Zaprojektowaną kwerendę zapisujemy za pomocą polecenia Plik | Zapisz.

Kwerendy krzyżowe na wielu tabelach

Czasami konieczne jest wykorzystanie danych zawartych w wielu tabelach w pojedynczej kwerendzie. Jednym ze sposobów rozwiązania tego problemu jest napisanie polecenia SELECT z wykorzystaniem polecenia JOIN.

Przeanalizujmy przykład dwóch tabel należących do bazy danych pewnego hotelu. Tabele zawierają informacje o gościach oraz o wynajętych przez nich pokojach. Pierwsza tabela nazywa się Goscie, druga – Wynajem.

R1EJlMFJN2lvE

Ilustracja przedstawiająca tabelę. Kolumny tabeli opisano kolejno: Numer, Imie oraz Nazwisko. W kolumnie Numer wpisano kolejne cyfry od 1 do 6. W kolumnie Imie wpisano imiona. W kolumnie Nazwisko wpisano nazwiska.

R1QWkOh07UbES

Ilustracja przedstawiająca tabelę dotyczącą wynajmów. Kolumny tabeli opisano kolejno: Numer_pokoju, Numer_goscia, Data oraz Cena. W kolumnie Numer_pokoju wpisano numery pokoi od 1 do 7. W kolumnie Numer gościa wpisano numery gości. W kolumnie Data wpisano daty. W kolumnie Cena podano ceny w złotówkach.

W tabeli Goscie mamy trzy pola:

• Nr, czyli pole klucza podstawowego, typ INTEGER (Liczba całkowita),

• pola tekstowe Imie i Nazwisko – typ TEXT (Krótki tekst).

W tabeli Wynajem mamy:

• pole klucza podstawowego Nr_pokoju typu INTEGER (Liczba całkowita),

• pole klucza obcego Nr_goscia typu INTEGER (Liczba całkowita),

• pole Data typu DATE (Data/Godzina),

• oraz pole Cena, typ DECIMAL (Waluta) z dwoma miejscami po przecinku.

Pobieramy załączone pliki, które zawierają dane w formacie CSV. Następnie tworzymy bazę danych o nazwie goscie.accdb w programie MS Access lub goscie.odb w programie LibreOffice Base, dodajemy pokazane wyżej tabele i importujemy do nich dane z pobranych plików: goscie.csv i wynajem.csv.

Naszym celem jest utworzenie kwerendy krzyżowej, która przedstawi, ile każdy z gości zapłacił za wynajem pokoju w poszczególnych dniach.

Pierwszym krokiem jest zaplanowanie kwerendy. Wiadomo, że imiona i nazwiska gości powinny znaleźć się w nazwach wierszy (to znaczy w rekordach), a daty w nazwach kolumn (alternatywnie kolumny i wiersze można odwrócić).

Potrzebujemy danych z wielu tabel, napiszemy więc polecenie SELECT, zwracające właśnie te dane.

SELECT Goscie.Imie, Goscie.Nazwisko, Wynajem.Data, Wynajem.Cena
FROM Goscie
INNER JOIN Wynajem
ON Wynajem.Nr_goscia = Goscie.Nr

RJu8gAtIj4iZv

Ilustracja przedstawiająca tabelę. Kolumny tabeli opisano kolejno: Imię, Nazwisko, Data oraz Cena. Komórka z opisem Cena ma czerwone wypełnienie. Dane wpisano w siedmiu wierszach. W kolumnie Imię wpisano imiona. W kolumnie Nazwisko wpisano nazwiska. W kolumnie Data wpisano daty. W kolumnie Cena podano ceny w złotówkach.

Kolejnym krokiem jest dodanie funkcji grupowania. W tym przypadku chcemy pogrupować rekordy zgodnie z rekordami i kolumnami przyszłej kwerendy krzyżowej (pola: Imie, Nazwisko, Data).

W tym celu modyfikujemy kwerendę:

SELECT Goscie.Imie, Goscie.Nazwisko, Wynajem.Data, Wynajem.Cena
FROM Goscie
INNER JOIN Wynajem
ON Wynajem.Nr_goscia = Goscie.Nr
GROUP BY Goscie.Imie, Goscie.Nazwisko, Wynajem.Data

SELECT Goscie.Imie, Goscie.Nazwisko, Wynajem.Data, Wynajem.Cena
FROM Goscie
INNER JOIN Wynajem
ON Wynajem."Nr goscia" = Goscie.Nr
GROUP BY Goscie.Imie, Goscie.Nazwisko, Wynajem.Data

SELECT Goscie.Imie, Goscie.Nazwisko, Wynajem.Data,
SUM(Wynajem.Cena) AS Suma_cen
FROM Goscie
INNER JOIN Wynajem
ON Wynajem.Nr_goscia = Goscie.Nr
GROUP BY Goscie.Imie, Goscie.Nazwisko, Wynajem.Data

RX5ovwh25KUez

Ilustracja przedstawiająca tabelę. Kolumny tabeli opisano kolejno: Imię, Nazwisko, Data oraz Suma cen. Komórka z opisem Suma cen ma czerwone wypełnienie. Dane wpisano w siedmiu wierszach. W kolumnie Imię wpisano imiona. W kolumnie Nazwisko wpisano nazwiska. W kolumnie Data wpisano daty. W kolumnie Suma cen podano ceny w złotówkach. Ostatni wiersz w tabeli jest zaznaczony.

Gdy wszystkie konieczne informacje są zebrane i pogrupowane w jednej kwerendzie, można ją zapisać w bazie danych pod nazwą Goscie_Data_Cena. W programie MS Access tak zapisane kwerendy nazywane są widokamiwidokwidokami.

Ostatnim krokiem jest wywołanie kwerendy krzyżowej, która pobiera dane z wyników poprzedniej kwerendy Goscie_Data_Cena. W programie MS Access używamy poleceń TRANSFORM i PIVOT:

TRANSFORM SUM(Suma_cen)
SELECT Imie, Nazwisko
FROM Goscie_Data_Cena
GROUP BY Imie, Nazwisko
PIVOT Data

W programie LibreOffice Base równoważnikiem będzie zapytanie wykorzystujące wyrażenie CASE WHEN warunek THEN rezultat END;, które zwraca rezultat, jeżeli warunek jest prawdziwy. Zapytanie przyjmie postać:

SELECT Imie, Nazwisko,
  SUM( CASE WHEN Data = {d '2020-01-01' } THEN Suma_cen END ) AS "01_01_2020",
  SUM( CASE WHEN Data = {d '2020-01-02' } THEN Suma_cen END ) AS "02_01_2020",
  SUM( CASE WHEN Data = {d '2020-01-03' } THEN Suma_cen END ) AS "03_01_2020"
FROM Goscie_Data_Cena
GROUP BY Imie, Nazwisko

Wyniki kwerendy krzyżowej będą takie jak na załączonych zrzutach, przy czym kolejność rekordów może być inna:

RBiO664jL03TA

Ilustracja przedstawia tabelę. Kolumny tabeli opisano kolejno: Imię, Nazwisko, 01_01_2020, 02_01_2020 oraz 03_01_2020. Komórka Imię ma czerwone wypełnienie. Dane wpisano w sześciu wierszach. W kolumnie Imię wpisano imiona. W kolumnie Nazwisko wpisano nazwiska. W kolumnie 01_01_2020, w komórce pierwszej, trzeciej i czwartej wpisano kwoty. W kolumnie 02_01_2020 w komórce drugiej i szóstej podano kwoty. W kolumnie 03_01_2020 w komórce trzeciej i piątej podano kwoty. Pod spodem znajduje się okno z kodem: SELECT "Goscie"."Imie", "Goscie"."Nazwisko" , SUM( CASE WHEN "Wynajem"."Data" = {d '2020‑01‑01' } THEN "Wynajem"."Cena" END ) AS "01_01_2020", , SUM( CASE WHEN "Wynajem"."Data" = {d '2020‑01‑02' } THEN "Wynajem"."Cena" END ) AS "02_01_2020", , SUM( CASE WHEN "Wynajem"."Data" = {d '2020‑01‑03' } THEN "Wynajem"."Cena" END ) AS "03_01_2020", FROM "Wynajem", "Goscie" WHERE "Wynajem"."Nr_goscia" = "Goscie"."Nr" GROUP BY "Goscie"."Imie", "Goscie"."Nazwisko"

RGy7MlytOBwSy

Ilustracja przedstawiająca tabelę. Kolumny tabeli opisano kolejno: Imię, Nazwisko, 01_01_2020, 02_01_2020 oraz 03_01_2020. Komórka Imię ma czerwone wypełnienie. Dane wpisano w sześciu wierszach. W kolumnie Imię wpisano imiona. W kolumnie Nazwisko wpisano nazwiska. W kolumnie 01_01_2020, w komórce pierwszej, trzeciej i piątej wpisano ceny w złotówkach. W kolumnie 02_01_2020 w komórce czwartej i szóstej podano ceny w złotówkach. W kolumnie 03_01_2020 w komórce trzeciej i czwartej podano ceny w złotówkach.

TRANSFORM SUM(Wynajem.Cena)
SELECT Goscie.Imie, Goscie.Nazwisko
FROM Goscie
INNER JOIN Wynajem
ON Wynajem.Nr_goscia = Goscie.Nr
GROUP BY Goscie.Imie, Goscie.Nazwisko
PIVOT Wynajem.Data

SELECT Goscie.Imie, Goscie.Nazwisko,
  SUM( CASE WHEN Wynajem.Data = {d '2020-01-01' } THEN Wynajem.Cena END ) AS "01_01_2020",
  SUM( CASE WHEN Wynajem.Data = {d '2020-01-02' } THEN Wynajem.Cena END ) AS "02_01_2020",
  SUM( CASE WHEN Wynajem.Data = {d '2020-01-03' } THEN Wynajem.Cena END ) AS "03_01_2020"
FROM Wynajem, Goscie
WHERE Wynajem.Nr_goscia = Goscie.Nr
GROUP BY Goscie.Imie, Goscie.Nazwisko

Wykorzystanie arkusza kalkulacyjnego

Wadą zapytań podanych dla LibreOffice Base wykorzystujących klauzulę CASE jest to, że musimy z góry wiedzieć, dla jakich konkretnych dat chcemy grupować wartości i wyświetlać je później w kolumnach. Jeżeli wartości jest wiele, nie jest to wygodne rozwiązanie. Wtedy korzystamy z możliwości arkusza kalkulacyjnego LibreOffice Calc.

Po uruchomieniu LibreOffice Calc sprawdzamy, czy potrzebna nam baza jest zarejestrowana jako źródło danych. Wybieramy Widok | Źródła danych. Jeżeli na wyświetlonej liście, nie ma omawianej bazy, musimy ją zarejestrować. Klikamy prawym klawiszem w polu listy i wybieramy polecenie Zarejestrowane bazy danych lub wybieramy Narzędzia | Opcje, a dalej w lewym oknie LibreOffice Base Bazy danych i klikamy przycisk Nowy. W oknie Zarejestrowane bazy danych klikamy Przeglądaj, wskazujemy plik bazy i ewentualnie zmieniamy rejestrowaną nazwę.

R1CwnKQiRyfC6

Ilustracja przedstawia fragment dokumentu ze źródłami danych w programie LibreOffice Calc . W górnym pasku dokumentu widać ikonki programu. Lewa połowa dokumentu to lista z rozwiniętymi opcjami: Bibliography : goście: Kwerendy: Goscie_Data_Cena, Kwerenda1, Kwerenda3, transform_pivot, Tabele. Prawa połowa dokumentu przedstawia tabelę. Kolumny tabeli opisano kolejno: Imię, Nazwisko, Data, Suma cen. Dane wpisano w siedmiu wierszach. W kolumnie Imię wpisano imiona. W kolumnie Nazwisko wpisano nazwiska. W kolumnie data wpisano daty, a w kolumnie Suma_cen kwoty.

Jeżeli baza jest zarejestrowana i widzimy ją na liście po wybraniu Widok | Źródła danych, wybieramy polecenie Dane | Tabela przestawna | Wstaw lub edytuj. Następnie w oknie Wybierz źródło zaznaczamy opcję Źródło danych zarejestrowane w LibreOffice. W kolejnym oknie wybieramy bazę, jako typ wybieramy Kwerenda, a jako źródło – kwerendę Goscie_Data_Cena.

Następnie wybieramy polecenie Dane | Tabela przestawna | Wstaw lub edytuj i w oknie Układ tabeli przestawnej projektujemy tabelę. Aby dane zostały pogrupowane, do pola wierszy przeciągamy pola Imie i Nazwisko, a do pola kolumn przeciągamy pole Data. W polu danych umieszczamy pole Suma_cen i pozostawiamy domyślną funkcję agregującą Suma.

R1UEiJrvrWXYt

Ilustracja przedstawia okno programu LibreOffice Calc zatytułowane: Układ tabeli przestawnej. Pod spodem znajdują się okna: puste pole Filtry, Pola kolumn z pozycjami: Dane, Data, Pola wierszy z pozycjami: Imie, Nazwisko, Pola danych z pozycją: Suma‑Suma_cen, Pola dostępne: Imie, Nazwisko, Data, Suma_cen. Pod nimi widnieje tekst: Przeciągnij elementy na pożądaną pozycję, a pod nim: Opcje, Źródło i cel. Na dole okna znajdują się trzy prostokątne przyciski: Pomoc, Anuluj i OK.

Słownik