[MySQL] Jeden Xten Datensatz einer Gruppierung

B

Blue Effect

Mitglied
Ich habe eine Tabelle mit diesen Spalten:
id rallye user hoehe
6 9 173 55911
5 9 1 537
7 9 175 22327
8 9 176 7534
9 10 177 9879
10 10 179 3074
11 10 180 4166
12 10 181 174
13 10 178 6212
14 10 182 3154
15 11 1 41
Jede ID ist einzigartig, eine Rallye hat x User, ein User hat x Rallyes aber jeder nur ein Mal.

Frage: Wie oft hat User 175 den dritten Platz gemacht? Der erste Platz ist der mit den höchsten Punkten, der zweite mit den zweitgrößten Punkten usw.

Mein Ansatz:
Code: 
SELECT * FROM `aktionen_staende` WHERE rallye=25 ORDER BY hoehe DESC LIMIT 2,1
Sucht schonmal den dritten Platz einer bestimmten Rallye raus. Aber ich brauche das zu jeder Rallye. Jetzt kann ich natürlich in PHP eine Schleife bauen, aber wie löse ich das performanter in einem sauberen Statement?

Danke für jeden Ansatz.
 
Also, zuerst sortieren wir die Daten mal richtig
Code: 
SELECT *
FROM aktionen_staende
ORDER BY rally, user, hoehe

Dann suchen wir mal wie wir eine Zeilennummerierung reinbringen.
Code: 
SELECT 
	@rownum := @rownum +1 AS rownum,
	s.*
FROM 
	(	SELECT *
		FROM aktionen_staende
		ORDER BY rally, user, hoehe DESC
	) s,
	(	SELECT @rownum :=0
	) r

Das ist natürlich noch nicht das was wir wollen. Aber du siehst schon mal die Grundtechnick. Wir definieren ein Select als Quelle mit einem referenzierten Feld @rownum. In jeder zeile wird diese @rownum um eins erhöht. und als Zeilennummer ausgegeben.

gut. Ersetzen wir mal @rownum durch @rang (wir wollen ja einen Rang) und basteln noch etwas mehr rein:
Code: 
SELECT
	@rang := IF(s.rally=@lastRally,@rang +1, 1) AS rank,
	@lastRally := s.rally AS lastRally,
	s.* 
FROM
	(	SELECT *
		FROM aktionen_staende
		ORDER BY rally, user, hoehe
	) s
	(SELECT @rang :=0, @lastRally := 0) r
Jetzt habe ich noch ein weiteres refernziertes Feld erstellt @lastRally. In diesem speichern wir immer die letzte rally.
Nun können wir mit einem IF definieren, wie sich @rang verhalten muss:
Ist die rally = @lastRally, müssen wir den Rang um eins erhöhen. bei einem Wechsel, müssen wir @rang zurücksetzen. Natürlich geht das nur richtig, weil wir im Subquery s die Reihenfolge der Daten so gesetzt haben.

So, nun das ganze nochmals einpacken um die Anzahl zu ermitteln und schon haben wirs. Die Einschränkungen in der HAVING-Sektion können beliebig ändern
Code: 
SELECT
	res.user,
	res.rang, 
	COUNT(*) AS anzahlRang
FROM
	(	SELECT
			@rang := IF(s.rally=@lastRally, @rang +1, 1) AS rank,
			@lastRally := s.rally AS lastRally,
			s.* 
		FROM
			(	SELECT *
				FROM aktionen_staende
				ORDER BY rally, user, hoehe
			) s
			(SELECT @rang :=0, @lastRally := 0) r
	) res
HAVING
	res.user = {$user}
	AND res.rang = {$rang}
GROUP BY
	res.user,
	res.rang

So sollte es eigentlich gehen - sofern ich keine Rechtschreibefehler reingepackt hab *g*
 
Wow, danke.
Über die IFs im Statement muss ich mir mal Tutorials durchlesen um die Syntax zu verstehen, vom Prinzip her habe ich es verstanden.

