Documentation Source Text

a "window" of one or more rows in the results set of a SELECT statement.


<tcl>
RecursiveBubbleDiagram window-function-invocation
</tcl>

<p>Window functions are distingished from ordinary SQL functions by the
<p>Window functions are distinguished from ordinary SQL functions by the
presence of an OVER clause.  If a function invocation has an OVER clause
then it is a window function, and if lacks a OVER clause it is an ordinary
function.  Window functions might also have a FILTER
clause in between the function and the OVER clause.

<p>Unlike ordinary functions, window functions
cannot use the DISTINCT keyword.

  <i>-- </i>
  SELECT x, y, row_number() OVER (ORDER BY y) AS row_number FROM t0 ORDER BY x;
</codeblock>

<p>The example uses the built-in window function row_number().
The row_number() window function
assigns a monotonically increasing integer to each
row in order of the the "ORDER BY" clause within the
row in order of the "ORDER BY" clause within the
<yynonterm>window-defn</yynonterm> (in this case "ORDER BY y"). Note that 
this does not affect the order in which results are returned from
the overall query.  The order of the final output is
is still governed by the ORDER BY clause attached to the SELECT
still governed by the ORDER BY clause attached to the SELECT
statement (in this case "ORDER BY x").

<p>Named <yynonterm>window-defn</yynonterm> clauses may also be added to a SELECT 
statement using a WINDOW clause and then refered to by name within window
statement using a WINDOW clause and then referred to by name within window
function invocations. For example, the following SELECT statement contains
two named <yynonterm>window-defs</yynonterm> clauses, "win1" and "win2":

<codeblock>
  SELECT x, y, row_number() OVER <b>win1</b>, rank() OVER <b>win2</b> 
  FROM t0 
  WINDOW <b>win1</b> AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),

         dense_rank() OVER win    AS dense_rank,
         percent_rank() OVER win  AS percent_rank,
         cume_dist() OVER win     AS cume_dist
  FROM t2
  WINDOW win AS (ORDER BY a);
</codeblock>

<p>The example below uses ntile() to divde the six rows into two groups (the
<p>The example below uses ntile() to divide the six rows into two groups (the
ntile(2) call) and into four groups (the ntile(4) call). For ntile(2), there
are three rows assigned to each group. For ntile(4), there are two groups of
two and two groups of one. The larger groups of two appear first.

<codeblock>
  <i>-- The following SELECT statement returns:</i>
  <i>-- </i>