PARTITION VS GROUP BY

The GROUP BY clause compresses the rows and gives minimal information rather than preserving each rows information, whereas using PARTITION clause we can preserve the row wise date along with the aggregated data or the data frames on which the window functions are applied. Using PARTITION is becomes an advantage to compare the data with over dataframe value by which column the dataframes are divided.

Let’s see the section wise Average , section wise Highest and section wise Least using the GROUP BY clause:

Query:

SELECT sectionName , AVG(studentMarks) AS sectionAverage,
MAX(studentMarks) AS sectionHighest,
MIN(studentMarks) AS sectionLeast
FROM studentsSectionWise
GROUP BY sectionName
ORDER BY sectionName

Explanation: In the query we have found the sectionName , sectionWiseAverage , sectionwiseHighest and the sectionwiseLowest using the GROUP BY clause on the sectionName and finally we have sorted the table using ORDER BY on sectionName. If we try to see the studentName we can’t see we get an error that is where we can’t see row wise data using the GROUP BY.

Output:

Explanation : In the result we can see that all the sectionName on which we have applied the aggregate function along with the aggreagted values like sectionAverage, sectionHighest and sectionLeast other than that we can’t get any other information.

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.