10.4 JOIN un sarežģīti vaicājumi

Tavs šīs stundas izaicinājums: Apvienot datus no vairākām tabulām, izmantojot JOIN un agregētas funkcijas.

SR 2.4.4. Iegūst, atlasa un apstrādā datus SR 2.4.5. Datu analīze un vizualizācija

Teorija: JOIN un agregēti vaicājumi

JOIN apvieno rindas no divām vai vairākām tabulām, balstoties uz attiecībām (parasti FK ↔ PK).

-- INNER JOIN — tikai tās rindas, kurām ir atbilstība abās tabulās
SELECT s.vards, sp.punkti, sp.spelets
FROM speletaji s
INNER JOIN speles sp ON s.id = sp.speletajs_id
ORDER BY sp.spelets DESC;

-- LEFT JOIN — visi spēlētāji, ieskaitot tos bez spēlēm
SELECT s.vards, COUNT(sp.id) AS speles_skaits
FROM speletaji s
LEFT JOIN speles sp ON s.id = sp.speletajs_id
GROUP BY s.vards;

-- Apvienots: TOP 5 spēlētāju kopējie punkti
SELECT s.vards, SUM(sp.punkti) AS kopa
FROM speletaji s
INNER JOIN speles sp ON s.id = sp.speletajs_id
GROUP BY s.vards
ORDER BY kopa DESC
LIMIT 5;

1. uzdevums: INNER JOIN praktiski

Saraksts ar visām spēlēm un to spēlētāju vārdiem.

Izpildes soļi:

  1. Izpildi: SELECT s.vards, sp.punkti, sp.spelets FROM speletaji s INNER JOIN speles sp ON s.id = sp.speletajs_id;
  2. Sakārto pēc datuma dilstoši (jaunākās augšā).
  3. Pievieno filtru — tikai šī mēneša spēles: WHERE sp.spelets >= NOW() - INTERVAL '1 month'.
  4. Pieraksti rezultātus failā.

2. uzdevums: LEFT JOIN un GROUP BY

Atrod, cik spēļu ir nospēlējis katrs spēlētājs (arī 0 spēles).

Izpildes soļi:

  1. Izveido vēl 2 spēlētājus, kuri nav nospēlējuši nevienu spēli.
  2. Izpildi: SELECT s.vards, COUNT(sp.id) AS skaits FROM speletaji s LEFT JOIN speles sp ON s.id = sp.speletajs_id GROUP BY s.vards;
  3. Salīdzini ar INNER JOIN — kā mainās rezultāts?
  4. Sakārto pēc skaita dilstoši.

3. uzdevums: Sarežģītā statistika

Izveido reālu "leaderboard" vaicājumu.

Izpildes soļi:

  1. TOP 5 pēc kopējiem punktiem: SELECT s.vards, SUM(sp.punkti) AS kopa FROM speletaji s INNER JOIN speles sp ON s.id = sp.speletajs_id GROUP BY s.vards ORDER BY kopa DESC LIMIT 5;
  2. Vidējie punkti vienai spēlei: AVG(sp.punkti) AS videjie.
  3. Spēlētāja personīgais rekords: pievieno MAX(sp.punkti) AS rekords.
  4. Izveido VIEW (saglabātu vaicājumu): CREATE VIEW leaderboard AS <tavs vaicājums>;
  5. Tagad TOP 5 var atvērt ar: SELECT * FROM leaderboard;

Papildus uzdevums: Subqueries

Izmēģini ligzdotos vaicājumus.

Izpildes soļi:

  1. Spēlētāji, kuriem augstāks vidējais nekā kopējais vidējais: SELECT vards FROM (SELECT s.vards, AVG(sp.punkti) AS v FROM speletaji s JOIN speles sp ON s.id=sp.speletajs_id GROUP BY s.vards) t WHERE t.v > (SELECT AVG(punkti) FROM speles);
  2. Salīdzini ar JOIN versiju — kura skaidrāka?

Biežākās kļūdas

Koda piemērs

-- TOP 5 spēlētāji ar pilnu statistiku
SELECT
    s.vards,
    COUNT(sp.id) AS speles_skaits,
    SUM(sp.punkti) AS kopa,
    AVG(sp.punkti)::INT AS videjie,
    MAX(sp.punkti) AS rekords
FROM speletaji s
INNER JOIN speles sp ON s.id = sp.speletajs_id
GROUP BY s.vards
ORDER BY kopa DESC
LIMIT 5;
vards | speles | kopa | videjie | rekords
Anna | 5 | 600 | 120 | 200
Jānis | 3 | 450 | 150 | 200
⬅ Iepriekšējā stunda Nākamā stunda ➡