龙空技术网

SQL开窗函数讲解,让查询统计更简单

腰鼓当当 736

前言:

目前姐妹们对“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开窗函数排序