精华内容
下载资源
问答
  • SQL 排名函数

    千次阅读 2008-12-22 09:49:00
    在业务中经常对某些数据排序后去编号,好多时候是在SQL语句中排好序,然后程序中手动添加序号,然而在SQL2005是有取序号的函数,这就是排名函数。下面举几个例子看看他们的用法。 数据库中有表TableA,有两个字段,...
             在业务中经常对某些数据排序后去编号,好多时候是在SQL语句中排好序,然后程序中手动添加序号,然而在SQL2005是有取序号的函数,这就是排名函数。下面举几个例子看看他们的用法。
    

           数据库中有表TableA,有两个字段,部门id(id),销售额(sale),记录的是某个部门某一次的销售额,数据如下:
    id             sale
    -------------------
    1              15
    2              10
    3              20
    4              10
    2              30
    3              10

    下面分别看一下我们要探讨的排名函数:
    ROW_NUMBER ( )      OVER ( [ partition_by_clause] order_by_clause )
    返回结果集分区内行的序列号,每个分区的第一行从 1 开始。

    ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
    partition_by_clause将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
    order_by_clause
    确定将 ROW_NUMBER 值分配给分区中的行的顺序。

    还是看例子吧,下例返回的是按照销售额升序排序的序列号:
    select id,sale, ROW_NUMBER() over (order by sale) as RowNumber
    from tablea
    结果如下:
    id              sale        RowNumber
    --------------------------------------
    2              10              1
    4              10              2
    3              10              3
    1              15              4
    3              20              5
    2              30              6
    如果既想取得上例中的RowNumber,有要让结果按照部门id进行排序只要用下面语句就可以了
    select id,sale, ROW_NUMBER() over (order by sale) as RowNumber
    from tablea
    order by id
    也就是说,ROW_NUMBER()只与后面的OVER的条件有关。

    RANK ( )     OVER ( [   partition_by_clause   ] order_by_clause   )
    返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
    如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名。例如,两次销售额相同并且最小,他们将拥有相同的名次第一。由于已有两行排名在前,下一个最小的销售额将排名第三。因此,RANK 函数并不总返回连续整数。
    用于整个查询的排序顺序决定了行在结果集中的显示顺序。这也隐含了行在每个分区中的排名。
    看下面的例子
    select id,sale, Rank() over (order by sale) as RowNumber
    from tablea
    结果:
    id              sale RowNumber
    --------------------------------
    2              10              1
    4              10              1
    3              10              1
    1              15              4
    3              20              5
    2              30              6
    同样如果还有其他条件,直接加在From句后面就可以了。

    DENSE_RANK ( )     OVER ( [ partition_by_clause   ] order_by_clause   )
    返回结果集分区中行的排名,在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。
    如果有两个或多个行受同一个分区中排名的约束,则每个约束行将接收相同的排名。例如,两次销售额相同并且最小,他们将拥有相同的名次第一。接下来最小的销 售额将排名第二。该排名等于该行之前的所有行数加一。因此,DENSE_RANK 函数返回的数字没有间断,并且始终具有连续的排名。
    整个查询所用的排序顺序确定了各行在结果中的显示顺序。这说明排名第一的行可以不是分区中的第一行。

    还是看下面的例子:
    select id,sale, DENSE_RANK () over (order by sale) as RowNumber
    from tablea
    运行结果:
    id               sale RowNumber
    ---------------------------
    2              10              1
    4              10              1
    3              10              1
    1              15              2
    3              20              3
    2              30              4

    上面的三个函数基本用法都是一样的,除了排名分页也是其重要的应用,他们都使用了OVER,其实OVER的用处很强大,如下例:
    select id,sale, sum(sale) over (partition by id) as SUM
    from tablea
    结果:
    id             sale SUM
    ---------------------------
    1              15              15
    2              10              40
    2              30              40
    3              10              30
    3              20              30
    4              10              10
    既显示了部门的每次销售记录,有把各个部门的销售总额求出来。OVER的作用,是强大的,有时间在好好研究一番。
    展开全文
  • SQL 排名函数面试宝典

    2019-04-13 11:46:05
    本文转载自sql 四大排名函数---(ROW_NUMBER、RANK、DENSE_RANK、NTILE)简介 1.ROW_NUMBER() 定义:ROW_NUMBER()函数作用就是将select查询到的数据进行排序,每一条数据加一个序号,他不能用做于学生成绩的排名,...

    本文转载自 sql 四大排名函数---(ROW_NUMBER、RANK、DENSE_RANK、NTILE)简介

    1.ROW_NUMBER()

    定义:ROW_NUMBER()函数作用就是将select查询到的数据进行排序,每一条数据加一个序号,他不能用做于学生成绩的排名,一般多用于分页查询, 
    比如查询前10个 查询10-100个学生。

    实例:

    1.1对学生成绩排序

     这里写图片描述
    这里number就是每个学生的序号 根据studentScore(分数)进行desc倒序

    1.2获取第二个同学的成绩信息

     这里写图片描述
    这里用到的思想就是 分页查询的思想 在原sql外再套一层select 
    where t.number>=1 and t.number<=10 是不是就是获取前十个学生的成绩信息纳。

    2.RANK()

    定义:RANK()函数,顾名思义排名函数,可以对某一个字段进行排名,这里为什么和ROW_NUMBER()不一样那,ROW_NUMBER()是排序,当存在相同成绩的学生时,ROW_NUMBER()会依次进行排序,他们序号不相同,而Rank()则不一样出现相同的,他们的排名是一样的。下面看例子:

    2.1对学生成绩进行排名

    这里写图片描述

    这里发现 ROW_NUMBER()和RANK()怎么一样?因为学生成绩都不一样所以排名和排序一样,下面改一下就会发现区别。

    这里写图片描述

    当出现两个学生成绩相同是里面出现变化。RANK()是 1 2 2,而ROW_NUMBER()则还是1 2 3,这就是RANK()和ROW_NUMBER()的区别了

    3.DENSE_RANK()

    定义:DENSE_RANK()函数也是排名函数,和RANK()功能相似,也是对字段进行排名,那它和RANK()到底有什么不同那?看例子:

    实例:

    这里写图片描述

    DENSE_RANK()密集的排名他和RANK()区别在于,排名的连续性,DENSE_RANK()排名是连续的,RANK()是跳跃的排名,所以一般情况下用的排名函数就是RANK()。

    4.NTILE()

    定义:NTILE()函数是将有序分区中的行分发到指定数目的组中,各个组有编号,编号从1开始,就像我们说的’分区’一样 ,分为几个区,一个区会有多少个。

    实例: 

    这里写图片描述

    这里查询了3次,第一次分为1个’区’ ,所以查询结果number全是1,第二次分为2个区,查询结果为 1 1 2,意思就是 第一个 ‘区’ 为 1 1 两个编号的数据 ,第二个’区’只有2这个数据。

    到这里,SQL的排名问题就说完了,下次介绍一些深层的SQL排名语句
     

    展开全文
  • SQL排名函数比较

    千次阅读 2013-11-01 17:41:57
    SQL Server2005中有如下四个排名函数: 1.row_number 2.rank 3.dense_rank 4.ntile 下面通过例子说明各自的作用:创建原始数据: select * into #MyTable from (select '语文' as 课程,70 as 成绩 union all ...

    在SQL Server2005中有如下四个排名函数:

      1.row_number

      2.rank

      3.dense_rank

      4.ntile  

    下面通过例子说明各自的作用:

    创建原始数据:

    select * into  #MyTable
    from
    (select '语文' as 课程,70 as 成绩
     union all
     select '数学' ,80 
     union all
     select '数学' ,80
     union all
     select '英语' ,90 
     union all
     select '英语' ,100 
     union all
     select '英语' ,70
    ) as tb
    课程   成绩
    ---- -----------
    语文   70
    数学   80
    数学   80
    英语   90
    英语   100
    英语   70
    
    select 成绩
    ,row_number()over( order by 成绩 desc) as row_number
    ,rank()      over( order by 成绩 desc) as rank
    ,dense_rank()over( order by 成绩 desc) as dense_rank
    ,ntile(4)    over( order by 成绩 desc) as ntile
    from #MyTable
    
    结果如下:
    成绩          row_number           rank                 dense_rank           ntile
    ----------- -------------------- -------------------- -------------------- --------------------
    100         1                    1                    1                    1
    90          2                    2                    2                    1
    80          3                    3                    3                    2
    80          4                    3                    3                    2
    70          5                    5                    4                    3
    70          6                    5                    4                    4

    可以看出:

    row_number 不论函数的值是什么 都一直顺序排序

    rank 相对于row_number,对于相同的值会并列排名,然后跳过并列的序号继续排名

    dense_rank 对于相同值会并列排名,然后按顺序继续排名,并不会跳过序号

    ntile 将数据分成若干块,主要用于分析

    当然还可以在 row_number()over()中加入partition by 在小组中分别排名:

    select 课程,成绩
    ,row_number()over( partition by 课程 order by 成绩 desc) as row_number
    ,rank()      over( partition by 课程 order by 成绩 desc) as rank
    ,dense_rank()over( partition by 课程 order by 成绩 desc) as dense_rank
    ,ntile(4)    over( partition by 课程 order by 成绩 desc) as ntile
    from #MyTable
    
     
    课程   成绩          row_number           rank                 dense_rank           ntile
    ---- ----------- -------------------- -------------------- -------------------- --------------------
    数学   80          1                    1                    1                    1
    数学   80          2                    1                    1                    2
    英语   100         1                    1                    1                    1
    英语   90          2                    2                    2                    2
    英语   70          3                    3                    3                    3
    语文   70          1                    1                    1                    1
    

    展开全文
  • 虽然都是排名函数,但三者间的些许差异很容易让人搞混,在这篇文章中,我将基于hive结合一个学生成绩排名的例子来对这三种排名函数进行解释与辨析。 一、学生成绩表准备 进行学生表的创建以及样例数据的导入 create ...

    前言

    在hive的学习中接触到了三种很实用的排名函数,这些函数在Mysql8.0版本中也已存在,
    分别是:Rank(),Dense_rank(),Row_number(),
    虽然都是排名函数,但三者间的些许差异很容易让人搞混,在这篇文章中,我将基于hive结合一个学生成绩排名的例子来对这三种排名函数进行解释与辨析。

    一、学生成绩表准备

    进行学生表的创建以及样例数据的导入

    create table stu(sno varchar(5),score int) row format delimited fields terminated by ',';
    
    load data local inpath '/opt/module/data/stu_score.txt' into table stu;
    

    查看学生表:
    学生表
    从数据中可以看出,s01,s03,s06号学生的成绩相同,本文要介绍的这三种排名函数的主要差异就是在对相同成绩的排名时采用不同的处理办法,以至于得到不同的结果。

    接下来就是根据具体例子对三种函数进行讲解。

    二、Rank()函数

    Rank()函数可以根据指定的字段来排序,排序时如果遇到排名字段值相同的情况时,会将相同的归为一组,也就是值相同,排名就是相同的,后面的排名会顺延,总排名数是不会改变的。

    举一个例子:Rank()函数排序可以理解为:每个人先根据成绩高低从左到右坐好座位,座位上先根据人数标好连续的序号,成绩相同的人座位的序号都改为成绩相同的第一个人的座位序号,后面的人座位的的序号还是原来排好的,不会改变。

    使用Rank()函数根据score字段对stu表进行降序排序的sql语句:

    select sno,score,rank() over (order by score desc) from stu;
    

    如下图所示,s01,s03,s06号学生成绩都是90分,使用Rank()排名时,他们三个人都是第二名,最后一名s05因为排名顺延,最终排名是第六,即总排名数是不会改变的,总共有六个人,总排名数就是六。

    rank函数排序结果

    三、Dense_rank()函数

    Dense_rank()函数可以根据指定的字段来排序,排序时如果遇到排名字段值相同的情况时,会将相同的归为一组,后面的接着前面的排序序号。

    Dense_rank()函数与Rank()函数的不同点在于:即使存在相同字段值的话,排名也不是跳跃的,Dense_rank()函数进行的排名是连续的。也就是相比于Rank()函数,Dense_rank()函数的总排名数会减少。

    举一个例子:Dense_rank()函数排序可以理解为:每个人先根据成绩高低从左到右坐好座位,座位上根据人数标好连续的序号,成绩相同的人座位的序号都改为成绩相同的第一个人的座位序号,后面的人座位的的序号接着前面的人的序号而改变,不保持原来的序号。

    使用Dense_rank()函数根据score字段对stu表进行降序排序的sql语句:

    select sno,score,dense_rank() over (order by score desc) from stu;
    

    如下图所示,s01,s03,s06号学生成绩都是90分,使用Dense_rank()排名时,他们三个人都是第二名,s04成为第三名,最后一名s05排名最终排名是第四,即总排名数因为有成绩相同的学生而减少。

    dense_rank函数排名结果

    四、Row_number()函数

    Row_number()函数可以根据指定的字段来排序,Row_number()函数比较直接,排好名后,排名序号就是从上到下连续的,比较容易理解。

    再举一个例子:Row_number()函数排序可以理解为:每个人先根据成绩高低从左到右坐好座位,座位上根据人数标好连续的序号,就可以了。

    使用Row_number()函数根据score字段对stu表进行降序排序的sql语句:

    select sno,score,row_number() over (order by score desc) from stu;
    

    如下图所示,s01,s03,s06号学生成绩都是90分,使用Row_number()排名时,他们三个人分别是第二第三第四名,没有出现相同排名的情况,最后总排名数与人数相同。

    row_number函数排名结果

    五、总结

    不论是在学习工作中还是面试中,hive中排名函数的使用都是一个十分重要的点,应该牢牢掌握。
    后续会总结出排名函数有关的题目与解答,供大家参考。

    展开全文
  • oracle --sql 排名函数

    2019-11-20 17:11:54
    排名函数 排名的几个分析函数 1.row_number 2.rank 3.dense_rank 语法:row_number() over(order by column) 重复的数据,不并列 比如 30,30,40 排名为3,4,5 语法:rank () over (order by b) 重复的数据,并列,...
  • Transact-SQL提供了4个排名函数: RANK(),DENSE_RANK(),ROW_NUMBER(),NTILE()下面是对这4个函数的解释:RANK() 返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。如果两个或多个行与一个排名关联,...
  • 任务1:格码大叔:... 任务2: 聚合函数 的练习: use School go --聚合函数 select ClassNo,count(*) 'Count',avg(SqlServer) 'SqlServer Average', avg(math) 'Math Average', AVG(G...
  • 三个分析函数都是按照col1分组内从1开始排序 row_number() 是没有重复值的排序(即使两天记录相等也是不重复的),可以利用它来实现分页 dense_rank() 是连续排序,两个第二名仍然跟着第三名 rank() 是跳跃拍学,两...
  • SQL内置函数之排名函数 主要有三个排名函数 row_number rank() dense rank() row_number 对查询结果按某列的值进行排序 具体语法 row_number() over (order by 列名 asc|desc) 例题:按生日从小到大排序,...
  • SQL查询排名函数实例

    2020-09-10 02:25:13
    本文主要讲解SQL查询排名函数实例,比较实用,希望能给大家做一个参考。
  • SQL SERVER 排名 函数

    千次阅读 2018-08-26 16:15:38
    SQL SERVER 排名 函数   ROW_NUMBER():行号。SQL Server2005中,使用ROW_NUMBER()非常方便 SELECT * , ROW_NUMBER() OVER ( ORDER BY createTime) AS 行号 FROM 表名 备注:数据在排行号时已经排序,如果有...
  • sql 四大排名函数

    2020-03-12 18:30:36
    sql 四大排名函数—(ROW_NUMBER、RANK、DENSE_RANK、NTILE)简介 1.ROW_NUMBER() 定义:ROW_NUMBER()函数作用就是将select查询到的数...
  • sql四大排名函数

    千次阅读 2021-06-12 14:21:42
    定义:ROW_NUMBER()函数作用就是将select查询到的数据进行排序,每一条数据加一个序号,他不能用做于学生成绩的排名,一般多用于分页查询, 比如查询前10个 查询10-100个学生。 实例: 1.1对学生成绩排序 这里...
  • 主要以一个实例讲解了sql server排名函数DENSE_RANK的用法,感兴趣的小伙伴们可以参考一下
  • SQL排名函数

    2013-12-16 11:22:35
    SQL Server 提供了一组排名函数(Ranking Functions),为结果集分区中的每一行返回一个排名值。根据所用到的函数和选项,某些行的排名值可能相同。排名函数包括RANK, NTILE, DENSE_RANK, ROW_NUMBER 四种,这四种...
  • SQL2008排名函数

    千次阅读 2014-04-10 11:35:36
    排名函数的常用使用格式:函数名() over (order by 列名 [asc|desc][,列名……]) 注意:返回结果集会根据使用排名函数时指定的列进行排序,因此不要在 from 子句后面再次使用 order by,会导致排名混乱。 ...
  • Sql Server 2005 引入了4个新的排名函数:ROW_NUMBER、BANK、DENSE_RANK和NTILE。
  • SQLServer 排名函数摘录

    2014-03-04 21:08:12
    1. SQL2005四个排名函数(row_number、rank、dense_rank和ntile)的比较: 点击打开链接 2. SQL Server 排序函数 ROW_NUMBER和RANK 用法总结 : 点击打开链接
  • --排名函数 ROW_NUMBER() RANK(), DENSE_RANK(),NTILE() Over()函数 为排名函数生成排序顺序、 select ROW_NUMBER() over(order by OrderDate) as Romber,SalesOrderID,OrderDate from SalesLT....
  • SQL排名函数的应用

    2017-10-17 17:06:16
    SQL中的排名函数ROW_NUMBER() RANK() DENSE_RANK() NTILE() 有很强大的功能,首次见到还是在做推荐的时候(使用NTILE将按大小排序后的数据,运用一种合理的机制划分为N个组,这实际上类似于一种打分),在此我们...
  • 排名函数 (Transact-SQL)

    2016-11-30 15:32:07
    排名函数为分区中的每一行返回一...Transact-SQL 提供下列排名函数: RANK NTILE DENSE_RANK ROW_NUMBER 示例 以下示例显示了用在同一查询中的四个排名
  • 博客文章地址:http://blog.csdn.net/pfe_nova/article/details/41357435 SQL2005之后添加的几个排名函数以及透析与反透析关键字,主要有Row_Number、RANK、DENSE_RANK、NTILE函数,pivot以及unpivot关键字
  • 主要介绍了sql四大排名函数之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
  • SQL Server 开窗排名函数

    千次阅读 2014-06-25 11:32:09
    内容摘要: Transact-SQL提供了4个排名函数: RANK(),DENSE_RANK(),ROW_NUMBER(),NTILE(),下文是对这4个函数进行的解释。 下面是对这4个函数的解释:  RANK()  返回结果集的分区内每行的排名。行的排名是...
  • 2.使用分析函数来为记录排名 3.使用分析函数为记录进行分组排名 一、使用rownum为记录排名: 在前面一篇《Oracle开发专题之:分析函数》,我们认识了分析函数的基本应用,现在我们再来考虑下面几个问题: ...
  • sql】— SQL Server 中的排名函数

    千次阅读 热门讨论 2018-04-05 16:21:39
    小编在项目中经常用到Row_Number()函数,它是sql server 2012 出现的四大排名函数(排名函数为分区中的每一行返回一个排名值)之一,下面为大家介绍一下它的用法。 正文 &nbsp;&nbsp;&nbsp;&nbsp;...
  • sql server中,我们常用的排名函数有三种,分别是 row_number、rank、dense_rank在这三种排名函数中,都要与over子句连用,而下面我们就来说一下它们的用法和差异。 1.row_number row_number函数是我平时运用最多...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 24,819
精华内容 9,927
关键字:

sql排名函数