Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: Sortowanie uczniów wg. ocen
Forum PHP.pl > Forum > Bazy danych > MySQL
Userr
Mam taką tabelę z polami: id INT, id_ucznia INT, imie_ucznia VARCHAR, oceny INT

Jej zawartość:
1, 1, Janek, 4
2, 3, Kasia, 5
3, 3, Kasia, 4
4, 2, Paweł, 3
5, 1, Janek, 4
6, 2, Paweł, 5
7, 2, Kasia, 5
8, 1, Janek, 2
9, 4, Robert, 3
10, 3, Kasia 4
11, 4, Robert, 4
12, 4, Robert, 4

Chciałbym, żeby wyświetlić uczniów w kolejności od najlepszych, do najsłabszych ocen. Z tym, że jeżeli jacyś uczniowie mają piątki, to liczy się liczba piątek (uczeń z większą liczbą piątek będzie przed uczniem z mniejszą liczbą piątek). Jeżeli okaże się, że jacyś uczniowie mają taką samą liczbą piątek, to liczy się liczba czwórek, itd. Chciałbym więc taką kolejność:

1. Kasia – 5, 5, 4, 4
2. Paweł – 5, 3
3. Robert – 4, 4, 3
4. Janek – 4, 4, 2

Jakim zapytaniem MySQL można by to było osiągnąć? Pewnie trzeba będzie wykorzystać GROUP BY i ORDER BY?
trueblue
Zakładam, że jednak nie liczy się liczba wyższych ocen, bo:
Uczeń1 5, 5, 4, 4
powinien być wyżej niż:
Uczeń2 5, 5, 5, 3, 3

Wydaje mi się, że zapytanie będzie więc takie:
  1. SELECT imie, GROUP_CONCAT(ocena ORDER BY ocena DESC)
  2. FROM ocena
  3. GROUP BY imie
  4. ORDER BY AVG(ocena) DESC

Userr
Cytat(trueblue @ 27.11.2016, 13:16:25 ) *
Zakładam, że jednak nie liczy się liczba wyższych ocen, bo:
Uczeń1 5, 5, 4, 4
powinien być wyżej niż:
Uczeń2 5, 5, 5, 3, 3


Masz rację. Średnia tych uczniów wyjdzie:

Uczeń1 (5 + 5 + 4 + 4) / 4 = 4.5
Uczeń2 (5 + 5 + 5 + 3 + 3) / 5 = 4.2

ale mi akurat w tym zapytaniu NIE zależy na sortowaniu wg. średniej ocen, tylko na tym, żeby liczyła się właśnie liczba wyższych ocen.
trueblue
Pierwsze takie:
  1. SELECT imie, GROUP_CONCAT(ocena ORDER BY ocena DESC)
  2. FROM ocena
  3. GROUP BY imie
  4. ORDER BY SUM(POWER(10,ocena)) DESC

Ale ma wadę, bo jeśli Uczeń1 ma tylko ocenę 2, a Uczeń2 ma jedenaście jedynek, to będzie wyżej. Czym wyższe oceny ma uczeń tym mniejsze prawdopodobieństwo takiego błędu. Można ewentualnie go zmniejszyć poprzez zwiększenie wykładnika, albo podstawy potęgi.

Drugie:
  1. SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC)
  2. FROM ocena AS o1
  3. JOIN (SELECT imie, MAX(ocena) AS maks FROM ocena GROUP BY imie) AS o2 ON o2.imie=o1.imie
  4. GROUP BY o1.imie
  5. ORDER BY maks*SUM(o1.ocena=o2.maks) DESC


P.S. Zamiast po imieniu, grupuj po id_ucznia.
Userr
Przetestowałem zapytanie grupując po id_ucznia (nie jestem pewny czy prawidłowo to zrobiłem):

  1. SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC)
  2. FROM ocena AS o1
  3. JOIN (SELECT imie, id_ucznia, MAX(ocena) AS maks FROM ocena GROUP BY id_ucznia) AS o2 ON o2.imie=o1.imie
  4. GROUP BY o1.id_ucznia
  5. ORDER BY maks*SUM(o1.ocena=o2.maks) DESC


