Agregare

• Overview
• Câte citiri de temperatură a înregistrat Frank Pabodie,și care a fost valoarea lor medie? Soluția
• Medie cu NULL
• Soluție
• Ce face această interogare?
• Soluție
• Ordonare la concatenare
• Puncte cheie

Overview

Predare: 10 min
Exerciții: 10 min

Întrebări

• Cum pot calcula sume, medii și alte valori de sinteză?

Obiective

• Definiți agregarea și dați exemple de utilizare a acesteia.

• Scrieți interogări care calculează valori agregate.

• Traduceți execuția unei interogări care efectuează agregarea.

• Explicați cum sunt tratate datele lipsă în timpul agregării.

Acum dorim să calculăm intervale și medii pentru datele noastre.Știm cum să selectăm toate datele din tabelul Visited:

SELECT dated FROM Visited;

-null-

dar pentru a le combina, trebuie să folosim o funcție de agregare, cum ar fi min sau max.Fiecare dintre aceste funcții ia ca intrare un set de înregistrări și produce ca ieșire o singură înregistrare:

SELECT min(dated) FROM Visited;

min și max sunt doar două dintre funcțiile de agregare încorporate în SQL.Alte trei sunt avg,count,și sum:

SELECT avg(reading) FROM Survey WHERE quant = 'sal';

SELECT count(reading) FROM Survey WHERE quant = 'sal';

.

SELECT sum(reading) FROM Survey WHERE quant = 'sal';

Am folosit count(reading) aici,dar am fi putut la fel de bine să numărăm quantsau orice alt câmp din tabel,sau chiar să folosim count(*),deoarece funcției nu-i pasă de valorile în sine,ci doar de câte valori există.

SQL ne permite să facem mai multe agregări în același timp.Putem,de exemplu,să găsim intervalul de măsurători sensibile ale salinității:

SELECT min(reading), max(reading) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

De asemenea, putem combina rezultatele agregate cu rezultatele brute,deși rezultatul ar putea să vă surprindă:

SELECT person, count(*) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

De ce apare numele lui Lake și nu al lui Roerich sau al lui Dyer?Răspunsul este că, atunci când trebuie să agregheze un câmp,dar nu i se spune cum să o facă, managerul bazei de date alege o valoare reală din setul de intrări.Ar putea să o folosească pe prima procesată, pe ultima, sau cu totul altceva.

Un alt fapt important este că atunci când nu există valori de agregat -de exemplu, atunci când nu există rânduri care să satisfacă clauza WHERE – rezultatul agregării este „nu știu „și nu zero sau o altă valoare arbitrară:

SELECT person, max(reading), sum(reading) FROM Survey WHERE quant = 'missing';

O ultimă caracteristică importantă a funcțiilor de agregare este aceea că acestea sunt inconsecvente cu restul SQL într-un mod foarte util.Dacă adăugăm două valori, iar una dintre ele este nulă, rezultatul este nul.Prin extensie, dacă folosim sum pentru a adăuga toate valorile dintr-un set, iar oricare dintre aceste valori este nulă, rezultatul ar trebui să fie, de asemenea, nul.Este mult mai util, totuși, ca funcțiile de agregare să ignore valorile nule și să le combine doar pe cele care nu sunt nule.Acest comportament ne permite să ne scriem interogările sub forma:

SELECT min(dated) FROM Visited;

în loc să trebuiască să filtrăm întotdeauna în mod explicit:

SELECT min(dated) FROM Visited WHERE dated IS NOT NULL;

Agregarea tuturor înregistrărilor deodată nu are întotdeauna sens.De exemplu,să presupunem că bănuim că există o distorsiune sistematică în datele noastre și că măsurătorile de radiații ale unor oameni de știință sunt mai mari decât ale altora.Știm că acest lucru nu funcționează:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad';

pentru că managerul bazei de date selectează un singur nume arbitrar de om de știință, în loc să facă o agregare separată pentru fiecare om de știință.Având în vedere că există doar cinci oameni de știință,am putea scrie cinci interogări de forma:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'AND person = 'dyer';

dar acest lucru ar fi plictisitor,iar dacă am avea vreodată un set de date cu cincizeci sau cinci sute de oameni de știință,șansele ca toate aceste interogări să fie corecte sunt mici.

Ceea ce trebuie să facem este să îi spunem managerului bazei de date să agregheze orele pentru fiecare om de știință în partefolosind o clauză GROUP BY:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'GROUP BY person;

