12.21.4命名为Windows

12.21.4 Named Windows

Windows can be defined and given names by which to refer to them in OVER clauses. To do this, use a WINDOW clause. If present in a query, the WINDOW clause falls between the positions of the HAVING and ORDER BY clauses, and has this syntax:

WINDOW window_name AS (window_spec)
    [, window_name AS (window_spec)] ...

For each window definition, window_name is the window name, and window_spec is the same type of window specification as given between the parentheses of an OVER clause, as described in Section 12.21.2, “Window Function Concepts and Syntax”:

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

A WINDOW clause is useful for queries in which multiple OVER clauses would otherwise define the same window. Instead, you can define the window once, give it a name, and refer to the name in the OVER clauses. Consider this query, which defines the same window multiple times:

SELECT
  val,
  ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',
  RANK()       OVER (ORDER BY val) AS 'rank',
  DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
FROM numbers;

The query can be written more simply by using WINDOW to define the window once and referring to the window by name in the OVER clauses:

SELECT
  val,
  ROW_NUMBER() OVER w AS 'row_number',
  RANK()       OVER w AS 'rank',
  DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);

A named window also makes it easier to experiment with the window definition to see the effect on query results. You need only modify the window definition in the WINDOW clause, rather than multiple OVER clause definitions.

If an OVER clause uses OVER (window_name ...) rather than OVER window_name, the named window can be modified by the addition of other clauses. For example, this query defines a window that includes partitioning, and uses ORDER BY in the OVER clauses to modify the window in different ways:

SELECT
  DISTINCT year, country,
  FIRST_VALUE(year) OVER (w ORDER BY year ASC) AS first,
  FIRST_VALUE(year) OVER (w ORDER BY year DESC) AS last
FROM sales
WINDOW w AS (PARTITION BY country);

An OVER clause can only add properties to a named window, not modify them. If the named window definition includes a partitioning, ordering, or framing property, the OVER clause that refers to the window name cannot also include the same kind of property or an error occurs:

• This construct is permitted because the window definition and the referring OVER clause do not contain the same kind of properties:

  OVER (w ORDER BY country)
  ... WINDOW w AS (PARTITION BY country)

• This construct is not permitted because the OVER clause specifies PARTITION BY for a named window that already has PARTITION BY:

  OVER (w PARTITION BY year)
  ... WINDOW w AS (PARTITION BY country)

The definition of a named window can itself begin with a window_name. In such cases, forward and backward references are permitted, but not cycles:

• This is permitted; it contains forward and backward references but no cycles:

  WINDOW w1 AS (w2), w2 AS (), w3 AS (w1)

• This is not permitted because it contains a cycle:

  WINDOW w1 AS (w2), w2 AS (w3), w3 AS (w1)

12.21.4命名为Windows

可以定义Windows并为其指定名称，以便在OVER子句中引用它们。为此，请使用 WINDOW子句。如果查询中存在WINDOW子句，则该 子句位于HAVINGandORDER BY 子句的位置之间，并具有以下语法：

WINDOW window_name AS (window_spec)
    [, window_name AS (window_spec)] ...

对于每个窗口定义， window_name都是窗口名称，并且 window_spec是与OVER子句括号之间给出的窗口规范类型相同的窗口规范 ，如 第12.21.2节“窗口函数的概念和语法”中所述：

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

甲WINDOW子句对于其中多个查询是有用OVER的条款，否则定义相同的窗口。相反，您可以定义一次窗口，为其命名，然后在OVER子句中引用该名称 。考虑一下此查询，它多次定义了相同的窗口：

SELECT
  val,
  ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',
  RANK()       OVER (ORDER BY val) AS 'rank',
  DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
FROM numbers;

通过使用WINDOW一次定义窗口并在OVER 子句中按名称引用窗口，可以更简单地编写查询 ：

SELECT
  val,
  ROW_NUMBER() OVER w AS 'row_number',
  RANK()       OVER w AS 'rank',
  DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);

命名窗口还使您更容易尝试使用窗口定义来查看对查询结果的影响。您只需要修改WINDOW子句中的窗口定义，而不需要修改 多个 OVER子句定义。

如果OVER子句使用而不是，则可以通过添加其他子句来修改命名窗口。例如，此查询定义一个包含分区的窗口，并在 子句中使用该窗口以不同方式修改该窗口： OVER (window_name ...)OVER window_nameORDER BYOVER

SELECT
  DISTINCT year, country,
  FIRST_VALUE(year) OVER (w ORDER BY year ASC) AS first,
  FIRST_VALUE(year) OVER (w ORDER BY year DESC) AS last
FROM sales
WINDOW w AS (PARTITION BY country);

一个OVER子句只能添加属性命名的窗口，不能修改它们。如果命名的窗口定义包含分区，排序或框架属性，则OVER引用窗口名称的 子句也不能包含相同类型的属性，否则会发生错误：

• 允许使用此构造，因为窗口定义和referringOVER子句不包含相同类型的属性：

  OVER (w ORDER BY country)
  ... WINDOW w AS (PARTITION BY country)

• 不允许使用此构造，因为该 OVER子句指定PARTITION BY已经具有的命名窗口 PARTITION BY：

  OVER (w PARTITION BY year)
  ... WINDOW w AS (PARTITION BY country)

命名窗口的定义本身可以以开头 window_name。在这种情况下，允许向前和向后引用，但不允许循环：

• 这是允许的；它包含前向和后向引用，但没有循环：

  WINDOW w1 AS (w2), w2 AS (), w3 AS (w1)

• 这是不允许的，因为它包含一个循环：

  WINDOW w1 AS (w2), w2 AS (w3), w3 AS (w1)