admin

Przegląd

Dydaktyka: 10 min
Ćwiczenia: 10 min
Pytania

  • Jak obliczać sumy, średnie i inne wartości sumaryczne?

Cele

  • Zdefiniuj agregację i podaj przykłady jej zastosowania.

  • Pisać zapytania obliczające wartości zagregowane.

  • Śledzić wykonanie zapytania wykonującego agregację.

  • Wyjaśnić, w jaki sposób obsługiwane są brakujące dane podczas agregacji.

Chcemy teraz obliczyć zakresy i średnie dla naszych danych.Wiemy jak wybrać wszystkie daty z tabeli Visited:

SELECT dated FROM Visited;

dated

-null-

, ale aby je połączyć, musimy użyć funkcji agregującej, takiej jak min lub max.Każda z tych funkcji przyjmuje zestaw rekordów jako dane wejściowe i produkuje pojedynczy rekord jako dane wyjściowe:

SELECT min(dated) FROM Visited;

min(data) 

SELECT max(dated) FROM Visited;

max(data)

min i max to tylko dwie z funkcji agregujących wbudowanych w SQL.Trzy inne to avg,count i sum:

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

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

count(odczyt)

. 

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

sum(czytanie)

Użyliśmy tutaj count(reading),ale równie dobrze mogliśmy policzyć quant lub dowolne inne pole w tabeli, albo nawet użyć count(*), ponieważ funkcja nie dba o same wartości, tylko o to, ile ich jest.

SQL pozwala nam na wykonywanie kilku agregacji jednocześnie.Możemy na przykład znaleźć zakres sensownych pomiarów zasolenia:

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

Możemy również połączyć zagregowane wyniki z wynikami surowymi, chociaż wynik może Cię zaskoczyć:

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

person count(*)
lake 7

Dlaczego pojawia się nazwisko Lake’a, a nie Roericha czy Dyera?Odpowiedź jest taka, że kiedy musi zagregować pole, ale nie jest powiedziane, jak to zrobić, menedżer bazy danych wybiera rzeczywistą wartość z zestawu wejściowego.Może użyć pierwszej przetworzonej, ostatniej, lub czegoś zupełnie innego.

Innym ważnym faktem jest to, że gdy nie ma żadnych wartości do agregacji – na przykład, gdy nie ma wierszy spełniających klauzulę WHERE – wynikiem agregacji jest „nie wiem”, a nie zero lub inna arbitralna wartość:

SELECT person, max(reading), sum(reading) FROM Survey WHERE quant = 'missing';
osoba max(odczyt) suma(odczyt)
-null- -.null- -null-

Jedną z ostatnich ważnych cech funkcji agregujących jest to, że są one niespójne z resztą języka SQL w bardzo użyteczny sposób.Jeśli dodajemy dwie wartości, a jedna z nich jest pusta, wynik jest pusty. Jeśli użyjemy sum do dodania wszystkich wartości w zestawie, a którakolwiek z tych wartości jest pusta, wynik również powinien być pusty. Jednak o wiele bardziej użyteczne jest to, że funkcje agregujące ignorują wartości puste i łączą tylko te, które nie są puste.Takie zachowanie pozwala nam pisać nasze zapytania jako:

SELECT min(dated) FROM Visited;

min(dated)

zamiast zawsze musieć jawnie filtrować:

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

min(dated)

Agregowanie wszystkich rekordów naraz nie zawsze ma sens.Na przykład, załóżmy, że podejrzewamy, że w naszych danych istnieje systematyczna tendencyjność i że odczyty promieniowania niektórych naukowców są wyższe niż innych.Wiemy, że to nie działa:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad';
osoba count(odczyt) round(avg(odczyt), 2)
roe 8 6.56

, ponieważ menedżer bazy danych wybiera jedną dowolną nazwę naukowca, zamiast agregować dane osobno dla każdego naukowca.Ponieważ jest tylko pięciu naukowców, moglibyśmy napisać pięć zapytań w postaci:

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

ale to byłoby żmudne, a jeśli kiedykolwiek mieliśmy zestaw danych z pięćdziesięcioma lub pięciuset naukowcami, szanse, że dostaniemy wszystkie te zapytania dobrze są małe.

What we need to do istell the database manager to aggregate the hours for each scientist separatelyusing a GROUP BY clause:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'GROUP BY person;
person count(reading) round(avg(reading), 2)
dyer 2 8.81
lake 2 1.82
pb 3 6.66
roe 1 11.25

