SQL窗口函数OVER用法实例整理

目录
  • OVER的定义
  • 语法
  • 用法
  • 示例
    • SUM后的开窗函数
    • 排序
    • ROWS|RANGE
  • 参考
  • 总结

OVER的定义

OVER用于为行定义一个窗口,它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。

语法

OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] [ROWS|RANGE BETWEEN 边界规则1 and 边界规则2])

PARTITION BY 子句进行分组;

ORDER BY 子句进行排序;

ROWS|RANGE 框架是对窗口进行进一步的分区,框架有两种范围限定方式:一种是使用ROWS子句,通过指定当前行之前或之后的固定数目的行来限制分区中的行数;另一种是RANGE子句,按照排序列的当前值,根据相同值来确定分区中的行数。
窗口函数OVER()指定一组行,开窗函数计算从窗口函数输出的结果集中各行的值。

RANGE表示按照值的范围进行范围的定义,而ROWS表示按照行的范围进行范围的定义;边界规则的可取值见下表

用法

OVER开窗函数必须聚合函数或排序函数一起使用,聚合函数一般指SUM(),MAX(),MIN,COUNT(),AVG()等常见函数。排序函数一般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()等。

示例

创建测试表

create table Employee
(
    ID INT PRIMARY KEY
    ,Name VARCHAR(20)
    ,GroupName VARCHAR(20)
    ,Salary INT
) INSERT into Employee VALUES(1, '小明', '开发部', 8000), (4, '小张', '开发部', 7600), (5, '小白', '开发部', 7000), (8, '小王', '财务部', 5000), (9, null, '财务部', NULL), (15, '小刘', '财务部', 6000), (16, '小高', '行政部', 4500), (18, '小王', '行政部', 4000), (23, '小李', '行政部', 4500), (29, '小吴', '行政部', 4700)
;

SUM后的开窗函数

select  *
        ,SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资
        ,SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资
        ,SUM(Salary) OVER(ORDER BY ID) 累计工资
        ,SUM(Salary) OVER() 总工资
from    Employee

结果如下:

其中开窗函数的每个含义不同,我们来具体解读一下:

SUM(Salary) OVER (PARTITION BY Groupname)

只对PARTITION BY后面的列Groupname进行分组,分组后求解Salary的和。

SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)

对PARTITION BY后面的列Groupname进行分组,然后按ORDER BY 后的ID进行排序,然后在组内对Salary进行累加处理。

SUM(Salary) OVER (ORDER BY ID)

只对ORDER BY 后的ID内容进行排序,对排完序后的Salary进行累加处理。

SUM(Salary) OVER ()

对Salary进行汇总处理

排序

ROW_NUMBER()

定义:ROW_NUMBER()函数作用就是将SELECT查询到的数据进行排序,每一条数据加一个序号

select  *
        ,ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) 班内排序
        ,ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序
from    Scores
;

RANK()

select  ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS [RANK]
        ,*
from    Scores
;

select  RANK() OVER (ORDER BY SCORE DESC) AS [RANK]
        ,*
from    Scores
;

NTILE()

定义:NTILE(N)函数是将数据分成N块,然后再添加1~N的序号。

select  *
        ,NTILE(1) OVER (ORDER BY SCORE DESC) AS 分区后排序
from    Scores
;
select  *
        ,NTILE(2) OVER (ORDER BY SCORE DESC) AS 分区后排序
from    Scores
;
select  *
        ,NTILE(3) OVER (ORDER BY SCORE DESC) AS 分区后排序
from    Scores
;

ROWS|RANGE

select  *
        ,SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID ROWS between CURRENT ROW and 5 FOLLOWING) 从当前行开始后5行的和
from    Employee

参考

https://www.modb.pro/db/42912

总结

