sql over函数_SQL窗口函数简介

本文介绍SQL窗口函数，读完后你将会：

1. 能够使用窗口函数语句

2. 了解窗口函数的存在目的，在需要的时候想到它

3. 知道三种不同目的窗口函数的存在

窗口函数能做什么？

窗口函数对表格中的一组“行”进行操作，并为每个行返回一个值。所谓“窗口“指的是函数计算某一行相应值时考虑的其他”行"的集合。窗口函数使用“窗口行”中的值来计算返回的值。

参考：https://drill.apache.org/docs/sql-window-functions-introduction/

窗口函数语句示例

对于下面这个表格(stu_info.class_table)：

我们使用窗口函数语句：

得到下面表格：

我们来解释下这个语句。其中rank()是排序的“窗口函数”。其计算时的具体要求是“每个class内按score排名”，然后再对学生进行排序。窗口函数实现的具体细节，或者说是这个例子里面窗口的定义是靠后面紧跟的 over() 语句实现。细致地来看over()内这句话可以分为两部分：

• 按class分组

  使用关键词partition by 实现

• 按score排名

  使用 order by 实现。默认是按照升序(asc)排列。在本例中加了desc关键词表示降序排列。

over()语句内，partitionby 是可选的，而 order by 是必须有的。

看一下窗口是具体怎么定义出来的

通过上面语句over()中，partition by & order by 两组关键词，我们具体看下对表格发生了什么变化：

1. 所有行按照 class 分成三组

2. 每组内按照分数从高到低排名

我们在之前提到过

所谓“窗口“指的是函数计算某一行相应值时考虑的其他”行"的集合。窗口函数使用“窗口行”中的值来计算返回的值。

通过 over(partition by…, order by…) 将原始表格整理成上面形式后，我们在使用窗口函数rank()计算某一行相应值时，所考虑的“窗口”行就可以直接找到了。具体规则如下：

1. 某一行对应的窗口行，在与此行数据共属同一组(指 partition by 区分开来的组别)

2. 某一行对应的窗口行，是在同一组内，比此行排名靠前的所有行的集合(其中排名由 over() 中的 order     by 决定)

我们来看下图示例：

窗口函数清单

SQL窗口函数可以分成下面三类

• 值返回

  • FIRST_VALUE()

  • LAG()

  • LAST_VALUE()

  • LEAD()

• 聚合

  • AVG()

  • COUNT()

  • MAX()

  • MIN()

  • SUM()

• 排名

  • CUME_DIST()

  • DENSE_RANK()

  • NTILE()

  • PERCENT_RANK()

  • RANK()

  • ROW_NUMBER()

这些函数的具体说明请参考：

• https://drill.apache.org/docs/value-window-functions/#lag-%7C-lead

• https://drill.apache.org/docs/aggregate-window-functions/

• https://drill.apache.org/docs/ranking-window-functions/

窗口函数语句中 partition by 与 group by 的区别

简而言之，partition by 出现在窗口函数后 over() 语句里，目标是通过给表格的行分组，最终帮助计算出窗口函数的结果。窗口函数紧跟在 select 后，实际上计算了一个新的列。计算结果不改变表格行数。 而 group by 在 SQL 中的目标是分组，然后进行总结计算。分组总结后，新表格的行数会减少，其数目和“组别”的个数一样。 所以说 partition by 是为了服务窗口函数创建新的列，group by 是为了将表格分组然后进行每组的统计总结计算。两者的区分具体在这篇知乎文章中有更细致的举例： https://zhuanlan.zhihu.com/p/92654574