Kapcsolások és csoportosítások

Kapcsolási típusok

• Descartes-szorzat
  • CROSS JOIN
• Belső összekapcsolás
  • NATURAL JOIN, INNER JOIN
• Külső összekapcsolás
  • OUTER JOIN

Belső összekapcsolás

• Természetes összekapcsolás
• Theta összekapcsolás
• Félig összekapcsolás
  • semi join, jele: \(\semijoin\)
  • Adott \(R(A_1, \ldots, A_n), S(B_1,\ldots,B_m)\) esetén \(R \semijoin S \equiv \Pi_{A_1,A_2,\ldots,A_n}(R \Join S)\)
• Anti összekapcsolás
  • anti join, jele: \(\antijoin\)
  • \(R \antijoin S \equiv R - R \semijoin S\)

SQL megfeleltetés - Belső összekapcsolás

Semi join

SELECT R.*
FROM R
WHERE EXISTS (
    SELECT * 
    FROM S
    WHERE R.A = S.A
);

Anti join

SELECT R.*
FROM R
WHERE NOT EXISTS (
    SELECT *
    FROM S
    WHERE R.A = S.A
);

Külső összekapcsolás

• Kiterjesztett relációs algebra
• \(R\leftouterjoin_C\,S\), \(R\rightouterjoin_C\,S\), \(R\fullouterjoin_C\,S\)
  • Természetes változatban a C feltétel nincs
• Lógó sorok
  • Azon sorok, amiknek a másik relációban nincs párja
• A külső kapcsolás megőrzi a lógó sorokat NULL értékkel helyettesítve a hiányzó értékeket

Külső összekapcsolás - változatok

• R <típus> OUTER JOIN S
  • NATURAL az OUTER előtt
  • vagy ON <feltétel> az S után
• Lógó sorok megőrzése
  • LEFT OUTER esetén R
  • RIGHT OUTER esetén S
  • FULL OUTER esetén mindkettő

SQL megfeleltetés - Külső összekapcsolás

Left outer join

SELECT *
FROM R
LEFT OUTER JOIN S
    ON R.A = S.A;

Right outer join

SELECT *
FROM R
RIGHT OUTER JOIN S
    ON R.A = S.A;

Full outer join

SELECT *
FROM R
FULL OUTER JOIN S
    ON R.A = S.A;

Aggregációk (Összesítések)

• SUM, AVG, COUNT, MIN és MAX függvények a SELECT záradékban egy-egy oszlopra
• COUNT(*) megadja az eredmény sorainak számát
• Nem számítanak a NULL értékek kiértékeléskor
  • Ha csak NULL érték va, akkor az összesítő eredménye is NULL
  • Kivétel a COUNT az üreshalamazon 0-t ad vissza

Aggregáció - Példa

Adjuk meg a Bud átlagos árát a Felszolgál(kocsma, sör, ár) alapján

SELECT AVG(ár)
FROM Felszolgál
WHERE sör = 'Bud';

Adjuk meg, hány különféle áron árulják a Budot

Ehhez a DISTINCT.re lesz szükség az összesítőn belül

SELECT COUNT(DISTINCT ár)
FROM Felszolgál
WHERE sör = 'Bud'

Csoportosítás

• GROUP BY <oszlopok> a WHERE záradék után
• Adott oszlopok alapján, rendre csoportosít
  • Tehát első oszlopon belül a második oszlop szerinte és így tovább

Összesítés esetén megkötések a SELECT záradékban felsorollt attribútumokra:

• Az aggregátorok csoportonként külön kerülnek kiértékelésre
  • Például minden sör átlagos árát a sörök csoportosításával és a AVG függvénnyel megoldható
• Összesítő operátor nélkül csak a GROUP BY _-ban felsorolt oszlopok kerülhetnek
  • Nem is volna értelme, hiszen ezek halmazok, nem lenne konzisztens a visszatérő érték
  • Az előző sörös példánál mondjuk ha a query: SELECT sör, AVG(ár) FROM Felszolgál GROUP BY sör;
    • akkor itt nem írhatjuk ki csak úgy a kocsmát, mert a sörre csoportosítottunk. (meg nem is logikus, hogy átlagosan hol volt eladva)

Csoportosítás szűrése - HAVING

• GROUP BY <oszlopok> HAVING <feltétel>
  • Ebben az esetben a feltétel a csoportokra vonatkozik, és csak a feltételnek eleget tevő csoportok kerülnek az eredménybe

Megkötések

• Az alkérdésekre nincsen semmilyen megkötés
• Az alkérdésen kívül a csoportosításra vonatkozó szabályok élnek
• Összesítéssel a FROM záradékban megadott relációk bármyely attribútumát használhatjuk a HAVING záradékban
• Összesítés nélkül csak olyan attribútumok használhatók, amik a GROUP BY _ záradékban is szerepelnek
• A HAVING záradékban az összesítés csak az éppen feldolgozott csoport soraira vonatkozik

Csoportosítás szűréssel - Példa

A Felszolgál(kocsma, sör, ár) és Sörök(név, gyártó) táblák felhasználásával adjuk meg azon sörök átlagos árát, melyeket legalább három kocsmában felszolgáltak, vagy Pete a gyártójuk

Nyilván a feketén eladott (NULL kocsmás) felszolgálások nem számítanak

SELECT sör, AVG(ár)
FROM Felszolgál
GROUP BY sör
HAVING COUNT(kocsma) >= 3 -- Azok a csoportok, amik legalább 3 nem-NULL kocsmában árulnak
    OR sör IN ( -- Pete által gyártott sörök
        SELECT név
        FROM Sörök
        WHERE gyártó = 'Pete'
    );