Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Extremely Advanced Query... 1

Status
Not open for further replies.

BFreshour

Programmer
Mar 20, 2002
84
I run a gaming website, and I've ran into a small snag. We've had 2 seasons and all the stats are stored in one season. Something like this:

PK USER_FK SEASON_FK STAT
1 1 1 45
2 2 1 56
---
500 1 2 34
501 2 2 45

This table is linked to another table with player ratings (it looks at all the stats does some calculations to get the rating). It looks something like this:

PK USER_FK SEASON_FK RATING
1 1 1 23.45
2 2 1 45.23
---
500 1 2 42.12
501 2 2 22.32

Ok I think you get the idea. Of course the USER_FK and SEASON_FK link to other tables, one that has all the users, the other that has the seasons.

Now I have an overall rating graph that is currently averaging the ratings together (adding together / number of seasons). However, I'd like to do it a different way. The following formula is how I'd like to do it, but I can not figure out how to write the SQL query.

1 = season 1
2 = season 2
r = rating
t = player time

avg. rating = (R1 * (T1/T1+T2)) + (R2 * (T2/T1+T2))

---
It's pretty complex and I'm sure it takes some CASE statements but I simple can't figure it out... Any help would be appreciated.
 
it's hard to say....I would consider it moderate complexity, I guess. Usually, 75% of the battle is just getting a picture in your mind of how the thing could work. In this case, once we could see that two derived tables (this seas and last seas) would do the job, it was fairly easy to code.

good luck,
brian perry
 
Can we take another look at this brian? I'm been trying to see if I could come up with someone.. It seems like to me we are going to have to use some variable and what not. If you have some free time, I'd appreciate some ideas on how to go about doing this for future seasons. For example the:

avg. rating = (R1 * (T1/T1+T2+T3...))
+ (R2 * (T2/T1+T2+T3...))
+ (R3 * (T3/T1+T2+T3...))
+ etc...

 
I can probably look into that some more. Anyone else reading this forum is also welcome to jump in if they care to.

Like I mentioned last week, a separate select statement of subquery will be needed to calculate the denominater T1+T2+T3....

I wouldn't be surprised if a temporary table is gonna be involved somehow.

Is it okay to wrap this up into a stored procedure? i.e. your client software (ASP?) would execute the SP to return the result set, instead of executing the complex SQL statement directly.

Are you using SQL 2000, in which case we have UDFs available to use if we want them.


Because this seems to be part of a login-process at your site, I am somewhat concerned that performance of this query will be what you need, due to the amount of data to be read. One way to possibly deal with that, if it became necessary, is to pre-calculate and store prior seasons statistics in a 'summary table', so that the same statistics aren't being re-calculated over and over again. (This of course is a de-normalization, which we don't 'normally' like.) Anyway, that's prob for down the road.

>> we are going to have to use some variable ...
Feel free to elaborate all you want on this train of thought. Anything you mention re: what you thinking, possible approaches, etc might all be useful input to the final solution.

rgrds, etc
brian perry
 
I notice that it seems we can factor out the T1+T2+T3, so that the equation becomes:

AvgRating = [ (R1*T1)+(R2*T2)+(R3*T3) ] / (T1+T2+T3)

Although, I'm not sure if this helps us or not. Perhaps.
 
I'm using SQL 2000 and ColdFusion. So yes, I can call a stored procedure and we do have UDF's available. I'll start thinking on it and let you know what I come up with...
 
Okay, here's something to play with.
Seems easier than I thought it might be. Actually, may be better than the first one we developed.
-------------------------------
declare @user int
set @user = 1

SELECT UserId,
SUM(Exten) /
(Select SUM(PlayerTime) from PlayerStats where UserId = @user) as AvgRating

FROM
(Select pr.UserID,
(pr.Rating * ps.PlayerTime) as Exten
From PlayerRatings pr Inner Join PlayerStats ps
On pr.UserId = ps.UserId and pr.Season = ps.Season
Where pr.UserId = @user
) as dt
GROUP BY UserId
 
Holy.. I can't follow this one that well, but it works. I don't have a User_PK of 1 so I changed it to 4 and I fixed a bunch of the column and table names.

