• 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]

    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)
     
    如有错误,恳求读者指出,发送到wu13213786609@outlook.com。
  • 相关阅读:
    暴雪HASH算法(转)
    实现的一些内存辅助操作函数
    仿SGI STL的traits技法
    c++实现kd树
    利用libpcap打印ip包
    在使用cognos时遇到的问题记录帖
    DW,DM,ODS的区别
    解决问题的8种职业能力
    ASP.NET网站怎么发布 Web项目程序怎么发布部署(暂时收藏)
    关于IIS和.NET 4.0的问题总结(转)
  • 原文地址:https://www.cnblogs.com/WLCYSYS/p/Named_Windows.html
Copyright © 2020-2023  润新知