GROUP BY face exact ceea ce îi spune numele:grupează toate înregistrările cu aceeași valoare pentru câmpul specificat pentru ca agregarea să poată procesa fiecare lot separat.Din moment ce toate înregistrările din fiecare lot au aceeași valoare pentru person,nu mai contează că managerul bazei de date alege una arbitrară pentru a o afișa alături de valorile reading agregate.

La fel cum putem sorta după mai multe criterii deodată,putem de asemenea grupa după mai multe criterii.Pentru a obține citirea medie în funcție de cercetător și de cantitatea măsurată,de exemplu,este suficient să adăugăm un alt câmp la clauza GROUP BY:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyGROUP BY person, quant;

Rețineți că am adăugat quant la lista de câmpuri afișate,deoarece altfel rezultatele nu ar avea prea mult sens.

Să mergem un pas mai departe și să eliminăm toate intrările în care nu știm cine a efectuat măsurarea:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyWHERE person IS NOT NULLGROUP BY person, quantORDER BY person, quant;

Cu o privire mai atentă,această interogare:

1. a selectat înregistrări din tabelul Surveyîn care câmpul person nu era nul;

2. a grupat aceste înregistrări în subseturi astfel încât valorile person și quant din fiecare subset să fie aceleași;

3. a ordonat acele subgrupuri mai întâi după person,și apoi în cadrul fiecărei subgrupe după quant;și

4. a numărat numărul de înregistrări din fiecare subgrup,a calculat media reading din fiecare,și a ales o valoare person și quant din fiecare (nu contează care dintre ele,deoarece toate sunt egale).

Câte citiri de temperatură a înregistrat Frank Pabodie,și care a fost valoarea lor medie?

Soluția

SELECT count(reading), avg(reading) FROM Survey WHERE quant = 'temp' AND person = 'pb';

numărul (citire)	vg(citire)
2	-20.0

Medie cu NULL

Media unui set de valori este suma valorilorîmpărțită la numărul de valori.Aceasta înseamnă că funcția avg returnează 2,0 sau 3,0 atunci când i se dau valorile 1,0, null și 5,0?

Soluție

Răspunsul este 3,0.NULL nu este o valoare; este absența unei valori.Ca atare, nu este inclusă în calcul.

Puteți confirma acest lucru, executând acest cod:

SELECT AVG(a) FROM ( SELECT 1 AS a UNION ALL SELECT NULL UNION ALL SELECT 5);

Ce face această interogare?

Vrem să calculăm diferența dintre fiecare citire individuală a radiațiilor și media tuturor citirilor radiațiilor.Scriem interogarea:

SELECT reading - avg(reading) FROM Survey WHERE quant = 'rad';

Ce produce de fapt această interogare și vă puteți gândi de ce?

Soluție

Interogarea produce un singur rând de rezultate, când ceea ce dorim cu adevărat este un rezultat pentru fiecare dintre măsurători.Funcția avg() produce o singură valoare și, deoarece este executată prima, tabelul este redus la un singur rând.Valoarea reading este pur și simplu una arbitrară.

Pentru a obține ceea ce ne dorim, ar trebui să executăm două interogări:

SELECT avg(reading) FROM Survey WHERE quant='rad';

Aceasta produce valoarea medie (6.5625), pe care o putem insera apoi într-o a doua interogare:

SELECT reading - 6.5625 FROM Survey WHERE quant = 'rad';

Aceasta produce ceea ce dorim, dar o putem combina într-o singură interogare folosind subinterogări.

SELECT reading - (SELECT avg(reading) FROM Survey WHERE quant='rad') FROM Survey WHERE quant = 'rad';

În acest fel nu trebuie să executăm două interogări.

În rezumat, ceea ce am făcut este să înlocuim avg(reading) cu (SELECT avg(reading) FROM Survey WHERE quant='rad') în interogarea originală.

Ordonare la concatenare

Funcția group_concat(field, separator)concatenează toate valorile dintr-un câmpfolosind caracterul de separare specificat (sau ‘,’ dacă separatorul nu este specificat).Folosiți această funcție pentru a produce o listă de nume de oameni de știință pe un singur rând,cum ar fi:

William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth

Puteți găsi o modalitate de a ordona lista după numele de familie?

Puncte cheie

• Utilizați funcții de agregare pentru a combina mai multe valori.

• Funcțiile de agregare ignoră valorile null.

• Agregarea are loc după filtrare.

• Utilizați GROUP BY pentru a combina subseturile separat.

• Dacă nu este specificată nicio funcție de agregare pentru un câmp, interogarea poate returna o valoare arbitrară pentru acel câmp.