partition_by in sql

Topics

• sql

The partition_by clause divides result set into partitions for window functions calculations.

SELECT department, name, salary,
       AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

Above sql calculates average salary per department without collapsing rows.

Note that PARTITION BY retains all rows; GROUP BY aggregates.

Related