W bazie SQLite3 zapisanej w pliku portal.db znajdują się dane dotyczące użytkowników pewnego portalu internetowego. Plik pobieramy i zapisujemy w wybranym katalogu.

Do wykonywania operacji na bazie danych w tym materiale wykorzystamy skrypt napisany w języku Python oraz moduł sqlite3 przeznaczony do obsługi baz SQLite3.

Schemat bazy

Poznajmy zawartość bazy. W wybranym edytorze tworzymy skrypt, w którym umieszczamy następujący kod:

import sqlite3

def wykonaj_zapytanie(kursor, zapytanie):
    kursor.execute(zapytanie)
    for rekord in kursor.fetchall():
        print(*rekord)

baza = 'portal.db'
pol = sqlite3.connect(baza)  # połączenie z bazą
kursor = pol.cursor()  # utworzenie obiektu kursora

kwerenda = "SELECT name, sql FROM sqlite_master WHERE type='table';"

wykonaj_zapytanie(kursor, kwerenda)

Do nawiązania połączenia z bazą wykorzystujemy metodę connect(). Przekazujemy jej nazwę pliku, w którym zapisana jest baza. Wykonywanie operacji na bazie umożliwia obiekt kursora zwracany przez metodę cursor(). Kursor udostępnia metodę execute() pozwalającą wykonywać zapytania SQL podane jako argument. Wyniki zapytania zwraca w postaci listy rekordów metoda fetchall().

Plik zapisujemy pod nazwą kwerendy_portal.py w tym samym katalogu, w którym zapisaliśmy wcześniej bazę danych.

CREATE TABLE sqlite_sequence(name,seq)
CREATE TABLE panstwa (
        idp INTEGER PRIMARY KEY AUTOINCREMENT,
        nazwa VARCHAR(30) NOT NULL CHECK(nazwa <> '')
)
CREATE TABLE uzytkownicy (
        idu INTEGER PRIMARY KEY AUTOINCREMENT,
        imie VARCHAR(20) NOT NULL CHECK(imie <> ''),
        nazwisko VARCHAR(30) NOT NULL CHECK(nazwisko <> ''),
  idp INTEGER REFERENCES panstwa(idp),
        plec CHAR(1) DEFAULT ''
)
CREATE TABLE znajomosci (
        idu1 INTEGER NOT NULL REFERENCES uzytkownicy(idu) ON DELETE CASCADE,
        idu2 INTEGER NOT NULL REFERENCES uzytkownicy(idu) ON DELETE CASCADE,
  data DATE,
  PRIMARY KEY(idu1, idu2)
)
CREATE TABLE zdjecia (
        idz INTEGER PRIMARY KEY AUTOINCREMENT,
        data DATE NOT NULL,
        idu INTEGER REFERENCES uzytkownicy(idu),
        szer INTEGER DEFAULT 0,
        wys INTEGER DEFAULT 0
)

Zapytania grupujące i funkcje agregujące

Rozwiązanie polecenia wymaga zliczenia rekordów z tabeli uzytkownicy. Użyjemy funkcji agregującejfunkcje agregującefunkcji agregującej COUNT(), która zlicza rekordy pasujące do zapytania:

SELECT COUNT(*) AS liczba_użytkowników
FROM uzytkownicy;

kwerenda = "SELECT name, sql FROM sqlite_master WHERE type='table';"
kwerenda = """
    SELECT COUNT(*) AS liczba_użytkowników
    FROM uzytkownicy;
"""

wykonaj_zapytanie(kursor, kwerenda)

Po wykonaniu kwerendy dowiemy się, że w bazie zapisano dane 84 użytkowników.

Odpowiedź wymaga pogrupowania użytkowników według identyfikatorów państw, a następnie zliczenia rekordów w poszczególnych grupach.

SELECT idp, COUNT(*) AS liczba_użytkowników
FROM uzytkownicy
GROUP BY idp;

W tym przykładzie widzimy, że grupowaniegrupowaniegrupowanie pozwala przeprowadzać obliczenia na polach rekordów wyodrębnionych według wartości kolumny podanej w klauzuli GROUP BY.

