The syntax of the window functions is as follows:

<function_name> (<function_arguments>)
OVER(
  [PARTITION BY <expression_list>]
  [ORDER BY <order_by_list>]
  [{ROWS | RANGE} <frame_start> |
  {ROWS | RANGE} BETWEEN <frame_start> AND <frame_end>])

The construct in the parentheses after the OVER keyword is called the window definition. The last part of the window definition, which starts with ROWS, is called the frame clause. The syntax of frame_start and frame_end will be described later.

Window functions, in general, work like aggregating functions. They process sets of records. These sets are built separately for each processed record. That's why, unlike the normal aggregating functions, window functions are evaluated for each row.

For each record, a set of rows to be processed by a window function is built in the following way.

At the beginning, the PARTITION BY clause is processed. All the records that have the same values after evaluating the expressions from the expression_list as the current row is taken. The set of these rows is called the partition. The current row is also included in the partition. In fact, the PARTITION BY clause has the same logic and syntax as the GROUP BY clause of the SELECT statement, except that it is not possible to refer to the output column names or numbers in PARTITION BY.

In other words, while processing each record, a window function will take a look at all the other records to check if any of them falls into the same partition as the current one. If no PARTITION BY is specified, it means that all the rows will be included in a single partition at this step.

Next, the partition is sorted according to the ORDER BY clause, which has the same syntax and logic as the ORDER BY clause in the SELECT statement. Again, no references to the output column names or numbers are allowed here. If the ORDER BY clause is omitted, then all the records of the set are considered to have the same position.

In the end, the frame clause is processed. It means taking a subset from the whole partition to pass it to the window function.

The subset is called the window frame. The frame has its starting and ending points. The start of the frame, which is referenced by frame_start in the preceding syntax diagram, can be any of the following:

• UNBOUNDED PRECEDING: The very first record of the partition.
• <N> PRECEDING: A record that is placed N records before the current one in the ordered partition. <N> is an integer expression that cannot return a negative value and which cannot use aggregating functions or other window functions. 0 PRECEDING points to the current row.
• CURRENT ROW: The current row itself.
• <N> FOLLOWING: A record that is placed N records after the current record in the ordered partition.

The ending point--frame_end--can be any one of the following:

• <N> PRECEDING
• CURRENT ROW
• <N> FOLLOWING
• UNBOUNDED FOLLOWING, the very last record of the partition

The starting point should precede the ending point. That's why, for example, ROWS BETWEEN CURRENT ROW AND 1 PRECEDING is not correct.

A window frame can be defined using the ROWS mode or the RANGE mode. It affects the meaning of the CURRENT ROW. In the ROWS mode, the CURRENT ROW points to the current record itself. In the RANGE mode, the CURRENT ROW points to the first or to the last record that has the same position when sorted according to the ORDER BY clause.

First or last will be chosen with a view to make the frame wider. In the RANGE mode only UNBOUNDED ... is used or CURRENT ROW can be used in the frame clause, <N> PRECEDING will not work.

If frame_end is omitted, then CURRENT ROW is used instead.

If the whole frame clause is omitted, then the frame will be built using the RANGE UNBOUNDED PRECEDING definition.

Look at the following example of a window definition:

OVER (PARTITION BY a ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND 5 FOLLOWING)

The preceding definition means that for every row, all the records with the same value of the field a will form the partition. Then, the partition will be ordered in an ascending manner by the values of the field b, and the frame will contain all the records from the first one to the fifth following the current row.