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 (
rather than window_name
...)OVER
, the named window can be modified by the addition of other clauses. For example, this query defines a window that includes partitioning, and uses window_name
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 specifiesPARTITION BY
for a named window that already hasPARTITION 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
子句,则该 子句位于HAVING
andORDER 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_name
ORDER BY
OVER
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
引用窗口名称的 子句也不能包含相同类型的属性,否则会发生错误:
-
允许使用此构造,因为窗口定义和referring
OVER
子句不包含相同类型的属性: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)