• 教为学:Oracle SQL学习之路(一):分析函数之排名


    教为学:Oracle SQL学习之路(一):分析函数之排名

    前言

    本来想对分析函数来个概述的,可是觉得这概念让人头疼,不如先来几个实例直截了当。

    三个分析排名函数的对比。

    先上SQL:

    1. with t as
    2. (
    3. select rownum*10 cnt from dual connect by rownum<5
    4. select rownum*40-10 from dual connect by rownum<3
    5. )
    6. select cnt,
    7. row_number() over(order by cnt) rn,
    8. rank() over(order by cnt) rk,
    9. dense_rank() over(order by cnt) drk
    10. from t;

    再上结果:

    CNT

    RN(无并列)

    RK(并列不连续)

    DRK(并列连续)

    10

    1

    1

    1

    20

    2

    2

    2

    30

    3

    3

    3

    30

    4

    3

    3

    40

    5

    5

    4

    70

    6

    6

    5

    从结果上可以很清晰的看出row_number,rank,dense_rank这几个函数在结果上的区别。

    接下来,我们一个个的讲解这些函数。

    Syntax

    RANK函数:

    DENSE_RANK函数:

    ROW_NUMBER函数:

    除了函数名,你看出它们之间有区别吗?没有,确实,在用法上,他们没有任何区别。区别在处理的结果上。

    区别:

    RANK() 函数:

    返回唯一值,碰到相同的值的时候,排名一致,不过接下的值会把相同的值所占的位置空缺出来。

    DENSE_RANK函数:

    返回唯一值,碰到相同的值的时候,排名一致,接下来的数值不会空缺,接上面的。

    ROW_NUMBER函数:

    返回唯一值,碰到相同的值按照一定的排序,不采用相同的值,一直排列下去。

    用法详解:

    代码如下:

    1. department_id,
    2. last_name,
    3. employee_id,
    4. row_number() over(partition by department_id order by employee_id) as emp_id
    5. from employees
    6. ;

    执行结果如下(省略一部分):

    DEPARTMENT_ID

    LAST_NAME

    EMPLOYEE_ID

    EMP_ID

    Grant

    178

    1

     

    10

    Whalen

    200

    1

    20

    Hartstein

    201

    1

    20

    Fay

    202

    2

    30

    Raphaely

    114

    1

    30

    Khoo

    115

    2

    30

    Baida

    116

    3

    30

    Tobias

    117

    4

    30

    Himuro

    118

    5

    30

    Colmenares

    119

    6

    40

    Mavris

    203

    1

    50

    Weiss

    120

    1

    50

    Fripp

    121

    2

    50

    Kaufling

    122

    3

    50

    Vollman

    123

    4

    50

    Mourgos

    124

    5

    50

    Nayer

    125

    6

    50

    Mikkilineni

    126

    7

    执行计划:

    1. Execution Plan
    2. ----------------------------------------------------------
    3. Plan hash value: 1919783947
    4.  
    5. --------------------------------------------------------------------------------
    6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    7. --------------------------------------------------------------------------------
    8. | 0 | SELECT STATEMENT | | 107 | 1605 | 4 (25)| 00:00:01 |
    9. | 1 | WINDOW SORT | | 107 | 1605 | 4 (25)| 00:00:01 |
    10. | 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
    11. --------------------------------------------------------------------------------
    12.  
    13.  
    14. Statistics
    15. ----------------------------------------------------------
    16.      1 recursive calls
    17.      0 db block gets
    18.      7 consistent gets
    19.      0 physical reads
    20.      0 redo size
    21.        3534 bytes sent via SQL*Net to client
    22.    496 bytes received via SQL*Net from client
    23.      9 SQL*Net roundtrips to/from client
    24.      1 sorts (memory)
    25.      0 sorts (disk)
    26.    107 rows processed

    row_number() over(partition by department_id order by employee_id)

    和普通函数不同的是,分析函数后面有一个over 关键字。或者说是条件函数?

    Partition by 表示依据什么分类,order by地球人都知道(在这几个函数中是必须的)

    从执行计划来看,一个全表扫描,然后一个WINDOW SORT(至于为什么叫WINDOW SORT以后解释)。

    统计信息:一个递归调用、七个一致性读、一个内存排序。

    关于执行计划不多说,以后有时间看能不能专门写个系列的文章。

  • 相关阅读:
    设置tableview的滚动范围--iOS开发系列---项目中成长的知识三
    把所有界面的状态栏字体颜色设置为白色--iOS开发系列---项目中成长的知识一
    iOS开发遇到的坑之四--图片命名不规范
    使用Xcode连接开源中国
    iOS使用技巧---高效使用你的xcode
    修改mysql密码出现报错:ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corres
    ubuntu 18.04 为 mysql 设置 root 初始密码
    sudo: netstat:找不到命令的解决办法
    xpath中的ends-with无效解决方案
    python用selenium获取元素标签内容和属性值
  • 原文地址:https://www.cnblogs.com/jiaoweixue/p/3107297.html
Copyright © 2020-2023  润新知