na tabelce z takimi wierszami jak podałem wyżej w poście:

  1. CREATE TABLE IF NOT EXISTS `ocena` (
  2. `id` int(50) UNSIGNED NOT NULL,
  3. `id_ucznia` int(20) DEFAULT NULL,
  4. `imie` varchar(30) DEFAULT NULL,
  5. `ocena` int(20) DEFAULT NULL
  6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
  7.  
  8. INSERT INTO `ocena` (`id`, `id_ucznia`, `imie`, `ocena`) VALUES
  9. (1, 1, 'Janek', 4),
  10. (2, 3, 'Kasia', 5),
  11. (3, 3, 'Kasia', 4),
  12. (4, 2, 'Pawel', 3),
  13. (5, 1, 'Janek', 4),
  14. (6, 2, 'Pawel', 5),
  15. (7, 2, 'Kasia', 5),
  16. (8, 1, 'Janek', 2),
  17. (9, 4, 'Robert', 3),
  18. (10, 3, 'Kasia', 4),
  19. (11, 4, 'Robert', 4),
  20. (12, 4, 'Robert', 4);


i otrzymałem nie takie rezultaty jak trzeba:

Kasia 5,5,3
Janek 4,4,2
Robert 4,4,3
Kasia 5,4,4

Czy wynika to z tego, że źle użyłem grupowania po id_ucznia?
trueblue
JOIN też na id_ucznia.
Userr
Wstawiłem id_ucznia do GROUP BY w JOIN() oraz do "ON o2.id_ucznia=o1.id_ucznia":

  1. SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC)
  2. FROM ocena AS o1
  3. JOIN (SELECT imie, id_ucznia, MAX(ocena) AS maks FROM ocena GROUP BY id_ucznia) AS o2 ON o2.id_ucznia=o1.id_ucznia
  4. GROUP BY o1.id_ucznia
  5. ORDER BY maks*SUM(o1.ocena=o2.maks) DESC


ale niestety rezultat jest taki sam:

Kasia 5,5,3
Janek 4,4,2
Robert 4,4,3
Kasia 5,4,4


trueblue
Masz pomieszanych uczniów. Paweł i Kasia mają id=2. Albo to jest inna Kasia i powinna mieć np. id 5, albo powinna mieć id=3.

Ale jest też błąd w drugim zapytaniu.
Zmień warunek na:
  1. ORDER BY POW(10,maks)*SUM(o1.ocena=o2.maks) DESC,SUM(o1.ocena) DESC

powinno nie być problemów.


Jeśli będą problemy, to proponuję Ci wykorzystać pierwsze zapytanie (pierwsze z tych dwóch) i zwiększyć bazę z 10 na 100 lub 1000. Zmiana wykładnika tu jednak nie pomoże, ale zmiana podstawy tak.
W przypadku bazy 100, jeśli Uczeń1 ma jedną 2, to Uczeń2 musiałby mieć 110 jedynek aby być wyżej, co raczej nie jest możliwe. W przypadku podstawy 1000, musiałby mieć 1100.
Userr
Poprawiłem i póki co działa świetnie smile.gif Dziękuję za pomoc.

Rezultat jest jak trzeba:

Kasia 5,5,4,4
Pawel 5,3
Robert 4,4,3
Janek 4,4,2

Czy w MySQL jest sposób, żeby wyświetlić kolejne numery przy każdym pobranym wyniku? Chodzi mi o coś takiego:

1. Kasia 5,5,4,4
2. Pawel 5,3
3. Robert 4,4,3
4. Janek 4,4,2

Pierwsze co mi przychodzi na myśl, to że można by to było zrobić w PHP - przy wyświetlaniu wyników w pętli zrobić jakiś licznik i byłoby OK, ale czy w MySQL też można to osiągnąć?
trueblue
Podejrzewam, że najwygodniej w CSS (lista numerowana lub licznik).
Jeśli w MySQL, to:
  1. SELECT @numer:=@numer+1 AS numer, imie, ocena FROM ocena, (SELECT @numer:=0) AS n


P.S. Wydaje mi się, że w tym ostatnim rozwiązaniu (drugim z dwóch), też może być problem kiedy zdarzy się ktoś z dużą liczbą czwórek, taką, że: POW(10,5)*liczba_piatek<POW(10,4)*liczba_czworek (lub 4/3, 3/2, 2/1). W tym przypadku również podniesienie podstawy rozwiązuje w jakimś stopniu problem.