GROUP BY robi dokładnie to, co sugeruje jego nazwa: grupuje wszystkie rekordy o tej samej wartości dla określonego pola razem, tak aby agregacja mogła przetwarzać każdą partię osobno.Ponieważ wszystkie rekordy w każdej partii mają tę samą wartość dla pola person, nie ma już znaczenia, że menedżer bazy danych wybiera dowolną wartość do wyświetlenia obok zagregowanych wartości reading.Aby uzyskać średni odczyt według naukowca i zmierzonej ilości, na przykład, po prostu dodajemy kolejne pole do klauzuli GROUP BY:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyGROUP BY person, quant;
person quant count(reading) round(avg(reading), 2)
-null- sal 1 0.06
-null- temp 1 -26.0
dyer rad 2 8.81
dyer sal 2 0,11
lake rad 2 1.82
lake sal 4 0.11
lake temp 1 -16.0
pb rad 3 6,66
pb temp 2 -20.0
roe rad 1 11.25
roe sal 2 32.05

Zauważ, że dodaliśmy quant do listy wyświetlanych pól, ponieważ w przeciwnym razie wyniki nie miałyby większego sensu.

Pójdźmy o krok dalej i usuńmy wszystkie wpisy, w których nie wiemy, kto dokonał pomiaru:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyWHERE person IS NOT NULLGROUP BY person, quantORDER BY person, quant;
osoba quant count(odczyt) round(avg(odczyt), 2)
dyer rad 2 8.81
dyer sal 2 0.11
lake rad 2 1.82
lake sal 4 0.11
lake temp 1 -16.0
pb rad 3 6,66
pb temp 2 -20.0
roe rad 1 11.25
roe sal 2 32.05

Patrząc dokładniej,to zapytanie:

  1. selekcjonowało rekordy z tabeli Survey, w których pole person nie miało wartości null;

  2. grupowało te rekordy w podzbiory tak, aby wartości person i quant w każdym podzbiorze były takie same;

  3. ordered those subsets first by person,and then within each sub-group by quant;and

  4. counted the number of records in each subset,calculated the average reading in each,and chose a person and quant value from each(it doesn’t matter which ones,since they’re all equal).

Liczenie odczytów temperatury

Ile odczytów temperatury zanotował Franciszek Pabodie i jaka była ich średnia wartość?

Rozwiązanie

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

Uśrednianie z NULL

Średnia zbioru wartości jest sumą wartości podzieloną przez liczbę wartości.Czy to oznacza, że funkcja avg zwraca 2.0 lub 3.0, gdy ma wartości 1.0, null i 5.0?

Rozwiązanie

Odpowiedź brzmi 3.0.NULL nie jest wartością; jest brakiem wartości.Jako taka nie jest uwzględniana w obliczeniach.

Możesz to potwierdzić, wykonując następujący kod:

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

Co robi to zapytanie?

Chcemy obliczyć różnicę między każdym indywidualnym odczytem promieniowania a średnią wszystkich odczytów promieniowania.Piszemy zapytanie:

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

Co to właściwie daje i dlaczego?

Rozwiązanie

Zapytanie daje tylko jeden wiersz wyników, gdy tak naprawdę chcemy uzyskać wynik dla każdego z odczytów. Funkcja avg() daje tylko jedną wartość, a ponieważ jest uruchamiana jako pierwsza, tabela jest zredukowana do jednego wiersza.Wartość reading jest po prostu wartością arbitralną.

Aby osiągnąć to, czego chcieliśmy, musielibyśmy uruchomić dwa zapytania:

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

W ten sposób otrzymujemy wartość średnią (6.5625), którą możemy następnie wstawić do drugiego zapytania:

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

W ten sposób otrzymujemy to, co chcemy, ale możemy połączyć to w jedno zapytanie za pomocą podzapytań.

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

W ten sposób nie musimy wykonywać dwóch zapytań.

Podsumowując to, co zrobiliśmy, to zastąpienie avg(reading) przez (SELECT avg(reading) FROM Survey WHERE quant='rad') w oryginalnym zapytaniu.

Ordering When Concatenating

Funkcja group_concat(field, separator)konkatenuje wszystkie wartości w polu używając określonego znaku separatora (lub ’,’ jeśli separator nie jest określony).Użyj tego do utworzenia jednowierszowej listy nazwisk naukowców, takiej jak:

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

Czy możesz znaleźć sposób na uporządkowanie listy według nazwisk?

Kluczowe punkty

  • Używaj funkcji agregujących do łączenia wielu wartości.

  • Funkcje agregujące ignorują wartości null.

  • Agregacja następuje po filtrowaniu.

  • Użyj GROUP BY, aby połączyć podzbiory oddzielnie.

  • Jeśli dla pola nie określono funkcji agregacji, zapytanie może zwrócić dowolną wartość dla tego pola.

.

admin

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.

lg