AVG() in Subquery limitieren

[Crake]

Das Problem ist ganz einfach erklärt:

AVG() soll nur auf die 3 höchsten Werte einer Abfrage angewendet werden. Normalerweise greife ich dafür auf einen Subquery zurück. Das Problem ist nur, dass sich AVG() bereits innerhalb eines Subqueries befindet und daher eine Referenz auf die äußere Abfrage nicht mehr möglich ist.

#1054 - Unknown column 'b.teamid' in 'on clause'

Hier der entsprechende Query:

SELECT a .* , b.teamid, b.teamname, b.disq AS tdisq, COUNT( a.userID )AS players, (

SELECT AVG( top3 )AS team_avg
FROM (

SELECT t.avg AS top3
FROM`ws_cup_user` s
LEFT JOIN`ws_cup_results` t ON s.gameaccount = t.login
WHERE t.avg ISNOTNULL 
AND s.team = a.team
ORDER BY t.avg ASC 
LIMIT 0 , 3
) AS avg_sub) AS team_avg
FROM ws_cup_user a
LEFTJOIN ws_cup_teams b ON a.team = b.teamid
WHERE a.team >0
AND (

SELECT COUNT( w.avg ) 
FROM`ws_cup_user` v
LEFT JOIN`ws_cup_results` w ON v.gameaccount = w.login
WHERE v.team = b.teamid
AND w.avg ISNOTNULL 
ORDER BY w.avg ASC 
LIMIT 0 , 3
) >=3
GROUP BY a.team
ORDER BY b.teamname ASC 
LIMIT 0 , 15

Hier noch der Ausgangscode ohne Limitierung:

SELECT a .* , b.teamid, b.teamname, b.disq AS tdisq, COUNT( a.userID )AS players, (

SELECT AVG( y.avg ) 
FROM`ws_cup_user` x
LEFT JOIN`ws_cup_results` y ON x.gameaccount = y.login
WHERE x.team = b.teamid
AND y.avg ISNOTNULL 
ORDERBY y.avg ASC 
LIMIT 0 , 3
) AS team_avg
FROM ws_cup_user a
LEFT JOIN ws_cup_teams b ON a.team = b.teamid
WHERE a.team >0
AND (

SELECT COUNT( w.avg ) 
FROM`ws_cup_user` v
LEFT JOIN`ws_cup_results` w ON v.gameaccount = w.login
WHERE v.team = b.teamid
AND w.avg ISNOTNULL 
ORDER BY w.avg ASC 
LIMIT 0 , 3
) >=3
GROUP BY a.team
ORDER BY b.teamname ASC 
LIMIT 0 , 15

[AmicaNoctis]

Mehr als

#1064 - You have an error in your SQL syntax

dürfte da nicht passieren. Es gibt weder einen LEFTJOIN Operator noch einen der ISNOTNULL oder ORDERBY heißt. Da du mit einer dreistufigen SELECT-Hierarchie arbeitest, hättest du den Code zur besseren Lesbarkeit einrücken können.

SELECT
	a.*,
	b.teamid,
	b.teamname,
	b.disq AS tdisq,
	COUNT(a.userID) AS players,
	(
		SELECT AVG(top3) AS team_avg
		FROM (
			SELECT t.avg AS top3
			FROM `ws_cup_user` s
			LEFT JOIN `ws_cup_results` t ON s.gameaccount = t.login
			WHERE t.avg ISNOTNULL AND s.team = a.team
			ORDER BY t.avg ASC 
			LIMIT 0 , 3
		) AS avg_sub
	) AS team_avg
FROM ws_cup_user a
LEFTJOIN ws_cup_teams b ON a.team = b.teamid
WHERE a.team > 0
	AND (
		SELECT COUNT( w.avg ) 
		FROM`ws_cup_user` v
		LEFT JOIN`ws_cup_results` w ON v.gameaccount = w.login
		WHERE v.team = b.teamid AND w.avg ISNOTNULL 
		ORDER BY w.avg ASC 
		LIMIT 0 , 3
	) >=3
GROUP BY a.team
ORDER BY b.teamname ASC 
LIMIT 0 , 15

SELECT
	a.*,
	b.teamid,
	b.teamname,
	b.disq AS tdisq,
	COUNT(a.userID) AS players,
	(
		SELECT AVG(y.avg) 
		FROM `ws_cup_user` x
		LEFT JOIN `ws_cup_results` y ON x.gameaccount = y.login
		WHERE x.team = b.teamid AND y.avg ISNOTNULL 
		ORDERBY y.avg ASC 
		LIMIT 0 , 3
	) AS team_avg
FROM ws_cup_user a
LEFT JOIN ws_cup_teams b ON a.team = b.teamid
WHERE a.team >0
	AND (
		SELECT COUNT(w.avg) 
		FROM`ws_cup_user` v
		LEFT JOIN`ws_cup_results` w ON v.gameaccount = w.login
		WHERE v.team = b.teamid AND w.avg ISNOTNULL 
		ORDER BY w.avg ASC 
		LIMIT 0 , 3
	) >=3
GROUP BY a.team
ORDER BY b.teamname ASC 
LIMIT 0 , 15

[Crake]

Hmm, ursprünglich war der Code eingerückt nur gingen die Tabulatoren sowie einige Leerzeichen beim Kopieren verloren. Ich danke dir daher für diesen Aufwand, zu solch später Stunde war ich dazu dann auch nicht mehr bereit.

Irgendwann in der Nacht hatte ich dann womöglich eine Idee. Ich verfrachte die Durchschnittsberechnung einfach in einen klassischen Join mit Subquery zur Limitierung. Sollte womöglich funktionieren, oder?

[AmicaNoctis]

Ich verfrachte die Durchschnittsberechnung einfach in einen klassischen Join mit Subquery zur Limitierung. Sollte womöglich funktionieren, oder?

Ich denke schon. Wenn ich mich nicht irre, lässt sich jede Art von korrelierter Unterabfrage auch in eine unkorrelierte Unterabfrage mit Joins umschreiben, was schon aus Performancegründen sinnvoll ist.

Wie das genau aussehen muss, kann ich dir ad hoc nicht sagen, da ich zugeben muss, dass ich deine Abfrage nicht ganz durchschaue, weil mir die Struktur deiner Daten und deren Beziehungen zueinander unbekannt sind. Üblicherweise gehe ich so vor, dass ich erstmal einfache Abfragen mache und die dann Schrittweise ausbaue, bis ich das hab, was ich will.