sql取字段前4位_从零学会SQL：SQL高级功能

前言：SQL高级功能窗口函数脑图

一、 什么是窗口函数

1、 窗口函数有什么用？

在日常工作中，经常会遇到在每组内排名，比如下面的业务需求：

排名问题：每个部门按业绩来排名
TopN问题：找出每个部门排名前N的员工进行奖励

面对这类需求，就需要使用SQL的高级功能窗口函数了。

2、 什么是窗口函数？

窗口函数，也叫OLAP函数（online analytical processing，联机分析处理），
可以对数据库数据进行实时分析处理。

窗口函数的基本语法如下：

<窗口函数> over（partition by <用于分组的列名>
Order by <用于排序的列名>）

那么语法中的<窗口函数>都有哪些呢？

<窗口函数> 的位置，可以放以下两种函数：

1） 专用窗口函数，包括后面要讲到的rank，dense_rank，row_number 等专用窗口函数。
2） 聚合函数，如sum，avg，count，max，min等

因为窗口函数是对where 或者group by 子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中

二、 如何使用窗口函数

接下来，就结合实例，给大家介绍几种窗口函数的用法。

1、 专用窗口函数rank

例如下图，是班级表中的内容

如果我们想在每个班级内按成绩排名，得到下面的结果。

以班级“1”为例，这个班级的成绩“95”排在第一位，这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内，按成绩排名了。

得到上面结果的SQL语句代码如下：

Select *,
      rank () over（partition by 班级
                    Order by 成绩 desc）as ranking 
From 班级表

1） 每个班级内：按班级分组

Partition by 用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）

2） 按成绩排名

Order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩desc）是按成绩这一列排序，加了desc关键词表示降序排列。

通过下图，我们就可以理解partition by（分组）和order by（在组内排序）的作用了。

窗口函数具备了我们之前学过的group by子句分组的功能和order by 子句排序的功能，那么，为什么还要用窗口函数呢？

这是因为，group by 分组汇总后改变了表的行数，一行只有一个类别。而partition by 和rank 函数不会减少原表中分行数。例如下面统计每个班级的人数。

相信通过这个例子，你已经明白了这个窗口函数的使用：

Select *,
  Rank() over(partition by 班级
              Order by 成绩 desc)as ranking 
From 班级表

现在我们说回来，为什么叫“窗口”函数呢？这是因为partition by分组后的结果成为“窗口”，这里的窗口不是我们的门窗，而是表示“范围”的意思。

简单来说，窗口函数有以下功能：

1） 同时具有分组和排序的功能
2） 不减少原表的行数
3） 语法如下
<窗口函数> over（partition by <用于分组的列名>
Order by <用于排序的列名>）

三、 其他专用窗口函数

专用窗口函数rank，dense_rank，row_number有什么区别呢？

它们的区别我举个例子，你们以下就能看懂：

Select *,
  Rank() over (order by 成绩 desc)as ranking,
  Dense_rank()over(order by 成绩 desc)as dese_rank,
  Row_number() over (order by 成绩 desc)as row_num 
From 班级表

得到结果：