P.P.S. Twoja tabela ma niepoprawną strukturę. Powinna być rozbita na dwie: uczen, ocena. W tej chwili daną nadmiarową jest imię/dane osobowe ucznia w tabeli ocena.
mihmih
Cytat
Ale ma wadę, bo jeśli Uczeń1 ma tylko ocenę 2, a Uczeń2 ma jedenaście jedynek, to będzie wyżej. Czym wyższe oceny ma uczeń tym mniejsze prawdopodobieństwo takiego błędu. Można ewentualnie go zmniejszyć poprzez zwiększenie wykładnika, albo podstawy potęgi.



A nie prościej i bez ryzyka błędu taki ciąg cyfr zamienić na pole tekstowe i uszeregować DESC?
trueblue
Masz na myśli uszeregowanie właśnie według wyniku GROUP_CONCAT?
Jeśli tak, to nie, bo wtedy "5,3,3" będzie wyżej niż "5".
mihmih
no zgadza się, nie znałem tego założenia że sama piątka ma być lepsza niż 5,3,3. Przepraszam za zamieszanie.
trueblue
Ja też nie smile.gif
Ale zakładam, że analogicznie do "5,3" i "5,4" (wyższa "średnia" - wyżej).
Może jeszcze autor się wypowie.
mihmih
można też taki ciąg uzupełnić osemkami do 100 pozycji (więcej ocen raczej nikt nie uzbiera w roku) i będzie już lepiej. Pytanie tylko czy lepsze jest wynik 5,5 czy 5,5,5 smile.gif Jeśli to drugie to pierwszy warunek sortowania można dać ilość piątek, a potem dopiero ciąg znaków z uzupełnionymi ósemkami.

Ale w ogóle takie ORDER BY jest oryginalne smile.gif
trueblue
Cytat(mihmih @ 27.11.2016, 22:38:12 ) *
Pytanie tylko czy lepsze jest wynik 5,5 czy 5,5,5 smile.gif Jeśli to drugie to pierwszy warunek sortowania można dać ilość piątek [...]

To drugie. Ale może być również sytuacja 4,4 i 4,4,4. Być może więc pierwszy warunek sortowanie będzie podobny do mojego.
Userr
Cytat(trueblue @ 27.11.2016, 21:06:36 ) *
Podejrzewam, że najwygodniej w CSS (lista numerowana lub licznik).
Jeśli w MySQL, to:
  1. SELECT @numer:=@numer+1 AS numer, imie, ocena FROM ocena, (SELECT @numer:=0) AS n


P.S. Wydaje mi się, że w tym ostatnim rozwiązaniu (drugim z dwóch), też może być problem kiedy zdarzy się ktoś z dużą liczbą czwórek, taką, że: POW(10,5)*liczba_piatek<POW(10,4)*liczba_czworek (lub 4/3, 3/2, 2/1). W tym przypadku również podniesienie podstawy rozwiązuje w jakimś stopniu problem.

P.P.S. Twoja tabela ma niepoprawną strukturę. Powinna być rozbita na dwie: uczen, ocena. W tej chwili daną nadmiarową jest imię/dane osobowe ucznia w tabeli ocena.


Numerowanie zrobiłem z wykorzystaniem PHP - chodzi o to, że jak kilka osób będzie miało takie same oceny, to powinny mieć taką samą pozycję na liście, np:

1. Kasia 5,5,4,4
2. Pawel 5,3
3. Janek 4,4,3 [zmieniłem celowo 2 na 3, żeby Janek miał takie same oceny jak Robert]
3. Robert 4,4,3

Więc w PHP jak wyświetlałem w pętli foreach zawartość pobraną z bazy danych, to przy każdym obiegu pętli sprawdzałem, czy oceny z aktualnie pobranego wiersza są różne niż oceny z wiersza pobranego w poprzednim obiegu pętli. Jeżeli są różne, to zwiększałem licznik (bo to oznacza, że uczeń jest już niże na liście), jeżeli natomiast są takie same, to nie zwiększam bo uczniowie są na tym samym miejscu na liście (ponieważ mają identyczne oceny - np. Robert i Janek). I ten kod PHP jak widać działa dobrze, ale chciałbym z ciekawości zapytać w jaki sposób mógłbym połączyć ten kod:

  1. SELECT @numer:=@numer+1 AS numer, imie, ocena FROM ocena, (SELECT @numer:=0) AS n


