Zastosowanie kwerend krzyżowych

Kwerendy krzyżowe przedstawiają informacje zawarte w danej tabeli w nowy sposób – pozwalają m.in. na obliczanie średnich lub maksimów, a także na grupowanie rekordów i kolumn zgodnie z dowolnymi warunkami.

Prosta kwerenda krzyżowa

Tworząc kwerendę krzyżową, wybieramy dwie kolumny z tabeli początkowej. Wartości z pierwszej kolumny staną się nowymi nazwami rekordów, a z drugiej – nazwami kolumn w tabeli wynikowej. Takie „skrzyżowanie” wartości z dwóch kolumn nadaje kwerendzie jej nazwę.

Tego rodzaju przekształcenie nie dodaje żadnych nowych informacji do tabeli, ale prezentuje je w bardziej zrozumiały sposób.

Przyjrzyjmy się tej kwestii na przykładzie tabeli przedstawiającej uczniów pewnego technikum oraz ich oceny z języka polskiego i angielskiego. W tej tabeli wywołamy kwerendę.

R1BZMkEF9nw18

Ilustracja przedstawiająca tabelę dotyczącą ocen uczniów z różnych przedmiotów. Kolumny tabeli opisano kolejno: Identyfikator, Imie, Nazwisko, Przedmiot oraz Ocena. W kolumnie Identyfikator wpisano cyfry od 1 do 8. W kolumnie Imie wpisano imiona. W kolumnie Nazwisko wpisano nazwiska. W kolumnie Przedmiot wpisano nazwy przedmiotów. W kolumnie Ocena wpisano oceny liczbowo.

Tabelę możesz pobrać z załącznika:

Tablica posiada wiele powtórzeń, np. nazwiska i przedmioty, co utrudnia wgląd w zawartość tablicy. Najlepszym rozwiązaniem byłoby znormalizowanieNormalizacjaznormalizowanie tej tablicy, jednak w tym przykładzie wykonamy kwerendę krzyżową, która przedstawi tablicę w przystępniejszej formie.

Utworzymy kwerendę, w której nazwiska uczniów będą nazwami rekordów, a przedmioty –  oznaczeniami kolumn (nazwami pól). Przy każdym nazwisku znajduje się teraz lista ocen z kolejnych przedmiotów, dzięki czemu tabela jest przejrzysta.

Wynik kwerendy krzyżowej:

R9qgjKjTpzsOL

Ilustracja przedstawiająca tabelę dotyczącą ocen uczniów z przedmiotów. Kolumny tabeli opisano kolejno: Nazwisko, Angielski oraz Polski. Komórka z opisem Polski ma czerwone wypełnienie. Dane wpisano w czterech wierszach. W kolumnie Nazwisko wpisano nazwiska. W kolumnie Angielski wpisano oceny z angielskiego. W kolumnie Polski wpisano oceny z polskiego.

Kreator kwerend

Przedstawioną tabelę można łatwo utworzyć przez wbudowany kreator kwerend. Znajduje się on w zakładce Tworzenie.

Najpierw należy wybrać Kreator kwerend krzyżowych w oknie dialogowym.

RnhOwVhAYeydE

Ilustracja przedstawia okno dialogowe: Nowa kwerenda.
Po prawej stronie widnieje lista z opcjami kreatora kwerend.
Są to kolejno: Kreator prostych kwerend, kreator kwerend krzyżowych, kreator kwerend wyszukujących duplikaty, kreator kwerend wyszukujących niepasujące dane.
Z opcji tych zaznaczono: Kreator kwerend krzyżowych.
Po lewej stronie wyświetla się opis zaznaczonej opcji: Ten kreator tworzy kwerendę krzyżową, która wyświetla dane w formacie kompaktowym podobnym do formatu arkusza kalkulacyjnego.
W dolnej części okna znajdują się przyciski OK oraz Anuluj.

W kolejnym kroku dokonuje się wyboru kolumn, które będą opisywały każdy wiersz. W tym przypadku są to Imię i Nazwisko.

RqZBEC7GrMR31

