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

用了这么多关系型数据库产品，开源的商业的，如：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

这就是常见的用法，其实还有很多聚合函数可以用，平均值、最大值、最小值等等。大家可以自行探索。