[Erledigt] Nummeriertes Result

[amlor]

Hi Folks,

ich habe folgendes Problem. Ich habe ein Query, welches mir ein Ranking berechnet. Das Query besteht aus 3 Joins und einigen Funktionen (Sum und Count). Die Top 1-n auszugeben ist jetzt kein Problem, nur würde ich halt auch gerne wissen, welche Position ein bestimmter User hat, dessen Rank > n ist..
Der Denkansatz ist, dass die DB ja eh irgendwie eine Art Index erstellen muss - ich könnte ja mit Limit genau auf bestimmte Positionen zugreifen.. aber wie kann ich diese Position(en) auch ausgeben ???
Für Hilfe und Hinweise wäre ich sehr dankbar.

So long

Amlor

[Chriz]

User und Ranking wurde schon relativ oft behandelt. Schau bitte nochmal zuerst in der Suche danach!

[amlor]

Dann verlink doch bitte die entsprechenden Beiträge.. sorry - doof bin ich auch nicht und ich habe gesucht.. die Standardmöglichkeit #SELECT (@num := @num + 1) rank' funktioniert hier ja eben nicht.. in meinem Post habe ich ja geschrieben :

Der Denkansatz ist, dass die DB ja eh irgendwie eine Art Index erstellen muss - ich könnte ja mit Limit genau auf bestimmte Positionen zugreifen..

Ich brauche also keine fortlaufende Nummerierung bei einer Ausgabe, sondern die Position innerhalb eines Querys..
Genauer:

SELECT
USERS.uid AS user_id, 
USERS.username AS user_name, 
COUNT(DISTINCT(VOTES.vote)) AS count_value,
SUM(HP.points) AS hp_value, 
SUM(HP.gametime) AS gametime, 
COUNT(HP.relation) AS game_played
FROM 
fe_users USERS    
LEFT JOIN
user_votes VOTES
ON
VOTES.user_id = USERS.uid
LEFT JOIN
user_healthpoint HP
ON
HP.user_id = USERS.uid
GROUP BY
USERS.uid
ORDER BY
count_value
DESC,
hp_value
DESC,
gametime
DESC,
game_played
ASC

wenn ich jetzt ein WHERE USER.uid = 12 mache, will ich die Position haben.. bei dem Ansatz, welchen ich (auch hier.. und bei Google) gefunden habe, würde er mir dann aber nur immer eine '1' als Rank liefern.

[Chriz]

Dann verlink doch bitte die entsprechenden Beiträge.

Warum, ich müsste sie doch auch suchen.


