[MySQL] Grupowanie i sortowanie przy warunku?

Nie dość, że nie założyłem tu konta, to jeszcze taka nazwa tematu… Przepraszam i proszę o jakieś zgrabne nazwanie.
Witam Cię drogi czytelniku, potencjalny wybawco.

Mam takie sobie zapytanie do bazy:

SELECT TeamName, count, teamrank FROM (
SELECT TeamName, COUNT(avg) AS count, SUM(avg)/COUNT(avg) AS teamrank
FROM players,rs_rank WHERE players.id=rs_rank.playerid
GROUP BY TeamName) as sub
 WHERE sub.count>=3
 ORDER BY sub.teamrank LIMIT 50

Wiem, że to niezbyt fachowe rzucać takimi hasłami, ale… Zapytanie liczy drużynowy ranking dla teamów, które mają w bazie sklasyfikowanych minimum 3 graczy. No, chyba że nie do końca je rozumiem.
Chciałbym, żeby liczyło ten ranking dla drużyn, które mają minimum trzech sklasyfikowanych osobników, ALE ranking był policzony tylko dla pierwszych trzech najlepszych wyników (tych z pola 'avg'). Dobry wynik, to niski wynik, więc wystarczy samo ORDER BY bez ASC, bo to chyba domyślnie, prawda?

Wynik tego query:

+--------------------------------+-------+------------------+
| TeamName                       | count | teamrank         |
+--------------------------------+-------+------------------+
| $000CR                         |     3 | 178666.666666667 |
| $00f〩$fff+ $f00oेsे      |    11 | 181258.636363636 |
| $000〠ĦěīЙΞĸēη〠     |     7 | 183809.714285714 |
| $F00ѕ$F30т$F60о$F90я$FC0м |    15 | 185466.733333333 |
| $FF6「$FC0тахі$FF6」     |     5 |           185887 |
| $f88~ΡеІісаиѕ~         |     4 |           190282 |
| $00d»ЯтА«                 |     4 |           191782 |
| $f0f.:$fffVrs$f0f:.            |     6 | 193487.166666667 |
| $7b0ήρ                       |    12 | 193568.333333333 |
| $F00FF$fff.$000X-Fi|$fff       |     6 |           193718 |
| $36fVøđΚα                  |     4 |           194769 |
| $ffeiaм。                    |    14 | 195684.928571429 |
| $fffLord $f00Team              |    11 | 195869.454545455 |
| $f00Ħ’$000я                |    10 |         196087.3 |
|                                |   155 |         196823.8 |
| $d00ης                       |    11 |           198331 |
| NULL                           |   215 | 199531.302325581 |
+--------------------------------+-------+------------------+

Sam nawias:

+--------------------------------+-------+-----------------+
| TeamName                       | count | teamrank        |
+--------------------------------+-------+-----------------+
| NULL                           |   215 | 199531.30232558 |
|                                |   155 |        196823.8 |
| $000〠ĦěīЙΞĸēη〠     |     7 | 183809.71428571 |
| $000CR                         |     3 | 178666.66666667 |
| $00d»ЯтА«                 |     4 |          191782 |
| $00f〩$fff+ $f00oेsे      |    11 | 181258.63636364 |
| $06fѝФχ                     |     2 |          185487 |
| $093в$ff0ž$36fκ             |     2 |          155282 |
| $36fVøđΚα                  |     4 |          194769 |
| $7b0ήρ                       |    12 | 193568.33333333 |
| $888*ฟのの*                |     2 |          198077 |
| $d00ης                       |    11 |          198331 |
| $F00FF$fff.$000X-Fi|$fff       |     6 |          193718 |
| $F00ѕ$F30т$F60о$F90я$FC0м |    15 | 185466.73333333 |
| $f00Ħ’$000я                |    10 |        196087.3 |
| $f0f.:$fffVrs$f0f:.            |     6 | 193487.16666667 |
| $f88~ΡеІісаиѕ~         |     4 |          190282 |
| $FF6「$FC0тахі$FF6」     |     5 |          185887 |
| $ffeiaм。                    |    14 | 195684.92857143 |
| $fffLord $f00Team              |    11 | 195869.45454545 |
+--------------------------------+-------+-----------------+

mysql> show tables;
+-----------------+
| Tables_in_aseco |
+-----------------+
| challenges      |
| match_details   |
| match_main      |
| players         |
| players_extra   |
| records         |
| rs_karma        |
| rs_rank         |
| rs_times        |
| teammembers     |
| teams           |
| votes           |
+-----------------+