Wynik dotychczasowego zapytania nie jest czytelny, identyfikatory państw warto byłoby zastąpić nazwami. W tym celu dodamy do zapytania złączenie, które pozwoli pobrać nazwy państw z drugiej tabeli. Dodatkowo wyniki uporządkujemy malejąco według liczby użytkowników:

SELECT panstwa.nazwa, COUNT(*) AS liczba_użytkowników
FROM uzytkownicy
INNER JOIN panstwa ON uzytkownicy.idp = panstwa.idp
GROUP BY panstwa.nazwa
ORDER BY liczba_użytkowników DESC;

Początkowe zwrócone rekordy:

Stany Zjednoczone 8
Wielka Brytania 6
Niemcy 5
Polska 4
Kanada 4

Możemy zauważyć, że potrzebujemy zapytania podobnego do poprzedniego, ale z dodatkowym warunkiem. W którym miejscu należy go dodać? Może w klauzuli WHERE? To jednak błędne przypuszczenie, ponieważ warunki z klauzuli WHERE nakładane są na rekordy przed ich grupowaniem. Ponadto żeby wybrać państwa z jednym użytkownikiem, trzeba wcześniej tych użytkowników policzyć. Z tego wynika, że warunek musi zostać nałożony po zgrupowaniu i policzeniu rekordów. W takich przypadkach stosujemy klauzulę HAVING, w której możemy używać funkcji agregujących. Kwerenda przyjmie następującą postać:

SELECT panstwa.nazwa
FROM panstwa, uzytkownicy
USING(idp)
GROUP BY panstwa.nazwa
HAVING COUNT(*) = 1;

W zapytaniu użyliśmy również klauzuli USING(), która może zastąpić klauzulę INNER JOIN ON, jeżeli pola tworzące złączenie mają taką samą nazwę w obu tabelach.

SELECT panstwa.nazwa
FROM panstwa JOIN uzytkownicy
USING(idp)
GROUP BY panstwa.nazwa
HAVING COUNT(*) = 1;

Początkowe zwrócone rekordy:

Algieria
Armenia
Austria
Chorwacja
Dania

Zacznijmy od pogrupowania rekordów z tabeli zdjęcia według identyfikatorów użytkowników i policzenia ich:

SELECT COUNT(*) FROM zdjecia GROUP BY idu";

Żeby wyeliminować z grupowania mężczyzn, musimy dołączyć tabelę uzytkownicy i dodać odpowiedni warunek, tym razem w klauzuli WHERE.

SELECT imie, nazwisko, COUNT(*) AS liczba_zdjęć
FROM zdjecia, uzytkownicy
USING(idu)
WHERE plec = 'K'
GROUP BY zdjecia.idu
ORDER BY COUNT(*) DESC;

Przeanalizujmy działanie zapytania: wszystkie rekordy z tabeli zdjecia złączone z odpowiednimi rekordami z tabeli uzytkownicy zostają wybrane, następnie odrzucane są te, które nie spełniają warunku z klauzuli WHERE, reszta jest grupowana według identyfikatora użytkownika z tabeli zdjecia i zliczana, na koniec zostają posortowane malejąco według liczby zdjęć.

Rozwiązanie zadania zaczniemy od wyświetlenia imion i nazwisk osób, które zawarły znajomość. W tabeli znajomosci mamy niepowtarzające się pary identyfikatorów użytkowników: idu1 i idu2. Kwerenda, która pokaże te identyfikatory oraz imię i nazwisko pierwszego użytkownika, może wyglądać tak:

SELECT idu1, imie, nazwisko, idu2
FROM uzytkownicy
INNER JOIN znajomosci ON idu=idu1;

Początkowe zwrócone rekordy:

1 Joshua King 22
1 Joshua King 26
1 Joshua King 36
1 Joshua King 46
1 Joshua King 47
1 Joshua King 69
1 Joshua King 74

Żeby wyświetlić imiona i nazwiska osób, których identyfikator zawarty jest w polu idu2, przekształcimy powyższą kwerendę w podzapytaniepodzapytaniepodzapytanie:

SELECT q1.idu1, q1.imie, q1.nazwisko, q1.idu2, u.imie, u.nazwisko
FROM (
    SELECT idu1, imie, nazwisko, idu2
    FROM uzytkownicy
    INNER JOIN znajomosci ON idu=idu1
    ) q1, uzytkownicy u
