PARTITION BY Clause | When & How to Use the PARTITION BY in SQL
In this article, I am going to discuss one of the most necessary topics PARTITION BY Clause, when and how to use the SQL partition by clause. Furthermore, compare it to using the GROUP BY clause. At the heart of each window function call is an OVER clause that describes how the windows of the records are built. Within the SQL OVER clause, there may be an optional or non-mandatory PARTITION BY subclause that describes the criteria for identifying which records to include in every window. Read on and take a necessary step in growing your SQL skills!. I hope you discover this article useful and enjoy it!
What is Partitioned by in SQL?
The following article provides an outline of PARTITION BY in SQL. The PARTITION BY is utilized to divide the result set into partitions. After that, it performs computation on every data subset of partitioned data. We use the ‘partition by’ clause to describe the partition in the table. The ‘partition by ‘clause is utilized along with the sub-clause ‘over’. Here, we use window functions to operate the partition separately and recalculate the data subset. Furthermore, the window functions are defined as RANK (), LEAD (), COUNT (), MIN (), MAX () and ROUND(), etc. Thus, the ‘partition by ‘clause is a scalar subquery. Which always returns a single value.
Syntax:
|
1 2 3 4 |
window_function ( expression ) OVER ( PARTITION BY expression_1, expression_2... order_clause ) |
expression_1 and expression_2 only refer to the columns by the ‘FROM’ clause. We can not refer to the alias in the select statement.
How to Implement PARTITION BY in SQL?
Let us take the two tables below and the implementation
Below are the "Parcel" and "borrow" tables. Furthermore, once we apply the ‘PARTITION BY’ in SQL to join we get the common rows between two tables.
Parcel Table
| Parcel_number | Trip_Number | Amount | No_of_Days | |
|---|---|---|---|---|
| 1 | 99 | 15 | 3500 | 11 |
| 2 | 111 | 18 | 4000 | 14 |
| 3 | 120 | 24 | 4400 | 21 |
| 4 | 151 | 32 | 5100 | 17 |
| 5 | 211 | 41 | 5500 | 12 |
The given Below is the partition by condition applied as per the 'Amount' partitioning by the 'Trip_Number'.
Code:
|
1 2 3 4 5 6 7 |
SELECT Parcel_number, Trip_Number, Amount, ROUND (AVG(Amount) OVER ( PARTITION BY Trip_Number )) AS Avg_Trip_Amount FROM Parcel; |
Borrow Table
| Borrow_ID | Borrow_Status | State | Borrow_Amount | App_Date |
|---|---|---|---|---|
| 11 | Open | Uttar Pradesh | 250000 | 2016-02-15 |
| 14 | Close | Ranchi | 300000 | 2016-03-21 |
| 21 | Approved | Delhi | 410000 | 2016-04-04 |
| 16 | Pending | Patna | 350000 | 2016-05-25 |
| 34 | Open | Bangalore | 220000 | 2016-06-18 |
| 44 | Rejected | Ranchi | 370000 | 2016-07-28 |
| 51 | Close | Agra | 230000 | 2016-08-10 |
The given below the partition by condition is applied as per the 'Borrow_Amount' partitioning by the 'Borrow_ID'.
Code:
|
1 2 3 4 5 6 7 8 |
SELECT Borrow_ID, Borrow_Status, State, Borrow_Amount, ROUND (AVG(Borrow_Amount) OVER ( PARTITION BY Borrow_Status )) AS Avg_Borrow_Amount FROM Borrow; |
ROW_NUMBER With PARTITION BY Clause
Here, we utilized ROW_NUMBER for the purpose of paging in SQL Server. Furthermore, it is utilized to provide the consecutive numbers for the rows in the result set by the 'SQL ORDER' clause. The sequence starts from 1.
Not essentially, here, we required a 'partition by' clause while we will use the row_number concept.
About Author