z poniższym zapytaniem?

  1. SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC)
  2. FROM ocena AS o1
  3. JOIN (SELECT imie, id_ucznia, MAX(ocena) AS maks FROM ocena GROUP BY id_ucznia) AS o2 ON o2.id_ucznia=o1.id_ucznia
  4. GROUP BY o1.id_ucznia
  5. ORDER BY POW(10,maks)*SUM(o1.ocena=o2.maks) DESC,SUM(o1.ocena) DESC


Próbowałem to połączyć, wstawić ten kod w różne miejsca, ale nie wychodzi. Pierwszy raz stykam się ze zmiennymi w MySQL i wydaje się to ciekawą rzeczą.

5,5,5 jest lepsze od 5,5, ale co ciekawsze 5,3,3 jest lepsze od samego 5 smile.gif

Tak na prawdę mam tabelę z innymi danymi, ale na forum chciałem to przedstawić na przykładzie ocen szkolnych, żeby dobrze wyjaśnić o co mi chodzi, a okazało się, że trochę zagmatwałem, bo średnią ocen liczy się przecież zupełnie inaczej.
Co do nadmiarowości danych - w prawdziwej tabeli nie mam tego, ale tutaj dla przykładu robiłem tabelę trochę "na szybko" i stąd nadmiarowość.

Wiec w mojej prawdziwej tabeli będę miał dużo więcej danych tego typu, np:
5,5,5,4,4,4,4,3,3,3,3,3,3,2,2,2,2,2,2,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
trueblue
Cytat(Userr @ 28.11.2016, 12:18:09 ) *
ale chciałbym z ciekawości zapytać w jaki sposób mógłbym połączyć ten kod:

  1. SELECT @numer:=@numer+1 AS numer, imie, ocena FROM ocena, (SELECT @numer:=0) AS n


z poniższym zapytaniem?

  1. SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC)
  2. FROM ocena AS o1
  3. JOIN (SELECT imie, id_ucznia, MAX(ocena) AS maks FROM ocena GROUP BY id_ucznia) AS o2 ON o2.id_ucznia=o1.id_ucznia
  4. GROUP BY o1.id_ucznia
  5. ORDER BY POW(10,maks)*SUM(o1.ocena=o2.maks) DESC,SUM(o1.ocena) DESC

Wstawiasz @numer:=@numer+1 AS numer na listę pól w SELECT, a (SELECT @numer:=0) AS n na listę we FROM głównego zapytania.

Cytat(Userr @ 28.11.2016, 12:18:09 ) *
5,5,5 jest lepsze od 5,5, ale co ciekawsze 5,3,3 jest lepsze od samego 5 smile.gif

To w takim razie moje zapytanie chyba będzie błędne. Wystarczy więc ORDER BY alias_dla_GROUP_CONCAT, to co sugerował mihmih. Do sprawdzenia.
Userr
Dostawiłem kod związany ze zmienną @numer:

  1. SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC) AS oceny_uczniow, @numer:=@numer+1 AS numer
  2. FROM ocena AS o1, (SELECT @numer:=0) AS n
  3. JOIN (SELECT imie, id_ucznia, MAX(ocena) AS maks FROM ocena GROUP BY id_ucznia) AS o2 ON o2.id_ucznia=o1.id_ucznia
  4. GROUP BY o1.id_ucznia
  5. ORDER BY oceny_uczniow DESC


ale niestety po jego wstawieniu pojawia się błąd:

#1054 - Unknown column 'o1.id_ucznia' in 'on clause'
trueblue
Musisz owinąć wszystkie tabele w klauzuli FROM w ( ).
Ale i tak numeracja w ten sposób nie zadziała, bo jest ORDER BY.
Musiałbyś oryginalne zapytanie wsadzić w jeszcze jeden zewnętrzny SELECT jako podzapytanie. W głównym zapytaniu nadać numerację.
Numerację można również zwiększać jeśli poprzednia wartość jest inna niż aktualna (trzeba wprowadzić dodatkową zmienną i IF).
Userr
Czy byłby sens zapisać wyniki tego selecta w czymś w rodzaju "pamięci cache"?

Chodzi o to, że z czasem ta tabela z ocenami będzie miała bardzo dużo wierszy. Będę chciał zrobić np. takie rzeczy:

