SQL中的开窗函数详解可代替聚合函数使用

在没学习开窗函数之前，我们都知道，用了分组之后，查询字段就只能是分组字段和聚合的字段，这带来了极大的不方便，有时我们查询时需要分组，又需要查询不分组的字段，每次都要又到子查询，这样显得sql语句复杂难懂，给维护代码的人带来很大的痛苦，然而开窗函数出现了，曙光也来临了。如果要想更具体了解开窗函数，请看书《程序员的SQL金典》，开窗函数在mysql不能使用。

开窗函数与聚合函数一样，都是对行的集合组进行聚合计算。它用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合），它对一组值进行操作，不需要使用group by语句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。定义看不懂不要紧，会用就行。

举个简单例子 查询每个工资小于5000的员工信息（姓名，城市 年龄 薪水），并且显示小于5000的员工个数，尝试使用下面语句：

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) FROM T_Person WHERE FSALARY<5000

消息 8120，级别 16，状态 1，第 1 行
选择列表中的列 'T_Person.FName' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

可以使用子查询实现，语句：

SELECT FName, FCITY, FAGE, FSalary, ( SELECT COUNT(FName) FROM T_Person WHERE FSALARY<5000 ) PersonNum FROM T_Person
WHERE FSALARY<5000

结果：

使用开窗函数实现，查询结果一模一样，就不粘贴了：

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) OVER() as PersonNum FROM T_Person
WHERE FSALARY<5000

1.开窗函数格式：函数名(列) OVER(选项)

2.聚合开窗函数格式：聚合函数(列) OVER(PARTITION BY 字段)

over关键字把聚合函数当成聚合开窗函数而不是聚合函数，SQL标准允许将所有的聚合函数用做聚合开窗函数。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果OVER关键字后的括号为空，则开窗函数会对结果集合的所有行进行聚合运算。

PARTITION BY来定义行的分区来进行聚合运算，与group by 不同，partition by 字句创建的分区是独立于结果集的，创建的分区只是用于进行聚合运算，而且不同的开窗函数所创建的分区不互相影响，例如：查询所有人员的信息，并查询所属城市的人员数以及同年龄的人员数：

SELECT FName,FCITY, FAGE, FSalary, COUNT(FName) OVER(PARTITION BY FCITY) CityNum,
COUNT(FName) OVER(PARTITION BY FAGE) AgeNum FROM T_Person ORDER by FCITY

查询所有人员的信息，并查询所属城市的人员数，每个城市的人按照年龄排序语句：

SELECT FName,FCITY, FAGE, FSalary, COUNT(FName) OVER(PARTITION BY FCITY ORDER BY FAGE) CityNum FROM T_Person

3.排序开窗函数格式：排序函数() OVER(ORDER BY 字段)

(1)主要函数有ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()

ROW_NUMBER() 加行号，一般可以用于分页查询（现在被offset  fetch取代 ），对于没有主键列的表加行号作用很明显，删除重复数据等。

按照薪水高低给所有人员排序，同样薪水的排名不一样，可以用row_number(),

with a as
(
SELECT FName, FSalary, FCity, FAge, ROW_NUMBER() over(ORDER BY FSalary) as RowNum FROM T_Person
)
SELECT * FROM a

使用rank()将每个城市的薪水排行，值一样的同一个排名，出现两个第一名的时候，排在两个第一名后的排名将是第三名

SELECT FName, FSalary, FCity, FAge, RANK() over(PARTITION BY FCITY ORDER BY FSalary) as RankNum FROM T_Person

使用dense_rank()将每个城市的薪水排行，值一样的同一个排名，出现两个第一名的时候，排在两个第一名后的排名将是第三名

ntile(数字) over(order by 字段):数字表示一组多少个数，并根据数量得出分组的数量

SELECT *,NTILE(5) OVER(ORDER BY FSalary) AS NileNum FROM T_Person

总结

