-1

How to optimize derived tables with union all query?

Problem

  • I want to remove the redundancy.
  • The table "team" is used twice
   SELECT series_id, team, SUM(Win) As Won, SUM(Loss) as Lost
    FROM
    ( SELECT *,radiant_name as team, 
         CASE WHEN radiant_win = 1 THEN 1 ELSE 0 END as Win, 
         CASE WHEN radiant_win = 1 THEN 0 ELSE 1 END as Loss
      FROM matches
      UNION ALL
      SELECT *,dire_name as team, 
         CASE WHEN radiant_win = 0 THEN 1 ELSE 0 END as Win, 
         CASE WHEN radiant_win = 0 THEN 0 ELSE 1 END as Loss
      FROM matches
    ) as temp
    WHERE series_id = 8313
    GROUP By team
    ORDER By Won, Lost DESC
Community
  • 1
  • 1
ronscript
  • 397
  • 1
  • 8
  • 33

2 Answers2

1

Areas to optimize this include:

Don't select *. Select just the fields you need. In other words, this:

SELECT *,radiant_name as team

can be this:

SELECT series_id,radiant_name as team

Next, your derived table has no filtering so it's querying the entire table. This:

WHERE series_id = 8313

should go into your subquery - twice.

Finally, if you are only interested in one series_id, there is no point selecting it. Your query could resemble this:

SELECT team, SUM(Win) As Won, SUM(Loss) as Lost
FROM
( SELECT radiant_name as team, 
 CASE WHEN radiant_win = 1 THEN 1 ELSE 0 END as Win, 
 CASE WHEN radiant_win = 1 THEN 0 ELSE 1 END as Loss
FROM matches
WHERE series_id = 8313

UNION ALL

 SELECT dire_name as team, 
 CASE WHEN radiant_win = 0 THEN 1 ELSE 0 END as Win, 
 CASE WHEN radiant_win = 0 THEN 0 ELSE 1 END as Loss
 FROM matches
WHERE series_id = 8313

) as temp
GROUP By team
ORDER By Won, Lost DESC
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
1

I think, those case-statements are the ugly part of your query. I would calculate wins or losses by subtracting the amount of wins of the overall count and vice versa:

select series_id, team, sum(won), sum(lost)
  from
    (
      select series_id, radiant_name as team, sum(radiant_win) as won, count(*)-sum(radiant_win) as lost
        from matches
        group by series_id, radiant_name
      union all
      select series_id, dire_name as team, count(*)-sum(radiant_win) as won, sum(radiant_win) as lost
        from matches
        group by series_id, dire_name
    )
where series_id = 8313
group by series_id, team
order by won, lost desc
René Hoffmann
  • 2,766
  • 2
  • 20
  • 43
  • Hi thanks. the result returns 2 columns how to make it merge to 1 row only? – ronscript Nov 21 '14 at 13:56
  • What columns are returned. I'm sorry, I could not test the query. – René Hoffmann Nov 21 '14 at 13:59
  • it return two teams. please check this http://stackoverflow.com/posts/comments/42626243?noredirect=1 – ronscript Nov 21 '14 at 14:01
  • can you review this http://stackoverflow.com/posts/comments/42626243?noredirect=1 maybe – ronscript Nov 21 '14 at 14:08
  • how to make it teamA and teamB? – ronscript Nov 21 '14 at 14:12
  • I didn't see your edited comment, when I wrote my previous comment. But I don't understand, why you want it in one row. (I think you were using "column" as a synonym for "row" which would be bad.) That's not what you asked for in this question here, but in the question you linked in your comment above. But maybe I just don't get your point until now, sorry for this. Could you provide some screenshot of the result you get with my query? – René Hoffmann Nov 21 '14 at 14:20
  • this is the result using your query http://s28.postimg.org/79sanca1p/results.png. – ronscript Nov 21 '14 at 14:31
  • 1
    Please, have a look at my comment to your linked question: http://stackoverflow.com/questions/27054402/how-to-query-counting-specific-wins-of-team-and-find-the-winner-of-the-series/27054575?noredirect=1#comment42641994_27054402 – René Hoffmann Nov 21 '14 at 14:53