Oracle应用之开窗函数笔记及应用场景

不念不忘少年蓝@ 2021-09-17 11:06 362阅读 0赞

介绍Oracle的开窗函数之前先介绍一下分析函数，因为开窗函数也属于分析函数

> 分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

上面是开窗函数的简单介绍。

> 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化

oracle开窗函数有很多，用的比较多的是over(…)，使用的话一般是和order、partition by、row\_number()、rank()、dense\_rank()几个函数一起使用

例子：成绩表的例子，

数据表为t\_score，字段分别为stuId，stuName，classId ，score

create table t_score(
       stuId varchar2(20),
       stuName varchar2(50),
       classId number,
       score float
    );
    insert into t_score(stuId,stuName,classId,score) values('111','小王',1,92);
    insert into t_score(stuId,stuName,classId,score) values('123','小李',1,90);
    insert into t_score(stuId,stuName,classId,score) values('134','小钱',1,92);
    insert into t_score(stuId,stuName,classId,score) values('145','小顺',1,100);

**over函数和row\_number一起使用：**  
开窗函数和row\_number函数一起使用的话，就是返回一行，不过这里其实不适合用来统计，因为统计成绩的话，可以存在分数一样的两条数据，而row\_number只返回一条数据。

select *
      from (select stuId, stuName, classId,
                   row_number() over(partition by classId order by score desc) rn
              from t_score)
     where rn = 1;

**over函数和rank一起使用：**  
rank函数功能也是排序，这里的话，假如有分数一样的两条数据的情况，如图sql是根据score排序的，有两人并列第二名，实际的第3名就被算成第4名了，如图，rn=4

select stuId,
            stuName,
            classId,
            rank() over(partition by classId order by score desc) rn
       from t_score;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]  
**over函数和dense\_rank一起使用：**  
dense\_rank函数是rank函数的补充，假如有分数一样的两条数据，出现了两人并列第二名的情况，实际的第3名算的是正确的

select stuId,
           stuName,
           classId,
           dense_rank() over(partition by classId order by score desc) rn
      from t_score;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]

ok，这种是同班级的情况，对于不同班级的情况，修改数据

INSERT INTO t_score(stuId,stuName,classId,score) VALUES('111','小王',2,92);
    INSERT INTO t_score(stuId,stuName,classId,score) VALUES('123','小李',1,90);
    INSERT INTO t_score(stuId,stuName,classId,score) VALUES('134','小钱',1,92);
    INSERT INTO t_score(stuId,stuName,classId,score) VALUES('145','小顺',2,100);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]  
可以看出，对办件进行分组再按成绩排序，oracle的分析函数功能还是做得比较齐全的

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210812/7e6bca3064594668b81c13d62f383647.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]: /images/20210812/9911702b381547d2bacb8902ae49d2b3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]: /images/20210812/23703e2cc38848d8bc152ac4c679123d.png