Extremely Advanced Query... 1

[BFreshour]

I run a gaming website,

http://www.igbl.myip.org

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.

 

[bperry]

I see that 'rating' comes from table2.
(a) where does 'player time' come from?
(b) The 'status' in table1 is ignored for what we are doing here, right?
bp

 

[BFreshour]

Player-time would be one of the STATs, STAT is one of many column names that lists the statistics for each player. The numbers 45, 56, 34, 45 should actually be under that, but it's hard to make a table out of text...

 

[bperry]

>>..but it's hard to make a table out of text...
That's okay, I see that layout.
But still not sure I understand....ie so player_time is the Stat??
ie User 1 in season 1 has a playertime of 45, but in season 2 the same user has a playertime of 34. Right?

 

[BFreshour]

Yes.. actually Player_Time is listed in seconds, but thats not really relevant. STAT could be one of many columns, including Kills, Deaths, Steals, Fumbles, Carry-Time, Player_Time, Pinches, Saves, Catches, Passes, etc... Understand any?

 

[BFreshour]

Grr wish I could edit. If you want check out the site and click on Player Stats on the left. You will be able to see the columns available and maybe understand what I'm trying to do if you change the drop-down from Season 2 to Career-Totals.

Note: I'm not advertising the site, just trying to get help. If this link is inappropriate feel free to remove it.

http://www.igbl.myip.org

 

[bperry]

1 = season 1
2 = season 2
r = rating
t = player time
avg. rating = (R1 * (T1/T1+T2)) + (R2 * (T2/T1+T2))

okay, so in this formula you provided, t1 is the Stat column from Table1 for season 1, and t2 is the Stat column from Table2 for season 2.

I can look at it this evening again, if no one else happens responds sooner for you.

bp

 

[BFreshour]

bp, that is is correct, in addition r1 is the rating column for season 1 [from the linked table (linked by season_fk and user_fk)] and r2 is the rating column for season 2.

 

[bperry]

okay, I think this is quite do-able.
When the requirement calls for looking at 'the previous record', or 'last year's record', then the technique is usually an embedded correlated subquery that can go get 'the previous season's record', and pass those values back into the main query.

In this case we will be doing something like:

Read each player's current season record (season 2)
For each record retrieved
- Go get the season 1 record for the same player
- do the formula,
- output the result


What should be done if a player has no previous season record (i.e. has a season 2 but no season1)? Do not report? Report something different?

 

[BFreshour]

If they have no previous season record then it should just report the rating. For example I've I played this season and last season, the formula for my 'OVERALL' rating would be (R1 * (T1/T1+T2)) + (R2 * (T2/T1+T2)) However, if I only played season 1 or just season 2 then it would be either R1 or R2, respectively.

 

[bperry]

>>However, if I only played season 1 or just season 2
>>then it would be either R1 or R2, respectively.
I'm glad you mentioned that; I'd hadn't really thought of it.
Sounds like we have three loose groups here:
(1) players with records in both seasons
(2) players with only records in "previous" season; none in current
(3) players with only records in "current" season; none in previous

And we want to include/report all three of these, right?

If all three of these conditions exist (and need to be reported), then we will be looking at an "full outer join" situation to make sure records are returned.

But if we don't have to report on (2) or (3), then we'll be able to use a left outer join.


>>If they have no previous season record
>>then it should just report the rating.

When I get to this (a little later), I am thinking that there will be two 'derived' tables, one called Season1, the other called Season2. Which season do we consider the "previous season"....1 or 2?

bp

 

[BFreshour]

We need ALL records so a full outer join is how we will have to do it. Season 1 will be considered the previous season. Are we gonig to be building this so next season, it will still work just have an extra season? Or will it break? This is just a pre-future thought...

 

[bperry]

I think I see.
You are saying that the typical calculation will NOT just take in two years,(current + previous), rolling forward so that the 'current season' later becomes 'the previous season'.

Instead of just two years, the calculation will automatically take in however many years are available?? i.e.

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

If this is the situation, then yes the complexity just went up by orders of magnitude. Probably we'll be doing some kinda temp table inside a stored procedure.
(Shucks, I had the other one just about worked out.)

Which interpretation is most correct here?

 

[BFreshour]

Doh! I'm sorry. Yeah, I would certainly like it to roll-forward and take as many years are availble, or IF there is only one year, just display that year, as in R1 or R2, etc...

Thanks a ton for helping me with this. I do this project on my spare time and I love the community, but I want to make the website as professional as possible (in hopes of getting picked up by a gaming company doing web site design).

