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