精华内容
下载资源
问答
  • sql开窗函数
    2021-11-18 10:52:38

    SQL 开窗函数以及复杂筛选·去重

    开窗函数 · 简介

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

    常用语法

    1、row_number() over(partition byorder by)   --增加一列,类似与增加伪列 
    2、rank() over(partition byorder by) 
    3、dense_rank() over(partition byorder by)   rank(): --跳跃排序,如果有两个第一级时,接下来就是第三级。  dense_rank(): 连续排序,如果有两个第一级时,接下来仍然是第二级。 
    4count() over(partition byorder by) 
    5max() over(partition byorder by) 
    6min() over(partition byorder by) 
    7sum() over(partition byorder by) 
    8avg() over(partition byorder by) 
    9、first_value() over(partition byorder by) 
    10、last_value() over(partition byorder by)   --与函数的功能一致,只是是开窗函数 
    11、lag() over(partition byorder by) 
    12、lead() over(partition byorder by)   --lag 和lead 可以 获取结果集中,按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联); lag ,lead 分别是向前,向后; lag 和lead 有三个参数,第一个参数是列名,第二个参数是偏移的offset,第三个参数是 超出记录窗口时的默认值)
    

    例子一

    去重查询,并按照指定列排序

    • 取出最新数据
    • 按照指定列去重
    SELECT * FROM 
    (
    select *,ROW_NUMBER() 
    over(partition by a,b order by c desc) AS toRow from 
    (
    SELECT a,b,c FROM PURTL tabA
    LEFT JOIN PURTM tabB ON tabA.a = tabB.b 
    ON a.a= b.a AND a.b = b.b
    ) as resA 
    ) AS resB WHERE u.toRow = 1
    

    持续更新中

    更多相关内容
  • 主要介绍了SQL开窗函数的具体实现详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
  • 开窗函数:在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在 2003 年 ISO SQL 标准加入了开窗函数开窗函数的使用使得这些经典的...
  • SQL开窗函数

    2021-06-16 21:22:45
    开窗函数有两类:一类是聚合开窗函数,一类是排序开窗函数; OVER关键字; PARTITION BY 子句; ORDER BY子句;

    概述

    需求背景:
    有时一组数据只返回一组值不能满足需求,如经常想知道各个地区的前几名、各个班或各个学科的前几名。这时候需要每一组返回多个值。

    2003 年 ISO SQL 标准加入开窗函数,目前在 MS SQLServer、Oracle、DB2等主流数据库中都提供对开窗函数的支持,不过MySQL暂时还未对开窗函数给予支持。

    简介:与聚合函数一样,开窗函数也是对行集组进行聚合计算,但是它不像普通聚合函数那样每组只返回一个值,开窗函数可以为每组返回多个值,因为开窗函数所执行聚合计算的行集组是窗口。在 ISO SQL 规定这样的函数为开窗函数,在 Oracle 中则被称为分析函数。

    开窗函数有两类:一类是聚合开窗函数,一类是排序开窗函数

    窗函数和聚合函数的区别:

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

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

    PARTITION BY 子句:
    开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同,PARTITION BY 子句创建的分区独立于结果集,创建的分区只是供进行聚合计算的,且不同的开窗函数所创建的分区也不互相影响。
    显示每一个人员的信息以及所属城市的人员数:
    select name, city, age, salary, count(*) over(partition by city) from person
    在同一个SELECT语句中可以同时使用多个开窗函数,而且这些开窗函数并不会相互干扰

    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部分用来定位聚合计算范围,这个子句又被称为定位框架。

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

    高级

    在开窗函数中可使用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 name, salary, age, 
    RANK() OVER(ORDER BY salary desc) RANK,
    DENSE_RANK() OVER(ORDER BY salary desc) DENSE_RANK,
    ROW_NUMBER() OVER(ORDER BY salary desc) ROW_NUMBER
    FROM person;
    

    参考

    SQL开窗函数

    展开全文
  • SQL开窗函数总结

    千次阅读 2021-06-03 21:39:20
    分析函数中,窗口函数内的ROWS条件

    分析函数中,窗口函数内的ROWS条件

    SQL窗口函数

    https://blog.csdn.net/qq_31183727/article/details/107023293

    一、什么是开窗函数

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

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

    开窗函数的调用格式为:

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

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

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

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

    2. 执行顺序

    在使用 row_number() over()函数时候,over()里头的分组以及排序的执行,晚于 where 、group by、  order by 的执行

    具体可查看文章:https://blog.csdn.net/qq_25221835/article/details/82762416

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

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

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

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

    几个排序函数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。

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

    查询结果如下:

    4. 其他开窗函数

    1、row_number() over(partition by … order by …)

    增加一列,类似与增加伪列

    2、rank() over(partition by … order by …)

    3、dense_rank() over(partition by … order by …)

    rank(): 跳跃排序,如果有两个第一级时,接下来就是第三级。 
    dense_rank(): 连续排序,如果有两个第一级时,接下来仍然是第二级。

    4、count() over(partition by … order by …)

    5、max() over(partition by … order by …)

    6、min() over(partition by … order by …)

    7、sum() over(partition by … order by …)

    8、avg() over(partition by … order by …)

    9、first_value() over(partition by … order by …)

    10、last_value() over(partition by … order by …)

    与函数的功能一致,只是是开窗函数

    11、lag() over(partition by … order by …)

    12、lead() over(partition by … order by …)

    lag 和lead 可以 获取结果集中,按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联); 
    lag ,lead 分别是向前,向后; 
    lag 和lead 有三个参数,第一个参数是列名,第二个参数是偏移的offset,第三个参数是 超出记录窗口时的默认值)本段转自:https://www.bbsmax.com/A/q4zVkPLxJK/

     

    展开全文
  • 开窗函数:在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在 2003 年 ISO 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

     

     

    展开全文
  • SQL 标准允许将所有聚合函数用作开窗函数,用OVER 关键字区分开窗函数和聚合函数 目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持,但在 mysql中还不支持。 经常能用到的排序函数 (1) ...
  • SQL开窗函数(窗口函数)详解

    万次阅读 多人点赞 2020-06-29 17:08:17
    一、什么是开窗函数 开窗函数/分析函数:over() 开窗函数也叫分析函数,有两类:一类是聚合开窗函数,一类是排序开窗函数开窗函数的调用格式为: 函数名(列名) OVER(partition by 列名 order by列名) 。 ...
  • Hive SQL开窗函数详解

    2021-01-31 19:05:21
    Hive 开窗函数 group by 是分组函数,一组出来一个数据 over() 开窗,针对每一条数据,都有一个独立的组 mk 3 jk 3 mk 3 select orderdate,cost,sum(cost) over(order by orderdate) over里面的函数 是对窗口大小的...
  • SQL开窗函数(Oracle)

    2019-07-16 11:22:15
    开窗函数出现之前存在着很多用 SQL 语句很难解决的...为了解决这些问题,在 2003 年 ISO SQL 标准加入了开窗函数开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数...
  • Hive SQL开窗函数实战

    2019-07-06 15:43:06
    Hive SQL开窗函数实战开窗函数是数据的一种查询统计语法糖,多是用于离线统计,这同时也是大数据技术栈的应用场景。今天学习Hive SQL的开窗(窗口)函数,对比与My...
  • 无法通过 lead, lag 等方法实现,因为开窗函数选定的数据框无法加上条件 思路 - 通过两次 Join 分别找到前后最近的 SSSSSS*记录,代码如下 var df = spark.read.option("header", "true").csv("C:\\Users\\XXX\\...
  • SQL 开窗函数

    2022-01-20 19:08:44
    SQL 开窗函数⭐️前言????开窗函数--Mysql???? 建表语句✌ ROW_NUMBER()????定义???? SQL???? 说明✌ RANK()????定义???? SQL???? 说明✌ DENSE_RANK()????定义???? SQL???? 说明✌ NTILE(n)????定义???? SQL?...
  • SQL Server 2012 开窗函数

    2020-09-09 20:14:31
    主要介绍了SQL Server 2012 开窗函数功能的实例代码,具体代码如下所示
  • 主要介绍了Sql Server 开窗函数Over()的使用,非常不错,具有一定的参考借鉴价值,需要的朋友可以参考下
  • SQL开窗函数

    2022-05-05 23:08:48
    SQL开窗函数ROW_NUMBER()
  • SQL开窗函数

    2022-01-04 17:20:47
    开窗函数 一、含义 开窗函数用于为行定义一个窗口(指运算将要操作的行的集合),它对一组值进行操作,不需要使用 Group By 子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。 二、语法 over...
  • hive sql常用开窗函数

    2022-05-09 18:22:56
    开窗函数:定义一个行为列,在查询结果上直接新增一列窗口函数值,开窗函数的特征是带有over()。开窗函数从使用目的上可以分为两类:排序开窗函数和聚合开窗函数。 常用排序开窗函数: row_number() over(partition...
  • SQL中的开窗函数

    千次阅读 2022-05-25 13:48:50
    OVER的定义 OVER用于为行定义一个窗口,它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。...开窗函数不需要使用GROUP BY就可以对数据进行分组,...
  • Hive SQL——开窗函数

    2022-06-23 23:32:47
    4、 窗口函数目录 4、 窗口函数 4.1 排序窗口函数rank 4.2 rank(), dense_rank(), row_number()区别 4.3 、排序截取数据lag(),lead(),ntile(),cume_dist() 4.4 聚合函数作为窗口函数 4.4、over(- - rows ...
  • 查询字段就只能是分组字段和聚合的字段,这带来了极大的不方便,有时我们查询时需要分组,又需要查询不分组的字段,每次都要又到子查询,这样显得sql语句复杂难懂,给维护代码的人带来很大的痛苦,然而开窗函数出现...
  • Spark SQL 开窗函数

    2020-03-23 22:28:09
    谈到 SQL开窗函数,要说到HIVE了,因为这个是HIVE支持的特性,但是在Spark SQL中支持HIVE 的。那么让我们看一看开窗函数是什么吧。 什么是开窗函数呢 ? 开窗函数也叫分析函数,有两类:一类是聚合开窗函数,一...
  • SQL知识点(一):SQL中的开窗函数

    千次阅读 2021-12-11 22:39:26
    SQL中的开创函数(附牛客SQL23题【 对所有员工的薪水按照salary降序进行1-N的排名 】)
  • sql开窗函数

    2022-02-23 16:19:10
    https://www.cnblogs.com/scwbky/p/9558203.html
  • SQL 开窗函数使用

    千次阅读 2019-08-21 15:31:30
    好多次笔试面试的题都考到开窗函数,若对开窗函数不了解,真的吃了好大的亏,今天花点时间整理一下。 此文章整理的知识点部分来自:https://www.cnblogs.com/lihaoyang/p/6756956.html 1.开窗函数简介 与聚合函数...
  • SQL开窗函数(窗口函数)

    千次阅读 多人点赞 2021-07-22 21:19:03
    4、 窗口函数 目录4、 窗口函数4.1 排序窗口函数rank4.2 rank(), dense_rank(), row_number()区别4.3 、排序截取数据lag(),lead(),ntile(),cume_dist()4.4 聚合函数作为窗口函数4.4、over(- - ...开窗函数一般分为两类,

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 62,281
精华内容 24,912
关键字:

sql开窗函数