Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Observar 1
Favorito 0
Wiki 捐赠
gooderp18绿色标准版
Você não pode selecionar mais de 25 tópicos Os tópicos devem começar com uma letra ou um número, podem incluir traços ('-') e podem ter até 35 caracteres.
Branch: master
Branches Tags
${ item.name }
Criar branch ${ searchTerm }
de master
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/functions-window.html

169 linhas
13KB
Original Link permanente Anotar Histórico 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.21. Window Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="functions-aggregate.html" title="9.20. Aggregate Functions" /><link rel="next" href="functions-subquery.html" title="9.22. Subquery Expressions" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.21. Window Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-aggregate.html" title="9.20. Aggregate Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-subquery.html" title="9.22. Subquery Expressions">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-WINDOW"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.21. Window Functions</h2></div></div></div><a id="id-1.5.8.26.2" class="indexterm"></a><p>

  3.    <em class="firstterm">Window functions</em> provide the ability to perform

  4.    calculations across sets of rows that are related to the current query

  5.    row.  See <a class="xref" href="tutorial-window.html" title="3.5. Window Functions">Section 3.5</a> for an introduction to this

  6.    feature, and <a class="xref" href="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" title="4.2.8. Window Function Calls">Section 4.2.8</a> for syntax

  7.    details.

  8.   </p><p>

  9.    The built-in window functions are listed in

  10.    <a class="xref" href="functions-window.html#FUNCTIONS-WINDOW-TABLE" title="Table 9.60. General-Purpose Window Functions">Table 9.60</a>.  Note that these functions

  11.    <span class="emphasis"><em>must</em></span> be invoked using window function syntax, i.e., an

  12.    <code class="literal">OVER</code> clause is required.

  13.   </p><p>

  14.    In addition to these functions, any built-in or user-defined

  15.    general-purpose or statistical

  16.    aggregate (i.e., not ordered-set or hypothetical-set aggregates)

  17.    can be used as a window function; see

  18.    <a class="xref" href="functions-aggregate.html" title="9.20. Aggregate Functions">Section 9.20</a> for a list of the built-in aggregates.

  19.    Aggregate functions act as window functions only when an <code class="literal">OVER</code>

  20.    clause follows the call; otherwise they act as non-window aggregates

  21.    and return a single row for the entire set.

  22.   </p><div class="table" id="FUNCTIONS-WINDOW-TABLE"><p class="title"><strong>Table 9.60. General-Purpose Window Functions</strong></p><div class="table-contents"><table class="table" summary="General-Purpose Window Functions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th></tr></thead><tbody><tr><td>

  23.        <a id="id-1.5.8.26.6.2.2.1.1.1" class="indexterm"></a>

  24.        <code class="function">row_number()</code>

  25.       </td><td>

  26.        <code class="type">bigint</code>

  27.       </td><td>number of the current row within its partition, counting from 1</td></tr><tr><td>

  28.        <a id="id-1.5.8.26.6.2.2.2.1.1" class="indexterm"></a>

  29.        <code class="function">rank()</code>

  30.       </td><td>

  31.        <code class="type">bigint</code>

  32.       </td><td>rank of the current row with gaps; same as <code class="function">row_number</code> of its first peer</td></tr><tr><td>

  33.        <a id="id-1.5.8.26.6.2.2.3.1.1" class="indexterm"></a>

  34.        <code class="function">dense_rank()</code>

  35.       </td><td>

  36.        <code class="type">bigint</code>

  37.       </td><td>rank of the current row without gaps; this function counts peer groups</td></tr><tr><td>

  38.        <a id="id-1.5.8.26.6.2.2.4.1.1" class="indexterm"></a>

  39.        <code class="function">percent_rank()</code>

  40.       </td><td>

  41.        <code class="type">double precision</code>

  42.       </td><td>relative rank of the current row: (<code class="function">rank</code> - 1) / (total partition rows - 1)</td></tr><tr><td>

  43.        <a id="id-1.5.8.26.6.2.2.5.1.1" class="indexterm"></a>

  44.        <code class="function">cume_dist()</code>

  45.       </td><td>

  46.        <code class="type">double precision</code>

  47.       </td><td>cumulative distribution: (number of partition rows preceding or peer with current row) / total partition rows</td></tr><tr><td>

  48.        <a id="id-1.5.8.26.6.2.2.6.1.1" class="indexterm"></a>

  49.        <code class="function">ntile(<em class="replaceable"><code>num_buckets</code></em> <code class="type">integer</code>)</code>

  50.       </td><td>

  51.        <code class="type">integer</code>

  52.       </td><td>integer ranging from 1 to the argument value, dividing the

  53.        partition as equally as possible</td></tr><tr><td>

  54.        <a id="id-1.5.8.26.6.2.2.7.1.1" class="indexterm"></a>

  55.        <code class="function">

  56.          lag(<em class="replaceable"><code>value</code></em> <code class="type">anyelement</code>

  57.              [, <em class="replaceable"><code>offset</code></em> <code class="type">integer</code>

  58.              [, <em class="replaceable"><code>default</code></em> <code class="type">anyelement</code> ]])

  59.        </code>

  60.       </td><td>

  61.        <code class="type">same type as <em class="replaceable"><code>value</code></em></code>

  62.       </td><td>

  63.        returns <em class="replaceable"><code>value</code></em> evaluated at

  64.        the row that is <em class="replaceable"><code>offset</code></em>

  65.        rows before the current row within the partition; if there is no such

  66.        row, instead return <em class="replaceable"><code>default</code></em>

  67.        (which must be of the same type as

  68.        <em class="replaceable"><code>value</code></em>).

  69.        Both <em class="replaceable"><code>offset</code></em> and

  70.        <em class="replaceable"><code>default</code></em> are evaluated

  71.        with respect to the current row.  If omitted,

  72.        <em class="replaceable"><code>offset</code></em> defaults to 1 and

  73.        <em class="replaceable"><code>default</code></em> to null

  74.       </td></tr><tr><td>

  75.        <a id="id-1.5.8.26.6.2.2.8.1.1" class="indexterm"></a>

  76.        <code class="function">

  77.          lead(<em class="replaceable"><code>value</code></em> <code class="type">anyelement</code>

  78.               [, <em class="replaceable"><code>offset</code></em> <code class="type">integer</code>

  79.               [, <em class="replaceable"><code>default</code></em> <code class="type">anyelement</code> ]])

  80.        </code>

  81.       </td><td>

  82.        <code class="type">same type as <em class="replaceable"><code>value</code></em></code>

  83.       </td><td>

  84.        returns <em class="replaceable"><code>value</code></em> evaluated at

  85.        the row that is <em class="replaceable"><code>offset</code></em>

  86.        rows after the current row within the partition; if there is no such

  87.        row, instead return <em class="replaceable"><code>default</code></em>

  88.        (which must be of the same type as

  89.        <em class="replaceable"><code>value</code></em>).

  90.        Both <em class="replaceable"><code>offset</code></em> and

  91.        <em class="replaceable"><code>default</code></em> are evaluated

  92.        with respect to the current row.  If omitted,

  93.        <em class="replaceable"><code>offset</code></em> defaults to 1 and

  94.        <em class="replaceable"><code>default</code></em> to null

  95.       </td></tr><tr><td>

  96.        <a id="id-1.5.8.26.6.2.2.9.1.1" class="indexterm"></a>

  97.        <code class="function">first_value(<em class="replaceable"><code>value</code></em> <code class="type">any</code>)</code>

  98.       </td><td>

  99.        <code class="type">same type as <em class="replaceable"><code>value</code></em></code>

  100.       </td><td>

  101.        returns <em class="replaceable"><code>value</code></em> evaluated

  102.        at the row that is the first row of the window frame

  103.       </td></tr><tr><td>

  104.        <a id="id-1.5.8.26.6.2.2.10.1.1" class="indexterm"></a>

  105.        <code class="function">last_value(<em class="replaceable"><code>value</code></em> <code class="type">any</code>)</code>

  106.       </td><td>

  107.        <code class="type">same type as <em class="replaceable"><code>value</code></em></code>

  108.       </td><td>

  109.        returns <em class="replaceable"><code>value</code></em> evaluated

  110.        at the row that is the last row of the window frame

  111.       </td></tr><tr><td>

  112.        <a id="id-1.5.8.26.6.2.2.11.1.1" class="indexterm"></a>

  113.        <code class="function">

  114.          nth_value(<em class="replaceable"><code>value</code></em> <code class="type">any</code>, <em class="replaceable"><code>nth</code></em> <code class="type">integer</code>)

  115.        </code>

  116.       </td><td>

  117.        <code class="type">same type as <em class="replaceable"><code>value</code></em></code>

  118.       </td><td>

  119.        returns <em class="replaceable"><code>value</code></em> evaluated

  120.        at the row that is the <em class="replaceable"><code>nth</code></em>

  121.        row of the window frame (counting from 1); null if no such row

  122.       </td></tr></tbody></table></div></div><br class="table-break" /><p>

  123.    All of the functions listed in

  124.    <a class="xref" href="functions-window.html#FUNCTIONS-WINDOW-TABLE" title="Table 9.60. General-Purpose Window Functions">Table 9.60</a> depend on the sort ordering

  125.    specified by the <code class="literal">ORDER BY</code> clause of the associated window

  126.    definition.  Rows that are not distinct when considering only the

  127.    <code class="literal">ORDER BY</code> columns are said to be <em class="firstterm">peers</em>.

  128.    The four ranking functions (including <code class="function">cume_dist</code>) are

  129.    defined so that they give the same answer for all peer rows.

  130.   </p><p>

  131.    Note that <code class="function">first_value</code>, <code class="function">last_value</code>, and

  132.    <code class="function">nth_value</code> consider only the rows within the <span class="quote">“<span class="quote">window

  133.    frame</span>”</span>, which by default contains the rows from the start of the

  134.    partition through the last peer of the current row.  This is

  135.    likely to give unhelpful results for <code class="function">last_value</code> and

  136.    sometimes also <code class="function">nth_value</code>.  You can redefine the frame by

  137.    adding a suitable frame specification (<code class="literal">RANGE</code>,

  138.    <code class="literal">ROWS</code> or <code class="literal">GROUPS</code>) to

  139.    the <code class="literal">OVER</code> clause.

  140.    See <a class="xref" href="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" title="4.2.8. Window Function Calls">Section 4.2.8</a> for more information

  141.    about frame specifications.

  142.   </p><p>

  143.    When an aggregate function is used as a window function, it aggregates

  144.    over the rows within the current row's window frame.

  145.    An aggregate used with <code class="literal">ORDER BY</code> and the default window frame

  146.    definition produces a <span class="quote">“<span class="quote">running sum</span>”</span> type of behavior, which may or

  147.    may not be what's wanted.  To obtain

  148.    aggregation over the whole partition, omit <code class="literal">ORDER BY</code> or use

  149.    <code class="literal">ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</code>.

  150.    Other frame specifications can be used to obtain other effects.

  151.   </p><div class="note"><h3 class="title">Note</h3><p>

  152.     The SQL standard defines a <code class="literal">RESPECT NULLS</code> or

  153.     <code class="literal">IGNORE NULLS</code> option for <code class="function">lead</code>, <code class="function">lag</code>,

  154.     <code class="function">first_value</code>, <code class="function">last_value</code>, and

  155.     <code class="function">nth_value</code>.  This is not implemented in

  156.     <span class="productname">PostgreSQL</span>: the behavior is always the

  157.     same as the standard's default, namely <code class="literal">RESPECT NULLS</code>.

  158.     Likewise, the standard's <code class="literal">FROM FIRST</code> or <code class="literal">FROM LAST</code>

  159.     option for <code class="function">nth_value</code> is not implemented: only the

  160.     default <code class="literal">FROM FIRST</code> behavior is supported.  (You can achieve

  161.     the result of <code class="literal">FROM LAST</code> by reversing the <code class="literal">ORDER BY</code>

  162.     ordering.)

  163.    </p></div><p>

  164.    <code class="function">cume_dist</code> computes the fraction of partition rows that

  165.    are less than or equal to the current row and its peers, while

  166.    <code class="function">percent_rank</code> computes the fraction of partition rows that

  167.    are less than the current row, assuming the current row does not exist

  168.    in the partition.

  169.   </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-aggregate.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-subquery.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.20. Aggregate Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 9.22. Subquery Expressions</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1