SQL Analytical Functions - Partition by (to split resultset into groups)

Partition by: 

Splits the resultset into groups. 

Employees -Table

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        100 Steven                    24000 
        101 Neena                     17000 
        102 Lex                       17000 
        103 Alexander                  9000 
        104 Bruce                      6000 
        105 David                      4800 
        106 Valli                      4800 
        107 Diana                      4200 

select e.employee_id, e.department_id, salary,
       row_number() over (partition by department_id order by salary desc) as rownumber 
       from employees e; 

The above query splits the result into small subgroups for every department. 
Syntax: Function(arg1,..., argn) OVER ( [PARTITION BY <..>] [ORDER BY <....>] 
[<window_clause>] )

partition by - create  subgroups for every department_id

order by - sort the records of the subgroup

Over  - condition for partitioning the data. If the clause is empty, the whole result is treated as a single group

Function - Function is applied to the records of the sub-group. examples of the function are  row_number(), count(), AVG(), RANK() etc., 

EMPLOYEE_ID DEPARTMENT_ID     SALARY  ROWNUMBER
----------- ------------- ---------- ----------
        103            60       9000          1 
        104            60       6000          2 
        105            60       4800          3 
        106            60       4800          4 
        107            60       4200          5 
        100            90      24000          1 
        101            90      17000          2 
        102            90      17000          3 

eg.1) Below example applies AVG(salary) for all the salaries in the subgroup 

select e.employee_id, e.department_id, e.salary ,
       AVG(e.salary) over (partition by department_id) as avg_dept_sal 
       from employees e; 

EMPLOYEE_ID DEPARTMENT_ID     SALARY AVG_DEPT_SAL
----------- ------------- ---------- ------------
        103            60       9000         5760 
        106            60       4800         5760 
        105            60       4800         5760 
        107            60       4200         5760 
        104            60       6000         5760 
        102            90      17000  19333.33333 
        101            90      17000  19333.33333 
        100            90      24000  19333.33333 

eg.2) Another example without over clause treats the whole resultset as a single subgroup

select e.employee_id, e.department_id, e.salary ,
       AVG(e.salary) over () as avg_dept_sal 
       from employees e; 

EMPLOYEE_ID DEPARTMENT_ID     SALARY AVG_DEPT_SAL
----------- ------------- ---------- ------------
        103            60       9000         10850
        106            60       4800         10850
        105            60       4800         10850
        107            60       4200         10850
        104            60       6000         10850
        102            90      17000         10850 
        101            90      17000         10850 
        100            90      24000         10850