Pages

Wednesday, November 17, 2010

SQL OVER Clause with aggregate functions

In one of my previous post, we saw the usage of OVER() clause for ranking functions. We can also use the OVER() Clause to simplify aggregate calculations. We can now add aggregate functions to any SELECT (even without a GROUP BY clause) by specifying an OVER() partition for each function. Consider the following table:

StudentID
Quesiton Reference No
Section
Required Score
1
Q-M100
Maths
10
1
Q-M200
Maths
15
1
Q-M300
Maths
20
1
Q-M400
Maths
10
1
Q-P100
Physics
10
1
Q-P200
Physics
30
1
Q-P300
Physics
10
1
Q-C100
Chemistry
50
1
Q-C200
Chemistry
10
1
Q-C300
Chemistry
15

Say, there is a requirement where you want to find the weightage of each question within a section
and also the wieghtage of each section for a student. Normally, you would create sub-queries to retrieve the summary values for calculations, as shown below:

SELECT [StudentID], [Quesiton Reference No], [Section], [Required Score],
((([Required Score]*1.0)/
  (SELECT SUM(A.[Required Score]) FROM Answers A WHERE A.[Section] = Answers.[Section]))*100) 
AS QuestionWeightageInSection,
((((SELECT SUM(A.[Required Score]) FROM Answers A WHERE A.[Section] = Answers.[Section])*1.0)/               (SELECT SUM(A.[Required Score]) FROM Answers A WHERE A.[StudentID] = 1))*100) 
AS SectionWeightage

FROM Answers WHERE Answers.[StudentID] = 1

But now with OVER() Clause this becomes more simple and efficient. This is how the query will look:

SELECT [StudentID], [Quesiton Reference No], [Section], [Required Score],
((([Required Score]*1.0)/(SUM([Required Score]) OVER(PARTITION BY [Section])))*100) 
AS QuestionWeightageInSection
((((SUM([Required Score]) OVER(PARTITION BY [Section]))*1.0)/(SUM([Required Score]) OVER(PARTITION BY [StudentID])))*100) 
AS SectionWeightage

FROM Answers WHERE Answers.[StudentID] = 1

StudentID
Quesiton Reference No
Section
Required Score
Question Weightage In Section
Section Weightage
1
Q-C100
Chemistry
50
66.66666667
41.66666667
1
Q-C200
Chemistry
10
13.33333333
41.66666667
1
Q-C300
Chemistry
15
20
41.66666667
1
Q-M400
Maths
10
18.18181818
30.55555556
1
Q-M100
Maths
10
18.18181818
30.55555556
1
Q-M300
Maths
20
36.36363636
30.55555556
1
Q-M200
Maths
15
27.27272727
30.55555556
1
Q-P100
Physics
10
20
27.77777778
1
Q-P200
Physics
30
60
27.77777778
1
Q-P300
Physics
10
20
27.77777778

The way it works is similar to joining an aggregated copy of a SELECT to itself. In my experience it is 20% or more faster than co-related sub queries. You can always look up the execution plan to see the differences between performance. You can use the OVER() Clause with all the other aggregate functions similarly. Read more about it here.

No comments:

Post a Comment