ebSkola

10. tēma / 10.4

10.4 JOIN un sarežģīti vaicājumi

Stundas uzdevums: 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

70 min plāns: Teorija un paraugs (~10 min) · 1. uzdevums (~15 min) - savieno tabulas ar INNER JOIN · 2. uzdevums (~25 min) - salīdzini INNER un LEFT JOIN · 3. uzdevums (~20 min) - izveido TOP sarakstu ar GROUP BY. Papildu uzdevumu sāc tikai tad, ja pārējie trīs ir gatavi.

Pirms sāc: atver savu datubāzi ar tabulām speletaji un speles. Pārliecinies, ka vismaz vienam spēlētājam nav nevienas spēles - to vajadzēs 2. uzdevumā.

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;

Praktiskie uzdevumi

1. uzdevums -

Savieno tabulas ar INNER JOIN

Beigās viens vaicājums parādīs datus no abām tabulām.

  1. Uzraksti SELECT s.vards, sp.punkti FROM speletaji s.
  2. Pievieno INNER JOIN speles sp ON s.id = sp.speletajs_id.
  3. Izpildi un pieraksti, cik rindu atgriezās.
  4. Pievieno ORDER BY sp.punkti DESC.
  5. Pievieno arī kolonnu sp.spelets.
  6. Pieraksti, ko nozīmē burti s un sp vaicājumā.

Gatavs, kad: rezultātā katrā rindā ir gan spēlētāja vārds, gan viņa spēles punkti.

2. uzdevums -

Salīdzini INNER un LEFT JOIN

Beigās tu zināsi, kurš JOIN paslēpj datus.

  1. Pārliecinies, ka vienam spēlētājam nav nevienas spēles.
  2. Izpildi savu INNER JOIN vaicājumu un meklē šo spēlētāju rezultātā.
  3. Pieraksti, vai viņš tur ir.
  4. Nomaini INNER JOIN uz LEFT JOIN un izpildi vēlreiz.
  5. Pieraksti, kas tagad redzams punktu kolonnā šim spēlētājam.
  6. Pieraksti, cik rindu atgriež katrs variants.
  7. Pieraksti vienu secinājumu: kad lietot LEFT JOIN.

Gatavs, kad: ar INNER JOIN spēlētājs bez spēlēm pazūd, bet ar LEFT JOIN viņš ir redzams ar NULL.

3. uzdevums -

Izveido TOP sarakstu ar GROUP BY

Beigās tev būs īsts rezultātu kopsavilkums.

  1. Uzraksti vaicājumu ar COUNT(sp.id) AS speles_skaits.
  2. Pievieno SUM(sp.punkti) AS kopa un MAX(sp.punkti) AS rekords.
  3. Pievieno GROUP BY s.vards.
  4. Izpildi un pārbaudi, vai katrs spēlētājs parādās tikai vienu reizi.
  5. Izņem GROUP BY rindu un izlasi kļūdu.
  6. Pievieno ORDER BY kopa DESC LIMIT 5.
  7. Pieraksti vienu secinājumu: kāpēc pie agregātfunkcijām vajag GROUP BY.

Gatavs, kad: katrs spēlētājs rezultātā parādās vienu reizi ar savu spēļu skaitu, kopsummu un rekordu.

Papildu uzdevums - Filtrē grupas ar HAVING

Ja pamatdarbs ir gatavs, atlasi tikai aktīvos spēlētājus.

  1. Pievieno vaicājumam HAVING COUNT(sp.id) >= 2.
  2. Izpildi un pieraksti, cik spēlētāju palika.
  3. Mēģini to pašu ar WHERE un izlasi kļūdu.
  4. Pieraksti, ar ko HAVING atšķiras no WHERE.
  5. Pievieno arī AVG(sp.punkti)::INT AS videjie.

Gatavs, kad: rezultātā ir tikai spēlētāji ar vismaz divām spēlēm, un tu vari izskaidrot HAVING.

Biežākās kļūdas

  • Aizmirsta GROUP BY: Lietojot agregātu (COUNT, SUM), visi neagregētie lauki jāpievieno GROUP BY.
  • Cartesian product: Bez ON nosacījuma JOIN reizina rindu skaitus - vienmēr norādi ON.
  • NULL salīdzinājums: WHERE x = NULL nav pareizi - lieto WHERE x IS NULL.

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