Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2007
    Posts
    23

    Unanswered: Tricky Aggregate View

    Hello,

    I've put together a website The Ultimate Showdown

    The website features two tables.

    fighters
    id (primary key)
    name

    fights
    winner (foreign key to fighters.id)
    loser (foreign key to fighters.id)

    Each fighter can be in many many fights and will either be a winner or a loser. Each fight must have exactly one winner and one loser.

    I'm trying to build a view for fighter's statistics
    fighterStats
    fighterId (fighters.id)
    wins (number of wins a fighter has obtained)
    losses (number of losses a fighter has obtained)
    totalFights (wins+losses)
    powerLevel ((wins+10)/(totalFights+20)*10000) (number ranging from 0-10000)

    I can build this view using sub queries in my select i.e.

    select
    ftr.id as fighterId,
    IFNULL((select count(1) from fights where winner = ftr.id group by winner), 0) as wins,
    IFNULL((select count(1) from fights where loser = ftr.id group by loser), 0) as losses,
    IFNULL(((select count(1) from fights where winner = ftr.id group by winner)+(select count(1) from fights where loser = ftr.id group by loser)), 0) as totalFights,
    ROUND(((IFNULL((select count(1) from fights where winner = ftr.id group by winner),0) + 10)/((IFNULL((select count(1) from fights where winner = ftr.id group by winner),0)+IFNULL((select count(1) from fights where loser = ftr.id group by loser),0))+ 20))* 10000) as powerLevel
    from fighters ftr

    While this works there is a large amount of calculation and redundant calculation at that because you can't use wins+losses for totalFights and instead you have to resort to a reproducing both the wins and losses query portion in their entirety summed together. When you work out powerLevel it gets so complicated that it's hard to follow. This worked... but it wasn't very efficient and as the fights grow I imagine it will become even less so.

    I tried doing something like

    select ftr.id, count(w.winner) wins, count(l.loser) losses
    from fighters ftr
    left outer join fights w
    on w.winner = ftr.id
    left outer join fights l
    on l.loser = ftr.id
    group by ftr.id

    but this results in wrong data as the wins and losses end up at the same total.

    I then tried something with a sub query in the from:

    select ftr.id fighterId, wins, losses, wins+losses as totalFights, ROUND((wins+10)/(wins+losses+20)*10000) as powerLevel
    from fighters ftr
    join (
    select count(1) wins, winner from fights
    group by winner
    ) as wins
    on ftr.id = wins.winner
    join (
    select count(1) losses, loser from fights
    group by loser
    ) as losses
    on ftr.id = losses.loser

    This worked fantastically but unfortunately views can't use subqueries in the from clause.

    Does anyone have any suggestions on a better way to do this? Maybe a different query I haven't though of. Or possibly a different table structure?

    Any advice is greatly appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    views can't use subqueries in the from clause? really?

    i mean, i haven't run across that myself before since i don't need a lot of views, but really?

    oh well, you prolly can't use this then...
    Code:
    SELECT fighterId
         , wins
         , losses
         , wins+losses AS totalFights 
         , (wins+10)/(wins+losses+20)*10000 AS powerLevel  
      FROM ( SELECT t.id AS fighterId
                  , ( SELECT COUNT(*) 
                        FROM fights 
                       WHERE winner = t.id 
                      GROUP 
                          BY winner) AS wins
                  , ( SELECT COUNT(*) 
                        FROM fights 
                       WHERE loser = t.id 
                      GROUP 
                          BY loser) AS losses
               FROM fighters AS t
           ) AS d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •