Cum funcționează
Aceasta e cea mai importantă subinterogare din tot Subiectul I, pentru că e singura în care nu
există variantă alternativă. La „maximul" din
problema #153 puteai încerca (greșit, dar
tentant) un ORDER BY. Aici nu ai ce sorta: media nu e o valoare din tabel, e o valoare calculată
din toate rândurile.
Ce nu se poate scrie:
SELECT username, nivel FROM Jucatori WHERE nivel > AVG(nivel); -- EROARE
Motivul e ordinea de execuție: WHERE se evaluează rând cu rând, iar când e pe rândul 1 nu știe
nimic despre celelalte — deci nu poate cunoaște o medie. AVG se calculează abia după ce toate
rândurile au fost citite. Subinterogarea rupe problema în două pași care se execută la momentul
potrivit:
1. (SELECT AVG(nivel) FROM Jucatori) -> 56.875 calculat o singura data, inainte
2. WHERE nivel > 56.875 comparat pe fiecare rand
Pe datele de test, nivelul mediu e 56.875. Jucătorii cu nivelul 64, 70, 78 și 91 trec; cel cu 55 nu.
O soluție care „aproximează" media la 57 sau la 50 dă alt răspuns.
De aceea comparația se face cu subinterogarea, nu cu media citită cu ochii din rezultatul cerinței
a). Interogarea trebuie să rămână corectă și după ce se înscrie un jucător nou.
Un detaliu: la cerința a) am pus ROUND(..., 2) pentru afișare (altfel apar zecimale inutile),
dar la cerința b) subinterogarea e fără ROUND. Comparația se face cu media exactă; rotunjirea la
57 ar exclude, teoretic, un jucător cu nivelul exact 57.
AVG ignoră valorile NULL: un jucător cu nivelul necompletat nu trage media în jos, pur și simplu
nu intră în calcul.
Variantă cu HAVING, pentru comparație
SELECT username, nivel FROM Jucatori
GROUP BY username, nivel
HAVING nivel > (SELECT AVG(nivel) FROM Jucatori);
Funcționează, dar gruparea nu are nicio treabă aici. WHERE e răspunsul corect: condiția e pe un rând,
nu pe un grup.
Greșeli frecvente
WHERE nivel > AVG(nivel) — eroare de execuție. WHERE nu poate vedea agregări.- Media scrisă de mână (
WHERE nivel > 56.875) — ai copiat rezultatul cerinței a); se strică la
primul jucător nou.
- Media rotunjită în subinterogare —
> ROUND(AVG(nivel)) schimbă răspunsul pentru jucătorii
aflați aproape de medie.
>= în loc de > — „peste medie" e strict mai mare.AVG(scor) în loc de AVG(nivel) — scorul e al jocului, nivelul e al jucătorului.SUM(nivel) / COUNT(*) în loc de AVG — dă alt rezultat dacă vreun nivel e NULL.