Sagen wir du hast schon einen Query (#), der in etwa das hier liefert:
user_id | points
1 | 1000
2 | 999
3 | 500
4 | 250
5 | 200
6 | 199
7 | 100

Jetzt kannst du doch einfach ein
SELECT COUNT(*) FROM (#) AS q WHERE q.points < (SELECT p.points FROM (#) AS p WHERE p.user_id = 5 LIMIT 1)

Wäre zumindest mein Ansatz.

[amlor]

In der Art hatte ich schon mal einen Ansatz aufgebaut.. das Problem ist dabei, dass ich 4 Sachen überprüfen muss also : count_value, hp_value, gametime und game_played... leider hatte das nie die wirkliche Position zurück gegeben.. vor allem, weil ich dann ja 5 Sub-Querys brauche.. da auf die DB mal ein paar mehr Leute losgelassen werden soll, hab ich da ein wenig Angst um die Auslastung von Speicher und CPU.. aber ich werde morgen noch mal in Ruhe ein entsprechendes Query aufbauen..
Ich ging aber davon aus, dass man die 'interne Positionierung' irgendwie 'sichtbar' machen könnte.. wie gesagt, das LIMIT zeigt ja, dass es eine entsprechende Verwaltung geben muss.. trotzdem danke für den Ansatz.. bestärkt mich, doch noch mal in die Richtung zu arbeiten.

[thomas_w]

Noch ein Vorschlag zum Ranking, sofern ich das Problem korrekt verstanden habe und hoffe im SQL ist kein Tippfehler, da ich hier keine Testdaten habe.

SELECT ranking.rank
FROM   (SELECT users.uid                     AS user_id,
               users.username                AS user_name,
               COUNT(DISTINCT( votes.vote )) AS count_value,
               SUM(hp.points)                AS hp_value,
               SUM(hp.gametime)              AS gametime,
               COUNT(hp.relation)            AS game_played,
               @rank := @rank + 1            AS rank
        FROM   (SELECT @rank := 0 AS rank) init,
               fe_users users
               LEFT JOIN user_votes votes
                 ON votes.user_id = users.uid
               LEFT JOIN user_healthpoint hp
                 ON hp.user_id = users.uid
        GROUP  BY users.uid
        ORDER  BY count_value DESC,
                  hp_value DESC,
                  gametime DESC,
                  game_played ASC) AS ranking
WHERE  ranking.user_id = 12;  

+------+
| rank |
+------+
|    3 |
+------+
1 row in set (0.02 sec)

Die Abfrage liefert den Rank des Users 12.

Grüße
Thomas

[nikosch]

Ganz schön kurzes Ergebnis für die lange Query

[amlor]

Hmm, leider liefert das bei mir auch wieder falsche Ergebnisse.. mein Query liefert folgendes Result:

user_id 	user_name 	count_value 	hp_value 	gametime 	game_played 
30	fry	4	NULL	NULL	0
36	Amy	2	8192	334	10
31	Leela	1	NULL	NULL	0
25	blitzbat	1	NULL	NULL	0
32	Bender	0	820	150	1
33	Farnsworth	0	NULL	NULL	0
27	monsterscherge	0	NULL	NULL	0
26	Amlor	0	NULL	NULL	0
35	Hermes	0	NULL	NULL	0
29	testuser	0	NULL	NULL	0
34	Zoidberg	0	NULL	NULL	0

Führe ich Dein Query für 36 Amy aus, bekomme ich Rank 15, bei Fy (30) bekomme ich 6...
Aber werde ich mir auf jeden Fall Morgen mal genauer angucken.. jetzt ist es definitiv zu spät

[thomas_w]

user_id 	user_name 	count_value 	hp_value 	gametime 	game_played 
30	fry	4	NULL	NULL	0
36	Amy	2	8192	334	10
31	Leela	1	NULL	NULL	0
25	blitzbat	1	NULL	NULL	0
32	Bender	0	820	150	1
33	Farnsworth	0	NULL	NULL	0
27	monsterscherge	0	NULL	NULL	0
26	Amlor	0	NULL	NULL	0
35	Hermes	0	NULL	NULL	0
29	testuser	0	NULL	NULL	0
34	Zoidberg	0	NULL	NULL	0

Wenn ich den Result richtig verstehe, ist er "nur" nach der 3. Spalte "count_value" sortiert. In Deinem SQL-Beispiel, dass ich als Grundlage verwendet habe, sind eine Menge mehr ORDER BY Spalten angegeben. Damit kommt natürlich auch ein anderes Ergebnis beim "rank" raus. Das Beste wäre, Du würdest ein kleines Testset an CREATE TABLE und Testdaten erstellen. Dann können alle mit den selben Daten testen.

Grüße
Thomas

[amlor]

In dem Fall ja, weil in den anderen Spalten/Tabellen noch nicht genug andere Informationen standen.. aber gerne stell ich hier mal Struktur und Daten zur Verfügung

CREATE TABLE IF NOT EXISTS `fe_users` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=10 ;
INSERT INTO `fe_users` (`uid`, `username`) VALUES
(1, 'fry'),
(2, 'leela'),
(3, 'bender'),
(4, 'farnsworth'),
(5, 'hermes'),
(6, 'amy'),
(7, 'zoidberg'),
(8, 'amlor'),
(9, 'blitzbat');
CREATE TABLE IF NOT EXISTS `user_healthpoint` (
  `user_id` int(10) unsigned NOT NULL,
  `points` int(10) unsigned NOT NULL,
  `gametime` int(10) unsigned NOT NULL,
  `relation` int(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `user_healthpoint` (`user_id`, `points`, `gametime`, `relation`) VALUES
(1, 1200, 64, 1),
(1, 960, 52, 1),
(1, 210, 50, 3),
(2, 2100, 120, 1),
(2, 580, 57, 2),
(3, 1100, 48, 1),
(5, 1500, 200, 2),
(9, 2370, 50, 1);
CREATE TABLE IF NOT EXISTS `user_votes` (
  `user_id` int(10) unsigned NOT NULL,
  `vote` int(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `user_votes` (`user_id`, `vote`) VALUES
(2, 4),
(2, 3),
(5, 1),
(6, 1),
(6, 2),
(1, 6),
(9, 1);

Das Result sieht entsprechend so aus:

user_id 	user_name 	count_value 	hp_value 	gametime 	game_played 
2	leela	2	5360	354	4
6	amy	2	NULL	NULL	0
1	fry	1	2370	166	3
9	blitzbat	1	2370	50	1
5	hermes	1	1500	200	1
3	bender	0	1100	48	1
4	farnsworth	0	NULL	NULL	0
7	zoidberg	0	NULL	NULL	0
8	amlor	0	NULL	NULL	0

Hier sieht man auch, dass jetzt auch weiter sortiert wird - wobei sich hier noch ein DESC in ein ASC wandeln muss - das soll hier aber nicht interessieren. Mit dem oberen Query von 'thomas_w' bekomme ich bei der User-Id 5 (hermes) ein Rank 20 raus.

[thomas_w]

Das Result sieht entsprechend so aus:

user_id 	user_name 	count_value 	hp_value 	gametime 	game_played 
2	leela	2	5360	354	4
6	amy	2	NULL	NULL	0
1	fry	1	2370	166	3
9	blitzbat	1	2370	50	1
5	hermes	1	1500	200	1
3	bender	0	1100	48	1
4	farnsworth	0	NULL	NULL	0
7	zoidberg	0	NULL	NULL	0
8	amlor	0	NULL	NULL	0

Wie sieht die Abfrage aus, mit der Du zu diesem Ergebnis kommst?

Grüße
Thomas

[amlor]

Das ist die aus meinem obrigem Posting:

SELECT
USERS.uid AS user_id, 
USERS.username AS user_name, 
COUNT(DISTINCT(VOTES.vote)) AS count_value,
SUM(HP.points) AS hp_value, 
SUM(HP.gametime) AS gametime, 
COUNT(HP.relation) AS game_played
FROM 
fe_users USERS    
LEFT JOIN
user_votes VOTES
ON
VOTES.user_id = USERS.uid
LEFT JOIN
user_healthpoint HP
ON
HP.user_id = USERS.uid
GROUP BY
USERS.uid
ORDER BY
count_value
DESC,
hp_value
DESC,
gametime
DESC,
game_played
ASC

[thomas_w]

Stimmt, wird bei mir auch falsch. Das muss ich in Ruhe testen. Wird etwas dauern.

Grüße
Thomas

[amlor]

Kein Problem.. vielen Dank überhaupt für die Hilfe.. was mir aufgefallen ist (hab mal das Query ein wenig zerlegt).. die Nummerierung, ist gar nicht so falsch.. nur wird halt Order By nach der Nummerierung (nach dem Select) durchgeführt.. das Rank orientiert sich somit nach der user-id...

[thomas_w]

Stimmt, das @rank wird nicht wie von mir erwartet hochgezählt. Mit anderen Beispielen hat dies aber so funktioniert.

Das es mit diesem Query nicht funktioniert, hängt wohl mit dem ORDER BY nach den Aggregat-Werten COUNT() etc. zusammen (Vermutung).

Ich teste mal...

Grüße
Thomas