到此这篇关于SQL窗口函数OVER用法的文章就介绍到这了,更多相关SQL窗口函数OVER内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • SQL2005 学习笔记 窗口函数(OVER)

    1.简介: SQL Server 2005中的窗口函数帮助你迅速查看不同级别的聚合,通过它可以非常方便地累计总数.移动平均值.以及执行其它计算. 窗口函数功能非常强大,使用起来也十分容易.可以使用这个技巧立即得到大量统计值. 窗口是用户指定的一组行. 开窗函数计算从窗口派生的结果集中各行的值. 2.适用范围: 排名开窗函数和聚合开窗函数. 也就是说窗口函数是结合排名开窗函数或者聚合开窗函数一起使用 OVER子句前面必须是排名函数或者是聚合函数 3.例题: 复制代码 代码如下: --建立订单表 c

  • MySQL窗口函数OVER()用法及说明

    目录 MySQL窗口函数OVER() 下面的讲解将基于这个employee2表 MySQL窗口函数OVER() 下面的讲解将基于这个employee2表 mysql> SELECT * FROM employee2; +----+-----------+------+---------+---------+ | id | name | age | salary | dept_id | +----+-----------+------+---------+---------+ | 3 | 小肖 |

  • SQL窗口函数OVER用法实例整理

    目录 OVER的定义 语法 用法 示例 SUM后的开窗函数 排序 ROWS|RANGE 参考 总结 OVER的定义 OVER用于为行定义一个窗口,它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列. 语法 OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] [ROWS|RANGE BETWEEN 边界规则1 and 边界规则2]) PARTITION BY 子句进行分组: ORDER BY

  • Mybatis动态SQL foreach标签用法实例

    需求:传入多个 id 查询用户信息,用下边两个 sql 实现: SELECT * FROM USERS WHERE username LIKE '%张%' AND (id =10 OR id =89 OR id=16) SELECT * FROM USERS WHERE username LIKE '%张%' AND id IN (10,89,16) 这样我们在进行范围查询时,就要将一个集合中的值,作为参数动态添加进来. 这样我们将如何进行参数的传递? 1.实体类 public class Qu

  • Sql Server中Substring函数的用法实例解析

    SQL 中的 substring 函数是用来抓出一个栏位资料中的其中一部分.这个函数的名称在不同的资料库中不完全一样: MySQL: SUBSTR( ), SUBSTRING( ) Oracle: SUBSTR( ) SQL Server: SUBSTRING( ) SQL 中的 substring 函数是用来截取一个栏位资料中的其中一部分. 例如,我们需要将字符串'abdcsef'中的'abd'给提取出来,则可用substring 来实现: select substring('abdcsef'

  • MyBatis动态SQL标签用法实例详解

    1.动态SQL片段 通过SQL片段达到代码复用 <!-- 动态条件分页查询 --> <sql id="sql_count"> select count(*) </sql> <sql id="sql_select"> select * </sql> <sql id="sql_where"> from icp <dynamic prepend="where&quo

  • Oracle addBatch()用法实例详解

    Oracle addBatch()用法实例详解 PreparedStatement.addbatch()的使用 Statement和PreparedStatement的区别就不多废话了,直接说PreparedStatement最重要的addbatch()结构的使用. 1.建立链接     Connection connection =getConnection(); 2.不自动 Commit connection.setAutoCommit(false); 3.预编译SQL语句,只编译一回哦,效

  • asp.net使用LINQ to SQL连接数据库及SQL操作语句用法分析

    本文实例讲述了asp.net使用LINQ to SQL连接数据库及SQL操作语句用法.分享给大家供大家参考,具体如下: LINQ简介 LINQ:语言集成查询(Language INtegrated Query)是一组用于c#和Visual Basic语言的扩展.它允许编写C#或者Visual Basic代码以查询数据库相同的方式操作内存数据. LINQ是一门查询语言,和SQL一样,通过一些关键字的组合,实现最终的查询. LINQ的分类 LINQ to Object LINQ to XML LIN

  • Vue2.0基于vue-cli+webpack Vuex的用法(实例讲解)

    在这之前,我已经分享过组件与组件的通信机制以及父子组件之间的通信机制,而我们的vuex就是为了解决组件通信问题的 vuex是什么东东呢? 组件通信的本质其实就是在组件之间传递数据或组件的状态(这里将数据和状态统称为状态),但可以看到如果我们通过最基本的方式来进行通信,一旦需要管理的状态多了,代码就会变得十分臃肿和庞大.对所有状态的管理便会显得力不从心,因此,vuex出现了,他就是帮助我们把公用的状态全抽出来放在vuex的容器中,然后根据一定的规则来进行管理,我们赶紧来用一下吧,想要掌握vuex的

  • PHP生成器功能与用法实例分析

    本文实例讲述了PHP生成器功能与用法.分享给大家供大家参考,具体如下: 1. 官方说明:生成器提供了一种更容易的方法来实现简单的对象迭代,相比较定义类实现 Iterator 接口的方式,性能开销和复杂性大大降低.生成器允许你在 foreach 代码块中写代码来迭代一组数据而不需要在内存中创建一个数组. 2. 生成器就像一个普通的自定义函数一样, 和普通函数只返回一次不同的是, 生成器可以根据需要 yield 多次,以便生成需要迭代的值. 3. 代码示例: //未使用生成器 echo '开始内存:

  • PHP设计模式之数据访问对象模式(DAO)原理与用法实例分析

    本文实例讲述了PHP设计模式之数据访问对象模式(DAO)原理与用法.分享给大家供大家参考,具体如下: 这个数据访问对象模式主要就是描述了如何创建透明访问数据源的对象,这种设计模式对于同时学过PHP和MySQL的人来说,是一个全新的和令人兴奋的概念,并且这种设计模式的目的就是为了解决重复与数据源抽象化这两种特定的问题. 我们可以先创建一个数据访问对象设计模式的对象,这个数据访问对象封装了创建sQL调用.减少实例创建的复杂性和重复以及更新进程的智能方式,并且,这个数据访问对象的使用者不会知道实际使用

  • mysql中关键词exists的用法实例详解

    目录 前言 语法解释 语法 说明 使用案例 环境准备 常用查询 exists与in的效率比较 循环嵌套查询执行原理 循环优化策略 exists和in查询原理的区别 结论 总结 前言 在日常开发中,用mysql进行查询的时候,有一个比较少见的关键词exists,我们今天来学习了解一下这个exists这个sql关键词的用法,这样在工作中遇到一些特定的业务场景就可以有更加多样化的解决方案 语法解释 语法 SELECT column1 FROM t1 WHERE [conditions] and EXI

随机推荐