<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="window-functions-frames">
      </a>
      14.20.3 Window Function Frame Specification
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045191912704">
  </a>
  <p>
   The definition of a window used with a window function can
        include a frame clause. A frame is a subset of the current
        partition and the frame clause specifies how to define the
        subset.
  </p>
  <p>
   Frames are determined with respect to the current row, which
        enables a frame to move within a partition depending on the
        location of the current row within its partition. Examples:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      By defining a frame to be all rows from the partition start
            to the current row, you can compute running totals for each
            row.
     </p>
    </li>
    <li class="listitem">
     <p>
      By defining a frame as extending
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      rows on either side of the
            current row, you can compute rolling averages.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The following query demonstrates the use of moving frames to
        compute running totals within each group of time-ordered
   <code class="literal">
    level
   </code>
   values, as well as rolling averages
        computed from the current row and the rows that immediately
        precede and follow it:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa48095713"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span>
         <span class="token datatype">time</span><span class="token punctuation">,</span> <span class="token keyword">subject</span><span class="token punctuation">,</span> val<span class="token punctuation">,</span>
         <span class="token function">SUM</span><span class="token punctuation">(</span>val<span class="token punctuation">)</span> <span class="token keyword">OVER</span> <span class="token punctuation">(</span><span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">subject</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token datatype">time</span>
                        <span class="token keyword">ROWS</span> <span class="token keyword">UNBOUNDED</span> <span class="token keyword">PRECEDING</span><span class="token punctuation">)</span>
           <span class="token keyword">AS</span> running_total<span class="token punctuation">,</span>
         <span class="token function">AVG</span><span class="token punctuation">(</span>val<span class="token punctuation">)</span> <span class="token keyword">OVER</span> <span class="token punctuation">(</span><span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">subject</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token datatype">time</span>
                        <span class="token keyword">ROWS</span> <span class="token operator">BETWEEN</span> <span class="token number">1</span> <span class="token keyword">PRECEDING</span> <span class="token operator">AND</span> <span class="token number">1</span> <span class="token keyword">FOLLOWING</span><span class="token punctuation">)</span>
           <span class="token keyword">AS</span> running_average
       <span class="token keyword">FROM</span> observations<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> time     <span class="token punctuation">|</span> subject <span class="token punctuation">|</span> val  <span class="token punctuation">|</span> running_total <span class="token punctuation">|</span> running_average <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:00:00 <span class="token punctuation">|</span> st113   <span class="token punctuation">|</span>   10 <span class="token punctuation">|</span>            10 <span class="token punctuation">|</span>          9.5000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:15:00 <span class="token punctuation">|</span> st113   <span class="token punctuation">|</span>    9 <span class="token punctuation">|</span>            19 <span class="token punctuation">|</span>         14.6667 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:30:00 <span class="token punctuation">|</span> st113   <span class="token punctuation">|</span>   25 <span class="token punctuation">|</span>            44 <span class="token punctuation">|</span>         18.0000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:45:00 <span class="token punctuation">|</span> st113   <span class="token punctuation">|</span>   20 <span class="token punctuation">|</span>            64 <span class="token punctuation">|</span>         22.5000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:00:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span>             0 <span class="token punctuation">|</span>          5.0000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:15:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>   10 <span class="token punctuation">|</span>            10 <span class="token punctuation">|</span>          5.0000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:30:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>            15 <span class="token punctuation">|</span>         15.0000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:45:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>   30 <span class="token punctuation">|</span>            45 <span class="token punctuation">|</span>         20.0000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 08:00:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>   25 <span class="token punctuation">|</span>            70 <span class="token punctuation">|</span>         27.5000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   For the
   <code class="literal">
    running_average
   </code>
   column, there is no
        frame row preceding the first one or following the last. In
        these cases,
   <a class="link" href="aggregate-functions.html#function_avg">
    <code class="literal">
     AVG()
    </code>
   </a>
   computes the
        average of the rows that are available.
  </p>
  <p>
   Aggregate functions used as window functions operate on rows in
        the current row frame, as do these nonaggregate window
        functions:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa33816518"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token function">FIRST_VALUE</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">LAST_VALUE</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">NTH_VALUE</span><span class="token punctuation">(</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   Standard SQL specifies that window functions that operate on the
        entire partition should have no frame clause. MySQL permits a
        frame clause for such functions but ignores it. These functions
        use the entire partition even if a frame is specified:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa99005626"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token function">CUME_DIST</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">DENSE_RANK</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">LAG</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">LEAD</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">NTILE</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">PERCENT_RANK</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">RANK</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">ROW_NUMBER</span><span class="token punctuation">(</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   The frame clause, if given, has this syntax:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3368279"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><em class="replaceable">frame_clause</em>:
    <em class="replaceable">frame_units</em> <em class="replaceable">frame_extent</em>

<em class="replaceable">frame_units</em>:
    {<span class="token keyword">ROWS</span> <span class="token operator">|</span> <span class="token keyword">RANGE</span>}</code></pre>
  </div>
  <p>
   In the absence of a frame clause, the default frame depends on
        whether an
   <code class="literal">
    ORDER BY
   </code>
   clause is present, as
        described later in this section.
  </p>
  <p>
   The
   <em class="replaceable">
    <code>
     frame_units
    </code>
   </em>
   value indicates the
        type of relationship between the current row and frame rows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       ROWS
      </code>
      : The frame is defined by beginning
            and ending row positions. Offsets are differences in row
            numbers from the current row number.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       RANGE
      </code>
      : The frame is defined by rows
            within a value range. Offsets are differences in row values
            from the current row value.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <em class="replaceable">
    <code>
     frame_extent
    </code>
   </em>
   value indicates the
        start and end points of the frame. You can specify just the
        start of the frame (in which case the current row is implicitly
        the end) or use
   <code class="literal">
    BETWEEN
   </code>
   to specify both frame
        endpoints:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90545286"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><em class="replaceable">frame_extent</em>:
    {<em class="replaceable">frame_start</em> <span class="token operator">|</span> <em class="replaceable">frame_between</em>}

<em class="replaceable">frame_between</em>:
    <span class="token operator">BETWEEN</span> <em class="replaceable">frame_start</em> <span class="token operator">AND</span> <em class="replaceable">frame_end</em>

<em class="replaceable">frame_start</em><span class="token punctuation">,</span> <em class="replaceable">frame_end</em>: {
    <span class="token keyword">CURRENT</span> <span class="token keyword">ROW</span>
  <span class="token operator">|</span> <span class="token keyword">UNBOUNDED</span> <span class="token keyword">PRECEDING</span>
  <span class="token operator">|</span> <span class="token keyword">UNBOUNDED</span> <span class="token keyword">FOLLOWING</span>
  <span class="token operator">|</span> <em class="replaceable">expr</em> <span class="token keyword">PRECEDING</span>
  <span class="token operator">|</span> <em class="replaceable">expr</em> <span class="token keyword">FOLLOWING</span>
}</code></pre>
  </div>
  <p>
   With
   <code class="literal">
    BETWEEN
   </code>
   syntax,
   <em class="replaceable">
    <code>
     frame_start
    </code>
   </em>
   must not occur later than
   <em class="replaceable">
    <code>
     frame_end
    </code>
   </em>
   .
  </p>
  <p>
   The permitted
   <em class="replaceable">
    <code>
     frame_start
    </code>
   </em>
   and
   <em class="replaceable">
    <code>
     frame_end
    </code>
   </em>
   values have these meanings:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       CURRENT ROW
      </code>
      : For
      <code class="literal">
       ROWS
      </code>
      ,
            the bound is the current row. For
      <code class="literal">
       RANGE
      </code>
      ,
            the bound is the peers of the current row.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       UNBOUNDED PRECEDING
      </code>
      : The bound is the
            first partition row.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       UNBOUNDED FOLLOWING
      </code>
      : The bound is the
            last partition row.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       PRECEDING
      </code>
      : For
      <code class="literal">
       ROWS
      </code>
      , the bound
            is
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      rows before the current
            row. For
      <code class="literal">
       RANGE
      </code>
      , the bound is the rows
            with values equal to the current row value minus
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      ; if the current row value is
      <code class="literal">
       NULL
      </code>
      , the bound is the peers of the row.
     </p>
     <p>
      For
      <code class="literal">
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       PRECEDING
      </code>
      (and
      <code class="literal">
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       FOLLOWING
      </code>
      ),
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      can be
            a
      <code class="literal">
       ?
      </code>
      parameter marker (for use in a
            prepared statement), a nonnegative numeric literal, or a
            temporal interval of the form
      <code class="literal">
       INTERVAL
       <em class="replaceable">
        <code>
         val
        </code>
       </em>
       <em class="replaceable">
        <code>
         unit
        </code>
       </em>
      </code>
      . For
      <code class="literal">
       INTERVAL
      </code>
      expressions,
      <em class="replaceable">
       <code>
        val
       </code>
      </em>
      specifies nonnegative
            interval value, and
      <em class="replaceable">
       <code>
        unit
       </code>
      </em>
      is a
            keyword indicating the units in which the value should be
            interpreted. (For details about the permitted
      <em class="replaceable">
       <code>
        units
       </code>
      </em>
      specifiers, see the
            description of the
      <a class="link" href="date-and-time-functions.html#function_date-add">
       <code class="literal">
        DATE_ADD()
       </code>
      </a>
      function in
      <a class="xref" href="date-and-time-functions.html" title="14.7 Date and Time Functions">
       Section 14.7, “Date and Time Functions”
      </a>
      .)
     </p>
     <p>
      <code class="literal">
       RANGE
      </code>
      on a numeric or temporal
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      requires
      <code class="literal">
       ORDER
            BY
      </code>
      on a numeric or temporal expression,
            respectively.
     </p>
     <p>
      Examples of valid
      <code class="literal">
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       PRECEDING
      </code>
      and
      <code class="literal">
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       FOLLOWING
      </code>
      indicators:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa2726447"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token number">10</span> <span class="token keyword">PRECEDING</span>
<span class="token keyword">INTERVAL</span> <span class="token number">5</span> <span class="token keyword">DAY</span> <span class="token keyword">PRECEDING</span>
<span class="token number">5</span> <span class="token keyword">FOLLOWING</span>
<span class="token keyword">INTERVAL</span> <span class="token string">'2:30'</span> <span class="token keyword">MINUTE_SECOND</span> <span class="token keyword">FOLLOWING</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       FOLLOWING
      </code>
      : For
      <code class="literal">
       ROWS
      </code>
      , the bound
            is
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      rows after the current
            row. For
      <code class="literal">
       RANGE
      </code>
      , the bound is the rows
            with values equal to the current row value plus
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      ; if the current row value is
      <code class="literal">
       NULL
      </code>
      , the bound is the peers of the row.
     </p>
     <p>
      For permitted values of
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      , see
            the description of
      <code class="literal">
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       PRECEDING
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   The following query demonstrates
   <a class="link" href="window-function-descriptions.html#function_first-value">
    <code class="literal">
     FIRST_VALUE()
    </code>
   </a>
   ,
   <a class="link" href="window-function-descriptions.html#function_last-value">
    <code class="literal">
     LAST_VALUE()
    </code>
   </a>
   , and two instances
        of
   <a class="link" href="window-function-descriptions.html#function_nth-value">
    <code class="literal">
     NTH_VALUE()
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa76527300"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span>
         <span class="token datatype">time</span><span class="token punctuation">,</span> <span class="token keyword">subject</span><span class="token punctuation">,</span> val<span class="token punctuation">,</span>
         <span class="token function">FIRST_VALUE</span><span class="token punctuation">(</span>val<span class="token punctuation">)</span>  <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'first'</span><span class="token punctuation">,</span>
         <span class="token function">LAST_VALUE</span><span class="token punctuation">(</span>val<span class="token punctuation">)</span>   <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'last'</span><span class="token punctuation">,</span>
         <span class="token function">NTH_VALUE</span><span class="token punctuation">(</span>val<span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'second'</span><span class="token punctuation">,</span>
         <span class="token function">NTH_VALUE</span><span class="token punctuation">(</span>val<span class="token punctuation">,</span> <span class="token number">4</span><span class="token punctuation">)</span> <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'fourth'</span>
       <span class="token keyword">FROM</span> observations
       <span class="token keyword">WINDOW</span> w <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">subject</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token datatype">time</span>
                    <span class="token keyword">ROWS</span> <span class="token keyword">UNBOUNDED</span> <span class="token keyword">PRECEDING</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> time     <span class="token punctuation">|</span> subject <span class="token punctuation">|</span> val  <span class="token punctuation">|</span> first <span class="token punctuation">|</span> last <span class="token punctuation">|</span> second <span class="token punctuation">|</span> fourth <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:00:00 <span class="token punctuation">|</span> st113   <span class="token punctuation">|</span>   10 <span class="token punctuation">|</span>    10 <span class="token punctuation">|</span>   10 <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:15:00 <span class="token punctuation">|</span> st113   <span class="token punctuation">|</span>    9 <span class="token punctuation">|</span>    10 <span class="token punctuation">|</span>    9 <span class="token punctuation">|</span>      9 <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:30:00 <span class="token punctuation">|</span> st113   <span class="token punctuation">|</span>   25 <span class="token punctuation">|</span>    10 <span class="token punctuation">|</span>   25 <span class="token punctuation">|</span>      9 <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:45:00 <span class="token punctuation">|</span> st113   <span class="token punctuation">|</span>   20 <span class="token punctuation">|</span>    10 <span class="token punctuation">|</span>   20 <span class="token punctuation">|</span>      9 <span class="token punctuation">|</span>     20 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:00:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span>     0 <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:15:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>   10 <span class="token punctuation">|</span>     0 <span class="token punctuation">|</span>   10 <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:30:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>     0 <span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:45:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>   30 <span class="token punctuation">|</span>     0 <span class="token punctuation">|</span>   30 <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span>     30 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 08:00:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>   25 <span class="token punctuation">|</span>     0 <span class="token punctuation">|</span>   25 <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span>     30 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Each function uses the rows in the current frame, which, per the
        window definition shown, extends from the first partition row to
        the current row. For the
   <a class="link" href="window-function-descriptions.html#function_nth-value">
    <code class="literal">
     NTH_VALUE()
    </code>
   </a>
   calls, the current
        frame does not always include the requested row; in such cases,
        the return value is
   <code class="literal">
    NULL
   </code>
   .
  </p>
  <p>
   In the absence of a frame clause, the default frame depends on
        whether an
   <code class="literal">
    ORDER BY
   </code>
   clause is present:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      With
      <code class="literal">
       ORDER BY
      </code>
      : The default frame includes
            rows from the partition start through the current row,
            including all peers of the current row (rows equal to the
            current row according to the
      <code class="literal">
       ORDER BY
      </code>
      clause). The default is equivalent to this frame
            specification:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa33025844"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">RANGE</span> <span class="token operator">BETWEEN</span> <span class="token keyword">UNBOUNDED</span> <span class="token keyword">PRECEDING</span> <span class="token operator">AND</span> <span class="token keyword">CURRENT</span> <span class="token keyword">ROW</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Without
      <code class="literal">
       ORDER BY
      </code>
      : The default frame
            includes all partition rows (because, without
      <code class="literal">
       ORDER
            BY
      </code>
      , all partition rows are peers). The default is
            equivalent to this frame specification:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa20547863"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">RANGE</span> <span class="token operator">BETWEEN</span> <span class="token keyword">UNBOUNDED</span> <span class="token keyword">PRECEDING</span> <span class="token operator">AND</span> <span class="token keyword">UNBOUNDED</span> <span class="token keyword">FOLLOWING</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   Because the default frame differs depending on presence or
        absence of
   <code class="literal">
    ORDER BY
   </code>
   , adding
   <code class="literal">
    ORDER
        BY
   </code>
   to a query to get deterministic results may change
        the results. (For example, the values produced by
   <a class="link" href="aggregate-functions.html#function_sum">
    <code class="literal">
     SUM()
    </code>
   </a>
   might change.) To obtain
        the same results but ordered per
   <code class="literal">
    ORDER BY
   </code>
   ,
        provide an explicit frame specification to be used regardless of
        whether
   <code class="literal">
    ORDER BY
   </code>
   is present.
  </p>
  <p>
   The meaning of a frame specification can be nonobvious when the
        current row value is
   <code class="literal">
    NULL
   </code>
   . Assuming that to
        be the case, these examples illustrate how various frame
        specifications apply:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND 15
            FOLLOWING
      </code>
     </p>
     <p>
      The frame starts at
      <code class="literal">
       NULL
      </code>
      and stops at
      <code class="literal">
       NULL
      </code>
      , thus includes only rows with value
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND
            UNBOUNDED FOLLOWING
      </code>
     </p>
     <p>
      The frame starts at
      <code class="literal">
       NULL
      </code>
      and stops at the
            end of the partition. Because an
      <code class="literal">
       ASC
      </code>
      sort
            puts
      <code class="literal">
       NULL
      </code>
      values first, the frame is the
            entire partition.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ORDER BY X DESC RANGE BETWEEN 10 FOLLOWING AND
            UNBOUNDED FOLLOWING
      </code>
     </p>
     <p>
      The frame starts at
      <code class="literal">
       NULL
      </code>
      and stops at the
            end of the partition. Because a
      <code class="literal">
       DESC
      </code>
      sort
            puts
      <code class="literal">
       NULL
      </code>
      values last, the frame is only
            the
      <code class="literal">
       NULL
      </code>
      values.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND
            UNBOUNDED FOLLOWING
      </code>
     </p>
     <p>
      The frame starts at
      <code class="literal">
       NULL
      </code>
      and stops at the
            end of the partition. Because an
      <code class="literal">
       ASC
      </code>
      sort
            puts
      <code class="literal">
       NULL
      </code>
      values first, the frame is the
            entire partition.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 10
            FOLLOWING
      </code>
     </p>
     <p>
      The frame starts at
      <code class="literal">
       NULL
      </code>
      and stops at
      <code class="literal">
       NULL
      </code>
      , thus includes only rows with value
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 1
            PRECEDING
      </code>
     </p>
     <p>
      The frame starts at
      <code class="literal">
       NULL
      </code>
      and stops at
      <code class="literal">
       NULL
      </code>
      , thus includes only rows with value
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ORDER BY X ASC RANGE BETWEEN UNBOUNDED PRECEDING
            AND 10 FOLLOWING
      </code>
     </p>
     <p>
      The frame starts at the beginning of the partition and stops
            at rows with value
      <code class="literal">
       NULL
      </code>
      . Because an
      <code class="literal">
       ASC
      </code>
      sort puts
      <code class="literal">
       NULL
      </code>
      values first, the frame is only the
      <code class="literal">
       NULL
      </code>
      values.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
