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

SQL Anywhere 12.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 子句执行分组和排序操作外,窗口还提供了另外一种方法。

 定义窗口说明