Im Having-Teil kann ich jetzt also sagen:
Code: 
HAVING
	res.user = 175
	AND res.rang = 3
?

Da kommt bei mir
Code: 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY res.user, res.rang' at line 20
 

Anhänge

Ui, da sind mir mehrere Fehler unterlaufen. rally anstelle von rallye, fehlendes Komma und falsche Sortierung.

Nichts desto trotz geht das SQL so nicht. Dank deinem angehängten Dump konnte ich es austesten.
MySQL mag kein Subquery neben dem Subquery mit den @-Definitionen.

Wenn du ein MySQL Server ab Version 5.1 hast, kann man das aber leicht austricksen mittels einer View.

Zuserst legen wir unsere sortierte View an (mittels SQL-Lasche in phpMyAdmin)
Code: 
 CREATE OR REPLACE VIEW v_aktionen_staende_sort AS
SELECT *
FROM aktionen_staende
ORDER BY rallye, hoehe

Nun habe ich in deinen Daten gesehen, dass es mehrere User auf dem gleichen Rang geben kann. Darum musste ich noch die hoehe merken und vergleichen. Im IF-Block wird nun folgendes gemacht:
->rallye ist gleich wie die letzte
-> hoehe ist gleich wie die letzte -> rang bleibt
-> hohe ist anders -> rang+1
->rallye ist anders -> rang = 1

Code: 
SELECT
	@rang := IF(s.rallye=@lastRallye, IF(s.hoehe=@lastHoehe, @rang, @rang +1), 1) AS rank,
	@lastRallye := s.rallye AS lastRallye,
	@lastHoehe := s.hoehe AS lastHoehe ,
	s.* 
FROM
        v_aktionen_staende_sort s,
	(SELECT @rang :=0, @lastRallye := 0, @lastHoehe := 0) r

So weit so gut. Doch jetzt weigert sich mein MySQL wieder dieses SQL als Unterabfrage zu gebrauchen. Also hab ich erst eine Liste mit den Rängen.
Diese Liste will er mir auch nicht als View anlegen *grummel*

Ich muss jetzt fort. Ich mach morgen weiter - die Aufgabe reizt mich *g*
 
Bei mir steht MySQL-Client-Version: 5.0.51a, dennoch konnte ich den View erstellen.
Wo sehe ich bei phpMyAdmin eigentlich neben den Daten des Views auch die entsprechende Abfrage, die hinter dem View steckt?

Also dass mehrere User bei EINER Rallye auf dem gleichen Rang sind, ist nicht beabsichtigt. Per Script kann ich das eigentlich auch ausschließen. :)
 
So, mit SQL alleine hab ichs nicht hingekriegt. Hier währe meine PHP-Lösung:
PHP: 
<?php

$ranks = new Ranks();
$result = $ranks->getCountRankPerUser(3, 175);

/**
 * Klasse mit Informationen über die Ränge
 */
class Ranks{
	private $ranks = array();
	private $rank;
	private $user;
	
	/**
	 * Construkter
	 */
	private function __construct(){
		$sql = "SELECT
					@rang := IF(s.rallye=@lastRallye, @rang +1, 1) AS rank,
					@lastRallye := s.rallye AS lastRallye,
					s.* 
				FROM
	    			v_aktionen_staende_sort s,
	    			(SELECT @rang:=0, @lastRallye:=0) vars";
		$recordset=mysql_query($sql) or exit;
	    while ($record=mysqli_fetch_object($recordset)){
	        array_push($this->ranks,$record);
	    }			
	}
	
	/**
	 * Ermittelt die Anzahl Rang per User
	 * @param int $rank
	 * @param int $user
	 * @return int
	 */
	public function getCountRankPerUser($rank, $user){
		$this->rank = $rank;
		$this->user = $user;
	    $filteresRanks = array_filter($this->ranks, "filterRanksUser");
	    return count($filteresRanks);		
	}

