Forum PHP.pl

AVG

Witam :-)

Pozwole sobie inaczej nazwac tabelki na potrzeby dydaktyczne - meczacy dzien i nie chce mi sie deszyfrowac co jest co u Ciebie ;-) A w koncu nie chciales rozwiazania na tacy ;-))

Jesli tabelki to:

1. uzytkownicy

id_uzytkownika
imie
nazwisko
...

2.oceny

id_oceny
id_uzytkownika
ocena INT/DECIMAL
...


To problem rozwiazesz np. tak :

1. Łączymy tabelki po id uzytkownika:

[SQL] pobierz, plaintext 
  SELECT * FROM  oceny o JOIN uzytkownicy u
                 ON (u.id_uzytkownika=o.id_uzytkownika)
 
[SQL] pobierz, plaintext 

W ten sposob, do kazdego wiersza z ocena podepnie sie odpowiedni user. Bedzie ladna, duza tabelka ;-)

2. Teraz, pobierz tylko te dwie kolumny (dlaczego tylko te - bedzie pozniej)

[SQL] pobierz, plaintext 
  SELECT o.id_uzytkownika,o.ocena  FROM  oceny o JOIN uzytkownicy u
                 ON (u.id_uzytkownika=o.id_uzytkownika)
[SQL] pobierz, plaintext 

3. Nastepnie, jak zasugerowal kolega - ocene traktujemy funkcja agregujaca AVG - ona obliczy srednia.

[SQL] pobierz, plaintext 
  SELECT o.id_uzytkownika,AVG(o.ocena) AS srednia  FROM  oceny o JOIN uzytkownicy u
                 ON (u.id_uzytkownika=o.id_uzytkownika)
 
[SQL] pobierz, plaintext 

Chwilowo, dostaniesz tylko jeden wiersz. Cos w rodzaju:

id_uzytkownika | srednia
123 4.34

AVG obliczylo srednia artymetyczna wszystkich ocen, a id_uzytkownika jest przypadkowe. UWAGA: jesli masz bardziej restrykcyjne ustawienia serwera MySQL lub pracujesz w PostgreSQL,
dostaniesz błąd. Zignoruj go chwilowo (pozniej wyjasnienie) - i przejdz do punktu 4 .

4. Zeby AVG zadzialalo na kazdym userze osobno , musisz wlaczyc grupowanie na kolumnie id_uzytkownika.

[SQL] pobierz, plaintext 
 SELECT o.id_uzytkownika,AVG(o.ocena) AS srednia  FROM  oceny o JOIN uzytkownicy u
                 ON (u.id_uzytkownika=o.id_uzytkownika)
                 GROUP BY o.id_uzytkownika
[SQL] pobierz, plaintext 

Dostaniesz cos w rodzaju:

id_uzytkownika | srednia
122 | 3.43
123 | 4.54
434 | 3.44
... .....

Czyli mamy numerki userow oraz ich srednie :-)

5. Teraz sortujemy malejaco wg sredniej:

[SQL] pobierz, plaintext 
 SELECT o.id_uzytkownika,AVG(o.ocena) AS srednia  FROM  oceny o JOIN uzytkownicy u
                 ON (u.id_uzytkownika=o.id_uzytkownika)
                 GROUP BY o.id_uzytkownika
                 ORDER BY srednia DESC
[SQL] pobierz, plaintext 

6. Wyciagamy pierwsze 3 rekordy:

[SQL] pobierz, plaintext 
SELECT o.id_uzytkownika,AVG(o.ocena) AS srednia  FROM  oceny o JOIN uzytkownicy u
                 ON (u.id_uzytkownika=o.id_uzytkownika)
                 GROUP BY o.id_uzytkownika
                 ORDER BY srednia DESC
                 LIMIT 3
[SQL] pobierz, plaintext 

7. I na koniec - wyciagamy imie i nazwisko :

[SQL] pobierz, plaintext 
 SELECT o.id_uzytkownika,AVG(o.ocena) AS srednia,u.imie,u.nazwisko  FROM  oceny o JOIN uzytkownicy u
                 ON (u.id_uzytkownika=o.id_uzytkownika)
                 GROUP BY o.id_uzytkownika
                 ORDER BY srednia DESC
                 LIMIT 3
[SQL] pobierz, plaintext 

Wazne: jesli twoj mysql bardziej sie trzyma standardow (lub dzialasz np. w postgresie), teraz bedziesz mial blad jak w punkcie 3.
Aby go rozwiazac, podaj na liscie grupowania kolumny imie i nazwisko:

[SQL] pobierz, plaintext 
 SELECT o.id_uzytkownika,AVG(o.ocena) AS srednia,u.imie,u.nazwisko  FROM  oceny o JOIN uzytkownicy u
                 ON (u.id_uzytkownika=o.id_uzytkownika)
                 GROUP BY o.id_uzytkownika, u.imie, u.nazwisko
                 ORDER BY srednia DESC
                 LIMIT 3
[SQL] pobierz, plaintext 

Czemu tak jest - troche zakrecone do wyjasnienia , a jest sobota ;-) Ale wystarczy ze zapamietasz, ze zgodnie ze standardem jak uzywasz f. agregujacych, to wyswietlane kolumny nie podlegajace agregacji
musisz potraktowac group by (heh heh - mysql zwykle tego nie wymusza, i latwo mozna nie zauwazyc bzdurnych wynikow w okreslonych sytuacjach).


Dodatkowo, jesli bedziesz czesto wyswietlal wyniki, zaloz index na kolumne id_uzytkownika w tabeli oceny (tj., na ich odpowiednik u Ciebie;-).



Pozdrowka serdeczne,
Kacper

p.s. a jesli masz chec sie doszkolic - zapraszam na szkolenie z MySQL+PostgreSQL (zaawansowany SQL, programowanie plpgsql, triggery, ograniczenia, wydajnosc). Zaprojektowane pod kątem programistow:-)
http://www.edusequence.com/szkolenie_mysql...ql/wprowadzenie