AVG() in Subquery limitieren

Einklappen
X
 
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • AVG() in Subquery limitieren

    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:

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

    Code:
    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
    Zuletzt geändert von Crake; 28.06.2011, 05:22.
    [COLOR=red]Gesellschaftsforum.net[/COLOR] - Projekt zur Wiederbelebung der Diskussionskultur im Internet
    [COLOR=orange]1st News[/COLOR] - Das Newsletterscript für den professionellen Einsatz

  • #2
    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.

    Code:
    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
    Code:
    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
    [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
    Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
    Super, danke!
    [/COLOR]

    Kommentar


    • #3
      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?
      [COLOR=red]Gesellschaftsforum.net[/COLOR] - Projekt zur Wiederbelebung der Diskussionskultur im Internet
      [COLOR=orange]1st News[/COLOR] - Das Newsletterscript für den professionellen Einsatz

      Kommentar


      • #4
        Zitat von Crake Beitrag anzeigen
        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.
        Zuletzt geändert von AmicaNoctis; 28.06.2011, 15:37.
        [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
        Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
        Super, danke!
        [/COLOR]

        Kommentar

        Lädt...
        X