WHERE u.idu =q1.idu2;

Początkowe zwrócone rekordy:

1 Joshua King 22 Frank Dijkstra
1 Joshua King 26 Joshua Nelson
1 Joshua King 36 Milan Petrovic
1 Joshua King 46 Laura Mello
1 Joshua King 47 Mia Hoffmann
1 Joshua King 69 Grace Doherty
1 Joshua King 74 Luka Wagner
...
22 Frank Dijkstra 2 Leonardo De Luca
22 Frank Dijkstra 8 Samuel Ramirez
22 Frank Dijkstra 36 Milan Petrovic
22 Frank Dijkstra 62 Filip Kralj
22 Frank Dijkstra 67 Artjoms Klavins
22 Frank Dijkstra 75 Elise Wouters

Po przejrzeniu wszystkich wyników zauważymy, że aby zliczyć wszystkie znajomości nawiązane przez użytkowników, trzeba wziąć pod uwagę znajomości, które nawiązali oni, i znajomości, które nawiązano z nimi.

Tworzymy dwa zapytania. Pierwsze pokaże imię, nazwisko oraz liczbę znajomości, które zawarli użytkownicy – rekordy zostaną zgrupowane według pola idu1. Drugie pokaże imię, nazwisko oraz liczbę znajomości, które z użytkownikami zawarły inne osoby – rekordy zgrupujemy według pola idu2. W kwerendach wykorzystamy aliasy:

SELECT imie, nazwisko, COUNT(idu1) AS z1
FROM uzytkownicy
INNER JOIN znajomosci ON idu=idu1
GROUP BY idu1;

Początkowe zwrócone rekordy:

Joshua King 7
Leonardo De Luca 8
Aleksandra Krawczyk 8
Luka Schulte 2
Jayden Harper 8

SELECT imie, nazwisko, COUNT(idu2) AS z2
FROM uzytkownicy
INNER JOIN znajomosci ON idu=idu2
GROUP BY idu2;

Początkowe zwrócone rekordy:

Joshua King 7
Leonardo De Luca 4
Aleksandra Krawczyk 13
Luka Schulte 5
Jayden Harper 6

Jeżeli popatrzymy na wyniki powyższych kwerend, zauważymy na przykład to, że użytkownik Leonardo De Luca zawarł osiem znajomości, a z nim zawarto cztery znajomości. Więc aby pokazać, ile znajomości zawarł użytkownik, musimy dopasować rekordy z obydwu kwerend według nazwisk użytkowników i dodać do siebie pola aliasowane jako z1 oraz z2. W ten sposób przygotowane kwerendy wykorzystamy jako podzapytaniapodzapytaniepodzapytania.

SELECT q1.imie, q1.nazwisko, q1.z1 + q2.z2 AS liczba_znajomosci
FROM (
    SELECT imie, nazwisko, COUNT(idu1) AS z1
    FROM uzytkownicy
    INNER JOIN znajomosci ON idu=idu1
    GROUP BY idu1
    ) q1
INNER JOIN (
    SELECT imie, nazwisko, COUNT(idu1) AS z2
    FROM uzytkownicy
    INNER JOIN znajomosci ON idu=idu2
    GROUP BY idu2
    ) q2
ON q1.nazwisko = q2.nazwisko
ORDER BY liczba_znajomosci DESC;

Na początku wykonana będzie pierwsza kwerenda po klauzuli FROM nazwana q1, następnie druga wstawiona po klauzuli INNER JOIN nazwana q2. Rekordy obydwu kwerend zostaną połączone na podstawie identycznych nazwisk. Wtedy z kwerendy q1 wybrane zostaną pola imie i nazwisko, a następnie przeprowadzone będzie sumowanie pól z1 i z2, przez co otrzymamy pole wyliczeniowepole wyliczeniowepole wyliczeniowe nazwane liczba_znajomosci. Wyniki zostaną uporządkowane malejąco według liczby znajomości.

Początkowe zwrócone rekordy:

Aleksandra Krawczyk 21
Grace Doherty 21
Filip Kralj 20
Lautero Quiroga 18
Andreas Jakobsson 18

Słownik