Advantages of using OVER clause
- It allows us to partition the data into window frames on the particular and allows us to apply the window function on the window frames.
- It allows use the OVER clause with PARTITION clause, ORDER BY and ROW or RANGE
- For analysis of data by using the window functions we can generate different statistics
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