前言:
目前姐妹们对“oracle分析函数窗口函数”可能比较注重,我们都想要了解一些“oracle分析函数窗口函数”的相关文章。那么小编也在网摘上搜集了一些有关“oracle分析函数窗口函数””的相关内容,希望兄弟们能喜欢,看官们快快来学习一下吧!用了这么多关系型数据库产品,开源的商业的,如:Oracle、MySql(注意5.7以上版本才可以使用)、SqlServer、postgreSQL。如果从应用角度来看,谁都逃离不了增删改查;而查又是难点颇多的点。
本次围绕着SQL开窗函数进行讲解,让大家再统计查询的时候事半功倍;
一、首先我们得了解什么是开窗函数:
简单直白的讲,就是定义一个窗口列(再该列进行运算等聚合函数操作),它可以对一组值进行相应操作,而不需要group by 子语句进行分组。
这里提到了一个重点,不需要在进行group by 子语句进行分组,说白了就是在整个SQL书写过程不会出现group by,我们可以直接在同行下同时返回基础行的列或聚合列;
二、开窗函数的格式:
开窗函数的书写格式: 字段 over(partition by 字段1,字段2 order by 字段1,字段2)
partition by就相当于分组的概念,我需要统计哪一部分的数据范围进行聚合;
order by 就很好理解了,如果进行排名则根据哪种排序规则进行展示;
三、开窗函数实例:
说了这么多该如何使用呢,我们来练习一下一些实例。本次我将用oracle进行操作:
创建学生信息表t_student_info
-- Create table
create table T_STUDENT_INFO
(
name VARCHAR2(60),
class VARCHAR2(60),
fraction NUMBER
)
创建数据:
insert into t_student_info (NAME, CLASS, FRACTION)
values ('张三', '一班', 80);
insert into t_student_info (NAME, CLASS, FRACTION)
values ('李四', '一班', 50);
insert into t_student_info (NAME, CLASS, FRACTION)
values ('王五', '一班', 70);
insert into t_student_info (NAME, CLASS, FRACTION)
values ('小六', '二班', 90);
insert into t_student_info (NAME, CLASS, FRACTION)
values ('大七', '二班', 60);
最终数据表呈现效果:
SQL如下:
select t.name,
t.class 班级,
count(1) over(partition by t.class) as 班级人数,
sum(t.fraction) over(partition by t.class) as 班级总分数,
row_number() over(order by t.fraction) as 排名A,
rank() over(order by t.fraction) as 排名B,
dense_rank() over(order by t.fraction) as 排名C
from t_student_info t
可以看出来通过开窗函数统计出来班级人数,班级总分数及排名情况;
SQL详解:
求行数:通过count函数获得行数,而上面提到了partition by的作用就是统计的作用域,通过下列这么书写,最终就得出来,一班和二班分别的总人数;
count(1) over(partition by t.class) as 班级人数,
求合计:通过sum()函数对分数列进行求和,原理与求行数一样
sum(t.fraction) over(partition by t.class) as 班级总分数,
计数:row_number的计数方式永远都是n+1同分数的情况仍然是+1排列,而order by则是需要计数的顺序,如果倒序可以加上desc即可
row_number() over(order by t.fraction) as 排名A,
排行方法1:rank函数则是当出现相同值时并列排行号,即大七和中八分数相同则都赋值2,后面则把重复占用的值过滤掉,即从4开始往下排,3则不出现。
rank() over(order by t.fraction) as 排名B,
排行方法2:与rank恰恰相反,并列分数结束后,继续往下排相应数字,不会跳过占用重复位
dense_rank() over(order by t.fraction) as 排名C
这就是常见的用法,其实还有很多聚合函数可以用,平均值、最大值、最小值等等。大家可以自行探索。
标签: #oracle分析函数窗口函数 #mysql查询班级人数 #oracle开窗函数取最大值 #oracle开窗函数sum #oracle开窗函数排序