mysql> describe players;
+------------+--------------+------+-----+---------------------+----------------+
| Field      | Type         | Null | Key | Default             | Extra          |
+------------+--------------+------+-----+---------------------+----------------+
| Id         | mediumint(9) | NO   | PRI | NULL                | auto_increment |
| Login      | varchar(50)  | NO   | UNI |                     |                |
| Game       | varchar(3)   | NO   | MUL |                     |                |
| NickName   | varchar(100) | NO   |     |                     |                |
| Nation     | varchar(3)   | NO   |     |                     |                |
| UpdatedAt  | datetime     | NO   |     | 0000-00-00 00:00:00 |                |
| Wins       | mediumint(9) | NO   |     | 0                   |                |
| TimePlayed | mediumint(9) | NO   |     | 0                   |                |
| TeamName   | char(60)     | YES  |     | NULL                |                |
+------------+--------------+------+-----+---------------------+----------------+

mysql> describe rs_rank;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| playerID | mediumint(9) | NO   | MUL | 0       |       |
| avg      | float        | NO   |     | 0       |       |
+----------+--------------+------+-----+---------+-------+

mysql> select teamname from players limit 10;
+----------------------------+
| teamname                   |
+----------------------------+
| NULL                       |
| $00f〩$fff+ $f00oेsे  |
| NULL                       |
| $000〠ĦěīЙΞĸēη〠 |
| NULL                       |
| $00f〩$fff+ $f00oेsे  |
| NULL                       |
|                            |
| NULL                       |
| NULL                       |
+----------------------------+

mysql> select avg from rs_rank limit 6;
+--------+
| avg    |
+--------+
| 195436 |
| 174000 |
| 200000 |
| 185026 |
| 200000 |
| 198974 |
+--------+

Nie znam MySQL'a, ale do tej pory jakoś sobie z nim radziłem. Wielu różnych wariantów/pomysłów już próbowałem. W razie czego tyle tych codes. Czegoś jeszcze potrzeba?

Przeczytałem trzy razy i stwierdziłem, że któryś z nas musi wrócić do szkoły... nauczyć się formułować myśli albo czytać ze zrozumieniem.

Potrzebny jest ranking drużyn, wyliczony ze średniej trzech najwyżej sklasyfikowanych zawodników. W rankingu mają być wzięte pod uwagę tylko drużyny z trzema lub większą ilością sklasyfikowanych zawodników.
Ranking graczy jest w 'avg'. Czym mniejsza wartość w avg, tym lepszy rank. Dlatego trzeba policzyć średnią z trzech najmniejszych tam wartości.
Nazwy drużyn są w 'TeamName'.
Do każdej, spełniającej warunki, drużyny trzeba przypisać policzone dla nich średnie i ustawić to w kolejności od najmniejszej do największej.

Dla lepszego zobrazowania:
Ktoś prowadzi statystykę wpadek polityków. Wygląda tak:

Nazwisko | Ilość wpadek | Partia
Kowalski | 1 | Wegetarianie
Niekowalski | 2 | Weganie
Takkowalski | 3 | Jarosze
Nowak | 2 | Weganie
Nienowak | 7 | Wegetarianie
Taknowak | 3 | Jarosze
Shrek | 3 | Stwory
E.T. | 2 | Stwory
ktośtam | 4 | inna
Piotrowski | 3 | Weganie

Pole "Ilość wpadek" zawiera, wyliczoną wcześniej, średnią wpadek posła na dzień w okresie jednego roku.

Wybieramy partie, które w statystyce mają minimum dwóch przedstawicieli:

• Wegetarianie
• Weganie
• Jarosze
• Stwory

Wybieramy po dwóch polityków z najmniejszą ilością jakichś tam "wpadek"

• Wegetarianie = Kowalski 1 + Nienowak 7
• Weganie = Niekowalski 2 + Nowak 2
• Jarosze = Takkowalski 3 + Taknowak 3
• Stwory = Shrek 3 + E.T. 2

Liczymy średnią:

• Wegetarianie = 4
• Weganie = 2
• Jarosze = 3
• Stwory = 2,5

Układamy od najmniejwpadkowych do tych przeciwnych:

1. Weganie = 2
2. Stwory = 2,5
3. Jarosze = 3
4. Wegetarianie = 4

No i analogicznie do tego drugiego przypadku. Coś nadal jest niezrozumiałe? Coś jeszcze z bazy powinienem Wam pokazać?

Sorry ale jeżeli wiesz dokładnie co zrobić i potrafisz to rozłożyć na czynniki pierwsze to dlaczego sobie sam nie napiszesz zapytania? Poradziłbyś sobie sam ale pewnie nie chce ci się uczyć SQL-a... z tego co pamiętam w regulaminie jest wyraźnie napisane, że nie tolerujemy na forum "lenistwa", więc powinienem w ogóle nie odpisywać.

Prawdopodobnie potrzebujesz coś takiego. Prawda, że proste? A teraz do książki od SQL bo więcej nie pomożemy.

SELECT sub.TeamName, SUM(sub.avg)/3 AS teamrank FROM (
SELECT TeamName, avg
FROM players,rs_rank 
WHERE players.id=rs_rank.playerid
GROUP BY TeamName, avg
HAVING COUNT(sub.avg)>=3
LIMIT 3) as sub
GROUP BY sub.TeamName
ORDER BY sub.teamrank LIMIT 50

