Oracle "Partition By" Keyword

In Oracle, the "PARTITION BY" keyword is used in conjunction with the "OVER" clause to divide the result set of a query into partitions to which the function is applied. It is used to perform calculations on a per-partition basis rather than on the entire result set.

For example, the following query uses the RANK() function with the PARTITION BY clause to assign a rank to each row within each partition of the result set based on the value of the "salary" column:

SELECT employee_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank

FROM employees;

This query will return the employee_id, salary and rank for each employee based on their salary within their department. The following query uses the SUM() function with the PARTITION BY clause to calculate the total salary of each department:

SELECT department_id, SUM(salary) OVER (PARTITION BY department_id) as total_salary

FROM employees;

This query will return the department_id and the total salary for each department.

In both cases, the "PARTITION BY" clause is used to divide the result set based on the specified column, in this case, "department_id", and then the function applied (RANK() and SUM() respectively) is calculated for each partition. It's also worth noting that you can use the "PARTITION BY" clause in conjunction with the windowing clause (ROWS or RANGE) to specify the range of rows or values over which the function is applied. The "PARTITION BY" clause is very powerful and allows you to perform complex calculations over specific subsets of data. It can be used with various aggregate functions like RANK, DENSE_RANK, ROW_NUMBER, SUM, AVG, MAX, MIN, etc. and analytic functions like LAG, LEAD, FIRST_VALUE, LAST_VALUE, etc.