到此这篇关于SQL中的开窗函数详解可代替聚合函数使用的文章就介绍到这了,更多相关SQL 开窗函数内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • SQL Server 2012 开窗函数

    废话不多说了,直接给大家贴代码了,具体代码如下所示: -- 开窗函数:在结果集的基础上进一步处理(聚合操作) -- Over函数,添加一个字段显示最大年龄 SELECT * , MAX(StuAge) OVER ( ) MaxStuAge FROM dbo.Student; -- Over函数,添加一个字段显示总人数 SELECT * , COUNT(StuID) OVER ( ) StuCount FROM dbo.Student; -- Partition By 分组统计数量 -- 根据性别

  • Sql Server 字符串聚合函数

    如下表:AggregationTable Id Name 1 赵 2 钱 1 孙 1 李 2 周 如果想得到下图的聚合结果 Id Name 1 赵孙李 2 钱周 利用SUM.AVG.COUNT.COUNT(*).MAX 和 MIN是无法做到的.因为这些都是对数值的聚合.不过我们可以通过自定义函数的方式来解决这个问题.1.首先建立测试表,并插入测试数据: 复制代码 代码如下: create table AggregationTable(Id int, [Name] varchar(10)) go

  • MySQL中聚合函数count的使用和性能优化技巧

    本文的环境是Windows 10,MySQL版本是5.7.12-log 一. 基本使用 count的基本作用是有两个: 统计某个列的数据的数量: 统计结果集的行数: 用来获取满足条件的数据的数量.但是其中有一些与使用中印象不同的情况,比如当count作用一列.多列.以及使用*来表达整行产生的效果是不同的. 示例表如下: CREATE TABLE `NewTable` ( `id` int(11) NULL DEFAULT NULL , `name` varchar(30) NULL DEFAUL

  • sql server如何利用开窗函数over()进行分组统计

    这是一道常见的面试题,在实际项目中经常会用到. 需求:求出以产品类别为分组,各个分组里价格最高的产品信息. 实现过程如下: declare @t table( ProductID int, ProductName varchar(20), ProductType varchar(20), Price int) --测试数据 insert @t select 1,'name1','P1',3 union all select 2,'name2','P1',5 union all select 3,

  • MySql 中聚合函数增加条件表达式的方法

    Mysql 与聚合函数在一起时候where条件和having条件的过滤时机 where 在聚合之前过滤 当一个查询包含了聚合函数及where条件,像这样的情况 select max(cid) from t where t.id<999 这时候会先进行过滤,然后再聚合.先过滤出ID<999的记录,再查找最大的cid返回. having 在聚合之后过滤 having在分组的时候会使用,对分组结果进行过滤,通常里面包含聚合函数. SELECT ip,MAX(id) FROM app GROUP BY

  • Sql Server 开窗函数Over()的使用实例详解

    利用over(),将统计信息计算出来,然后直接筛选结果集 declare @t table( ProductID int, ProductName varchar(20), ProductType varchar(20), Price int) insert @t select 1,'name1','P1',3 union all select 2,'name2','P1',5 union all select 3,'name3','P2',4 union all select 4,'name4

  • SQL中的开窗函数详解可代替聚合函数使用

    在没学习开窗函数之前,我们都知道,用了分组之后,查询字段就只能是分组字段和聚合的字段,这带来了极大的不方便,有时我们查询时需要分组,又需要查询不分组的字段,每次都要又到子查询,这样显得sql语句复杂难懂,给维护代码的人带来很大的痛苦,然而开窗函数出现了,曙光也来临了.如果要想更具体了解开窗函数,请看书<程序员的SQL金典>,开窗函数在mysql不能使用. 开窗函数与聚合函数一样,都是对行的集合组进行聚合计算.它用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合),它对一组值进行操作,不

  • 深入SQL中PIVOT 行列转换详解

    深入SQL中PIVOT 行列转换详解

    PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合.UNPIVOT与PIVOT执行相反的操作,将表值表达式的列转换为列值. 通俗简单的说:PIVOT就是行转列,UNPIVOT就是列传行 一.PIVOT实例 1. 建表 建立一个销售情况表,其中,year字段表示年份,quarter字段表示季度,amount字段表示销售额.quarter字段分别用Q1, Q2, Q3, Q4表示一.二.三.四季度. CREATE TABLE

  • mybatis 查询sql中in条件用法详解(foreach)

    foreach属性主要有item,index,collection,open,separator,close 1.item表示集合中每一个元素进行迭代时的别名, 2.index指定一个名字,用于表示在迭代过程中,每次迭代到的位置, 3.open表示该语句以什么开始, 4.separator表示在每次进行迭代之间以什么符号作为分隔符, 5.close表示以什么结束, 6.collection属性,该属性是必须指定的,但是在不同情况下,该属性的值是不一样的, 主要有一下3种情况: a.如果传入的是单

  • SQL中的连接查询详解

    Join 连接 (SQL Join) SQL Join (连接) 是利用不同数据表之间字段的关连性来结合多数据表之检索. SQL Join是结合多个数据表而组成一抽象的暂时性数据表以供数据查询,在原各数据表中之纪录及结构皆不会因此连接查询而改变. 这是一个客户数据表「customers」: C_Id Name City Address Phone 1 张一 台北市 XX路100号 02-12345678 2 王二 新竹县 YY路200号 03-12345678 3 李三 高雄县 ZZ路300号

  • Linux 中可重入函数与不可重入函数详解

    Linux 中可重入函数与不可重入函数详解 可重入函数和不可重入函数说起来有点拗口,其实写过多进程(线程)程序的人肯定很快就能明白这两种函数是个神马东西.下面是我对这两个函数的理解: 可重入函数可以理解为是能被中断的函数,并且它被中断返回后也不会出现什么错误. 不可重入函数可以理解为如果函数被中断的话,就会出现不可预料的错误.这是因为函数中使用了一些系统资源,比如全局变量区,中断向量表之类的.比如多个进程同时对一个文件进行写操作,如果没有同步机制的话,对文件的写入就会变得难以控制. 在多进程(线

  • javascript回调函数详解

    在高级语言层出不穷的年代, 各个语言都号称有着一切皆为对象的自豪说法, 而 js 作为一门脚本语言却相对于java等传统面向对象语言有很大的不同之处, 除了 js 诡异的继承体系之外, 最令人着迷的一个特性就是回调函数, 当然也有很多人对他诟病, 笔者认为 回调函数 和 异步 是js语言特性的两大最为突出的店, 当然正如所有优点需要满足自我的需求, 这个世界没有银弹, 比如大量的使用回调函数将会使你的代码冗余, 错乱影响代码人的视觉与思维体验. 本文是自己对学习回调函数的的体会, 难免不完善甚至

  • SQL Server COALESCE函数详解及实例

    SQL Server COALESCE函数详解及实例

    SQL Server COALESCE函数详解 很多人知道ISNULL函数,但是很少人知道Coalesce函数,人们会无意中使用到Coalesce函数,并且发现它比ISNULL更加强大,其实到目前为止,这个函数的确非常有用,本文主要讲解其中的一些基本使用:  首先看看联机丛书的简要定义: 返回其参数中第一个非空表达式语法: COALESCE ( expression [ ,...n ] ) 如果所有参数均为 NULL,则 COALESCE 返回 NULL.至少应有一个 Null 值为 NULL

  • OraclePL/SQL单行函数和组函数详解

    正在看的ORACLE教程是:OraclePL/SQL单行函数和组函数详解. 1 函数是一种有零个或多个参数并且有一个返回值的程序.在SQL中Oracle内建了一系列函数,这些函数都可被称为SQL或PL/SQL语句,函数主要分为两大类:  2  3 单行函数  4  5 组函数   6  7 本文将讨论如何利用单行函数以及使用规则.  8  9 SQL中的单行函数 10 11 SQL和PL/SQL中自带很多类型的函数,有字符.数字.日期.转换.和混合型等多种函数用于处理单行数据,因此这些都可被统称

  • Oracle中的游标和函数详解

     Oracle中的游标和函数详解 1.游标 游标是一种 PL/SQL 控制结构:可以对 SQL 语句的处理进行显示控制,便于对表的行数据 逐条进行处理. 游标并不是一个数据库对象,只是存留在内存中. 操作步骤: 声明游标    打开游标 取出结果,此时的结果取出的是一行数据 关闭游标 到底那种类型可以把一行的数据都装进来 此时使用 ROWTYPE 类型,此类型表示可以把一行的数据都装进来. 例如:查询雇员编号为 7369 的信息(肯定是一行信息). 例:查询雇员编号为 7369 的信息(肯定是一

  • Mysql注入中的outfile、dumpfile、load_file函数详解

    在利用sql注入漏洞后期,最常用的就是通过mysql的file系列函数来进行读取敏感文件或者写入webshell,其中比较常用的函数有以下三个 into dumpfile() into outfile() load_file() 我们本次的测试数据如下 读写文件函数调用的限制 因为涉及到在服务器上写入文件,所以上述函数能否成功执行受到参数 secure_file_priv 的影响.官方文档中的描述如下 翻译一下就是 其中当参数 secure_file_priv 为空时,对导入导出无限制 当值为一

随机推荐