need help with complex sql statement

[theGlove]

I'm trying to form an sql statement to compute the kill/death ratio of players that I am keeping stats for.

the database table has the following columns:

UserData Table: userName, defeatedName, location, killDate


sample data for the table:

http://i5.photobucket.com/albu...love113/sampledata.jpg

basically, 'userName' is the person who killed 'defeatedName'.



I have a sql query that computes the top 20 users with most kills as:

SELECT userName, count(userName),
FROM UserData
WHERE killDate > DATE_SUB(now(),
INTERVAL 1 HOUR)
Group By userName
order by count(userName)
DESC limit 20;


but I can't figure out how to compute the top 20 users with the best kill ratio.
I'm thinking I should right a stored procedure to do this but I think there should be
an sql query that can do this?

 

[brandonb]

select top 20 "kills", k.username
from (
select username, count(username) as count
from table
group by username
) as k
order by k.count DESC

UNION

select top 20 "deaths", d.defeatedName
from (
select defeatedName, count(defeatedName) as count
from table
group by defeatedName
) as d
order by d.count DESC

----

Thats where I'd start, I havent debugged it but thats what comes to mind.

If you just want ratio

select top 20 "killratio", kr.username, kr.ratio
from (

select username, cast((r.kills / r.deaths) as numeric(18,6)) as ratio
from
(

select username, count(username) as kills, d.deaths
from table
Inner join (

select defeatedName, count(defeatedName) as deaths
from table
group by defeatedName ) as d on table.username =d.username
group by username
) as r
) as kr
order by kr.count DESC

Sorry about the formatting, its hard to read I know. But you just do seperate queries, one for kills, one for deaths, group them together as a ratio, then pick out the 20 top ones. That statement won't really work as is, but I think I'm on the right path.

 

[drebo]

Sometimes it's OK to break 3rd normal form for the sake of simplicity in situations exactly like this.

Instead of counting how many kills a person has every time they look at their tally (that can be REALLY slow if your dataset gets big), simply include a counter for kills and deaths in the main user table.

Is it 3rd normal form? Not quite, there's some repetition in the data. But, it's a sacrifice that will increase the efficiency and speed of reports which use those values.

 

[Train]

you could just write a trigger to update a ratio column on any update?

that way all ratios are always up to date

 

[theGlove]

thanks all for the help, you guys are good i'm gonna try out the sql that brandonb has written and see the performance i get
with that. since the table is fairly large 50k+ rows, i might have to create additional tables as suggested. thanks

 

[nakedfrog]

Originally posted by: Train
you could just write a trigger to update a ratio column on any update?

that way all ratios are always up to date

That seems to me like it result in an excessive amount of overhead.

Does MySQL support calculated fields?

 

[Train]

Originally posted by: nakedfrog

Originally posted by: Train
you could just write a trigger to update a ratio column on any update?

that way all ratios are always up to date

That seems to me like it result in an excessive amount of overhead.

Does MySQL support calculated fields?

Less overhead than computing the whole table on each select then sorting them all to grab the top 20.

With a trigger only affected records get recalculated, instead of the entire table. Would make the select statment he's looking for much faster.

 

[theGlove]

ok just ran the query and it looks about .2 seconds or so, pretty good for what I'm doing.
thanks all.

here's a screen shot of the results and sql
http://i5.photobucket.com/albu...heGlove113/results.jpg

 

[brandonb]

Nice job on getting my query to work. ha ha.

I noticed that you can probably combine the sub query for kills + deaths to the ratio one so you can remove that nested SQL there. It would probably speed it up somewhat. Give it a shot.

Just move the:

select username, cast((r.kills / r.deaths) as numeric(18,6)) as ratio

to the select statement below like:

select username, count(username) as kills, d.deaths, count(r.kills) / r.death as ratio

and reduce the nested SQL by one level there.

I don't know if it will work, but you can play around with doing things like that to see if it helps or not with performance and readability.