Zrzut ekranu przedstawia kreator kwerend krzyżowych.
Po prawej stronie kreatora znajdują się dwie listy: Dostępne pola oraz Wybrane pola.
W liście Dostępne pola znajdują się: Identyfikator, Przedmiot, Ocena.
W liście wybrane pola znajdują się: Imie, Nazwisko.
Pomiędzy listami znajdują się cztery przyciski: Strzałka w prawo, podwójna strzałka w prawo, strzałka w lewo, podwójna strzałka w lewo.
Po lewej stronie znajduje się sekcja z tekstem:
Które wartości pól mają być nagłówkami wierszy? Możesz wybrać najwyżej trzy pola. Wybierz pola w kolejności, w której mają być posortowane informacje. Można na przykład sortować i grupować wartości według pól Kraj i Region.
W dolnej sekcji kreatora przedstawiona jest przykładowa kwerenda.
Kwerenda o nagłówkach kolumn: Imie, Nazwisko, Nagłówek1, Nagłówek2, Nagłówek3.
W kolumnie Imie wypisano Imie1 do Imie4, w kolumnie Nazwisko wypisano Nazwisko1 do Nazwisko4.
Kolumny Nagłówek1, Nagłówek2, Nagłówek3 są połączone i znajduje się w nich napis: PODSUMOWANIE.
Na samym dole znajdują się przyciski: anuluj, wstecz, dalej, zakończ.

Zwróć uwagę na fakt, że po przeniesieniu tych pół do okna Wybrane pola, pojawią się one w przykładzie tabeli na dole okna, tak jak na przedstawionej ilustracji.

Następnie należy wybrać pola, które staną się kolumnami tworzonego przez nas zestawienia. W tym przypadku chcemy wypisać Oceny dla każdego Przedmiotu, w związku czym wybieramy pole Przedmiot, jako nagłówki kolumn.

RXfFpXkRGi3Ta

Zrzut ekranu przedstawia kreator kwerend krzyżowych.
Po prawej stronie kreatora znajduje się lista z polami: Identyfikator, Przedmiot, Ocena.
Zaznaczono pole Przedmiot.
Po lewej stronie znajduje się sekcja z tekstem:
Które wartości pól mają być użyte jako nagłówki kolumn? Na przykład, aby zobaczyć nazwiska pracowników jako kolumn, należy wybrać pole Nazwisko pracownika.
W dolnej sekcji kreatora przedstawiona jest przykładowa kwerenda.
Kwerenda o nagłówkach kolumn: Imie, Nazwisko, Przemiot1, Przemiot2, Przemiot3.
W kolumnie Imie wypisano Imie1 do Imie4, w kolumnie Nazwisko wypisano Nazwisko1 do Nazwisko4.
Kolumny Przemiot1, Przemiot2, Przemiot3 są połączone i znajduje się w nich napis: PODSUMOWANIE.
Na samym dole znajdują się przyciski: anuluj, wstecz, dalej, zakończ.

Ta zmiana także jest widoczna w przykładzie na dole okna.

Ostatnim krokiem jest wybranie, jaka informacja ma się pojawić na skrzyżowaniu wierszy i kolumn. Są to oczywiście Oceny.

R10ztIhhIqjJS

Zrzut ekranu przedstawia kreator kwerend krzyżowych.
Po prawej stronie kreatora znajdują się dwie listy: Pola oraz Funkcje.
W liście Pola znajdują się: Identyfikator, Ocena.
W liście Funkcje znajdują się: Maksimum, Minimum, OdchStd, Ostatni, Pierwszy, Średnia, Suma, Wariancja, Zlicz.
Po lewej stronie znajduje się sekcja z tekstem:
Jakie liczby mają być obliczane dla każdego przecięcia kolumny i wiersza? Można na przykład obliczać sumę dla pola Wartość zamówienia dla każdego pracownika (kolumna) według krajów i regionów (wiersz). Czy chcesz sumować każdy wiersz?
Poniżej znajduje się nie zaznaczone pole wyboru: Tak, dołącz sumy wierszy
W dolnej sekcji kreatora przedstawiona jest przykładowa kwerenda.
Kwerenda o nagłówkach kolumn: Imie, Nazwisko, Przedmiot1, Przedmiot2, Przedmiot3.
W kolumnie Imie wypisano Imie1 do Imie4, w kolumnie Nazwisko wypisano Nazwisko1 do Nazwisko4.
Kolumny Przedmiot1, Przedmiot2, Przedmiot3 są połączone i znajduje się w nich napis: Maksimum(Ocena).
Na samym dole znajdują się przyciski: anuluj, wstecz, dalej, zakończ.

