SQL窗口函数相关总结

1、窗口函数基本语法

窗口函数是对表中一组数据进行计算的函数，一组数据跟当前行相关

基本语法：

<window_function> OVER (PARTITION BY column1, column2 ... column_n)

1.1 <window_function>

 聚合函数，（`COUNT()`, `SUM()`, `AVG()` 等）rank等排序函数，分析函数等。

1. 排序函数 (Ranking Function) ；

2. 聚合函数 (Aggregate Function) ；

3. 分析函数 (Analytic Function) ；

4. NEXT VALUE FOR Function， 这是给sequence专用的一个函数；

1.2 OVER(...)

窗口函数的窗框通过`OVER(...)` 子句定义，通过`OVER(...)` 定义窗框 (开窗方式和大小)。

注意：over()窗口函数先执行，聚合函数后执行。

partition_clause：

`PARTITION BY` 的作用与 `GROUP BY`类似：将数据按照传入的列进行分组，与 `GROUP BY` 的区别是，`PARTITION BY` 不会改变结果的行数。

2. PARTITION BY与GROUP BY区别

① group by是分组函数，partition by是分析函数

② 在执行顺序上：from > where > group by > having > 窗口函数，而partition by应用在以上关键字之后，可以简单理解为就是在执行完select之后，在所得结果集之上进行partition by分组

③ partition by相比较于group by，能够在保留全部数据的基础上，只对其中某些字段做分组排序（类似excel中的操作），而group by则只保留参与分组的字段和聚合函数的结果（类似excel中的pivot透视表）

2、排序函数(Ranking Function)

排序函数中，ROW_NUMBER()较为常用，可用于去重、分页、分组中选择数据，生成数字辅助表等等。排序函数在语法上要求OVER子句里必须含ORDER BY，否则语法不通过。

通过排序获取序号的函数主要有：

- ​    RANK() – 返回排序后的序号 rank ，有并列的情况出现时序号不连续
- ​    DENSE_RANK() – 返回 连续 序号
- ​    ROW_NUMBER() – 返回连续唯一的行号，与排序`ORDER BY` 配合返回的是连续不重复的序号
 

2.1 rank()函数（排序后的序号rank，序号不连续）

```sql
RANK() OVER (ORDER BY ...)
```

`RANK()`会返回每一行的等级（序号） 

`ORDER BY`对行进行排序将数据按升序或降序排列

` RANK（）OVER（ORDER BY ...）`是一个函数，与`ORDER BY` 配合返回序号
 

2.2 dense_rank()函数

RANK() 函数返回的序号，可能会出现不连续的情况

如果想在有并列情况发生的时候仍然返回连续序号可以使用 `dense_rank()函数`，总结：有并列且连续

2.3 row_number()函数（不重复行号）

想获取排序之后的序号，也可以通过ROW_NUMBER() 来实现，从名字上就能知道，意思是返回行号。

这个行号是不重复的（相同值的rank排序的序号相同，但是row_number返回的是排序后的行号，绝对不同。）

```sql
SELECT
  name,
  platform,
  editor_rating,
  ROW_NUMBER() OVER(ORDER BY editor_rating) `row_number`
FROM game;
```

3、聚合函数 (Aggregate Function)

SQL Server 2005中，窗口聚合函数仅支持PARTITION BY，也就是说仅能对分组的数据整体做聚合运算；

SQL Server 2012开始，窗口聚合函数支持ORDER BY，以及ROWS/RAGNE选项，原本需要子查询来实现的需求，如： 移动平均 (moving averages), 总计聚合 (cumulative aggregates), 累计求和 (running totals) 等，变得更加方便；

包含 AVG, SUM, MAX,MIN, COUNT等，在此不详细介绍：
sum() over()    avg() over（）   count() over()

4、分析函数 (Analytic Function)

与偏移量相关，通过两个函数可取得前N行（lag）、后N行（lead）作为独立的列，以更方便做数据过滤：

4.1 LAG ( ) OVER ( [query_partition_clause] order_by_clause )

LEAD(<sql_expr>,<offset>, <default>) OVER (<analytic_clause>)

<sql_expr> ：通常是字段名。

<offset> ：表示相对于当前行的偏移幅度（对LEAD来说是向后偏移，对LAG来说则是向前），正整数，默认为1。

<default> ：当偏移幅度超出该分区（PARTITION）的范围时返回的值。

4.2 LEAD ( ) OVER ( [query_partition_clause] order_by_clause )

LEAD(<sql_expr>,<offset>, <default>) OVER (<analytic_clause>)

<sql_expr> ：通常是字段名。

<offset> ：表示相对于当前行的偏移幅度（对LEAD来说是向后偏移，对LAG来说则是向前），正整数，默认为1。

<default> ：当偏移幅度超出该分区（PARTITION）的范围时返回的值。

5、参考资料

sql之窗口函数（一）_嘉嘉嘉Jessie的博客-CSDN博客_sql 窗口函数sql 窗口函数 over() 排序函数https://blog.csdn.net/weixin_49588247/article/details/12170653016. 窗口函数 (Window Function) 的使用 - 张骞 - 博客园一. 排序函数 (Ranking Function)； 二. 聚合函数 (Aggregate Function)； 三. 分析函数 (Analytic Function)； 四. NEXThttps://www.cnblogs.com/seusoftware/p/9316139.htmlOracle之分析函数（Analytic Function）入门_xiaoziteng的博客-CSDN博客_analytic function1. 引言最近心血来潮去参加了一个PL/SQL工程师的面试，期间被问到了Oracle分析函数，PL/SQL开发并非我的老本行，在之前的工作中，也很少使用分析函数，原因之一是对数据库移植问题的考虑；其二是很少遇到非用分析函数不可的情况；其三是分析函数的语法相对复杂，令人缺乏兴趣。这https://blog.csdn.net/xiaoziteng/article/details/6641486?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2~default~BlogCommendFromBaidu~Rate-1.pc_relevant_default&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2~default~BlogCommendFromBaidu~Rate-1.pc_relevant_default&utm_relevant_index=1