Usage of OVER Clause
To understand the OVER Clause better we need a table for performing the operations. In this article, we have studentsSectionWise table which consist of studentId, studentName, sectionName and studentMarks as a columns. If you don’t know How to Create a table in SQL Server then refer to section-wisethis.
After Inserting some data into our studentsSectionWise table looks like:
Example 1:
check the section-wise Average , section-wise Highest, and section wise Least using the OVER with PARTITION subclause.
Query:
SELECT * , AVG(studentMarks) OVER (PARTITION BY sectionName) AS sectionAverage,
MAX(studentMarks) OVER (PARTITION BY sectionName) AS sectionHighest,
MIN(studentMarks) OVER (PARTITION BY sectionName) AS sectionLeast
FROM studentsSectionWise
ORDER BY sectionName
The Result Looks Like:
Explanation:
In the query, we have used three three window functions which are AVG, MIN and MAX for the table studentsSectionWise by partitioning it on the basis of the sectionName using the OVER clause and atlast we have sorted the table based on of sectionName using ORDER BY clause.
In the result, we can see we have the row wise data along with the window function values for the windows which are partitioned on the basis of the sectionName and are sorted in the descending order on the basis of sectionName at last.
Example 2:
Let’s Check the Count of Students in Each Section Using OVER Clause with PARTITION CLAUSE
SELECT * , COUNT(*) OVER (PARTITION BY sectionName) AS studentStrength
FROM studentsSectionWise
ORDER BY sectionName
The Result Looks Like:
Explanation: In the result, we can see we have the row wise data along with the window function values for the windows which are partitioned on the basis of the sectionName and are sorted in the descending order on the basis of sectionName at last.
Example 3:
Let’s check the running section wise Average,, section wise Highest and section wise Least using the OVER with PARTITION and ORDER subclause.
Query:
SELECT * , AVG(studentMarks) OVER (PARTITION BY sectionName ORDER BY studentMarks) AS sectionAverage,
MAX(studentMarks) OVER (PARTITION BY sectionName ORDER BY studentMarks) AS sectionHighest,
MIN(studentMarks) OVER (PARTITION BY sectionName ORDER BY studentMarks) AS sectionLeast
FROM studentsSectionWise
ORDER BY sectionName
Explanation: In the query we have used three window functions which are AVG, MIN,at last and MAX for the table studentsSectionWise by partitioning it on the basis of the sectionName and ordering it on the basis of student marks using the OVER clause and at last we have sorted the table based on sectionName using ORDER BY clause.
Note: The ORDER BY clause in the OVER clause helps us finding the running average or cumulative aggregation as the records get added the until that point the window function applies which means we get the statistics of the column in the realtime based on which column we have partitioned.
The default range of the window is UNBOUNDED PRECEDING until the CURRENT ROW which can be changed using the ROW or RANGE clause. The default order by which the values in the column of the particular window get sorted is ascending which can be changed to descending as well.
Output:
Explanation:
In the result, we can see the students marks are ordered in ascending order and the sectionAverage, sectionHighest, sectionLeast gets changed as we go down in the rows of the particular section in the ascending order in that particular window.
For instance in the partition of the section A we have:
- Geek10: sectionAverage: 206, sectionHighest: 206 sectionLeast: 206
- Geek10, Geeks7: sectionAverage: (206 + 436)/2 = 321, sectionHighest: 321, sectionLeast: 206
- Geek10, Geeks7, Geek4: sectionAverage: (206 + 436+ 446)/3 = 383, sectionHighest: 446, sectionLeast: 206
- Geek10, Geeks7, Geek4,Geek13: sectionAverage: (206 + 436+ 446+446)/4 = 383, sectionHighest: 446, sectionLeast: 206
- Geek10, Geeks7, Geek4,Geek13,Geek1: sectionAverage: (206 + 436+ 446+446+461)/4 = 399, sectionHighest: 446, sectionLeast: 206
The same for remaining section B and C , we can see as the marks are increasing in the ascending order we have the running average , the maximum until that row, the minimum until that row.
SQL Server – OVER Clause
The OVER clause is used for defining the window frames of the table by using the sub-clause PARTITION, the PARTITION sub-clauses define in what column the table should be divided into the window frames. The most important part is that the window frames are then used for applying the window functions like Aggregate Functions, Ranking functions, and Value functions. The main advantage of the OVER clause is that it prevents row-wise data loss while aggregating the data, partitioned as the widow frames.
Syntax:
SELECT col1, col2 , windowFunction(col3,OVER ( [ PARTITION BY col_name ] [ ORDER BY col_name]
[ ROW or RANGE clause] ) AS col_name
FROM table_name
The main components of the OVER clause:
- Window functions: The windows functions are needed for applying them on the window frames that are defined by the OVER clause. A window frame is a set of rows belonging to a common condition.
- PARTITION BY sub-clause: This is the main sub-clause that partitions the rows into windows and for each row, the values of window functions applied will be calculated.
- ORDER BY: This is used to order the rows in the partition by default it is the ascending order.
- ROWS or RANGE: It limits the rows from a start point and endpoint in the particular window, to use the ROWS and RANGE clause we need to ORDER BY clause as well. The RANGE and ROWS clauses are similar but the only difference is ROWS clause considers duplicates as well whereas the RANGE class doesn’t consider duplicates.
Contact Us