Oprócz pola należy wybrać także funkcję, która zostanie wykorzystana w przypadku kiedy istnieje wiele wartości dla wybranych przez nas wierszy i kolumn. Dotyczy to sytuacji, kiedy np. jeden uczeń (opisywany przez Imię i Nazwisko) posiada wiele ocen z jednego przedmiotu.

W tabeli występującej w tym zadaniu nie ma tego problemu — każdy uczeń ma tylko jedną ocenę z każdego przedmiotu. W związku z czym możemy wybrać np. funkcję Maksimum.

Po wybraniu opcji Dalej zaprojektowane przez nas zestawienie zostanie utworzone.

Składnia SQL

Mimo że składnia SQL jest bardziej złożona, to daje także znacznie większe możliwości w modyfikacji zestawienia, które projektujemy. Utwórzmy więc analogiczne zestawienie, wykorzystując ten język.

Oprócz zwykłego polecenia SELECT w kwerendach krzyżowych wykorzystuje się dwie dodatkowe operacje:

PIVOT – wybiera kolumnę, której wartości staną się nowymi nazwami kolumn,

TRANSFORM – wybiera wartości, które mają się pojawić w polach na skrzyżowaniu kolumn i rekordów, w tym przypadku są to oceny.

Składnia kwerendy krzyżowej to:

TRANSFORM MAX(Ocena)
SELECT Imie, Nazwisko
FROM [Uczniowie_i_Oceny]
GROUP BY Imie, Nazwisko
PIVOT Przedmiot

Przeanalizujmy tę składnię. W pierwszej linijce wybieramy wartość na skrzyżowaniu nowych rekordów i kolumn. Musimy zastosować tutaj funkcję agregującąFunkcja agregującafunkcję agregującą. Jest to zabezpieczenie na wypadek, gdybyśmy otrzymali wiele ocen spełniających późniejsze parametry (np. kilka ocen z jednego przedmiotu).

W drugiej linijce rozpoczynamy funkcję SELECT, tutaj należy wybrać kolumny, których wartości staną się rekordami w tabeli wynikowej. W tym przypadku to imiona i nazwiska.

Trzecia linijka wybiera tabelę (lub wiele tabel) zawierającą dane, które chcemy wyświetlić w kwerendzie.

Czwarta linijka zawiera kolumny, według których wynik ma być grupowany. Chcemy, aby oceny zostały pogrupowane według uczniów (obok każdego ucznia mają znajdować się jego oceny), więc zamieszczamy tutaj pola Imie i Nazwisko. W praktyce najczęściej wpisuje się tutaj te same kolumny, co w poleceniu SELECT.

Dopiero w ostatniej linijce wybiera się kolumnę, której wartości staną się nazwami rekordów. W tym przypadku jest to oczywiście kolumna Przedmiot.

Ta kwerenda zwróci wynik niemal identyczny jak poprzednia – tym razem pojawią się jednak również imiona uczniów.

Sumowanie kwerendą krzyżową

Kwerendy krzyżowe pozwalają na zastosowanie funkcji agregujących, dzięki czemu przydają się do tworzenia różnego rodzaju podsumowań oraz raportów.

Przeanalizujmy teraz tabelę Zamowienia, przedstawiającą sprzedaż pewnej restauracji.

RMwqJ2bsu3oPw

Ilustracja przedstawiająca tabelę dotyczącą zamówień dań. Kolumny tabeli opisano kolejno: Identyfikator, Nazwa, Kategoria, Numer klienta oraz Cena. W kolumnie Identyfikator wpisany liczby od 1 do 11. W kolumnie Nazwa pisano nazwy dań. W kolumnie Kategoria wpisano nazwy kategorii dań. W kolumnie Numer klienta wpisano numery klientów. W kolumnie Cena podano ceny w złotówkach.

Tabelę możesz pobrać z załącznika:

Przekształćmy tę tabelę w taki sposób, aby otrzymać informację, ile poszczególni klienci wydali na zamówienia każdej kategorii produktów. Jako nazwy rekordów wykorzystamy wartości Nr_klienta, a jako nazwy kolumn – Kategoria. W polach na skrzyżowaniu Nr_klienta/Kategoria ma się wyświetlić suma cen tych zamówień.

Kod do tej kwerendy prezentuje się następująco:

TRANSFORM SUM(Cena)
SELECT [Nr_klienta]
FROM Zamowienia
GROUP BY [Nr_klienta]
PIVOT Kategoria

Ponownie omówmy szczegółowe działanie kwerendy krok po kroku.

Przedstawione przykłady dotyczą zamówień z numerami od 1 do 4.

W pierwszym kroku wybierane są pola Cena ze wszystkich rekordów. Odpowiada za to linijka TRANSFORM.

Następnie zamówienia są grupowane według klientów, którzy je złożyli (GROUP BY>). Na tym etapie dane wyglądają następująco:

RkXpLIkEqqmzm

Ilustracja przedstawiająca tabelę dotyczącą zamówień. Kolumny tabeli opisano kolejno: Numer klienta oraz Cena‑Identyfikator. W kolumnie Numer klienta wpisano cyfry 1, 2, 2. W kolumnie Cena‑Identyfikator wpisano cenę w złotówkach, a po myślniku numer identyfikatora.

Identyfikator odpowiada numerowi zamówienia. Przy jego pomocy określamy, do jakiej kategorii należy zamówienie (sama cena o tym nie świadczy).

W kolejnym kroku wewnątrz grup cen (pole Cena‑Identyfikator) dochodzi do kolejnego grupowania, tym razem ze względu na kategorię (definiuje to PIVOT).

RrbIQaGSUv4OS

Ilustracja przedstawiająca tabelę dotyczącą zamówień. Kolumny tabeli opisano kolejno: Numer klienta, Napoje, Pizza oraz Przystawki. W kolumnie Numer klienta wpisano cyfry 1, 2, 2. W kolumnach Napoje, Pizza oraz Przystawki podano ceny w złotówkach.

W ostatnim kroku każda podgrupa odpowiadająca danej kategorii zostaje sumowana (SUM(Cena)). Wynikowe rekordy zostają wyświetlone obok kolumn wybranych w SELECT. W tym wypadku jest to kolumna Nr_klienta.

Zwróć uwagę, że kategoria Napoje została usunięta, ponieważ żadne z zamówień od 1 do 4 ich nie zawiera.

R1c3vfxYymWRg

Ilustracja przedstawiająca tabelę dotyczącą zamówień. Kolumny tabeli opisano kolejno: Numer klienta, Pizza oraz Przystawki. W kolumnie Numer klienta wpisano cyfry 1, 2, 2. W kolumnach Pizza oraz Przystawki podano ceny w złotówkach.

Wywołanie tej kwerendy w całej tabeli dałoby następujący rezultat.

R1Q8VvBHVRd9h

Ilustracja przedstawiająca tabelę dotyczącą zamówień. Kolumny tabeli opisano kolejno: Numer klienta, Napoje, Pizza oraz Przystawki. Komórka z opisem Numer klienta ma czerwone wypełnienie. W kolumnie Numer klienta wpisano cyfry od 1 do 6. W kolumnie Napoje w komórkach 3, 5 i 6 podano ceny w złotówkach. W kolumnie Pizza w komórkach 1, 2, 4 i 5 podano ceny w złotówkach. W kolumnie Przystawki w komórkach 3,4 i 5 podano ceny w złotówkach.
W kolumnie Napoje, komórki 1, 2, 4 są puste.
W kolumnie Pizza, komórki 3, 6 są puste.
W kolumnie Przystawki, komórki 1, 2, 6 są puste.

Otrzymujemy w ten sposób sprawne podsumowanie stosunkowo dużej tabeli.

Słownik