Oracle “Partition By” Keyword

Question

Can someone please explain what the partition by keyword does and give a simple example of it in action, as well as why one would want to use it? I have a SQL query written by someone else and I'm trying to figure out what it does.

An example of partition by:

SELECT empno, deptno, COUNT(*) 

OVER (PARTITION BY deptno) DEPT_COUNT

FROM emp

The examples I've seen online seem a bit too in-depth.

Answer 1

To set the range of records that will be used for each "GROUP" within the OVER clause, you can use PARTITION BY clause.

In above code:

 SQL, DEPT_COUNT will return the number of employees within that department for every employee record.

 It is like you're de-nomalising the emp table; and you still return every record in the emp table.

emp_no  dept_no  DEPT_COUNT

1       10       3

2       10       3

3       10       3 <- Here, 3 because there are three "dept_no = 10" records

4       20       2

5       20       2 <- Here, 2 because there are two "dept_no = 20" records

If there was another column (say state) then you would  count how many departments in that State.

It is like getting the results of a GROUP BY  clause (SUM, AVG, etc.) without aggregating the result-set (i.e. removing matching records).

Example:

This is helpful once you use the LAST OVER or MIN OVER functions to induce, the lowest and highest salary within the department and so use that in an exceedingly calculation against this records salary while not a sub choose, that is far quicker.

Refer to this link for more information AskTom article