精华内容
下载资源
问答
  • 2021-09-13 20:38:54

    Sql格式

    SELECT *,
    row_number() over(ORDER BY investment desc) as `row_number`,
    dense_rank() over(order by investment desc) as `dense_rank`,
    rank() over(order by investment desc) as `rank`
    FROM investmenttab
    
    • row_number 排序值从小到大,依次排列

    • dense_rank 相同数据,并列保存,不存在断值(一直连续)

    • rank 相同数据并列保存,下一个值跳值(断续)
      在这里插入图片描述

    • 分组获取每组前十

    select aaa.* 
    from 
    (
    SELECT
    area,investment,
    rank() over(PARTITION by area order by investment desc) as `rank`
    from 
    investmenttab tab 
    left join users us
    on tab.userid = us.userid
    ) aaa
    where aaa.rank <10
    

    在这里插入图片描述

    更多相关内容
  • 排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数row_numberrankdense_rank和ntile,需要的朋友可以参考下。
  • 本技巧文章将讨论ROW_NUMBER(),RANK()和DENSE_RANK()之间的区别。
  • 1.ROW_NUMBER() 定义:ROW_NUMBER()函数作用就是将select查询到的数据进行排序,每一条数据加一个序号,他不能用做于学生成绩的排名,一般多用于分页查询,  比如查询前10个 查询10-100个学生。 实例: 1.1对学生...
  • ROW_NUMBER()说明:返回结果集分区内行的序列号,每个分区的第一行从 1 开始。语法:ROW_NUMBER () OVER ( [ <partition> ] <order> ) 。备注:ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。...
  • drop table if exists SC; create table SC( Sno char(10), Cno char(10), Grade smallint not null, constraint pk_sno primary key (sno,cno) ) 首先简单创建一个表,并为其插入主码 Insert Into SC (Sno ,...
  • 本文主要是对Oracle分析函数Rank, Dense_rank, row_number的使用法,通过这些函数,我们可以对数据进行排序分组,需要的朋友可以参考下。
  • 三者的区别如下: rank()排序相同时会重复,总数不变,即会出现1、1、3这样的排序结果; ...create table rownumber( id varchar(10) not null, name varchar(10) null, age varchar(10) null,

    三者的区别如下:

    • rank()排序相同时会重复,总数不变,即会出现1、1、3这样的排序结果;
    • dense_rank()排序相同时会重复,总数会减少,即会出现1、1、2这样的排序结果;
    • row_number()排序相同时不会重复,会根据顺序排序。

    具体实例

    建表、插入数据

    create table rownumber(
           id varchar(10) not null,
           name varchar(10) null,
           age varchar(10) null,
           salary int null
    );
    
    insert into rownumber(id,name,age,salary) values(1,'a',10,8000);
    insert into rownumber(id,name,age,salary) values(1,'a2',11,7500);
    insert into rownumber(id,name,age,salary) values(2,'b',12,7500);
    insert into rownumber(id,name,age,salary) values(2,'b2',13,4500);
    insert into rownumber(id,name,age,salary) values(3,'c',14,8000);
    insert into rownumber(id,name,age,salary) values(3,'c2',15,20000);
    insert into rownumber(id,name,age,salary) values(4,'d',16,30000);
    insert into rownumber(id,name,age,salary) values(5,'d2',17,8000);
    select * from rownumber;

    数据

    row_number()

    select *, row_number() over(order by salary) as `rank` from rownumber;

    row_number()排序结果

    rank()

    select *, rank() over(order by salary) as `rank` from rownumber;

    rank()排序结果

    dense_rank()

    select *, dense_rank() over(order by salary) as `rank` from rownumber;

    dense_rank()排序结果

    row_number()的详细用法

    1.分组排序

    select *, row_number() over(partition by id order by salary) as `rank` from rownumber;

    2. 每组内第二名的信息

    select * from (
    select *, row_number() over(partition by id order by salary) as `rank` from 
    rownumber) as temp
    where `rank` = 2;

    3. 给年龄在13岁到16岁的数据按salary排名

    select *, row_number() over (order by salary) as `rank` from rownumber
    where age between 13 and 16;

    注:

    row_number函数得到的列别名可用于order by 排序,因为order by执行在select之后。

    where, group by, having都不可引用该列,因为这些语句执行在select之前,此时函数尚未计算出值。

    --创建测试表
    create table te.sc(id int, name varchar(20),class varchar(20), score int);
    
    --给测试表插入数据
    insert into te.sc values (1,'张飞','一年一班',100);
    insert into te.sc values (2,'刘备','一年一班',99);
    insert into te.sc values (3,'李逵','一年一班',95);
    insert into te.sc values (4,'小动','一年一班',97);
    insert into te.sc values (5,'小智','一年一班',80);
    insert into te.sc values (6,'吕布','一年二班',67);
    insert into te.sc values (7,'赵云','一年二班',90);
    insert into te.sc values (8,'典韦','一年二班',89);
    insert into te.sc values (9,'关羽','一年二班',70);
    insert into te.sc values (10,'马超','一年二班',98);
    insert into te.sc values (11,'张媛','一年一班',100);

     

    --列出每个班分数排名前三的学生
        select * from (select id, name, class, score , 
                    row_number() over (partition by class order by score desc) as r1,
                    rank() over (partition by class order by score desc) as r2 ,
                    dense_rank() over (partition by class order by score desc) as r3 from te.sc) B where r1<=3 ;
    
    id     name     class     score     r1   r2   r3
    1      张飞    一年一班    100        1    1    1
    11     张媛    一年一班    100        2    1    1
    2      刘备    一年一班    99         3    3    2
    10     马超    一年二班    98         1    1    1
    7      赵云    一年二班    90         2    2    2
    8      典韦    一年二班    89         3    3    3

    这三个函数的区别主要在分数一致的情况下,row_number()不重复排序,rank()重复且跳数字排序,dense_rank()重复且不跳数字排序。

    展开全文
  • 排名函数主要有row_numberrank和dense_rank。 简单排名 row_number是行号,不会重复,rank数据相同的,给出并列排名,但是会跳跃,dense_rank类似于rank,但不会跳跃,通过下面的案例来看一下区别。 准备数据 drop...

    排名函数主要有row_number、rank和dense_rank。

    简单排名

    row_number是行号,不会重复,rank数据相同的,给出并列排名,但是会跳跃,dense_rank类似于rank,但不会跳跃,通过下面的案例来看一下区别。

    准备数据

    drop table if exists test.test_zw;
    CREATE TABLE if not exists test.test_zw(
      name string COMMENT '姓名',
      score double comment '成绩'
    
    )
    COMMENT '测试表'
    STORED as parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
      
    -- 插入数据 
    insert into test.test_zw values
    ('小王',100),
    ('小李',90),
    ('小张',90),
    ('小红',80);
    

    简单查询

    select name,score
    from test.test_zw
    order by score desc 
    

    在这里插入图片描述

    按照成绩降序排名

    select name,score
    ,row_number() over( order by score desc) `row_number排名`
    ,rank() over (  order by score desc) `rank排名`
    ,dense_rank() over (  order by score desc) `dense_rank排名`
    from test.test_zw
    order by score desc 
    

    在这里插入图片描述

    分组排名

    over里面使用partition by可以指定分组,给出分组排名。例如查看数学和语文的排名。

    准备数据

    drop table if exists test.test_zw;
    CREATE TABLE if not exists test.test_zw(
      name string COMMENT '姓名',
      course string comment '课程',
      score double comment '成绩'
    
    )
    COMMENT '测试表'
    STORED as parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
      
    -- 插入数据 
    insert into test.test_zw values
    ('小王','数学',100),
    ('小李','数学',90),
    ('小张','数学',90),
    ('小红','数学',80),
    ('小王','语文',60),
    ('小李','语文',80),
    ('小张','语文',80),
    ('小红','语文',100);
    

    简单查询

    select name,course,score
    from test.test_zw
    order by course,score desc 
    

    在这里插入图片描述

    先按course分组,再按score降序排名

    select name,course,score
    ,row_number() over( partition by course order by score desc) `row_number排名`
    ,rank() over ( partition by course  order by score desc) `rank排名`
    ,dense_rank() over ( partition by course order by score desc) `dense_rank排名`
    from test.test_zw
    order by course, score desc 
    

    在这里插入图片描述

    练习1-取前两名学生

    需求:分别获取数学和语文前两名的学生

    分析:先使用rank函数按照course分组score降序排名,然后取排名小于等于2的数据。注意,使用rank可以跑出并列排名,最终可能导致跑出的数据多于要求的数据。

    select name,course,score,`rank排名` 
    from (
        select name,course,score
        ,row_number() over( partition by course order by score desc) `row_number排名`
        ,rank() over ( partition by course  order by score desc) `rank排名`
        ,dense_rank() over ( partition by course order by score desc) `dense_rank排名`
        from test.test_zw
    ) t1
    where `rank排名`<=2
    order by course, score desc 
    

    在这里插入图片描述

    练习2-连续签到次数

    需求:有如下数据,取出每个用户最高连续签到次数。

    准备数据:

    drop table if exists test.test_zw;
    CREATE TABLE if not exists test.test_zw(
      name string COMMENT '姓名',
      sign_date string comment '签到日期'
    
    )
    COMMENT '测试表'
    STORED as parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
      
    -- 插入数据 
    insert into test.test_zw values
    ('老王','2021-01-01'),
    ('老王','2021-01-02'),
    ('老王','2021-01-03'),
    ('老王','2021-01-07'),
    ('小张','2021-01-01'),
    ('小张','2021-01-03'),
    ('小张','2021-01-04');
    

    简单查询:从数据上看,老王最高连续签到3次,小张最高连续签到2次。

    select name,sign_date
    from test.test_zw
    order by name,sign_date
    

    在这里插入图片描述

    第一步:给每一组连续签到打标,方法是签到日期减去排名,如果是连续签到,返回的日期相同。比如老王1号、2号、3号连续签到,返回2020-12-31。

    select name
    ,sign_date
    ,rank() over(partition by name order by sign_date asc) as `排名`
    ,date_sub(sign_date, rank() over(partition by name order by sign_date asc))as label  -- 连续签到分组标签
    from test.test_zw
    order by name,sign_date
    

    在这里插入图片描述

    第二步:按照姓名和连续签到分组标签分组,签到日期升序排名。

    select name,sign_date,label
    ,rank() over(partition by name,label order by sign_date asc) as `连续签到排名`
    from (
        select name
        ,sign_date
        ,rank() over(partition by name order by sign_date asc) as `排名`
        ,date_sub(sign_date, rank() over(partition by name order by sign_date asc))as label  -- 连续签到分组标签
        from test.test_zw
    ) t1
    order by name,sign_date
    

    在这里插入图片描述
    最后一步:取出每个用户连续签到排名的最大值,至此大功告成。

    select name,max(`连续签到排名`) as `最大连续签到次数`
    from (
        select name,sign_date,label
        ,rank() over(partition by name,label order by sign_date asc) as `连续签到排名`
        from (
            select name
            ,sign_date
            ,rank() over(partition by name order by sign_date asc) as `排名`
            ,date_sub(sign_date, rank() over(partition by name order by sign_date asc))as label  -- 连续签到分组标签
            from test.test_zw
        ) t1
    ) t2
    group by name 
    

    在这里插入图片描述

    展开全文
  • 文章目录1 概述2 分类2.1 排序函数2.2 分组子句 1 概述 1. 完整格式 (1) row_number() over(partition by 列1 order by 列2) ... (3) dense_rank(): '密集' 排序,如:1 2 2 3 3. 分组子句 (1) partition by 列

    1 概述

    1. 完整格式
       (1) row_number() over(partition by1 order by2)
       
    2. 三种排序函数
       (1) row_number()'连续' 排序,如:1 2 3 4
       (2) rank()'跳跃' 排序,如:1 2 2 4 
       (3) dense_rank()'密集' 排序,如:1 2 2 3
    
    3. 分组子句
       (1) partition by1 order by2
       (2) order by ... 不可省略(报错提醒)
    

    2 分类

    2.1 排序函数

    with student_info as (
      select 1 sno, '瑶瑶' sname, 18 age from dual union all
      select 2 sno, '倩倩' sname, 19 age from dual union all
      select 3 sno, '优优' sname, 19 age from dual union all
      select 4 sno, '丽丽' sname, 20 age from dual union all
      select 5 sno, '萌萌' sname, 21 age from dual
    )
    select si.sno 学号,
           si.sname 姓名,
           si.age 年龄,
           row_number() over(order by si.age) 连续排序,
           rank() over(order by si.age) 跳跃排序,
           dense_rank() over(order by si.age) 密集排序
      from student_info si;
    

    根据 “年龄 age” 排序的结果:三种排序函数间的区别
    在这里插入图片描述

    2.2 分组子句

    with student_info as (
      select 1 sno, '瑶瑶' sname, 18 age from dual union all
      select 2 sno, '倩倩' sname, 19 age from dual union all
      select 3 sno, '优优' sname, 19 age from dual
    )
    select si.sno 学号,
           si.sname 姓名,
           si.age 年龄,
           row_number() over(partition by si.age order by si.age) rn
      from student_info si;
    

    根据 “年龄 age” 分组的结果:
    在这里插入图片描述

    展开全文
  • oracle(ROW_NUMBERRANKDENSE_RANK)三大排名函数的区别 最近同学面试找工作,面试官问到了(ROW_NUMBERRANKDENSE_RANK)这三大排名函数的区别,他说他确实用过,但是真要说区别,还真不清楚,那么今天就...
  • 有如下表1.row_number row_number在排名时序号 连续 不重复,即使遇到表中的两个3时亦如此 执行 select row_number() OVER(order by e.salary desc) as row_num , e.salary from employee e 结果如图注意:在使用...
  • 1.1 区别RANKDENSE_RANK和ROW_NUMBER RANK并列跳跃排名,并列即相同的值,相同的值保留重复名次,遇到下一个不同值时,跳跃到总共的排名。 DENSE_RANK并列连续排序,并列即相同的值,相同的值保留重复名次,遇到下...
  • 一般情况下row_number()函数over连用 1)ROW_NUMBER() OVER(partition by col1 order by col2) 表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内是连续且唯一的...
  • row_number 排序字段值相同时,序号不同,下一个序号顺序自增 rank 排序字段值相同时,序号相同,下一个序号跳跃自增 dense_rank排序字段值相同时,序号相同,下一个序号顺序自增
  • row_number,rank,dense_rank这三个都是排序函数,几乎每种数据库都支持这几个函数。 它们都是配合开窗函数over(partition by col1 order by col2)使用。下面根据实例看看这三个有什么区别: 首先建个临时表,然后...
  • row_number, rank(), dense_rank()的用法与区别
  • rank / dense_rank / row_number的语法都是一样的,不同的只是几个特性: rank / dense_rank都考虑了并列的情况,所以序号可能不唯一,rank在出现并列之后会不连续,而dense_rank是连续的 row_number不考虑并列...
  • 1.ROW_NUMBER() 定义:ROW_NUMBER()函数作用就是将select查询到的数据进行排序,每一条数据加一个序号,他不能用做于学生成绩的排名,一般多用于分页查询,  比如查询前10个 查询10-100个学生。 实例: 1.1对...
  • row_numer():按查出的记录数前后排序,序号不重复。即第1条记录序号为1,第2条记录序号...dense_rank():连续排序。例如前4条记录中,12的排序字段值相同,34的排序字段值相同,则4条记录的排序号为1,1,2,2。 ...
  • 排序函数row_numberrankdense_rank的区别 口诀: row_number 不存在并列 dense_rank 和rank存在并列,但rank很跳。 口诀的意思是: row_number:不考虑并列的情况,哪怕分数相同,排名都是一溜下来的自然数。 ...
  • MS SQL 2005 四个排序函数ROW_NUMBERRANKDENSE_RANK NTILE简介用法结果排名排序
  • hive中分组取topN、row_numberrank和dense_rank使用介绍
  • 这三个函数都是排序函数,那么有什么不同呢,下面看个例子:SQL> select empno,2 ename,3 deptno,4 sal,5 row_number() over(partition by deptno order by sal) sql_rownum,6 rank() over(partition...
  • 排名函数是Sql Server2005新增的功能,下面简单介绍一下他们各自的用法区别。我们新建一张Order表并添加一些初始数据方便我们查看效果。 CREATE TABLE [dbo].[Order]( [ID] [int] IDENTITY(1,1) NOT NULL, ...
  • 1.ROW_NUMBER() 定义:ROW_NUMBER()函数作用就是将select查询到的数据进行排序,每一条数据加一个序号,他不能用做于学生成绩的排名,一般多用于分页查询, 比如查询前10个 查询10-100个学生。 实例: 1.1对学生...

空空如也

空空如也

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

row_number和rank和dense_rank