精华内容
下载资源
问答
  • SQL开窗函数

    2021-06-16 21:22:45
    2003 年 ISO SQL 标准加入开窗函数,目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供对开窗函数的支持,不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。 简介:与聚合函数一样,开窗函数也是对行集组...

    概述

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

    2003 年 ISO SQL 标准加入开窗函数,目前在 MSSQLServer、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 开窗函数

    2018-12-13 17:24:57
    开窗函数:在开窗函数出现之前存在着很多用 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 标准允许将所有聚合

    与聚合函数一样,开窗函数也是对行集组进行聚合计算,但是普通聚合函数每组只能返回一个值,而开窗函数可以每组返回多个值。

    实验一
    比如我们想查询每个工资小于5000元的员工信息(城市以及年龄),并且在每行中都显示所有工资小于5000元的员工个数,执行下面的SQL语句

    select t.fcity,t.fage,count(*) from person t where t.fsalary<5000
     
    这个语句显然是错误的,因为count()是聚合函数,然后fname和fage字段没有包含分组里面。

    实验二
    那么,这样写呢?

    select t.fcity,t.fage,count(*) from person t where t.fsalary<5000 group by t.fcity,t.fage
    查询结果


    这与我们每行中都显示所有工资小于5000元的员工个数这个条件是不符合的,那么应该怎么写呢?

    实验三
    select t.fcity,
           t.fage,
           (select count(*) from person f where f.fsalary < 5000)
      from person t
     where t.fsalary < 5000    
    查询结果:


    这次的查询结果和我们想要的结果一样了,但是这样写多了一个子查询,非常麻烦。使用开窗函数可以大大简化实现,下面看一下开窗函数要实现这个效果怎么写

    实验四
    select t.fcity, t.fage, count(*) over()
       from person t
      where t.fsalary < 5000
    看下执行效果:


    可以看到这个SQL语句与我们第一个实验不同的是我们在count(*)后面加了一个over关键字。

    开窗函数的调用格式为:

             函数名(列)over(选项)

    over关键字表示把函数当成开窗函数而不是聚合函数,SQL标准允许将所有聚合函数用做开窗函数,使用over关键字来区分这两种用法。

    在上面的例子中,开窗函数count(*) over()对于查询结果的每一行都返回所有符合条件的行的条数,over关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围(后面博客会持续更新),如果over关键字后的括号中选项为空,则开窗函数会对结果集中的所有行进行聚合运算。当然,不只是count(*) over,max(fage) over(),min(fage) over()都可以。
    --------------------- 开窗函数与聚合函数一样,也是对行集组进行聚合计算,但是它不像普通聚合函数那样每组只返回一个值,开窗函数可以为每组返回多个值,因为开窗函数所执行聚合计算的行集组是窗口。 
    语法:主要是over( PARTITION BY (根据某条件分组,形成一个小组)….ORDER BY(再组内进行排序) …. ) 
    常用函数:(最常用的应该是1.2.3 的排序) 
    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,第三个参数是 超出记录窗口时的默认值)
    --------------------- 

    1.基本概念

    开窗函数分为两个部分分别是

    1.聚合,排名,偏移,分布函数 。

    2.开窗分区,排序,框架。

    下面举个例子

    复制代码

    SELECT empid, ordermonth, val,
      SUM(val) OVER(PARTITION BY empid
                    ORDER BY ordermonth
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW) AS runval
    FROM Sales.EmpOrders;

    复制代码

    sum(val)  就是集合函数

    over() 就是开窗     PARTITION BY empid  就是开窗分区(分组)   ORDER BY ordermonth 开窗排序  

     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  开窗架构

    2.排名开窗函数

     SQL SERVER 支持4个排名函数 ROW_NUMBER,RANK,DENSE_RANK ,NTLE   来看看它们分别的作用

    复制代码

    SELECT orderid, custid, val,
    ROW_NUMBER() OVER(ORDER BY val) AS rownum,
    RANK() OVER(ORDER BY val) AS rank,
    DENSE_RANK() OVER(ORDER BY val) AS dense_rank,
    NTILE(10) OVER(ORDER BY val) AS ntile
    FROM Sales.OrderValues
    ORDER BY val;

    复制代码

    可以看到 它们不同排序规则

    ROW_NUMBER() 对排序字段行号进行排序  

    RANK() 对数值进行排序 对相同数值有行号占用

    DENSE_RANK() 也是对数值排序 如果有相同数值 依旧会按照原先行号加

    NTILE 分区排序 为每一行分配一个区号 如果分10区 会对所有数据进行分区  总数据/分区数  就是每多少数据为一区

    ROW_NUMBER()  默认在 DISTINCT 筛选重复项之前计算

    2.偏移开窗函数 

    sql server 中总共有4个偏移函数 LAG 和 LEAD, FIRST_VALUE 和 LAST_VALUE   

    LAG函数在当前行之前查找

    LEAD函数在当前行之后查找

    LAST_VALUE    返回查找结果的最后一行

    FIRST_VALUE  返回查找结果的第一行

     3.聚合开窗函数

    SUM() OVER()

    AVG() OVER()

    COUNT() OVER()

    MAX() OVER() 等等

    分析及开窗函数

    转自http://hi.baidu.com/edgar108/blog/item/e24c7fd66b0817d7a144dfc8.html 

    我以oracle中的emp 和dept表为例,讲一下开窗函数。

    假如,现在有这样的要求:查出所有的员工的名字ename,薪水sal 以及他的薪水占说有员工薪水的比例。
    一开始,我们的思路可能是这样:
    select ename ,sal ,sal/sum(sal) from emp;
    但是这样写是不对的,sum()是一个单行统计函数,只返回一个值,不能和其他字段同时出现。
    解决办法就是使用开窗函数over()
    select ename ,sal ,sal/sum(sal) over() as percent from emp;
    查询结果:
    ENAME             SAL    PERCENT
    ---------- ---------- ----------
    SMITH             800 .027562446
    ALLEN            1600 .055124892
    WARD             1250 .043066322
    JONES            2975 .102497847
    MARTIN           1250 .043066322
    BLAKE            2850 .098191214
    CLARK            2450 .084409991
    SCOTT            3000 .103359173
    KING             5000 .172265289
    TURNER           1500 .051679587
    ADAMS            1100 .037898363

    ENAME             SAL    PERCENT
    ---------- ---------- ----------
    JAMES             950 .032730405
    FORD             3000 .103359173
    MILLER           1300 .044788975

    已选择14行。

    上面的over是指把前面的函数(本例中是sum())当成开窗函数而不是统计函数,SQL标准允许讲所有的统计函数
    用作开窗函数,使用over关键字来区分这两种用法。

    上面的“sum(sal) over()”的意思是,对于每一条记录,都去计算一次sal的和。如果over关键字后的括号中的选项为空,

    把上面的sql改进一下:
    select ename ,sal ,'0'||round(sal/sum(sal) over(),3) as percent from emp;
    查询结果:

    ENAME             SAL PERCENT
    ---------- ---------- -----------------------------------------
    SMITH             800 0.028
    ALLEN            1600 0.055
    WARD             1250 0.043
    JONES            2975 0.102
    MARTIN           1250 0.043
    BLAKE            2850 0.098
    CLARK            2450 0.084
    SCOTT            3000 0.103
    KING             5000 0.172
    TURNER           1500 0.052
    ADAMS            1100 0.038

    ENAME             SAL PERCENT
    ---------- ---------- -----------------------------------------
    JAMES             950 0.033
    FORD             3000 0.103
    MILLER           1300 0.045

    已选择14行。

    如果现在像查询每个员工的姓名ename,工资sal,以及他的工资占他所在部门的比例,按照上面的思路,这次要这样写:

    select ename,deptno,sal,'0'|| round(sal/sum(sal) over(partition by deptno),3) from emp;

    如果需要对sal排序,再partition by deptno 后面 再加上order by sal:

    select ename,deptno,sal,'0'|| round(sal/sum(sal) over(partition by deptno order by sal),3) from emp;

    ORDER BY 的完整语法为 ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2
    RANGE 表示 按照值的范围进行范围的定义,而 ROWS 表示按照行的范围进行范围的定义

    边界规则的取值见下表:

    可取值                                                说明                                   示例

    CURRENT ROW                              当前行

    N PRECEDING                                 前N行                                    2 PRECEDING

    UNBOUNDED PRECEDING          一直到第一条记录      

    N FOLLOWING                                 后N行                                  2 FOLLOWING

    UNBOUNDED FOLLOWING          一直到最后一条记录


    但是,如果这样写,会报错:
    select ename,deptno,sal,'0'|| round(sal/sum(sal) over(order by sal partition by deptno ),3) from emp;可能 order by不能写在partition by的前面。


    如果现在按照员工的姓名排序,并计算工资的累加和:

    select ename ,sal ,sum(sal) over(order by sal rows between unbounded preceding and current row) as result from emp;

    order by sal rows between unbounded preceding and current row 的意思是: 按照sal进行排序,然后计算从第一行(unbounded preceding)到当前行
    (current row)的和,这样的结果就是按照工资进行排序的工作值的累加和。

    因为ROWS 表示按照行的范围进行范围的定义,所以计算从第一行到当前行的累加和。

    如果把ROWS换成 RANGE :

    select ename ,sal ,sum(sal) over(order by sal range between unbounded preceding and current row) as result from emp;

    ENAME             SAL     RESULT
    ---------- ---------- ----------
    SMITH             800        800
    JAMES             950       1750 (800+950)
    ADAMS            1100       2850 (800+950+1100)
    WARD             1250       5350
    MARTIN           1250       5350
    MILLER           1300       6650
    TURNER           1500       8150
    ALLEN            1600       9750
    CLARK            2450      12200
    BLAKE            2850      15050
    JONES            2975      18025

    ENAME             SAL     RESULT
    ---------- ---------- ----------
    SCOTT            3000      24025
    FORD             3000      24025
    KING             5000      29025

    已选择14行。


    RANGE 表示 按照值的范围进行范围的定义 ,在计算累加和的过程中,如果遇到相同的值(本例中为sal),则计算所有的相同值同时累加
    (本例中SCOTT,FORD的sal全是3000,所以值是 18025+3000+3000=24025)

    select ename ,sal ,sum(sal) over(order by sal rows between 2 preceding and 2 following) as result from emp;

    ENAME             SAL     RESULT
    ---------- ---------- ----------
    SMITH             800       2850 (800+950+1100)
    JAMES             950       4100
    ADAMS            1100       5350
    WARD             1250       5850
    MARTIN           1250       6400
    MILLER           1300       6900 
    TURNER           1500       8100 (1250+1300+1500+1600+2450)
    ALLEN            1600       9700
    CLARK            2450      11375
    BLAKE            2850      12875
    JONES            2975      14275 (2450+2850+2975+3000+3000)

    ENAME             SAL     RESULT
    ---------- ---------- ----------
    SCOTT            3000      16825
    FORD             3000      13975
    KING             5000      11000 (3000+3000+5000)

    已选择14行。

    sum(sal) over(order by sal rows between 2 preceding and 2 following)
    按照sal进行排序,然后计算从当前行前两行(2 preceding) 到 当前行后两行(2 following)的累加和

    对于第1行到第2行(n=2),“前2行”是不存在或不完整的,所以按照前两行不存在或不完整来计算,最后2行类似。

    select ename ,sal ,sum(sal) over(order by sal rows between 1 following and 3 following) as result from emp;

    ENAME             SAL     RESULT
    ---------- ---------- ----------
    SMITH             800       3300 (950+1100+1250)
    JAMES             950       3600
    ADAMS            1100       3800
    WARD             1250       4050
    MARTIN           1250       4400
    MILLER           1300       5550
    TURNER           1500       6900
    ALLEN            1600       8275
    CLARK            2450       8825
    BLAKE            2850       8975
    JONES            2975      11000

    ENAME             SAL     RESULT
    ---------- ---------- ----------
    SCOTT            3000       8000
    FORD             3000       5000
    KING             5000                 (后面没有数据了,所以是NULL)

    已选择14行。

    计算的某一列后1行到后3行的值

    select ename ,sal ,sum(sal) over(order by sal range between unbounded preceding and current row) as result from emp;

    select ename ,sal ,sum(sal) over(order by sal) as result from emp;
    是等价的。
    也就是说 range between unbounded preceding and current row 是默认的定位方式。

    select ename ,sal ,count(*) over(order by sal desc rows between unbounded preceding and current row) as result from emp;

    ENAME             SAL     RESULT
    ---------- ---------- ----------
    KING             5000          1
    FORD             3000          2
    SCOTT            3000          3
    JONES            2975          4
    BLAKE            2850          5
    CLARK            2450          6
    ALLEN            1600          7
    TURNER           1500          8
    MILLER           1300          9
    WARD             1250         10
    MARTIN           1250         11

    ENAME             SAL     RESULT
    ---------- ---------- ----------
    ADAMS            1100         12
    JAMES             950         13
    SMITH             800         14

    已选择14行。
    order by sal desc rows between unbounded preceding and current row 表示按照sal的降序排列,计算从第一行到当前行的个数,所以这个可以看作员工工资的排名。

    展开全文
  • sql开窗函数

    2019-01-15 09:32:39
    开窗函数 rank()跳跃排序,有两个第二名时后边跟着的是第四名 dense_rank() 连续排序,有两个第二名时仍然跟着第三名 over()开窗函数: 在使用聚合函数后,会将多行变成一行,而开窗函数是将一行变成多行; 并且...

    开窗函数

    rank()跳跃排序,有两个第二名时后边跟着的是第四名
    dense_rank() 连续排序,有两个第二名时仍然跟着第三名
    over()开窗函数:
    在使用聚合函数后,会将多行变成一行,而开窗函数是将一行变成多行;
    并且在使用聚合函数后,如果要显示其他的列必须将列加入到group by中,
    而使用开窗函数后,可以不使用group by,直接将所有信息显示出来。
    开窗函数适用于在每一行的最后一列添加聚合函数的结果。
    常用开窗函数:
    1.为每条数据显示聚合信息.(聚合函数() over())
    2.为每条数据提供分组的聚合函数结果(聚合函数() over(partition by 字段) as 别名)
    –按照字段分组,分组后进行计算
    3.与排名函数一起使用(row number() over(order by 字段) as 别名)
    常用分析函数:(最常用的应该是1.2.3 的排序)
    1、row_number() over(partition by … order by …)
    2、rank() over(partition by … order by …)
    3、dense_rank() over(partition by … order by …)
    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,第三个参数是 超出记录窗口时的默认值

    展开全文
  • SQL 开窗函数

    2019-07-05 18:07:00
    使用开窗函数的时候遇到一个问题 :分组的第一行如果不满足条件则为null,原来over()开窗函数如果使用了order by的时候默认窗口是第一行到当前行,因此会出现第一行为null的情况 如果over()没有使用order by 则...
    使用开窗函数的时候遇到一个问题 :分组的第一行如果不满足条件则为null,原来over()开窗函数如果使用了order by的时候默认窗口是第一行到当前行,因此会出现第一行为null的情况
     

    如果over()没有使用order by 则窗口是分组内的第一行至最后一行

     

    OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。
    CURRENT ROW:当前行
    n PRECEDING:往前n行数据
    n FOLLOWING:往后n行数据
    UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING表示到后面的终点
    LAG(col,n,default_val):往前第n行数据
    LEAD(col,n, default_val):往后第n行数据
    NTILE(n):把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。注意:n必须为int类型
    窗口就是分析函数分析时要处理的数据范围

    开窗函数over()出现分组(partition by)子句时,

    unbounded preceding即第一行是指表中一个分组里的第一行, unbounded following即最后一行是指表中一个分组里的最后一行;

    开窗函数over()省略了分组(partition by)子句时, 

    unbounded preceding即第一行是指表中的第一行, unbounded following即最后一行是指表中的最后一行。

    而出现order by子句的时候,不一定要有窗口子句,但效果会很不一样,此时的窗口默认是当前组的第一行到当前行!

     

    参考https://www.cnblogs.com/cjm123/p/8033892.html

    转载于:https://www.cnblogs.com/chungfenghuayu/p/11139945.html

    展开全文
  • 主要介绍了SQL开窗函数的具体实现详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
  • Hive SQL开窗函数实战

    2019-07-06 15:43:06
    Hive SQL开窗函数实战开窗函数是数据的一种查询统计语法糖,多是用于离线统计,这同时也是大数据技术栈的应用场景。今天学习Hive SQL的开窗(窗口)函数,对比与My...
  • 开窗函数:在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在 2003 年 ISO SQL 标准加入了开窗函数开窗函数的使用使得这些经典的...
  • Spark SQL 开窗函数

    2020-03-23 22:28:09
    谈到 SQL开窗函数,要说到HIVE了,因为这个是HIVE支持的特性,但是在Spark SQL中支持HIVE 的。那么让我们看一看开窗函数是什么吧。 什么是开窗函数呢 ? 开窗函数也叫分析函数,有两类:一类是聚合开窗函数,一...
  • 开窗函数:在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在 2003 年 ISO SQL 标准加入了开窗函数开窗函数的使用使得这些经典的...
  • SQL开窗函数(窗口函数)详解

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

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

    2020-07-26 19:45:33
    目录一、定义:1.1 使用group by进行查询1.2 使用开窗函数1.3 使用ORDER BY1.4 rows关键字 指定窗口范围二、多种分析函数的使用2.1 min函数2.2 sum开窗函数2.3 first_value开窗函数2.4 last_vlaue开窗函数2.5 lag...
  • --1.简介: --SQL Server 中的窗口函数帮助你迅速查看不同级别的聚合,通过它可以非常方便地累计总数、移动平均值、以及执行其它计算。...--排名开窗函数和聚合开窗函数. --也就是说窗口函数是结...
  • 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里面的函数 是对窗口大小的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,328
精华内容 531
关键字:

sql开窗函数