SQL PARTITION BY Clause

SQL PARTITION BY lets us split the result set into partitions and do calculations on every subset of partitioned data.

SQL PARTITION BY Used in every function invocation such as MAX(), RANK(), and AVG., the Partition By SQL clause is a subclause of the OVER clause.

Functions are usually run with the "PARTITION BY" clause in SQL. It specifies the columns by which a result set's rows should be split into divisions. The window function runs separately within each partition, treating the rows in each partition as though they were a separate group.

 

The SQL PARTITION BY statement lets us define the column on which we must aggregate using the OVER clause. The results show combined values akin to a GROUP By statement.

Partitioning by a single column:

SELECT post, AVG(salary)
FROM employees
GROUP BY post
PARTITION BY post;

Partitioning by multiple columns:

SELECT post, branch, AVG(salary)
FROM employees
GROUP BY post, branch
PARTITION BY post, branch;

Partitioning by a calculated value:

SELECT floor(salary/30) as month, AVG(salary)
FROM employees
GROUP BY floor(salary/30)
PARTITION BY floor(salary/30);

The SQL "PARTITION BY" clause defines the columns by which a query's data should be partitioned or grouped.