I have a rather long and complex query I need to optimize if at all possible. The problem I'm running into is that I need to set two constants (an count of a column in a derived table and an average of the same column in a derived table) and then use them to arithmatically derive a column of values in a third query.
Here's the actual query I'm using ... any tips for cleaning it up?
I believe the first 2 sets can be combined into a single SELECT.
DECLARE @avrating Decimal(38,3); DECLARE @avraters Decimal(38,3);
Beyond that, I'm not entirely sure I understand the purpose of the code. I reformatted your query trying to figure it out. I agree with Jesse's comment about the AND/OR in the WHERE clause. My main question, though, is this: Why are you calculating the overall average rating and average number of ratings for any given show and then adding that to the calculated rating for a given show?
Here's the reformatted code:
Essentially, this query will shift the ratings for individual shows to be closer to the overall average. Is this really what you want it to do?
answered Oct 20, 2009 at 04:08 PM
Jesse is right, I did want to use this instead:
Just that one change has cleaned things up significantly, and I'll try to use Tom's single SELECT method for the first set of queries.
To answer your question, Tom, yes, the behavior of shifting ratings for individual shows closer to the average is what I expect and want to do. This method of working with ratings is meant to make shows with higher numbers of ratings balance with the shows that have just a handful of ratings (The ratings of a show with 1,000,000 fans don't overly dwarf a show with 2 fans).
It's actually called a "Bayesian Rating" and it used by organizations like Netflix as well as my client.
Does that bring what I'm trying to do into a better light?
answered Oct 20, 2009 at 08:29 PM
From the original query, this line looks suspicious:
Do you really mean this?:
If so, you'll have to change it, since the logic is very different. Also, if you need to change it, you could shorten it to:
If your original logic is as intended, then I recommend using parentheses to separate the ANDs and ORs (even if not needed, they help for readability):
Based on my understanding of what you are trying to do I'd probably start by doing something like this:
answered Oct 20, 2009 at 03:01 PM