Shorten queries with multiple joins-CodePudding

Is there a way to shorten this query? This query returns the result I want but I feel like this is to long. Are there tips to make an efficient query wilth miltiple joins?

SELECT home.team_id, home.name, ((home.hwins away.awins)*1.0/(home.hwins away.awins draw.nowin)) as winratio
FROM(
    SELECT m.home_team_api_id AS team_id, t.team_long_name AS name, COUNT(m.id) as hwins
    FROM match m
    LEFT JOIN team t
        ON m.home_team_api_id=t.team_api_id
    WHERE m.home_team_goal > m.away_team_goal
    GROUP BY m.home_team_api_id) AS home
LEFT JOIN(
    SELECT m.away_team_api_id AS team_id, t.team_long_name AS name, COUNT(m.id) as awins
    FROM match m
    LEFT JOIN team t
        ON m.away_team_api_id=t.team_api_id
    WHERE m.away_team_goal > m.home_team_goal
    GROUP BY m.away_team_api_id) AS away
    ON home.team_id=away.team_id
LEFT JOIN(
    SELECT m.away_team_api_id AS team_id, t.team_long_name AS name, COUNT(m.id) as nowin
    FROM match m
    LEFT JOIN team t
        ON m.away_team_api_id=t.team_api_id
    WHERE m.away_team_goal = m.home_team_goal
    GROUP BY m.away_team_api_id) AS draw
    ON home.team_id=away.team_id
GROUP BY home.team_id
ORDER BY winratio DESC
LIMIT 10;

The result:

team_id	name	winratio
8634	FC Barcelona	0.8897338403041825
8633	Real Madrid CF	0.8871595330739299
9925	Celtic	0.8825910931174089
9823	FC Bayern Munich	0.8693693693693694
10260	Manchester United	0.8687782805429864
9885	Juventus	0.8669724770642202
9772	SL Benfica	0.8644859813084113
9773	FC Porto	0.8632075471698113
8593	Ajax	0.861904761904762
9931	FC Basel	0.861244019138756

CodePudding user response：

You can use conditional aggregation, according to which you count only when there's a satisfied condition. This will avoid you to have three subqueries.

SELECT team_id, name, ((hwins awins)*1.0/(hwins awins nowin)) as winratio
FROM(SELECT m.home_team_api_id                                     AS team_id, 
            t.team_long_name                                       AS name, 
            COUNT(CASE WHEN m.home_team_goal > m.away_team_goal
                       THEN m.id END)                              AS hwins,
            COUNT(CASE WHEN m.away_team_goal > m.home_team_goal
                       THEN m.id END)                              AS awins,
            COUNT(CASE WHEN m.away_team_goal = m.home_team_goal
                       THEN m.id END)                              AS nowin
     FROM      match m
     LEFT JOIN team t
            ON m.home_team_api_id=t.team_api_id
     GROUP BY m.home_team_api_id, t.team_long_name
     ) AS all_matches
ORDER BY winratio DESC
LIMIT 10;

Or if you wish to do it without subqueries:

SELECT m.home_team_api_id                                     AS team_id, 
       t.team_long_name                                       AS name, 
       (COUNT(CASE WHEN m.home_team_goal <> m.away_team_goal 
                   THEN m.id END) *0.1) / COUNT(*)            AS winratio
FROM      match m
LEFT JOIN team t
       ON m.home_team_api_id=t.team_api_id
GROUP BY m.home_team_api_id, t.team_long_name

Note: You're missing some fields in your aggregation, and the final outer GROUP BY is not needed. In general you want to use this clause only when you're using aggregate functions.