I know alot of ColdFusion and I thought I knew a lot about SQL until I wanted to do this...

 

[bperry]

As the data begins to pile up in the two tables, perhaps execution time/response.time could become an issue? i.e. under the new scenario, we will be reading *all* the data in both tables, then doing additional summarizing on them. Whereas in our original 'two season' scenario, we would be reading the latest two years only.

Just something to keep in mind.

 

[BFreshour]

Yeah, I understand that... I guess if it got too bad I could archive old season statistics and just have it work with whats in the table.

 

[bperry]

Okay, here's the first version (uses only 2 seasons). It returns two columns: the UserId and the AverageRating.

My table names and column names might be different than yours, so adjust to suit.
Also, my two numeric fields (PlayerTime and Rating) are Numeric (5,2). If yours are not, then you might not get exactly the same results.

This works quite well with the test data you provided. I decided to continue with this 2-season approach because it will help confirm our understanding of the requirements before tackling the more general solution.

Here it is:
----------------
SELECT
Coalesce(season1.UserId,season2.UserId) as UserId,

CAST
(
CASE
when season1.Season IS NULL then season2.Rating
when season2.Season IS NULL then season1.Rating
Else
(season1.Rating * (season1.PlayerTime/(season1.PlayerTime + season2.PlayerTime)))
+
(season2.Rating * (season2.PlayerTime/(season1.PlayerTime + season2.PlayerTime)))
END
as numeric(5,2)
)
as AvgRating

FROM
(
Select pr.UserID, pr.Season, pr.Rating, ps.PlayerTime
from PlayerRatings pr Inner Join PlayerStats ps
ON pr.UserId = ps.UserId
where pr.Season = 1 and ps.Season = 1
) as season1

FULL OUTER JOIN

(
Select pr.UserID, pr.Season, pr.Rating, ps.PlayerTime
from PlayerRatings pr Inner Join PlayerStats ps
ON pr.UserId = ps.UserId
where pr.Season = 2 and ps.Season = 2
) as season2

ON season1.UserId = season2.UserId
----------------------------------

 

[BFreshour]

Hooray! You're a genous. It worked. Here's the final version of the way we have it now. I added Play_Minutes because I needed to make sure they have atleast 150 play minutes before it puts them on the leader board.

Begin Long Query:

SELECT
Coalesce(season1.User_FK,season2.User_FK) as UserId,

CAST
(
CASE
  when season1.Season_FK IS NULL then season2.Rating
  when season2.Season_FK IS NULL then season1.Rating
  Else
    (season1.Rating * (season1.PlayerTime/(season1.PlayerTime + season2.PlayerTime)))
    +
    (season2.Rating * (season2.PlayerTime/(season1.PlayerTime + season2.PlayerTime)))
END
as numeric(5,2)
)
as AvgRating,
(
CASE
  when season1.PlayerTime IS NULL then season2.PlayerTime
  when season2.PlayerTime IS NULL then season1.PlayerTime
  ELSE
    (season1.PlayerTime + season2.PlayerTime)
END
) AS Play_Minutes

FROM
(
Select pr.User_FK, pr.Season_FK, pr.Rating, CONVERT(DECIMAL(3), ps.Play_Seconds/60) AS PlayerTime
from Player_Ratings pr Inner Join User_Statistics ps
ON pr.User_FK = ps.User_FK
where pr.Season_FK = 1 and ps.Season_FK = 1
) as season1

FULL OUTER JOIN

(
Select pr.User_FK, pr.Season_FK, pr.Rating, CONVERT(DECIMAL(3), ps.Play_Seconds/60) AS PlayerTime
from Player_Ratings pr Inner Join User_Statistics ps
ON pr.User_FK = ps.User_FK
where pr.Season_FK = 2 and ps.Season_FK = 2
) as season2

ON season1.User_FK = season2.User_FK

 

[bperry]

Okay, good.
Looks like you did a nice job adding PlayMin....I can see that you were able to follow how we put the whole thing together.

Perhaps after Easter we will think about the broader solution.
That query won't be as efficient because of the denominator in the formula:

R1 * (T1/T1+T2+T3....etc)

In other words, for each player we have to first total up the n+ playertime values. For each player, we'll have to run a separate subquery that returns the totaltime value to be used in the denominator.

rgrds, etc
brian perry

 

[BFreshour]

Brian, Let me think about it.. maybe we can just use 2 seasons worth of data. I don't want to put you through anything more than I already have. Just for kicks, I would like to know how to do it, but I'm sure it's extremely advanced.

BTW: Was this really that advanced of a query or am I just a complete newbie?