Oracle PARTITION BY (Window Clause)
Oracle PARTITION BY (Window Clause) is a SQL function in the Window Functions category. Oracle: window functions with PARTITION BY for running totals, moving averages, and ranking per group. The syntax is SUM(col) OVER (PARTITION BY grp ORDER BY date ROWS UNBOUNDED PRECEDING). It accepts col. It returns window function result. A typical example: SELECT employee_id, name, department, salary, SUM(salary) OVER ( PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_dept_total, RANK() OVER ( PARTITION BY department ORDER BY salary DESC ) AS dept_salary_rank FROM employees; -- Running total of salaries per department by hire date -- Rank within department by salary -- Oracle-specific: -- PARTITION BY can include functions: -- OVER (PARTITION BY TRUNC(hire_date, 'MM')) A close relative is ROW_NUMBER(), which assigns a unique sequential integer to each row within its partition (1, 2, 3...). A close relative is RANK(), which similar to ROW_NUMBER but gives the same rank to equal values. Leaves gaps in ranking.