`
winie
  • 浏览: 223144 次
  • 性别: Icon_minigender_1
  • 来自: 重庆
社区版块
存档分类
最新评论

分析函数rank,dense_rank,row_number

SQL 
阅读更多
一.RANK聚合函数
1.语法


RANK(cntexpr1[,cntexpr2,...,cntexprn]) WITHIN GROUP (ORDER BY expr1[DESC|ASC][,expr2[DESC|ASC],...,exprn[DESC|ASC])
2.说明


计算给定组合,在一个排序集合中的所处的等级位置.
①cntexpr系列要求是常量
②相同的数值所处等级值相同
③ 下一等级值是前面所有等级记录数量加1
3.例子

SQL> select * from test;

        F1         F2
---------- ----------
         1          1
         1          2
         2          1
         2          2
         2          3
         3          1
         3          2
         3          3
         4          1
         4          2
         5          1

已选择11行。

SQL> select rank(0) within group(order by f1) r0,
  2  rank(1) within group(order by f1) r1,
  3  rank(2) within group(order by f1) r2,
  4  rank(3) within group(order by f1) r3,
  5  rank(4) within group(order by f1) r4,
  6  rank(5) within group(order by f1) r5
  7  from test;

        R0         R1         R2         R3         R4         R5
---------- ---------- ---------- ---------- ---------- ----------
         1          1          3          6          9         11


SQL> select rank(2,3) within group(order by f1,f2) r22,
  2  rank(4,1) within group(order by f1,f2) r41 from test;

       R22        R41
---------- ----------
         5          9


SQL> select rank(2,3) within group(order by f1,f2 desc) r22,
  2  rank(4,1) within group(order by f1,f2 desc) r41 from test;

       R22        R41
---------- ----------
         3         10


二.RANK分析函数
1.语法


RANK() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn])
2.目的


先将记录按PARTITION分组,组内再以ORDER BY排序,算出当前记录在组内所处的级别.
PRATITION条件省略时,表明不分组,或将全录记录作为一组
3.例子

SQL> select f1,f2,
  2  rank() over(order by f1) r1,
  3  rank() over(order by f1,f2) r12 from test;

        F1         F2         R1        R12
---------- ---------- ---------- ----------
         1          1          1          1
         1          2          1          2
         2          1          3          3
         2          2          3          4
         2          3          3          5
         3          1          6          6
         3          2          6          7
         3          3          6          8
         4          1          9          9
         4          2          9         10
         5          1         11         11

SQL> select f1,f2,rank() over(partition by f1 order by f2) r_pf1_f2 from test;

        F1         F2   R_PF1_F2
---------- ---------- ----------
         1          1          1
         1          2          2
         2          1          1
         2          2          2
         2          3          3
         3          1          1
         3          2          2
         3          3          3
         4          1          1
         4          2          2
         5          1          1
        

SQL> select f1,f2,rank() over(partition by f2 order by f1) r_pf2_f1 from test;

        F1         F2   R_PF2_F1
---------- ---------- ----------
         1          1          1
         2          1          2
         3          1          3
         4          1          4
         5          1          5
         1          2          1
         2          2          2
         3          2          3
         4          2          4
         2          3          1
         3          3          2

三.ROW_NUMBER函数
1.语法


ROW_NUMBER() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn])
2.说明


相同的记录也会有不同的ROW_NUMBER值,ROW_NUMBER值是连续的
3.例子

SQL> select f1,f2,
  2  row_number() over(order by f1) r1,
  3  row_number() over(order by f1,f2) r12 from test;

        F1         F2         R1        R12
---------- ---------- ---------- ----------
         1          1          1          1
         1          2          2          2
         2          1          3          3
         2          2          4          4
         2          3          5          5
         3          1          6          6
         3          2          7          7
         3          3          8          8
         4          1          9          9
         4          2         10         10
         5          1         11         11

已选择11行。

SQL> select f1,f2,row_number() over(partition by f1 order by f2) r_pf1_f2 from test;

        F1         F2   R_PF1_F2
---------- ---------- ----------
         1          1          1
         1          2          2
         2          1          1
         2          2          2
         2          3          3
         3          1          1
         3          2          2
         3          3          3
         4          1          1
         4          2          2
         5          1          1

已选择11行。

SQL> select f1,f2,row_number() over(partition by f2 order by f1) r_pf2_f1 from test;

        F1         F2   R_PF2_F1
---------- ---------- ----------
         1          1          1
         2          1          2
         3          1          3
         4          1          4
         5          1          5
         1          2          1
         2          2          2
         3          2          3
         4          2          4
         2          3          1
         3          3          2

已选择11行。

四.DENSE_RANK聚合函数
1.语法


DENSE_RANK(cntexpr1[,cntexpr2,...,cntexprn]) WITHIN GROUP (ORDER BY expr1[DESC|ASC][,expr2[DESC|ASC],...,exprn[DESC|ASC])
2.说明


计算给定组合,在一个排序集合中的所处的等级位置.
①cntexpr系列要求是常量
②相同的数值所处等级值相同
③ 等级值是连续的,从1开始.
3.例子

SQL> select dense_rank(0) within group(order by f1) r0,
  2  dense_rank(1) within group(order by f1) r1,
  3  dense_rank(2) within group(order by f1) r2,
  4  dense_rank(3) within group(order by f1) r3,
  5  dense_rank(4) within group(order by f1) r4,
  6  dense_rank(5) within group(order by f1) r5
  7  from test;

        R0         R1         R2         R3         R4         R5
---------- ---------- ---------- ---------- ---------- ----------
         1          1          2          3          4          5

五.DENSE_RANK分析函数
1.语法


DENSE_RANK() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn])
2.目的


先将记录按PARTITION分组,组内再以ORDER BY排序,算出当前记录在组内所处的级别.
PRATITION条件省略时,表明不分组,或将全录记录作为一组
3.例子

SQL> select f1,f2,
  2  dense_rank() over(order by f1) r1,
  3  dense_rank() over(order by f1,f2) r12 from test;

        F1         F2         R1        R12
---------- ---------- ---------- ----------
         1          1          1          1
         1          2          1          2
         2          1          2          3
         2          2          2          4
         2          3          2          5
         3          1          3          6
         3          2          3          7
         3          3          3          8
         4          1          4          9
         4          2          4         10
         5          1          5         11

已选择11行。

SQL> select f1,f2,dense_rank() over(partition by f1 order by f2) r_pf1_f2 from t
est;

        F1         F2   R_PF1_F2
---------- ---------- ----------
         1          1          1
         1          2          2
         2          1          1
         2          2          2
         2          3          3
         3          1          1
         3          2          2
         3          3          3
         4          1          1
         4          2          2
         5          1          1

已选择11行。

SQL> select f1,f2,dense_rank() over(partition by f2 order by f1) r_pf2_f1 from t
est;

        F1         F2   R_PF2_F1
---------- ---------- ----------
         1          1          1
         2          1          2
         3          1          3
         4          1          4
         5          1          5
         1          2          1
         2          2          2
         3          2          3
         4          2          4
         2          3          1
         3          3          2

已选择11行。

分享到:
评论

相关推荐

    SQL2005 四个排名函数(row_number、rank、dense_rank和ntile)的比较

    还是以上述为例,当field1字段值相同时,dense_rank函数会连续分配序号,即后三条记录的序号分别为1、1、1,而第4条记录的序号将是2,保持序号的连续性,后面的记录依然依次递增。SQL语句如下: ```sql select ...

    ROW_NUMBER、RANK、DENSE_RANK 和 NTILE

    MS SQL 2005 中的排名函数包括 ROW_NUMBER、RANK、DENSE_RANK 和 NTILE,这四个函数可以有效地分析数据并提供排序值。下面将详细介绍每个函数的用法和示例。 一、ROW_NUMBER 函数 ROW_NUMBER 函数返回结果集分区...

    分析函数ROW_NUMBER、RANK、DENSE_RANK的用法

    ### 分析函数ROW_NUMBER、RANK、DENSE_RANK的用法 #### 一、ROW_NUMBER()函数 **ROW_NUMBER()** 函数是SQL Server 2005引入的一个新的窗口函数,它为每一行返回一个唯一的整数值。该函数特别适用于需要对查询结果...

    Oracle开发之分析函数(Rank, Dense_rank, row_number)

    Oracle分析函数Rank、Dense_rank和row_number是用于处理数据集的高级工具,它们在数据库查询中发挥着关键作用,特别是在需要对数据进行排序和分组时。这三种函数都有各自的特点,适用于不同的业务场景。 1. **row_...

    Hive中分组取topN_row_number-rank和dense_rank的使用.pdf

    在这篇文档中,我们将详细学习在Hive中如何进行分组取topN,以及如何使用row_number()、rank()和dense_rank()三种窗口函数进行数据排序和排名。 首先,Hive中的数据表创建和数据插入操作是数据查询和分析的前提。...

    "rank()、dense_rank()和row_number()的区别"score.txt

    “rank()、dense_rank()和row_number()的区别”文章的date

    sql四大排名函数之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用介绍

    本文主要讲解SQL中的四大排名函数:ROW_NUMBER、RANK、DENSE_RANK以及NTILE,它们在数据分析和报表生成中起到关键作用。 1. ROW_NUMBER() ROW_NUMBER()函数用于为查询结果集中的每一行分配一个唯一的数字序列。这个...

    ORACLE 常用分析函数

    分析函数2(Rank, Dense_rank, row_number);分析函数3(Top/Bottom N、First/Last、NTile);窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述  ROW_NUMBER () OVER([partition_clause]...

    SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法

    在SQL Server 2005中,`ROW_NUMBER()`、`RANK()`和`DENSE_RANK()`是三种常用的排名函数,它们在处理大数据集时非常有用,尤其在数据分析和报表生成方面。这些函数都可以帮助我们根据指定的条件对数据进行排序并赋予...

    实例讲解sql server排名函数DENSE_RANK的用法

    传统的`ROW_NUMBER()`函数可以做到这一点,但当存在相同分数时,它会给每个相同分数的学生分配不同的排名。而`DENSE_RANK`则可以解决这个问题,确保相同分数的学生共享相同的排名。 让我们看看如何使用`DENSE_RANK`...

    SQL server中row_number(),rank(),dense_rank()排序

    `row_number()`、`rank()`和`dense_rank()`都是窗口函数,用于对查询结果集进行排序并分配唯一的序列号。它们在处理分组数据、排名问题或者实现数据分页时特别有用。让我们详细探讨这三个函数的用法和差异。 首先,...

    ROW_NUMBER(),RANK()和DENSE_RANK()之间的区别

    在实际工作中,理解和掌握ROW_NUMBER()、RANK()和DENSE_RANK()的区别至关重要,能够帮助我们编写更高效、精确的SQL查询,从而优化数据处理和分析流程。通过阅读"The-Difference-Between-ROW-NUMBER-RANK-and-DENSE-R...

    [sql server]SQL Server2005杂谈(3):四个排名函数的比较.doc

    SQL Server 2005 排名函数比较 ...row_number 函数可以用于实现查询表中指定范围的记录或实现分页功能,rank 函数和 dense_rank 函数用于考虑排序字段值相同的情况,ntile 函数用于将查询结果分为多个组。

    oracle 分析函数详解(有例子)

    2 Oracle开发专题之:分析函数 Rank Dense rank row number 3 Oracle开发专题之:分析函数3 Top Bottom N First Last NTile 4 Oracle开发专题之:窗口函数 5 Oracle开发专题之:报表函数 6 Oracle开发专题之:...

    oracle row_number用法

    `ROW_NUMBER()`可以与`RANK()`和`DENSE_RANK()`进行比较。这些函数都可以用来给数据排序并分配等级,但是它们在处理并列的情况时有所不同: - **Rank**:如果两个或多个行具有相同的排序值,则这些行都将获得相同...

    SQL 获取顺序号的四种方法 IDENTITYRANKDENSE RANKROW NUMBER

    SQL 获取顺序号的四种方法:IDENTITY、RANK、DENSE_RANK、ROW_NUMBER 在 SQL 中,获取顺序号是非常常见的操作。今天,我们将讨论四种获取顺序号的方法:IDENTITY、RANK、DENSE_RANK、ROW_NUMBER。每种方法都有其优...

Global site tag (gtag.js) - Google Analytics