February 8, 2008 at 8:38 am
Hi all,
On my website (mydartschallenge.com) I provide an ranked overview of the averages of users. Currently I'm only ranking on 1 dart average, but would like to include in this ranking the amount of uploads a particular user has done (and add weight to this).
So a user with the highest 1 dart average, but has only uploaded once, should come lower in the rank.
Any idea how I can alter my SP to get this information out of the database?
Current code:
Select users.userID, Username, avg(average1dart), count(*) as Total, RANK() over (order by AVG(average1dart) DESC) as Rank
from stats INNER JOIN Users ON Stats.UserID = Users.UserID
group by users.userid, users.username
Kind regards,
Ronald
February 8, 2008 at 8:46 am
Sounds to me like you'll need to add a column to the Rank function. Something like "(select count(*) from uploads where userid = users.userid)". Join to a CTE/Derived Table with that count in it, and you'll probably get okay performance. Then add the column to the rank function's "order by", and you should have it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2008 at 9:25 am
Thanks for your response...
I have now altered the code to:
Select users.userID, Username, avg(average1dart), count(*) as Total, RANK() over (order by count(*) DESC, AVG(average1dart) DESC) as Rank
from stats INNER JOIN Users ON Stats.UserID = Users.UserID
group by users.userid, users.username
This is okay... so thanks..
I was just wondering if there's a way to add weight to the values or columns.. like average weighs 5 and count ways 10. Searching through google shows that you can do this within full text search indexes, but haven't found anything on working with values from a select statement.
Thanks!
Ronald
February 8, 2008 at 10:01 am
You'd want to think up an appropriate way to combine the two into a single value and ORDER by that.
In other words, in the case you mentioned where the count has weight 10 and the average has weight 5, then
Select users.userID,
Username,
avg(average1dart),
count(*) as Total,
RANK() over (order by (count(*)*10+AVG(average1dart)*5) DESC) as Rank
from stats INNER JOIN Users ON Stats.UserID = Users.UserID
group by users.userid, users.username
by keeping the two separate - there's no way for a high average to "overtake" a higher count(*).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 8, 2008 at 10:06 am
I'm also thinking you might get slightly better performance with a CTE:
;with DartCTE as
(
Select users.userID,
Username,
avg(average1dart) as avg1dart,
count(*) as Total
from stats INNER JOIN Users ON Stats.UserID = Users.UserID
group by users.userid, users.username
)
select dartcte.*,
RANK() over (order by (Total*10+avg1dart*5) DESC) as Rank
from DartCTE
Finally - unless each posting has EXACTLY the same weight, the average of an average will return a false result (because it's inaccurately weighted). So if someone played 10 games one night and averaged 50, and then played 15 and averaged 100, the average of the averages would yield 75, whereas the "true" average is (10*50+15*100)/25=80.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 9, 2008 at 1:14 am
That's the one!
Thanks very much for your (all) assistance.
Ronald
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply