# [MySQL] Jeden Xten Datensatz einer Gruppierung



## Blue Effect (9. November 2009)

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: 

```
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.


----------



## Yaslaw (9. November 2009)

Also, zuerst sortieren wir die Daten mal richtig

```
SELECT *
FROM aktionen_staende
ORDER BY rally, user, hoehe
```

Dann suchen wir mal wie wir eine Zeilennummerierung reinbringen.

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

```
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

```
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*


----------



## Blue Effect (9. November 2009)

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: 

```
HAVING
	res.user = 175
	AND res.rang = 3
```
?

Da kommt bei mir 
	
	
	



```
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
```


----------



## Yaslaw (9. November 2009)

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)

```
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


```
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*


----------



## Blue Effect (9. November 2009)

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.


----------



## Yaslaw (10. November 2009)

So, mit SQL alleine hab ichs nicht hingekriegt. Hier währe meine PHP-Lösung:

```
<?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.


----------



## Blue Effect (10. November 2009)

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?


----------



## Yaslaw (10. November 2009)

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?


----------



## Blue Effect (10. November 2009)

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)


Ah danke. 



yaslaw hat gesagt.:


> Un mit welchen Werten für Rang und User hast du getestet?



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

```
//Zeile 155:
$filteresRanks = array_filter($this->ranks, "filterRanksUser");
```


----------



## Yaslaw (10. November 2009)

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



$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;
	}	
}
?>
```


----------



## Blue Effect (10. November 2009)

Klasse, funktioniert. Vielen lieben Dank.  

Als nächstes muss ich dann noch alle Rallye-IDs herausbekommen, bei dem ein bestimmter User 1. bis 3. war. Mal schauen wie das funktionieren wird.


----------

