• 深入浅出谈开窗函数(一)


            在开窗函数出现之前存在着非常多用 SQL 语句非常难解决的问题,非常多都要通过复杂的相关子查询或者存储过程来完毕。为了解决这些问题,在2003年ISO  SQL标准添�了开窗函数,开窗函数的使用使得这些经典的难题能够被轻松的解决。眼下在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持,只是非常遗憾的是 MYSQL 临时还未对开窗函数给予支持。 

    为了更加清楚地理解,我们来建表并进行相关的查询(截图为MSSQLServer中的结果)

            MYSQL,MSSQLServer,DB2: 
            

    CREATE TABLE T_Person 
    (
    	FName VARCHAR(20),
    	FCity VARCHAR(20), 
    	FAge INT,
    	FSalary INT
    ) 


            Oracle: 
            CREATE TABLE T_Person (FName VARCHAR2(20),FCity VARCHAR2(20), FAge INT,FSalary INT) 

    注:下面结果仅仅在MSSQLServer中演示:

    T_Person 表保存了人员信息,FName 字段为人员姓名,FCity 字段为人员所在的城市名,

    FAge  字段为人员年龄,FSalary 字段为人员工资。然后运行以下的SQL语句向 T_Person

    表中插入一些演示数据: 

           

    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('Tom','BeiJing',20,3000); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('Tim','ChengDu',21,4000); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('Jim','BeiJing',22,3500); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('Lily','London',21,2000); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('John','NewYork',22,1000); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('YaoMing','BeiJing',20,3000); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('Swing','London',22,2000); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('Guo','NewYork',20,2800); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('YuQian','BeiJing',24,8000); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('Ketty','London',25,8500); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('Kitty','ChengDu',25,3000); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('Merry','BeiJing',23,3500); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('Smith','ChengDu',30,3000); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)
    VALUES('Bill','BeiJing',25,2000); 
    INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
    VALUES('Jerry','NewYork',24,3300); 
    

    查看表中的内容:

    select * from T_Person

    开窗函数简单介绍 
     
    合函数一样,开窗函数也是对行集组进行聚合计算,可是它不像普通聚合函数那样

    每组仅仅返回一个值,开窗函数能够为每组返回多个值,由于开窗函数所运行聚合计算的行

    集组是窗体。在ISO SQL规定了这种函数为开窗函数,在 Oracle中则被称为分析函数,

    而在DB2中则被称为OLAP函数。  

    要计算全部人员的总数,我们能够运行以下的 SQL语句: 
    SELECT COUNT(*) FROM T_Person 
     
           除了这样的较简单的使用方式, 有时须要从不在聚合函数中的行中訪问这些聚合计

    算的值。比方我们想查询每一个工资小于 5000元的员工信息(城市以及年龄) ,而且在

    每行中都显示全部工资小于5000元的员工个数,尝试编写以下的 SQL语句: 
    SELECT FCITY , FAGE , COUNT(*)  
    FROM T_Person 
    HERE FSALARY<5000 
      运行上面的SQL以后我们会得到以下的错误信息: 
    选择列表中的列  'T_Person.FCity' 无效,由于该列没有包括在聚合函数或 

     GROUP BY 子句中。 
      这是由于全部不包括在聚合函数中的列必须声明在GROUP BY 子句中,

    能够进行例如以下改动: 
    SELECT FCITY, FAGE, COUNT(*)  
    FROM T_Person 
    WHERE FSALARY<5000 
    GROUP BY FCITY , FAGE 
      运行完成我们就能在输出结果中看到以下的运行结果: 
                   

       

      这个运行结果与我们想像的是全然不同的,这是由于GROUP  BY子句对结果集

    进行了分组,所以聚合函数进行计算的对象不再是全部的结果集,而是每个分组。

     能够通过子查询来解决问题,SQL例如以下: 
    SELECT FCITY , FAGE ,  

      SELECT COUNT(* ) FROM T_Person 
      WHERE FSALARY<5000 

    FROM T_Person 
    WHERE FSALARY<5000 
      运行完成我们就能在输出结果中看到以下的运行结果:


      尽管使用子查询可以解决问题,可是子查询的使用很麻烦,使用开窗函数

    则能够大大简化实现,以下的SQL语句展示了假设使用开窗函数来实现相同的效果: 

    SELECT FCITY , FAGE , COUNT(*) OVER() 
    FROM T_Person 
    WHERE FSALARY<5000 
      运行完成我们就能在输出结果中看到以下的运行结果: 

    能够看到与聚合函数不同的是,开窗函数在聚合函数后添加�了一个OVER keyword。 
    开窗函数的调用格式为: 
    函数名(列) OVER(选项) 
        OVER   keyword表示把函数当成开窗函数而不是聚合函数。SQL  标准同意将全部聚 
    合函数用做开窗函数,使用OVER keyword来区分这两种使用方法。 
        在上边的样例中,开窗函数COUNT(*) OVER()对于查询结果的每一行都返回全部 
    符合条件的行的条数。OVERkeyword后的括号里还常常加入�选项用以改变进行聚合运算的窗 
    口范围。假设OVERkeyword后的括号里的选项为空,则开窗函数会对结果集中的全部行进行 
    聚合运算。  
      

    总结:上述讲述的是开窗函数的基本使用方法,希望对大家有所帮助!

  • 相关阅读:
    Opensource .NET and Mono REST Web Services framework.NET Community Content on InfoQ Servicestack
    动态软件框架开发模型图
    数据库镜像
    DDD:策略模式如何结合动态表达式
    CentOS下j2ee环境搭建
    C#代码段编辑/编译工具
    .NET平台下不借助Office实现Word、Powerpoint等文件的解析(完)
    .NET Attribute(特性)的作用与用法
    tornado开发输入输出,数据库操作,内置模板,综合示例
    基于事件的异步编程模式(EMP)
  • 原文地址:https://www.cnblogs.com/hrhguanli/p/3954363.html
Copyright © 2020-2023  润新知