Byłoby to proste, gdyby ten limit 3 brał pierwsze trzy wyniki dla każdego, zgrupowanego już po TeamName. On bierze 3 pierwsze z całości i stop. Nie poradziłem sobie sam przez jakieś 2 miesiące (no, codziennie przy tym nie siedziałem), więc zacząłem szukać pomocy wśród znajomych i teraz forum. SUM(sub.avg)/3 z Twojej propozycji byłoby dobrym rozwiązaniem, gdyby ten limit 3 rozwiązywał problem. Moim problemem jest skodzenie czegoś, co jakby z jednej listy wyników zrobi dwie i dopiero na nich zadziała. Na pewno wiem i umiem jeszcze bardzo malutko, nie zaprzeczam. Dam znać, jeśli kiedyś uda mi się to rozwiązać.
Jeśli tłumaczyć zatrzymanie służbowego samochodu (czy inne zdalaodkompowe sytuacje), żeby spisać sobie nowy pomysł do komóry, jako lenistwo, to ok - jestem leniwy. Jeśli nie potrafię czegoś rozwiązać, to na ogół nie daje mi to długo spokoju. Tym bardziej, że uważam, że wszystko da radę zrobić (dział: komputery). Dzięki za próbę rozwiązania.

Człowieku rusz głową od czego ją masz? Jeżeli znasz SQL i siedzisz nad tym 2 miechy to już dawno powinieneś na to wpaść.

Pomyślmy co chcesz uzyskać?

1. Chcesz uzyskać listę zespołów...

SELECT p.TeamName
FROM players p

2)...oraz ich teamrank, który jest średnią trzech najlepszych zawodników.
Zastanówmy się najpierw jak wyciągnąć 3 najlepszych zawodników:

SELECT r.avg FROM rs_rank r ORDER BY r.avg DESC LIMIT 3

Teraz jak wyciągnąć trzech najlepszych dla określonej drużyny, np. tej z id = 1

SELECT r.avg FROM rs_rank r WHERE r.idplayer = 1 ORDER BY r.avg DESC LIMIT 3

3)Teraz wystarczy to złączyć w całość (w podzapytaniu r.idplayer = 1 zamieniamy na r.idplayer = p.id

SELECT p.TeamName, r.avg
FROM players p
INNER JOIN rs_rank r ON r.playerid = p.id
WHERE r.avg IN (SELECT r.avg FROM rs_rank r WHERE r.idplayer = p.id ORDER BY r.avg DESC LIMIT 3)

Następnie zsumować i podzielić sumę tak aby wyliczyć teamrank:

SELECT p.TeamName, SUM(r.avg)/3
FROM players p
INNER JOIN rs_rank r ON r.playerid = p.id
WHERE r.avg IN (SELECT r.avg FROM rs_rank r WHERE r.idplayer = p.id ORDER BY r.avg DESC LIMIT 3)
GROUP BY p.TeamName

Prawda, że proste?

/edit: jeszcze jedna rzecz mi się przypomniała przez która uważam, że MySQL jest do d***. LIMIT nie można używać w podzapytaniach... aby to ominąć trzeba zrobić taki myk:

SELECT p.TeamName, SUM(r.avg)/3
FROM players p
INNER JOIN rs_rank r ON r.playerid = p.id
WHERE r.avg IN (SELECT X.* FROM (SELECT r.avg FROM rs_rank r WHERE r.idplayer = p.id ORDER BY r.avg DESC LIMIT 3) AS X)
GROUP BY p.TeamName

Nie sprawdzałem czy to działa bo nie mam MySQL ale powinno chodzić.

/edit2: jeszcze trzecia metoda chyba najwłaściwsza w tym wypadku CROSS APPLY zdaje się chodzi na MySQL. Dopisałem jeszcze warunek na ilość zawodników >= 3

SELECT p.TeamName, SUM(t.avg)/3
FROM players p
CROSS APPLY (SELECT r.avg, r.idplayer FROM rs_rank r WHERE r.idplayer = p.id ORDER BY r.avg DESC LIMIT 3) AS t
GROUP BY p.TeamName, t.idplayer
HAVING COUNT(t.idplayer) >= 3;

Na co dzień pracuję w MSSQL, więc nie gwarantuję, że to co napisałem zadziała na MySQL aczkolwiek powinno :)

Raz jeszcze dzięki za chęci, ale chyba muszę pozostać przy swoim brzydkim query, czyli select dla każdego teamu z osobna; z tego wszystkiego (selecty w nawiasie połączone za pomocą UNION) select teamname i średniej drużynowej + ORDER. No i tyle… Wiem, że to karygodne, ale chociaż działa ; )

Napisałem że nie znam SQL, ale do tej pory radziłem sobie z MySQL-em. Chociażby takimi głupimi rozwiązaniami jak opisane wyżej.