开窗函数 订阅
开窗函数用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合),它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。 展开全文
开窗函数用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合),它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。
信息
领    域
计算机
适用范围
数据库
分    类
数据库函数
中文名
开窗函数(数据库函数)
外文名
Analytical Functions
开源解释
开窗函数与聚合函数计算方式一样,开窗函数也是对行集组进行聚合计算,但是它不像普通聚合函数那样每组只返回一个值,开窗函数可以为每组返回多个值。开窗函数的语法为:over(partition by 列名1 order by 列名2 ),括号中的两个关键词partition by 和order by 可以只出现一个。over() 前面是一个函数,如果是聚合函数,那么order by 不能一起使用。开窗函数主要分为以下两类:窗口函数OVER()指定一组行,开窗函数计算从窗口函数输出的结果集中各行的值。开窗函数不需要使用GROUP BY就可以对数据进行分组,还可以同时返回基础行的列和聚合列。1.排名开窗函数ROW_NUMBER、DENSE_RANK、RANK属于排名函数。排名开窗函数可以单独使用ORDER BY 语句,也可以和PARTITION BY同时使用。PARTITION BY用于将结果集进行分组,开窗函数应用于每一组。ORDER BY 指定排名开窗函数的顺序,在排名开窗函数中必须使用ORDER BY语句。ROW_NUMBER()为每一组的行按顺序生成一个连续序号。RANK()也为每一组的行生成一个序号,与ROW_NUMBER()不同的是如果按照ORDER BY的排序,如果有相同的值会生成相同的序号,并且接下来的序号是不连序的。例如两个相同的行生成序号2,那么接下来会生成序号4。DENSE_RANK()和RANK()类似,不同的是如果有相同的序号,那么接下来的序号不会间断。也就是说如果两个相同的行生成序号2,那么接下来生成的序号还是3。2.聚合开窗函数很多聚合函数都可以用作窗口函数的运算,如SUM、AVG、MAX、MIN、COUNT。聚合开窗函数只能使用PARTITION BY子句,ORDER BY不能与聚合开窗函数一同使用。
收起全文
精华内容
下载资源
问答
  • 开窗函数
    2022-01-07 21:05:12

    分析函数

    这里的分析函数也就是我们常说的开窗函数,通常有两类:

    一类是聚合开窗函数(SUM、AVG、MAX、MIN、COUNT等),

    一类是排序开窗函数(ROW_NUMBER、DENSE_RANK、RANK等)。

    本文主要内容转自《高效使用Greenplum》一书。

    分析函数是Greenplum数据库管理系统自带函数中的一种专门解决具有复杂统计需求的函数,它可以对数据分组,然后基于组中数据进行分析统计,最后在每组数据集的每一行返回这个统计值。

    分析函数不同于分组统计(Group By),分组统计只能按照分组字段返回一个固定的统计值,不能在原来的数据行上附带这个统计值,而分析函数正式专门解决这类统计需求所开发出来的函数。分析函数已经逐步称为SQL标准的一部分,有越来越多的数据库系统开始支持分析函数。

    开窗函数和聚合函数的区别如下:

    • SQL 标准允许将所有聚合函数用作开窗函数,用OVER 关键字区分开窗函数和聚合函数。
    • 聚合函数每组只返回一个值,开窗函数每组可返回多个值。

    分析函数的语法结构如下:

    SELECT table.column,

    analysis_function() OVER ( [PARTITION BY 字符] [ORDER BY 字段 [rows]] ) as 统计值

    FROM table

    其中:

    analysis_function():指定分析函数名称,常用的分析函数有sum、max、first_value、last_value、lag、lead、rank、desn_rank、row_number等。

    OVER():开窗函数名,PARTITION BY指定进行数据分组的字段,ORDER BY指定进行排序的字段,ROWS指定数据窗口(即指定分析函数要操作的行数),语法形式为OVER(PARTITION BY xxx ORDER BY yyy ROWS BETWEEN zzz)。

    这里的ROWS有多个范围值(一般情况下会省略),具体如下:

    1. unbounded preceding:无限/不限定往前的范围;
    2. n preceding:往前统计n行;
    3. unbounded following:无限/不限定往后的范围;
    4. n following:往后统计n行;
    5. current row:当前行;

    案例:下图是员工表和部门表数据内连接后的结果集。

    select b.dept_id,b.dept_name,t.emp_id,t.emp_name,t.age,t.salary

    from emp t,dept b

    where t.dept_id = b.dept_id

    dept_id

    dept_name

    emp_id

    emp_name

    age

    salary

    1100

    销售部

    3

    Jack

    40

    15000

    1100

    销售部

    4

    Michael

    36

    9800

    1200

    研发部

    2

    Alen

    29

    13500

    1200

    研发部

    5

    Lily

    36

    12500

    1110

    销售一部

    6

    David

    30

    7900

    1120

    销售二部

    7

    Timmy

    26

    8500

    1000

    总裁办

    1

    Paul

    45

    36000

    案例1:利用min(),max()分析函数分别取出不同部门不同员工工资的最高值和最低值,附带在原始数据上。

    select b.dept_id,b.dept_name,t.emp_id,t.emp_name,t.age,t.salary

    -- 获取组中工资最高值

    max(t.salary) OVER(PARTITION BY t.dept_id) AS salary_max,

    -- 获取组中工资最低值

    min(t.salary) OVER(PARTITION BY t.dept_id) AS salary_min,

    -- 分组窗口的第一个值(指定窗口为组中第一行到末尾行)

    first_value(t.salary) OVER(PARTITION BY t.dept_id ORDER BY t.salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS salary_first,

    -- 分组窗口的最后一个值(指定窗口为组中第一行到末尾行)

    last_value(t.salary) OVER(PARTITION BY t.dept_id ORDER BY t.salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS salary_last,

    -- 分组窗口的第一个值(不指定窗口)

    first_value(t.salary) OVER(PARTITION BY t.dept_id ORDER BY t.salary DESC) AS salary_first_1,

    -- 分组窗口的最后一个值(指定窗口才可以取到最低值,否则只能取到当前行)

    last_value(t.salary) OVER(PARTITION BY t.dept_id ORDER BY t.salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS salary_last_1

    from emp t,dept b

    where t.dept_id = b.dept_id

    order by t.dept_id,t.emp_id

    根据查询结果可以看出如下信息:

    min()和max()分析函数直接获取组中最小值和最大值;

    first_value()和last_value()返回窗口的第一行和最后一行数据,因为我们通过工资字段对分组内的数据进行了降序排列,所以也可以达到在一定的窗口内获取最大值和最小值的功能;

    排序不指定窗口时,就按照组内的第一行到当前行作为窗口,然后取出窗口的第一行和最后一行;

    窗口子语句当中的第一行是UNBOUNDED PRECEDING,当前行是CURRENT ROW,最后一行是UNBOUNDED FOLLOWING,正是利用窗口范围是第一行到最后一行,得到同一部门内的最高工资和最低工资。

    注意:ROWS的默认值是:BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

    案例2:利用rank()、dense_rank()、row_number()函数对员工的年龄进行排序,比较三个不同关键字的差异。

    select t.emp_id,t.emp.name,t.age,

    row_number() OVER(ORDER BY t.age) AS “row_number排名”,

    rank() OVER(ORDER BY t.age) AS “rank排名”,

    dense_rank() OVER(ORDER BY t.age) AS “dense_rank排名”

    from emp t

    order by t.age

    根据查询结果可以看出如下信息:

    emp_id

    emp_name

    age

    row_number

    rank

    dense_rank

    7

    Timmy

    26

    1

    1

    1

    2

    Alen

    29

    2

    2

    2

    6

    David

    30

    3

    3

    3

    4

    Michael

    36

    4

    4

    4

    5

    Lily

    36

    5

    4

    4

    3

    Jack

    40

    6

    6

    5

    1

    Paul

    45

    7

    7

    6

    row_number()函数排名返回唯一值,当遇到相同的数据时,排名按照记录集中的记录顺序依次递增;

    rank()函数返回唯一值,当遇到相同的数据时,所有相同的数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名;

    dense_rank()函数返回唯一值,当遇到相同的数据时,所有相同数据的排名是一样的,同时在最后一条相同记录和下一条不同记录的排名之间不空出排名。

    案例3:利用开窗函数对员工工资进行不同条件的汇总,以便对比ORDER BY和PARTITION BY的作用。

    select t.emp_id,t.emp_name,t.age,t.salary,

    sum(t.salary) OVER() AS “全局汇总”,

    sum(t.salary) OVER(ORDER BY t.emp_id) AS “逐行累加”,

    sum(t.salary) OVER(PARTITION BY t.dept_id) AS “分组汇总”,

    sum(t.salary) OVER(PARTITION BY t.dept_id ORDER BY t.emp_id) AS “分组逐行汇总”

    from emp t

    order by t.emp_id

    根据查询结果可以看出如下信息:

    OVER()默认是全局汇总,即所有可以查到的行数指标合集,可用于计算占比;

    OVER+ORDER BY用于根据条件逐行相加汇总,可用于计算类似于“工资占前80%的员工明细”之类的需求;

    OVER+PARTITION BY用于分组汇总,可以计算分组的合计、分组的占比、分组的最大值和最小值等;

    OVER+PARTITION BY+ORDER BY用于分组逐行汇总,可用于计算分组的排名,可以满足例如“取每一个组的前五名”之类的需求;

    案例4:查询部门总工资大于所有部门平均总工资的部门员工信息及部门平均工资、公司平均工资。

    select * from (

    select emp_id,emp_name,dept_id,salary,

    avg(salary) OVER(PARTITION BY dept_id) as dept_avg_salary,

    avg(salary) OVER() as comp_avg_salary

    from emp) t

    where t.dept_avg_salary > t.comp_avg_salary;

    更多相关内容
  • 开窗函数:在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在 2003 年 ISO SQL 标准加入了开窗函数开窗函数的使用使得这些经典的...
  • 开窗函数:在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在 2003 年 ISO SQL 标准加入了开窗函数开窗函数的使用使得这些经典的...
  • mysql 开窗函数

    2021-01-07 14:23:19
    场景1: 取max 表结构 create table tb_test_info( student_id char(4) comment '学号', grade_id int comment '课程号', score int comment '分数', primary key (student_id,grade_id) );...
  • 前言: 今天在优化工作中遇到的sql慢的问题,发现以前用了挺多游标来处理数据,这样就导致在数据量多的情况下,需要一行一行去...下面模拟工作中通过开窗函数代替游标的例子,通过期初余额与单据的预收金额、应收金额
  • 主要介绍了SQL中的开窗函数详解可代替聚合函数使用,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
  • 工作中用到了几个hive开窗函数,便想把hive开窗函数系统梳理一遍。 开窗函数 普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值,而开窗函数则可为窗口中的每...
  • SQL Server 2012 开窗函数

    2020-09-09 20:14:31
    主要介绍了SQL Server 2012 开窗函数功能的实例代码,具体代码如下所示
  • 主要介绍了Sql Server 开窗函数Over()的使用,非常不错,具有一定的参考借鉴价值,需要的朋友可以参考下
  • 特征分析与偏移分析什么是开窗函数?学习目标:1、累计计算窗口函数(1)sum(…) over(……)(2)avg(…) over(……)(3)语法总结:2、分区排序窗口函数3、分组排序窗口函数4、偏移分析窗口函数练习总结: 什么是...
  • 开窗函数出现之前存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在2003年ISO SQL标准加入了开窗函数开窗函数的使用使得这些经典的难题可以被轻松...
  • 开窗函数

    千次阅读 2021-03-30 16:39:25
    一、什么是开窗函数 开窗函数/分析函数:over() 开窗函数也叫分析函数,有两类:一类是聚合开窗函数,一类是排序开窗函数开窗函数的调用格式为: 函数名(列名) OVER(partition by 列名 order by列名) 。 ...

    一、什么是开窗函数

    开窗函数/分析函数:over()

    开窗函数也叫分析函数,有两类:一类是聚合开窗函数,一类是排序开窗函数。

    开窗函数的调用格式为:

    函数名(列名) OVER(partition by 列名 order by列名) 。

    如果你没听说过开窗函数,看到上面开窗函数的调用方法,你可能还会有些疑惑。但只要你了解聚合函数,那么理解开窗函数就非常容易了。

    我们知道聚合函数对一组值执行计算并返回单一的值,如sum(),count(),max(),min(), avg()等,这些函数常与group by子句连用。除了 COUNT 以外,聚合函数忽略空值。

    但有时候一组数据只返回一组值是不能满足需求的,如我们经常想知道各个地区的前几名、各个班或各个学科的前几名。这时候需要每一组返回多个值。用开窗函数解决这类问题非常方便。

    开窗函数和聚合函数的区别如下:

    (1)SQL 标准允许将所有聚合函数用作开窗函数,用OVER 关键字区分开窗函数和聚合函数。

    (2)聚合函数每组只返回一个值,开窗函数每组可返回多个值。

    注:常见主流数据库目前都支持开窗函数,但mysql数据库目前还不支持。

    二、常见面试题

    1. 分区排序:row_number () over()

    有如下学生成绩表:students_grades

     

    查询每门课程course_name前三名的学生姓名及成绩,要求输出列格式如下:

    course_name, number, stu_name, grades

    查询语句如下:

     

    2.几个排序函数row_number() over()、rank() over()、dense_rank() over()、ntile() over()的区别

    (1) row_number() over():对相等的值不进行区分,相等的值对应的排名相同,序号从1到n连续。

    (2) rank() over():相等的值排名相同,但若有相等的值,则序号从1到n不连续。如果有两个人都排在第3名,则没有第4名。

    (3) dense_rank() over():对相等的值排名相同,但序号从1到n连续。如果有两个人都排在第一名,则排在第2名(假设仅有1个第二名)的人是第3个人。

    (4) ntile( n ) over():可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每一行,排序对应的数字为桶号。如果不能平均分配,则较小桶号的桶分配额外的行,并且各个桶中能放的数据条数最多相差1。

    学生成绩表同上,查询语句如下:

     

    查询结果如下:

    展开全文
  • MySQL开窗函数练习SQL文件
  • Oracle开窗函数

    千次阅读 2020-07-28 17:30:50
    SQL开窗函数 开窗函数:在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在 2003 年 ISO SQL 标准加入了开窗函数开窗函数的使用...

    SQL开窗函数

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

    开窗函数简介:与聚合函数一样,开窗函数也是对行集组进行聚合计算,但是它不像普通聚合函数那样每组只返回一个值,开窗函数可以为每组返回多个值,因为开窗函数所执行聚合计

    算的行集组是窗口。在 ISO SQL 规定了这样的函数为开窗函数,在 Oracle 中则被称为分析函数。
     

     

    数据表(Oracle):T_Person 表保存了人员信息,FName 字段为人员姓名,FCity 字段为人员所在的城市名,FAge 字段为人员年龄,FSalary 字段为人员工资

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

    向 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:

    要计算所有人员的总数,我们可以执行下面的 SQL 语句:SELECT COUNT(*) FROM T_Person

    除了这种较简单的使用方式,有时需要从不在聚合函数中的行中访问这些聚合计算的值。比如我们想查询每个工资小于 5000 元的员工信息(城市以及年龄),并且在每行中都显示所有工资小于 5000 元的员工个数:

    复制代码

    select fname,
           fcity,
           fsalary,
           (select count(*) from t_person where fsalary < 5000) 工资少于5000员工总数
      from t_person
     where fsalary < 5000

    复制代码

     

    虽然使用子查询能够解决这个问题,但是子查询的使用非常麻烦,使用开窗函数则可以大大简化实现,下面的 SQL 语句展示了如果使用开窗函数来实现同样的效果:

     

    select fname, fcity, fsalary, count(*) over() 工资小于5000员工数
      from t_person
     where fsalary < 5000

    可以看到与聚合函数不同的是,开窗函数在聚合函数后增加了一个 OVER 关键字。

    开窗函数格式: 函数名(列) OVER(选项)

    OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数,使用 OVER 关键字来区分这两种用法。
    在上边的例子中,开窗函数 COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空,则开窗函数会对结果集中的所有行进行聚合运算。

    PARTITION BY 子句:

    开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同,PARTITION BY 子句创建的分区是独
    立于结果集的,创建的分区只是供进行聚合计算的,而且不同的开窗函数所创建的分区也不互相影响。下面的 SQL 语句用于显示每一个人员的信息以及所属城市的人员数:

    select fname,fcity,fage,fsalary,count(*) over(partition by fcity) 所在城市人数 from t_person

    COUNT(*) OVER(PARTITION BY FCITY)表示对结果集按照FCITY进行分区,并且计算当前行所属的组的聚合计算结果。比如对于FName等于 Tom的行,它所属的城市是BeiJing,同
    属于BeiJing的人员一共有6个,所以对于这一列的显示结果为6。
     

    这就不需要先对fcity分组求和,然后再和t_person表连接查询了,省事儿。

    在同一个SELECT语句中可以同时使用多个开窗函数,而且这些开窗函数并不会相互干
    扰。比如下面的SQL语句用于显示每一个人员的信息、所属城市的人员数以及同龄人的人数:

    复制代码

    --显示每一个人员的信息、所属城市的人员数以及同龄人的人数:
    select fname,
           fcity,
           fage,
           fsalary,
           count(*) over(partition by fcity) 所属城市的人个数,
           count(*) over(partition by fage) 同龄人个数
      from t_person

     

     ORDER BY子句:

    开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则,而且有的开窗函数还要求必须指定排序规则。使用ORDER BY子句可以对结果集按
    照指定的排序规则进行排序,并且在一个指定的范围内进行聚合运算。ORDER BY子句的语法为:

    ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2

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

    “RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2”部分用来定位聚合计算范围,这个子句又被称为定位框架。

    例子程序一:查询从第一行到当前行的工资总和:

    复制代码

    select fname,
           fcity,
           fage,
           fsalary,
           sum(fsalary) over(order by fsalary rows between unbounded preceding and current row) 到当前行工资求和
      from t_person

    复制代码

    这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN
    UNBOUNDED PRECEDING AND CURRENT ROW)”表示按照FSalary进行排序,然后计算从第
    一行(UNBOUNDED PRECEDING)到当前行(CURRENT ROW)的和,这样的计算结果就是按照
    工资进行排序的工资值的累积和。

    “RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”是开窗函数中最常使用的定位框架,为了简化使用,如果使用的是这种定位框架,则可以省略定位框架声明部分,
    也就是说上边的sql可以简化成:

    复制代码

    select fname,
           fcity,
           fage,
           fsalary,
           sum(fsalary) over(order by fsalary) 到当前行工资求和
      from t_person

    复制代码

    例子程序二:把例子程序一的row换成了range,是按照范围进行定位的

    复制代码

    select fname,
           fcity,
           fage,
           fsalary,
           sum(fsalary) over(order by fsalary range between unbounded preceding and current row) 到当前行工资求和
      from t_person

    复制代码

     

    区别:

    复制代码

    这个SQL语句与例1中的SQL语句唯一不同的就是“ROWS”被替换成了“RANGE”。“ROWS”
    是按照行数进行范围定位的,而“RANGE”则是按照值范围进行定位的,这两个不同的定位方式
    主要用来处理并列排序的情况。比如 Lily、Swing、Bill这三个人的工资都是2000元,如果按照
    “ROWS”进行范围定位,则计算从第一条到当前行的累积和,而如果 如果按照 “RANGE”进行
    范围定位,则仍然计算从第一条到当前行的累积和,不过由于等于2000元的工资有三个人,所
    以计算的累积和为从第一条到2000元工资的人员结,所以对 Lily、Swing、Bill这三个人进行开
    窗函数聚合计算的时候得到的都是7000( “ 1000+2000+2000+2000 ”)。

    复制代码

     下边这的估计不常用:

    例子程序三:

    SELECT FName,
           FSalary,
           SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)  前二后二和
      FROM T_Person;

    复制代码

    这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2
    PRECEDING AND 2 FOLLOWING)”表示按照FSalary进行排序,然后计算从当前行前两行(2
    PRECEDING)到当前行后两行(2 FOLLOWING)的工资和,注意对于第一条和第二条而言它们
    的“前两行”是不存在或者不完整的,因此计算的时候也是要按照前两行是不存在或者不完整进
    行计算,同样对于最后两行数据而言它们的“后两行”也不存在或者不完整的,同样要进行类似
    的处理。

    复制代码

    例子程序四:

    SELECT FName, FSalary,
    SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) 后面一到三之和
    FROM T_Person;

    这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1
    FOLLOWING AND 3 FOLLOWING)”表示按照FSalary进行排序,然后计算从当前行后一行(1
    FOLLOWING)到后三行(3 FOLLOWING)的工资和。注意最后一行没有后续行,其计算结果为
    空值NULL而非0。

    例子程序五:算工资排名

    SELECT FName, FSalary,
    COUNT(*) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND
    CURRENT ROW)
    FROM T_Person;

    这里的开窗函数“COUNT(*) OVER(ORDER BY FSalary RANGE BETWEEN UNBOUNDED
    PRECEDING AND CURRENT ROW)”表示按照FSalary进行排序,然后计算从第一行
    (UNBOUNDED PRECEDING)到当前行(CURRENT ROW)的人员的个数,这个可以看作是计算
    人员的工资水平排名。

    不再用ROWNUM 了  省事了。这个over简写就会出错。

    例子程序6:结合max求到目前行的最大值

    SELECT FName, FSalary,FAge,
    MAX(FSalary) OVER(ORDER BY FAge) 此行之前最大值
    FROM T_Person;

    这里的开窗函数“MAX(FSalary) OVER(ORDER BY FAge)”是“MAX(FSalary)
    OVER(ORDER BY FAge RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)”
    的简化写法,它表示按照FSalary进行排序,然后计算从第一行(UNBOUNDED PRECEDING)
    到当前行(CURRENT ROW)的人员的最大工资值。

     例子程序6:over(partition by XX  order by XX)  partition by和order by 结合

    员工信息+同龄人最高工资,按工资排序

    SELECT FName, FSalary,FAge,
    MAX(FSalary) OVER(PARTITION BY FAge order by fsalary) 同龄人最高工资
    FROM T_Person;

     

    PARTITION BY子句和ORDER BY 可以 共 同 使用,从 而 可以 实现 更 加复 杂 的 功能
    ==================================================================================

    高级开窗函数/ 排名的实现ROW_NUMBER();rank() ,dense_rank()

    除了可以在开窗函数中使用COUNT()、SUM()、MIN()、MAX()、AVG()等这些聚合函数,
    还可以在开窗函数中使用一些高级的函数,有些函数同时被DB2和Oracle同时支持,比如
    RANK()、DENSE_RANK()、ROW_NUMBER(),而有些函数只被Oracle支持,比如
    RATIO_TO_REPORT()、NTILE()、LEAD()、LAG()、FIRST_VALUE()、LAST_VALUE()。
    下面对这几个函数进行详细介绍。

    RANK()和DENSE_RANK()函数都可以用于计算一行的排名,不过对于并列排名的处理方式
    不同;ROW_NUMBER()函数计算一行在结果集中的行号,同样可以将其当成排名函数。这三个
    函数的功能存在一定的差异,举例如下:工资从高到低排名:

    SELECT FName, FSalary,FAge,
    RANK() OVER(ORDER BY fsalary desc) f_RANK,
    DENSE_RANK() OVER(ORDER BY fsalary desc) f_DENSE_RANK,
    ROW_NUMBER() OVER(ORDER BY fsalary desc) f_ROW_NUMBER
    FROM T_Person;

    rank(),dense_rank()语法:

    复制代码

    RANK()
    dense_rank()
    【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )
        dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
    
    【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
    【参数】dense_rank与rank()用法相当,
    【区别】dence_rank在并列关系是,相关等级不会跳过。rank则跳过
    rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内) 
    dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。

    复制代码

    row_number() 函数语法:

    复制代码

    ROW_NUMBER()
    【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 
    【功能】表示根据COL1分组,在分组内部根据 COL2排序,而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的) 
    row_number() 返回的主要是“行”的信息,并没有排名
    【参数】
    【说明】Oracle分析函数
    
    主要功能:用于取前几名,或者最后几名等

    复制代码

    ===================================================================

    排序函数实际场景使用:计算排行榜,排名

    微信活动,每天参与,有得分,活动结束后选出排名靠前的发奖。

    每参与一次,就是一个订单,表结构:

     

    比如要查询期号issue为20170410期的排行榜,按得分倒叙排序,得分一样按订单创建先后,算排行,sql需要这么写:

    select ROWNUM rank, t.*
      from (select *
              from t_zhcw_order
             where issue = '20170410'
             order by integral desc, create_date asc) t

     

    使用了开窗函数后就可以简化:

    select t.*,
                   row_number() over(order by t.integral desc, t.create_date asc) 排名
              from t_zhcw_order t
             where issue = '20170410'

     

     如果想只要排名范围,可以在外边再包一层,这也是高效分页的一种方式:

    复制代码

    select tt.*  from (
    select t.id,
           t.integral,
           t.cell,
           t.create_date,
           row_number() over(order by t.integral desc, t.create_date asc) rankNum
      from t_zhcw_order t
     where t.issue = 20170331
    )tt where tt.rankNum<=50

    复制代码

     

     


     

     

    展开全文
  • MySQL开窗函数

    千次阅读 2022-04-18 19:26:13
    关于MySQL8.0中开窗函数的学习

    一、开窗函数

    1. 介绍

    该函数只有MySQL8.0版本才存在

    MySQL 8.0 关于开窗函数的官方文档https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

    2. 需求场景

    当在业务中,既要显示 聚合前的数据 又要显示 聚合后的数据,这时我们可以使用开窗函数来实现。

    具体实例:下表是关于一年中每个月每个员工的工资记录表(Payroll_records),现有需求是在保留原表数据基础上,增加一列累计工资(salary_accumulation)累计的计算一年的工资。

    原表 Payroll_records

    目标表

    3. 语法介绍

    (1)含义

    窗口函数:窗口 + 函数

    • 窗口: 函数运行时 计算的数据集的范围
    • 函数:运行时的函数
      • 聚合函数:COUNT,SUM,MIN,MAX,AVG
      • 内置窗口函数:
        • 取值
          • FIRST_VALUE:取窗口第一个值;
          • LAST_VALUE:取窗口最后一个值;
        • 串行
          • LEAD:窗口内 向下 第n行的值;
          • LAG:窗口内 向上 第n行的值;
        • 排序
          • NTILE:把数据平均分配 指定 N个桶 ,如果不能平均分配 ,优先分配到 编号 小的里面;
          • RANK: 从1 开始 , 按照顺序 相同会重复 名次会留下 空的位置 生成组内的记录编号;
          • ROW_NUMBER: 从1 开始 , 按照顺序 生成组内的记录编号;
          • DENSE_RANK:从1 开始 , 按照顺序 生成组内的记录编号 相同会重复 名次不会会留下空的位置;
          • CUME_DIST
          • PERCENT_RANK

    (2)语法结构

    • 函数 over([partition by xxx,...] [order by xxx,...] )
    • over() :以谁进行开窗 【table】
    • partition by: 以谁进行分组 【group by column】
    • order by: 以谁进行排序 【column】

    (3)实例说明

    我们以每个服务器每天的累计启动次数为例来介绍该函数,sql表如下:

    • 聚合函数使用——SUM
    select name,
    dt,
    cnt,
    sum(cnt) over (partition by name order by dt ) as cnt_all
    from linux;
    
    # 解释:该开窗函数是以name开窗,以dt日期排序,对cnt(每天的启动次数)加和.
    # 即1+6+13+15+18+28+32

    • 聚合函数使用——SUM
    select name,
           dt,
           cnt,
           sum(cnt) over (partition by name order by dt )	as sum_all,
           sum(cnt) over (partition by name order by dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )	as sum_all1,
           sum(cnt) over (partition by name order by dt ROWS BETWEEN 3 PRECEDING AND CURRENT ROW )	as sum_all2,
           sum(cnt) over (partition by name order by dt ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING )	as sum_all3,
           sum(cnt) over (partition by name order by dt ROWS BETWEEN 3 PRECEDING AND UNBOUNDED FOLLOWING)	as sum_all4,
           sum(cnt) over (partition by name order by dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )	as sum_all5
    from linux;
    
    # 解释:
    #  		BETWEEN …… AND:哪行与哪行之间;
    #			UNBOUNDED:无限制;
    #     PRECEDING:前面的行,当前面加数字n时,代表窗口从当前行上数n行开始;
    #			CURRENT ROW :当前行;
    #			FOLLOWING:后面的行,当前面加数字n时,代表窗口从当前行下数n行开始
    
    #		1、sum_all:与上一个需求相同,实现的就是从头到尾的累加;
    
    #		2、sum_all1:窗口范围会随着当前行的变化而发生变化,由上面的三个词的语法可以看出:
    #     当执行到第1行时,窗口只有第1行本身;当到第2行时,窗口变为1,2两行,此时sum结果就是1+5=6
    #			以此类推,最后的结果如sum_all1。
    
    #		3、sum_all2:窗口从当前行的前三行开始到当前行,例如当前行为第5行时,前三行是2,3,4行
    #		所以是5+7+2+3=17;
    
    #		4、sum_all3:窗口从当前行的前三行开始到当前行的下一行,例如当前行为第2行时,前三行只有第1行
    #		所以是1+2+7=13;
    
    #		5、sum_all4:窗口从当前行的前三行开始到向下无限制,例如当前行为第7行时,前三行是4,5,6
    #		下面没有其余行,所以从第4加到7行:2+3+4+10+=19;
    
    #		6、sum_all5:窗口上下无限制,所以都是所有数加和1+5+7+2+3+10+4=32;
    
    #		注意:开窗函数的聚合函数是sum(cnt),因此累加都是对于cnt的值来说的。与此同时,也要明白
    #		加和的过程要一行一行的,因为一些情况是相对于当前行的,所以窗口大小是动态的。

    • 内置窗口函数——NTITLE 分组或者分类(个人理解)
    select name,
           dt,
           cnt,
           sum(cnt) over (partition by name order by dt ) as sum_all,
           ntile(2) over (partition by name order by dt ) as n2,
           ntile(3) over (partition by name order by dt ) as n3
    from linux;
    
    
    # 解释:
    #		ntitle: 把数据平均分配 指定 N个桶 ,如果不能平均分配 ,优先分配到 编号 小的里面;
    #		Eg:n2这列是将窗口分为两类,由于不能均分所以1号有四个2号有3个。

    • 内置窗口函数——RANK、ROW_NUMBER、DENSE_RANK 编号
    select name,
           dt,
           cnt,
           sum(cnt) over (partition by name order by dt )           as sum_all,
           RANK() over (partition by name order by cnt desc )       as rk,
           ROW_NUMBER() over (partition by name order by cnt desc)  as rw,
           DENSE_RANK() over (partition by name order by cnt desc ) as d_rk
    from linux;
    
    
    #	解释:
    #		我又插入了一些数据来体现三者的不同。
    #   RANK:  从1 开始,按照顺序相同会重复名次会留下空的位置 生成组内的记录编号,如rk列的446;
    #   ROW_NUMBER: 从1 开始,按照顺序生成组内的记录编号,第456行直接顺序记录的编号;
    #   DENSE_RANK:从1 开始,按照顺序生成组内的记录编号相同会重复名次不会会留下空的位置,如44;

    注意:这三种内置函数只是赋予编号并不是直接能够排列,需要over()中的order by,当删除上述代码中的desc时,编号就会变为降序,注意下面的rk:

    select name,
           dt,
           cnt,
           sum(cnt) over (partition by name order by dt )    as sum_all,
           RANK() over (partition by name order by cnt )    as rk,
           ROW_NUMBER() over (partition by name order by cnt desc)  as rw,
           DENSE_RANK() over (partition by name order by cnt desc ) as d_rk
    from linux;

    • 内置窗口函数——LEAD、LAG 取值之串行
    select name,
           dt,
           cnt,
           sum(cnt) over (partition by name order by dt )                  as sum_all,
           LEAD(dt, 1, "9999-99-99") over (partition by name order by dt ) as lead_alias,
           LAG(dt, 1, "9999-99-99") over (partition by name order by dt )  as lag_alias
    from linux;
    
    #	解释:
    #		LEAD:窗口内 向下 第n行的值
    #		LAG:窗口内 向上 第n行的值
    #		参数1:要取值的列
    #		参数2:取上数或者下数第几行的值	
    #		参数3:如果没有值,则用参数3的值代替,例如LAG函数,lag_alias列 第1行上面取不到,
    # 	则用9999-99-99;

    • 内置窗口函数——FIRST_VALUE、LAST_VALUE取值之窗口首尾值
    select name,
           dt,
           cnt,
           sum(cnt) over (partition by name order by dt )         as sum_all,
           FIRST_VALUE(cnt) over (partition by name order by dt ) as fv,
           LAST_VALUE(cnt) over (partition by name order by dt )  as lv
    from linux;
    
    # 解释
    # 	FIRST_VALUE:取窗口第一个值
    #		LAST_VALUE:去窗口的最后一个值
    #		注意:这里也是一个动态的当第n行时,如果不做特殊指定,当到达第n行时,每组的窗口范围是
    #		从第1行到第n行,因此第一组的窗口的FIRST_VALUE都是第1行的值,LAST_VALUE都是当前行的值


     SQL文件在这里,可以自己下载试一下https://download.csdn.net/download/Mr__Sun__/85172998

    展开全文
  • MySQL——开窗函数

    千次阅读 2022-02-08 22:10:14
    # 开窗函数语法 func_name() OVER([PARTITION BY ] [ORDER BY <order_by_list> ASC|DESC]) 开窗函数语句解析: 函数分为两部分,一部分是函数名称,开窗函数的数量比较少,总共才11个开窗函数+聚合函数(所有的聚合...
  • SQL中的开窗函数

    2022-05-25 13:48:50
    OVER的定义 OVER用于为行定义一个窗口,它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。...开窗函数不需要使用GROUP BY就可以对数据进行分组,...
  • MySql只支持Union(并集)集合运算;但是对于交集Intersect、差集Except,就没有实现了。一般的解决方案用in和not in来解决,小量数据还可以,但数据...伪列:实现类似Oracle,MySqlServer的Over() partition by 开窗函数
  • Hive 开窗函数

    万次阅读 多人点赞 2018-08-06 00:19:44
    工作中用到了几个hive开窗函数,便想把hive开窗函数系统梳理一遍。 开窗函数 普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值,而开窗函数则可为窗口中...
  • 大数据-Hive开窗函数

    千次阅读 2021-12-20 22:01:42
    Hive开窗函数结构: xx() over(partition by xx order by xx) Hive开窗函数,大致有以下两类: 1. 排序开窗函数 row_number():组内排名后获取行号 rank():组内排名(连续) dense_rank():组内排名(跳跃) first_...
  • mysql开窗函数

    千次阅读 2021-01-20 01:06:09
    开窗函数:它可以理解为记录集合,开窗函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,...
  • 常见的开窗函数

    千次阅读 2022-04-27 21:49:29
    开窗函数与聚合函数计算方式一样,开窗函数也是对行集组进行聚合计算,但是它不像普通聚合函数那样每组只返回一个值,开窗函数可以为每组返回多个值。 开窗函数的语法为:over(partition by 列名1 order by 列名2 )...
  • 开窗函数(分析函数)2.7.1 特点:开窗函数也就是在满足某种条件的记录集合上执行的特殊函数;对于每条记录都要在此窗口内执行函数;有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,...
  • oracle开窗函数

    千次阅读 2021-08-03 14:29:51
    开窗函数为 : over() 说明 oracle开窗函数和group by还是有不一样的地方, group by 是分组后就直接去重了, 开窗函数分组后不去重,并且还可以进行排序 使用方法 over(PARTITION BY age ) 表示对结果集按照age...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 380,738
精华内容 152,295
关键字:

开窗函数