	/**
	 * Filter 
	 * @param object $record
	 * @return boolean
	 */
	private function filterRanksUser($record){
		return $record->user == $this->user && $record->rank == $this->rank;
	}	
}
?>

Man könnte es auch ohne Klasse machen, dann wird aber für jede Abfrage das ganze SQL ausgeführt. So kann man es immerhin cachen.
 
Hey Cool :)

Jetzt haben wir es fast, bekomme immer 0.
Bei mir läuft es leider noch nicht. Um nachzuvollziehen, wo es hängt, habe ich das Statement in $sql mal direkt ausgeführt und bekomme nur Zeilen bei denen rallye=10 ist. Ist das normal?
 
Wenn du es in phpMyAdmin ausführst, solltes du noch " LIMIT 0, 400" am Schluss dranhängen, damit du mehr als 30 Zeilen siehst (also 400 in diesem Beispiel)

Un mit welchen Werten für Rang und User hast du getestet?
 
yaslaw hat gesagt.:
Wenn du es in phpMyAdmin ausführst, solltes du noch " LIMIT 0, 400" am Schluss dranhängen, damit du mehr als 30 Zeilen siehst (also 400 in diesem Beispiel)
Zum Vergrößern anklicken....
Ah danke.

yaslaw hat gesagt.:
Un mit welchen Werten für Rang und User hast du getestet?
Zum Vergrößern anklicken....

User 175, Rank 2.

Übrigens: Ist die Sortierung im View nicht verkehrt? Es soll ja der mit dem höchsten Punktestand gewinnen. Habe es auf "ORDER BY rallye ASC, hoehe DESC" erfolgreich geändert, aber das Ergebnis bleibt dasselbe.

Ich habe das Script mal als Standalone ausprobiert.
Der Constructor muss auf jeden Fall public sein, oder?
Dann muss habe ich das mysqli zu mysql gemacht damit es einheitlich ist.
Jetzt bekomme ich trotzdem noch:
The second argument, 'filterRanksUser', should be a valid callback in /.../index.php on line 155
PHP: 
//Zeile 155:
$filteresRanks = array_filter($this->ranks, "filterRanksUser");
 
Zuletzt bearbeitet:
Mit dem DESC hast du recht. Hab meine View jetzt auch umgebaut.

Nachdem ich mein Erguss jetzt auch mal getestet habe, hab ich einige Fehler behoben und hier nun die getestete Version (liefert bei mir mit deinen geposteten Testdaten 4):

PHP: 
<?php



$ranks = new Ranks();
$result = $ranks->getCountRankPerUser(2, 175);
echo $result;

/**
 * Klasse mit Informationen über die Ränge
 */
class Ranks{
	private $ranks = array();
	private $rank;
	private $user;

	/**
	 * Construkter
	 */
	public function __construct(){
		$db  = new MySQLi('localhost','root','','test');
		$sql = "SELECT
					@rang := IF(s.rallye=@lastRallye, @rang +1, 1) AS rank,
					@lastRallye := s.rallye AS lastRallye,
					s.* 
				FROM
	    			v_aktionen_staende_sort s,
	    			(SELECT @rang:=0, @lastRallye:=0) vars";
		
		$recordset=$db->query($sql);
	    while ($record=mysqli_fetch_object($recordset)){
	        array_push($this->ranks,$record);
	    }
	    mysqli_free_result($recordset);			
	}
	
	/**
	 * Ermittelt die Anzahl Rang per User
	 * @param int $rank
	 * @param int $user
	 * @return int
	 */
	public function getCountRankPerUser($rank, $user){
		$this->rank = $rank;
		$this->user = $user;
	    $filteresRanks = array_filter($this->ranks, array($this,"filterRanksUser"));
	    return count($filteresRanks);		
	}

	/**
	 * Filter 
	 * @param object $record
	 * @return boolean
	 */
	private function filterRanksUser($record){
		return $record->user == $this->user && $record->rank == $this->rank;
	}	
}
?>
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück