Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (中文) » SQL Anywhere 服务器 - SQL 的用法 » 查询和修改数据 » OLAP 支持 » 窗口函数

 

定义窗口

可以使用 SQL 窗口扩展来配置窗口边界,以及输入行的分区和排序。逻辑上,分区作为计算查询说明结果的语义的一部分,将在 GROUP BY 子句所定义的组创建后,在对最终 SELECT 列表和查询的 ORDER BY 子句进行计算之前创建。因此,SQL 语句中子句的计算顺序为:

  1. FROM

  2. WHERE

  3. GROUP BY

  4. HAVING

  5. WINDOW

  6. DISTINCT

  7. ORDER BY

构建查询时,应考虑计算顺序的影响。例如,在引用同一 SELECT 查询块中窗口函数的表达式中不能没有谓语。然而,通过在派生表中放置查询块,就可以在派生表中指定谓语。以下查询失败,并发出一条消息,指出导致失败的原因是在窗口函数中指定了谓语:

SELECT DepartmentID, Surname, StartDate, Salary,
       SUM( Salary ) OVER ( PARTITION BY DepartmentID
          ORDER BY StartDate
          RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS "Sum_Salary" 
   FROM Employees
   WHERE State IN ( 'CA', 'UT', 'NY', 'AZ' ) 
      AND DepartmentID IN ( '100', '200' )
   GROUP BY DepartmentID, Surname, StartDate, Salary
   HAVING Salary > 0 AND "Sum_Salary" > 200
   ORDER BY DepartmentID, StartDate;

使用派生表 (DT) 并在其中指定谓语以获得所需的结果:

SELECT * FROM ( SELECT DepartmentID, Surname, StartDate, Salary,
                       SUM( Salary ) OVER ( PARTITION BY DepartmentID
                         ORDER BY StartDate
                         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS "Sum_Salary"
                  FROM Employees
                  WHERE State IN ( 'CA', 'UT', 'NY', 'AZ' ) 
                     AND DepartmentID IN ( '100', '200' )
                  GROUP BY DepartmentID, Surname, StartDate, Salary
                  HAVING Salary > 0
                  ORDER BY DepartmentID, StartDate ) AS DT
   WHERE DT.Sum_Salary > 200;

因为窗口分区跟在 GROUP BY 运算符之后,所以任何集合函数(如 SUM、AVG 或 VARIANCE)的结果均可用于针对分区所进行的计算。因此,除了通过查询的 GROUP BY 和 ORDER BY 子句执行分组和排序操作外,窗口还提供了另外一种方法。

定义窗口说明

定义在其上运行窗口函数的窗口时,应指定以下一项或多项:

  • 分区(PARTITION BY 子句)   PARTITION BY 子句定义输入行的分组方式。如果省略,则将整个输入视为单个分区。分区可以是一个、多个或所有输入行,这视您指定的内容而定。来自两个分区的数据从不混合。也就是说,当窗口到达两分区间的边界时,它先处理完一个分区中的数据,然后再开始下一分区中数据的处理。这意味着窗口大小在分区的开始和结束处可能有所变化,这要根据窗口边界如何进行的定义而定。

  • 排序(ORDER BY 子句)   ORDER BY 子句定义输入行在由窗口函数处理之前如何进行排序。ORDER BY 子句仅在使用 RANGE 子句指定边界时或秩函数引用窗口时才必须使用。否则,ORDER BY 子句是可选的。如果省略,数据库服务器将以最有效的方式处理输入行。

  • 边界(RANGE 和 ROWS 子句)   当前行为确定窗口的开始行和结束行提供参考点。可以使用窗口定义的 RANGE 和 ROWS 子句设置这些边界。RANGE 根据偏移当前行值的数据值范围 定义窗口。因此,如果指定 RANGE,还必须指定 ORDER BY 子句,因为范围计算要求对数据进行排序。

    ROWS 根据偏移当前行的行数 定义窗口。

    由于 RANGE 根据数据值范围定义一组行,所以 RANGE 窗口中包含的行可包括超出当前行的行。这与 ROWS 的处理方式有所不同。下图说明了 ROWS 子句与 RANGE 子句之间的差异:

    在 ROWS 说明与 RANGE 说明相似的情况下,比较二者的窗口大小,可发现后者包含的行更多。
    在 ROWS 和 RANGE 子句中,可以(有选择地)指定窗口的开始行和结束行(相对于当前行)。为此,可使用 PRECEDING、BETWEEN 和 FOLLOWING 子句。这些子句采用表达式以及关键字 UNBOUNDED 和 CURRENT ROW。如果未给窗口定义边界,则缺省的窗口边界设置如下:
    • 如果窗口说明中含有 ORDER BY 子句,则相当于指定 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

    • 如果窗口说明中不含有 ORDER BY 子句,则相当于指定 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

    下表包含了一些示例窗口边界及其所包含的行的说明:

    说明 含义

    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    从分区起始处开始,以当前行结束。当计算累计结果(如累计总和)时使用此示例。

    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

    使用分区中的所有行。当希望集合函数的值与分区中每行相同时,使用此示例。

    ROWS BETWEEN x PRECEDING AND y FOLLOWING

    创建一个大小固定的移动窗口,窗口的行从距离当前行 x 处开始,在距离当前行 y 处结束(包括起始行和结束行)。在您要计算移动平均值或者要计算相邻行的差值时,可使用此示例。

    对于包括多个行的移动窗口,当计算分区中第一行或最后一行时会出现 NULL 值。之所以出现这种情况,是因为当前行是分区的第一行或最后一行时,该行没有相应的上一行或下一行可以在计算中使用。因此,使用 NULL 值来代替。

    ROWS BETWEEN CURRENT ROW AND CURRENT ROW

    具有一行(即当前行)的窗口。

    RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING

    创建一个以行中的值为基础的窗口。例如,假定 ORDER BY 子句为当前行所指定的列中包含值 10。如果指定窗口大小为 RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING,则所指定的窗口大小,将会确保窗口中的第一行在列中包含 5,最后一行在列中包含 15。窗口在分区中向下移动时,窗口大小可能会相应满足范围说明所需的尺寸而增大或缩小。

    使窗口说明尽可能明确。否则,缺省值可能不会返回期望的结果。

    在一组值不连续时,使用 RANGE 子句可以避免由于窗口函数的输入有间距而产生的问题。使用 RANGE 子句设置窗口边界时,数据库服务器会自动处理相邻行以及包含重复值的行。

    RANGE 使用无符号的整数值。根据 ORDER BY 表达式的域和 RANGE 子句所指定的值域而定,可能会出现截断范围表达式的情况。

    当使用秩函数或行编号函数时,不要指定窗口边界。