精华内容
下载资源
问答
  • Hive开窗函数Rank函数
    2019-03-04 16:42:07

    开窗函数

    over():跟在聚合函数后面,指定分析函数工作的数据窗口大小,决定了数据的聚合范围,默认范围是整个数据窗口,即所有行。可使用partition by将数据进行分组聚合;使用order by除了排序(默认asc升序),还会改变聚合范围(从开始行到当前行的聚合),使用distribute by…sort by…和partition by…order by…效果一样。
    如下的窗口子句,能对聚合数据进一步的划分:
    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开始,一共分成n片。

    例子

    create table business(name string, orderdate string, cost int);
    

    (1)查询在2019年1月份购买过的顾客及总人数

    select name,count(*) over()
    from business
    where date_format(orderdate, 'yyyy-MM')='2019-01'
    group by name;
    

    注意“总人数”指顾客数量,如果不使用over(),结果将是每位顾客对应的购买次数,此处over()省略窗口字句ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING,即针对所有行。
    (2)查询顾客的购买明细及月购买总额

    select name, orderdate, cost, sum(cost) over(partition by month(orderdate))
    from business;
    或:
    select name, orderdate, cost, sum(cost) over(distribute by month(orderdate))
    from business;
    

    由于查询字段中含有与orderdate无关的字段,故使用不了group by。
    (3)查询顾客购买明细,以及每个顾客的cost按照日期进行累加

    select name, orderdate, cost, sum(cost) over(partition by name order by orderdate)
    from business;
    

    order by的聚合范围即从开始行到当前行的聚合,加上窗口语句“ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”效果一样。
    (4)查询顾客上次的购买时间

    select name, orderdate, lag(orderdate, 1, '0000-00-00') over(partition by name order by orderdate) last_time
    from business;
    

    lag函数如果没有上n次的值,返回null,或者指定的默认值。
    (5)查询前20%时间的订单信息

    select name, orderdate, cost from(
    select name, orderdate, cost, ntile(5) over(order by orderdate) arr
    from business
    )
    where arr = 1;
    

    需要查询前20%(即1/5)的数据可以使用ntile函数,切成5片数据,再从中取第一份数据。

    Rank函数

    rank():相同时排名值会重复,总数不会变,故相同时排名值会跳跃而不连续
    row_number():相同时排名值会根据顺序计算,不会重复
    dense_rank():相同时排名值会重复,总数会减少

    例子

    create table score(name string, subject string, score int);
    

    计算每门学科成绩排名

    select name, subject,score,rank()
    over(partition by subject order by score desc)
    from score;
    
    更多相关内容
  • 开窗函数(分析函数)2.7.1 特点:开窗函数也就是在满足某种条件的记录集合上执行的特殊函数;对于每条记录都要在此窗口内执行函数;有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,...

    开窗函数(分析函数)

    2.7.1 特点:开窗函数也就是在满足某种条件的记录集合上执行的特殊函数;

    对于每条记录都要在此窗口内执行函数;

    有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;

    有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口;

    开窗函数的本质还是聚合运算,只不过它更具灵活性,它对数据的每一行,都使用与该行相关的行进行计算并返回计算结果。

    2.7.2 开窗函数和普通聚合函数的区别聚合函数是将多条记录聚合为一条;而开窗函数是每条记录都会执行,有几条记录执行完还是几条

    聚合函数也可以用于开窗函数中。

    2.7.3 语法开窗函数名([]) over([partition by ] [order by [desc]] [])partition by子句:按照指定字段进行分区,两个分区由边界分隔,开窗函数在不同的分区内分别执行,在跨越分区边界时重新初始化。

    order by子句:按照指定字段进行排序,开窗函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用,也可以单独使用。

    frame子句:当前分区的一个子集,用来定义子集的规则,通常用来作为滑动窗口使用。

    关于over的参数over参数为空,没有指定分区、排序、滑动窗口时,开窗函数将整个表作为一个区,默认计算的是所有值;

    over指定了分区,未指定排序和滑动窗口,开窗函数默认计算分区内的所有值;

    over指定了分区、排序,未指定滑动窗口,开窗函数默认计算的时第一行到当前行的值;

    2.7.4 滑动窗口范围指定的两种方式方式一:基于行

    对于滑动窗口的范围指定,通常使用 between frame_start and frame_end 语法来表示行范围,frame_start和frame_end可以支持如下关键字,来确定不同的动态行记录:current row 边界是当前行,一般和其他范围关键字一起使用

    unbounded preceding 边界是分区中的第一行

    unbounded following 边界是分区中的最后一行

    expr preceding 边界是当前行减去expr的值

    expr following 边界是当前行加上expr的值

    移动平均可用来消除差额较大的情况,平滑数据方式二:基于范围

    和基于行类似,但有些范围不是直接可以用行数来表示的,比如希望窗口范围是一周前的订单开始,截止到当前行,则无法使用rows来直接表示,此时就可以使用范围来表示窗口:interval 7 day preceding。

    2.7.5 开窗函数分类动态窗口函数:first_value() / last_value()/nth_value()/聚合函数用于开窗

    如没有指定排序和滑动窗口范围,默认计算的是分区内的所有记录。 指定分区和排序后,如没有指定滑动窗口范围,默认计算的是分区内的第一行到当前行。静态窗口函数:row_number() / rank() / dense_rank()/percent_rank() / cume_dist()/lag() / lead()/ntile()

    不管是否指定滑动窗口范围,窗口都是固定的,所以指定的滑动窗口范围无效。

    2.7.6 按照函数功能不同,MySQL支持的开窗函数分为如下几类:序号函数:row_number() / rank() / dense_rank()

    函数名显示内容显示样式row_number()显示分区中不重复不间断的序号1,2,3,4...dense_rank()显示分区中重复不间断的序号1,1,2,3,3...rank()显示分区中重复间断的序号1,1,3,4...分布函数:percent_rank() / cume_dist()percent_rank():和之前的rank()函数相关,每行计算:(rank - 1) / (rows - 1)

    其中,rank为rank()函数产生的序号,rows为当前窗口的记录总行数。rank() over(partition by user_no order by amount desc) rank1,

    percent_rank() over(partition by user_no order by amount desc) percentcume_dist(): 分组内小于等于当前rank值的行数/分组内总行数

    示例:查询大于等于当前订单金额的订单比例rank() over(partition by user_no order by amount desc) rank1,

    cume_dist() over(partition by user_no order by amount desc) cume前后函数:lag() / lead()

    分区中位于当前行前n行(lag)/后n行(lead)的记录值

    示例:查询上一个订单距离当前订单的间隔天数lag(create_date,1) over(partition by user_no order by create_date) last_date头尾函数:first_value() / last_value()

    分区中的第一个/最后一个指定参数的值

    示例:查询截止到当前订单,按照日期排序第一个订单和最后一个订单的订单金额first_value(amount) over(partition by user_no order by create_date) first_amount,

    last_value(amount) over(partition by user_no order by create_date) last_amount其他函数:nth_value() / nfile()nth_value(expr,n):返回窗口中第N个expr的值,expr可以是表达式,也可以是列名

    示例:每个用户订单中显示金额排名第二和第三的订单金额nth_value(amount,2) over(partition by user_no order by amount) second_amount,

    nth_value(amount,3) over(partition by user_no order by amount) third_amount

    2. nfile(n):将分区中的有序数据分为n个桶,记录桶号

    示例:将每个用户的订单按照订单金额分成3组

    此函数在数据分析中应用较多,比如由于数据量大,需要将数据平均分配到N个并行的进程分别计算,此时就可以用nfile(n)对数据进行分组,由于记录数不一定被n整除,所以数据不一定完全平均,然后将不同桶号的数据再分配。

    展开全文
  • 开窗函数:在开窗函数出现之前存在着很多用 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

    展开全文
  • 开窗函数over(),rank()的用法及例子

    千次阅读 2021-04-15 14:56:31
    开窗函数over的常用方法 先创建一个表(目前条件有限,日后再补,用表格先凑合一下。。。) 1.为每条数据显示聚合信息.(聚合函数() over() select *, max(num) over () as '杀敌最大数' fromtable_name 结果...

    介绍

    窗口函数OVER()指定一组行,开窗函数计算从窗口函数输出的结果集中各行的值。

    开窗函数不需要使用GROUP BY就可以对数据进行分组,还可以同时返回基础行的列和聚合列。

    开窗函数over的常用方法

    over() 的语法为:over([patition by ] <order by >)。

    需要注意的是,over() 前面是一个函数,如果是聚合函数,那么order by 不能一起使用。

    over() 的另一常用情景是与 row_number() 一起用于分页。

    先创建一个表(目前条件有限,日后再补,用表格先凑合一下。。。)

    1.为每条数据显示聚合信息.(聚合函数() over()

    select *, max(num) over () as  '杀敌最大数' from table_name

    结果:

    select *, sum(num) over () as  '杀敌总数' from table_name

    2.位每条数据提供分组的聚合函数结果(聚合函数() over(partition by 字段) as 别名)

    select *, sum(num) over(partition by name ) as'个人杀敌总数' from table_name

    将数据先进行分组再计算

    select *, max(num) over(partition by name ) as'个人杀敌总数' from table_name order by name


    例如最小数、最大数计算同理

    3.与排名函数一起使用(row number() over(order by 字段) as 别名)---固定搭配

    select *, row_number() over(partition by name  order by name) as '分组排序' from table_name

    先按姓名排序:

    按分组后排序

    select *, row_number() over(order by name) as '分组排序' from table_name

    rank()函数同理

    rank()over(partition by user_id order by user_date)

    上面不区分主键,只是按排序顺序进行数字递增,这个用的多

    -----------------------------------------------------------------------------------

    注意点1:row_number()函数是在distinct子句之前处理的

    有需要的后续补充吧

    展开全文
  • 1、窗口函数CURRENT ROW:当前行nPRECEDING:往前n行数据nFOLLOWING:往后n行数据UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点,UNBOUNDEDFOLLOWING表示到后面的终点LAG(col,n,default_val):往前第n行...
  • 开窗函数用法

    2020-12-20 13:24:36
    开窗函数OVER(PARTITIONBY)函数介绍开窗函数Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。开窗函数指定了...
  • 对于窗口函数,比如row_number(),rank(),...这是一个原始数据表,数据用于测试 第一部分:开窗函数和排名类函数结合1.使用SQL查看工资排名SELECT`name`,`dept`,`salary`,row_number () over (PARTITION BY `dept...
  • 报错信息是:开窗函数只能出现在 SELECT 或 ORDER BY 子句中。 这一点要注意咯~~ 下面,汇总每个学生的学科排行信息: Select *,rank()over(partition by studentid order by score desc) as [rank] ...
  • 看下这个例子,秒懂排序函数之间的区别   例子为五个同学的成绩,按从小到大排序,三个函数的结果如下: 简单来说:   rank() over () 并列有间隔,rank值为:1 2 2 4 5   dense_rank() over() 并列不间断...
  • MySQL8.0之后开始支持开窗函数,对于低版本的MySQL通过复杂一些的方式也能得到同样的效果: 表结构: CREATE TABLE `class` ( `student_id` char(4) NOT NULL, `class_id` tinyint NOT NULL, `score` double(6,0)...
  • Hive 开窗函数

    万次阅读 多人点赞 2018-08-06 00:19:44
    工作中用到了几个hive开窗函数,便想把hive开窗函数系统梳理一遍。 开窗函数 普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值,而开窗函数则可为窗口中...
  • 这是hql语句 查询结果 percent_rank() 含义就是 当前行-1 / 当前组总行数-1 比如第一行: 1-1/14-1 = 0 ; 第二行:2-1/14-1=0.07692,,,,,,
  • 开窗函数 --over()

    2021-01-12 14:45:37
    这个问题应该还是相对简单,其实就用聚合函数就好了。select id,name,max(score) from Student group by id,name order by name上边这种情况只适用id 和name是一一对应的,否则查询出来的数据是不正确的。例如 : 1 ...
  • Hive开窗函数

    2019-02-17 16:14:13
    开窗函数 普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值,而开窗函数则可为窗口中的每行都返回一个值。简单理解,就是对查询的结果多出一列,这一列...
  • 1、开窗函数和分组函数的区别分组函数是指按照某列或者某些列分组后进行某种计算,比如计数,求和等聚合函数进行计算。开窗函数是指基于某列或某些列让数据有序,数据行数和原始数据数相同,依然能曾现个体数据的...
  • 分析函数:ROW_NUMBER、RANK、DENSE_RANK、NTILE 总结: ROW_NUMBER、RANK、DENSE_RANK --并列的话123 113 112 ROW_NUMBER 功能:用于实现分区内记录编号 语法:row_number() over (partition by col1 order by col...
  • Oracle–开窗函数函数排序rank、desc_rank、row_number rank() over([partition by par_col] order by order_col):相同数据并列排序并且会跳过并列的应该有的次序 select salary,rank() over(order by salary desc) ...
  • 作为一名开发人员来讲,我感觉在职场白混...今天将要介绍SQL Sever的开窗函数,何谓开窗函数,不懂吧。反正对于我来说,我是摸不着头脑了,第一次听说过。那么,什么是开窗函数,其实可以理解为是聚合函数的一个加强版
  • mysql开窗函数

    千次阅读 2021-01-20 01:06:09
    开窗函数:它可以理解为记录集合,开窗函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,...
  • 开窗函数详细介绍,包括聚合开窗函数和排序开窗函数,sum,min,max,avg,first_value,last_value,lag,lead,cume_dist,row_number,rank,dense_rank,percent_rank,ntile等函数
  • 一类是排序开窗函数(ROW_NUMBER、DENSE_RANKRANK等)。 本文主要内容转自《高效使用Greenplum》一书。 分析函数是Greenplum数据库管理系统自带函数中的一种专门解决具有复杂统计需求的函数,它可以对数据...
  • mysql实现开窗函数

    2021-02-02 13:00:27
    mysql实现开窗函数开窗分析函数简单来讲就是相似partition by aaa, bbb order by ccc这样的形式mysql在使用mysql的过程当中遇到过这样的需求,须要求出一段时间内每一个item的最大值及对应的那一条记录。最大值很...
  • 这意味着要对其分组,按时间分组,并且同一时间返回多个结果普通分组函数(group by按某一个条件分组,必须要使用聚合函数,每组返回一个结果)这就需要使用rank() over(partition by 字段1 ord...
  • Oracle开窗函数

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

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

    千次阅读 2021-03-28 09:43:27
      上篇文章给大家介绍MySQL中常用的一些函数,其中主要包括数学函数、字符串函数、日期时间函数、分组合并函数、逻辑函数,其实还有开窗函数,只不过开窗函数是MySQL新提出来的,因此,我们单独用一篇文章将其详细...
  • 一、什么是开窗函数开窗函数/分析函数:over()开窗函数也叫分析函数,有两类:一类是聚合开窗函数,一类是排序开窗函数开窗函数的调用格式为:函数名(列名) OVER(partition by 列名 order by列名) 。如果你没听说...
  • Spark SQL 之开窗函数

    千次阅读 2019-06-20 19:08:39
    目录 一:开窗函数 二:row_number()开窗函数实战 三:开窗函数与一般函数使用的...为了解决这些问题,在2003年ISO SQL标准增加了开窗函数开窗函数的使用使得这些经典的难题能够被轻松的解决。眼下在 MSSQLSe...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,898
精华内容 2,759
关键字:

rank 开窗函数

友情链接: hss1.rar