Like aggregate functions, that may operate alone or in relation to a group, window functions may also operate on a group, which is called a partition.

Syntax

  |<window function>(...) OVER (PARTITION BY <expr> [, <expr> ...])

Aggregation over a group could produce more than one row, so the result set generated by a partition is joined with the main query using the same expression list as the partition.

Continuing the EMPLOYEE example, instead of getting the portion of each employee’s salary over the all-employees total, we would like to get the portion based on the employees in the same department:

  |select
  |    id,
  |    department,
  |    salary,
  |    salary / sum(salary) OVER (PARTITION BY department) portion
  |  from employee
  |  order by id;

Results

  |id  department  salary  portion
  |--  ----------  ------  ----------
  |1   R & D        10.00      0.3448
  |2   SALES        12.00      0.6000
  |3   SALES         8.00      0.4000
  |4   R & D         9.00      0.3103
  |5   R & D        10.00      0.3448