【数据分析第七天】复杂sql语句查询

窗口函数总介绍

窗口函数的位置一般可以放以下2种函数

（一）静态窗口函数：

排名函数 rank（）、dense_rank()、row_number()；

（二）滑动窗口函数：

聚合函数 sum、 avg、percent_rank()；

取值函数 first_value()、last_value()、nth_value()、lag()、lead()、ntile()；

本文主要研究排名函数

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

排序窗口函数

排序窗口函数主要有以下3种：

这3种函数的主要区别是在对于重复数值的处理上的的区别，就好比给学生成绩排序，假如有3个并列第一，那么对于前4名，rank()的排序是1、1、1、4;dense_rank()的排序是1、1、1、2，row_num()的排序是1、2、3、4.

窗口函数后面的over()子句内容中，partition by、order by和我们学过的group by, order by功能高度重合，但是group by分组汇总后会改变表的行数，一行只有一类，而partition by不会改变行数，只会在原有的基础上增加一列作为排序后的结果。

group by 同条件结果

另外，partition是可以省略的，即不分组直接排序，如图，order by 是不可以省略的

相信通过以上内容，大家对于排序窗口函数也已经有了一定了解，我认为在sql中排序窗口函数的关键字在于排名，最高，最低

下面让我们来看一道例题

我们的目标是选出票数最高的候选人，很显然，我们首先要1.倒序排序，然后再得出2.票数最高的候选人，需要用到子查询。我们思路如下，要排序，选择rank（）窗口函数，首先根据partition by contituency,进行分区，然后通过order by votes排序，

select constituency,party,rank()over(partition by constituency order by votes desc) posn 
from ge
where constituency  BETWEEN 'S14000021' AND 'S14000026'
AND yr  = 2017

通过以上我们就完成了分区倒序排序，部分结果如下

那么我们如何取出第一名呢？可以在把以上代码作为查询范围，选出posn = 1的即为所求，代码如下

select constituency,party 
from
(select constituency,party,rank() over(partition by constituency order by votes desc) posn 
  from ge
  where constituency  BETWEEN 'S14000021' AND 'S14000026'
 AND yr  = 2017) rk
where rk.posn=1

那么这道例题就完成啦！

图片解释来自https://yrzu9y4st8.feishu.cn/mindnotes/bmncnhnbFWnUNvUq6qTkq49IQ3c#outline

你今天很棒！