- wyświetlić listę uczniów posortowaną wg. najlepszych ocen (wg. tych kryteriów, które podawałem wcześniej) z danego dnia roku (każdy dzień będzie miał swoją posortowaną listę na oddzielnej podstronie)
- po kliknięciu w imię ucznia będzie pokazane, które zajął pozycję w tych dziennych listach

Tak sobie pomyślałem, że jeżeli ta tabela z ocenami z czasem będzie miała milion i więcej wierszy, to takie zapytania mogą być bardzo obciążające dla serwera? A skoro te uporządkowane listy będę tworzone tylko raz na dzień, to może lepiej byłoby gdzieś je zapisać?

Tylko jak to zrobić? Czy dobrym rozwiązaniem byłyby widoki?

Czy widoków może być dowolna liczba w bazie (zdaje się, że liczba tabel w bazie jest ograniczona, stąd moje pytanie jak to będzie z widokami, skoro po roku zrobi się ich ponad 350 - jeden widok dla jednego dnia)?
viking
Mógłby pomóc na normalnym SBD ale mysql nie posiada widoków zmaterializowanych. Musisz używać kombinacji z triggerami http://www.materialized.info/ i tabelami tymczasowymi. Nie ma limitów http://dev.mysql.com/doc/refman/5.5/en/dat...ount-limit.html
Userr
Cytat(trueblue @ 29.11.2016, 12:59:17 ) *
Musisz owinąć wszystkie tabele w klauzuli FROM w ( ).
Ale i tak numeracja w ten sposób nie zadziała, bo jest ORDER BY.
Musiałbyś oryginalne zapytanie wsadzić w jeszcze jeden zewnętrzny SELECT jako podzapytanie. W głównym zapytaniu nadać numerację.
Numerację można również zwiększać jeśli poprzednia wartość jest inna niż aktualna (trzeba wprowadzić dodatkową zmienną i IF).


Udało mi się stworzyć numerację:

  1. SET @numer=0;
  2. SELECT @numer:=@numer+1 AS numer, lista_uczniow_wg_ocen.imie, lista_uczniow_wg_ocen.oceny_uczniow
  3. FROM (SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC) AS oceny_uczniow
  4. FROM ocena AS o1
  5. JOIN (SELECT imie, id_ucznia, MAX(ocena) AS maks FROM ocena GROUP BY id_ucznia) AS o2 ON o2.id_ucznia=o1.id_ucznia
  6. GROUP BY o1.id_ucznia
  7. ORDER BY oceny_uczniow DESC) AS lista_uczniow_wg_ocen


ale rezultat jest nie do końca taki jaki chciałem:

1 Kasia 5,5,4,4
2 Pawel 5,3
3 Janek 4,4,3
4 Robert 4,4,3

ponieważ Janek i Robert powinni być równo na 3 miejscu (ponieważ mają identyczne oceny), a kolejny uczeń powinien być na 5 miejscu. Rozumiem, że mogłaby się tutaj przydać dodatkowa zmienna i IF o którym wspominałeś? Tylko w którym miejscu to wstawić i jak wykryć w MySQL poprzednią wartość (wartość z poprzedniego wiersza)?
trueblue
Jedna zmienna i jeden IF wystarczy gdybyś chciał mieć numerację: 1,2,3,3,4.
Jeśli chcesz mieć: 1,2,3,3,5, to musi być coś takiego:

SET @numer=0;
SET @poprzednie_oceny=0;
SET @poprzedni_numer=0;
SELECT @numer:=@numer+1 AS numer, imie, oceny_uczniow,if(concat(@poprzednie_oceny,'')<>oceny_uczniow,@poprzedni_numer:=@numer,@poprzedni_numer:=@poprzedni_numer),if
(concat(@poprzednie_oceny,'')<>oceny_uczniow,@poprzednie_oceny:=oceny_uczniow, @poprzednie_oceny:=concat(@poprzednie_oceny,'')),@poprzedni_numer


CONCAT jest tam dla castowania zmiennej do stringu, nie wiem dlaczego bez tego zmienna przyjmuje pierwszą wartość ze sklejonego ciągu (z "5,5,1,1,1" przyjmie "5").
To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.
Invision Power Board © 2001-2024 Invision Power Services, Inc.