--Begin--
Code:
declare @user int
set @user = 4

SELECT User_FK,
ROUND(SUM(Exten) /
(Select SUM(Play_Seconds) from User_Statistics where User_FK = @user), 2) as AvgRating

FROM
  (Select pr.User_FK,
   (pr.Rating * ps.Play_Seconds) as Exten
   From Player_Ratings pr Inner Join User_Statistics ps
   On pr.User_FK = ps.User_FK and pr.Season_FK = ps.Season_FK
   Where pr.User_FK = @user
   ) as dt
GROUP BY User_FK
---End---

I guess now we need to add the logic to increase the User_FK each time until it reaches the end.
 
>>declare @user int
>>set @user = 1
Yeah, well that's meant to be whatever the current user is that we are running this calculation for. If this query was inside an SP, for example, then we would be passing in the userid that we wanted to process.


The key to understanding this query is to recognize that we first have a defined table called dt, (after the FROM clause) which is a table created in memory on the fly. Run that query all by itself to see that it produces an Rn*Tn ('Exten' for Extension) record for every season that a player has.

Select pr.UserID,
(pr.Rating * ps.PlayerTime) as Exten
From PlayerRatings pr Inner Join PlayerStats ps
On pr.UserId = ps.UserId and pr.Season = ps.Season
Where pr.UserId = @user

Then, we wrap those results in an outer query that calculates the AvgRating by SUMming up the extensions, then dividing that sum by the Total Time for that player (which is calculated by the separate Select subquery after the / sign).
In other words, we are using the revised equation where we factored out the T1+T2+T3.

rgrds, etc
brian perry
 
Brian, so if I was wanting it to run through a specific list of players? How would I do that? For instance, this data is populated in a CFGRAPH that lists the top 15 player ratings..
 
>>...so if I was wanting it to run through a specific list of players?
Hmm, not just exactly sure what you are meaning here.

The previous version calculated the AvgRating for a specific player.
This version below has been modified to calculater for all players.
Is that what you want, or something else?
------------------------------
Select UserId,
SUM(Exten) / (Select SUM(PlayerTime) from PlayerStats ps2
where ps2.UserId = dt.UserId) as AvgRating
FROM
(
Select pr.UserID,
(pr.Rating * ps.PlayerTime) as Exten
from PlayerRatings pr Inner Join PlayerStats ps
ON pr.UserId = ps.UserId and pr.Season = ps.Season
) as dt
Group By UserId
--------------------------------------------
 
Brian, I owe you so much. You've been a tremendous amount of help. Trying to figure this out for myself I ended up doing this, which didn't do anything but create about 1000 different recordsets, one for each player.

declare @user int
set @user = 1

WHILE (Select Count(*) FROM Users) >= @user
BEGIN
SELECT User_FK,
ROUND(SUM(Exten) /
(Select SUM(Play_Seconds) from User_Statistics where User_FK = @user), 2) as AvgRating

FROM
(Select pr.User_FK,
(pr.Rating * ps.Play_Seconds) as Exten
From Player_Ratings pr Inner Join User_Statistics ps
On pr.User_FK = ps.User_FK and pr.Season_FK = ps.Season_FK
Where pr.User_FK = @user
) as dt
GROUP BY User_FK
ORDER BY AvgRating
set @user = @user + 1
END

----
That easily shows your SQL level versus my SQL level. Anyhow, here is the final statment that works beautifully.

SELECT User_FK,
ROUND(SUM(Exten) /
(Select SUM(Play_Seconds) from User_Statistics ps2 where ps2.User_FK = dt.User_FK), 2) as AvgRating

FROM
(Select pr.User_FK,
(pr.Rating * ps.Play_Seconds) as Exten
From Player_Ratings pr Inner Join User_Statistics ps
On pr.User_FK = ps.User_FK and pr.Season_FK = ps.Season_FK
) as dt
GROUP BY User_FK
ORDER BY AvgRating DESC

----
Maybe someday I'll be able to help out someone as much as you did me. Once again, thanks a TON!

Brad
 
Okay, great, so glad it worked.
That's how we teach ourselves....by helping out someone else.

rgrds, etc
brian p
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top