Sortowanie uczniów wg. ocen Opcje

[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?

Ten post edytował Userr 27.11.2016, 13:14:34

[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:

[SQL] pobierz, plaintext 
SELECT imie, GROUP_CONCAT(ocena ORDER BY ocena DESC)
FROM ocena
GROUP BY imie
ORDER BY AVG(ocena) DESC
[SQL] pobierz, plaintext 

[Userr]

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:

[SQL] pobierz, plaintext 
SELECT imie, GROUP_CONCAT(ocena ORDER BY ocena DESC)
FROM ocena
GROUP BY imie
ORDER BY SUM(POWER(10,ocena)) DESC
[SQL] pobierz, plaintext 

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:

[SQL] pobierz, plaintext 
SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC)
FROM ocena AS o1
JOIN (SELECT imie, MAX(ocena) AS maks FROM ocena GROUP BY imie) AS o2 ON o2.imie=o1.imie
GROUP BY o1.imie
ORDER BY maks*SUM(o1.ocena=o2.maks) DESC
[SQL] pobierz, plaintext 

P.S. Zamiast po imieniu, grupuj po id_ucznia.

Ten post edytował trueblue 27.11.2016, 14:01:24

[Userr]

Przetestowałem zapytanie grupując po id_ucznia (nie jestem pewny czy prawidłowo to zrobiłem):

[SQL] pobierz, plaintext 
SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC)
FROM ocena AS o1
JOIN (SELECT imie, id_ucznia, MAX(ocena) AS maks FROM ocena GROUP BY id_ucznia) AS o2 ON o2.imie=o1.imie
GROUP BY o1.id_ucznia
ORDER BY maks*SUM(o1.ocena=o2.maks) DESC
[SQL] pobierz, plaintext 

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

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `ocena` (
`id` int(50) UNSIGNED NOT NULL,
  `id_ucznia` int(20) DEFAULT NULL,
  `imie` varchar(30) DEFAULT NULL,
  `ocena` int(20) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
 
INSERT INTO `ocena` (`id`, `id_ucznia`, `imie`, `ocena`) VALUES
(1, 1, 'Janek', 4),
(2, 3, 'Kasia', 5),
(3, 3, 'Kasia', 4),
(4, 2, 'Pawel', 3),
(5, 1, 'Janek', 4),
(6, 2, 'Pawel', 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);
[SQL] pobierz, plaintext 

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?

Ten post edytował Userr 27.11.2016, 17:17:42

[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":

[SQL] pobierz, plaintext 
SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC)
FROM ocena AS o1
JOIN (SELECT imie, id_ucznia, MAX(ocena) AS maks FROM ocena GROUP BY id_ucznia) AS o2 ON o2.id_ucznia=o1.id_ucznia
GROUP BY o1.id_ucznia
ORDER BY maks*SUM(o1.ocena=o2.maks) DESC
[SQL] pobierz, plaintext 

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:

[SQL] pobierz, plaintext 
ORDER BY POW(10,maks)*SUM(o1.ocena=o2.maks) DESC,SUM(o1.ocena) DESC
[SQL] pobierz, plaintext 

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.

Ten post edytował trueblue 27.11.2016, 19:21:01

[Userr]

Poprawiłem i póki co działa świetnie (IMG:style_emoticons/default/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ąć?

Ten post edytował Userr 27.11.2016, 21:02:54

[trueblue]

Podejrzewam, że najwygodniej w CSS (lista numerowana lub licznik).
Jeśli w MySQL, to:

[SQL] pobierz, plaintext 
SELECT @numer:=@numer+1 AS numer, imie, ocena FROM ocena, (SELECT @numer:=0) AS n
[SQL] pobierz, plaintext 

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.

Ten post edytował trueblue 27.11.2016, 21:15:32

[mihmih]

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 (IMG:style_emoticons/default/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 (IMG:style_emoticons/default/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 (IMG:style_emoticons/default/smile.gif)

[trueblue]

Pytanie tylko czy lepsze jest wynik 5,5 czy 5,5,5 (IMG:style_emoticons/default/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]

Podejrzewam, że najwygodniej w CSS (lista numerowana lub licznik).
Jeśli w MySQL, to:

[SQL] pobierz, plaintext 
SELECT @numer:=@numer+1 AS numer, imie, ocena FROM ocena, (SELECT @numer:=0) AS n
[SQL] pobierz, plaintext 

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:

[SQL] pobierz, plaintext 
SELECT @numer:=@numer+1 AS numer, imie, ocena FROM ocena, (SELECT @numer:=0) AS n
[SQL] pobierz, plaintext 

z poniższym zapytaniem?

[SQL] pobierz, plaintext 
SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC)
FROM ocena AS o1
JOIN (SELECT imie, id_ucznia, MAX(ocena) AS maks FROM ocena GROUP BY id_ucznia) AS o2 ON o2.id_ucznia=o1.id_ucznia
GROUP BY o1.id_ucznia
ORDER BY POW(10,maks)*SUM(o1.ocena=o2.maks) DESC,SUM(o1.ocena) DESC
[SQL] pobierz, plaintext 

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 (IMG:style_emoticons/default/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

Ten post edytował Userr 28.11.2016, 12:19:39

[trueblue]

ale chciałbym z ciekawości zapytać w jaki sposób mógłbym połączyć ten kod:

[SQL] pobierz, plaintext 
SELECT @numer:=@numer+1 AS numer, imie, ocena FROM ocena, (SELECT @numer:=0) AS n
[SQL] pobierz, plaintext 

z poniższym zapytaniem?

[SQL] pobierz, plaintext 
SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC)
FROM ocena AS o1
JOIN (SELECT imie, id_ucznia, MAX(ocena) AS maks FROM ocena GROUP BY id_ucznia) AS o2 ON o2.id_ucznia=o1.id_ucznia
GROUP BY o1.id_ucznia
ORDER BY POW(10,maks)*SUM(o1.ocena=o2.maks) DESC,SUM(o1.ocena) DESC
[SQL] pobierz, plaintext 

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.

5,5,5 jest lepsze od 5,5, ale co ciekawsze 5,3,3 jest lepsze od samego 5 (IMG:style_emoticons/default/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.

Ten post edytował trueblue 28.11.2016, 12:25:03

[Userr]

Dostawiłem kod związany ze zmienną @numer:

[SQL] pobierz, plaintext 
SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC) AS oceny_uczniow, @numer:=@numer+1 AS numer
FROM ocena AS o1, (SELECT @numer:=0) AS n
JOIN (SELECT imie, id_ucznia, MAX(ocena) AS maks FROM ocena GROUP BY id_ucznia) AS o2 ON o2.id_ucznia=o1.id_ucznia
GROUP BY o1.id_ucznia
ORDER BY oceny_uczniow DESC
[SQL] pobierz, plaintext 

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)?

Ten post edytował Userr 29.11.2016, 14:31:15

[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]

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ę:

[SQL] pobierz, plaintext 
SET @numer=0;
SELECT @numer:=@numer+1 AS numer, lista_uczniow_wg_ocen.imie, lista_uczniow_wg_ocen.oceny_uczniow
FROM (SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC) AS oceny_uczniow
FROM ocena AS o1
JOIN (SELECT imie, id_ucznia, MAX(ocena) AS maks FROM ocena GROUP BY id_ucznia) AS o2 ON o2.id_ucznia=o1.id_ucznia
GROUP BY o1.id_ucznia
ORDER BY oceny_uczniow DESC) AS lista_uczniow_wg_ocen
[SQL] pobierz, plaintext 

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)?

Ten post edytował Userr 29.11.2016, 15:22:50

[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").