神奇的 SQL ，高级处理之窗口函数，这可是魅力十足的功能！窗口函数是标准的SQL功能，而非特定数据库的功能，它与聚合函

开心一刻

今天儿子跟老婆聊天 儿子：妈妈，我为什么没有两个爸爸呀 老婆：每个人都只有一个爸爸呀，你看谁有两个爸爸了 儿子一脸真诚的看着老婆：那你为什么就有两个爸爸呢 老婆一脸疑惑的望向儿子：我哪有两个爸爸了？ 儿子有点不服气，温柔地说道：你管爷爷叫爸爸，你管姥爷还叫爸爸，这不就是两个爸爸吗 老婆轻声解释道：虽然我管他们两个都叫爸爸，但是姥爷才是我的爸爸，爷爷是爸爸的爸爸，是我公公，明白了吗 儿子两眼朝天上看了下，若有所思道：公公不是太监吗 老婆惊讶道：什么太监呀，我说的公公和你说的公公不是一回事，你这一天天的脑子里都想什么呢 儿子生气道：你不用解释了，待会我就告诉奶奶，你说爷爷是太监

什么是窗口函数

英文名称：Window Function，亦称 OLAP（Online Analytical Processing）Function，用来对数据库数据进行实时分析处理，例如 市场分析、财务报表 等

Window Function 是标准的 SQL 功能，而非某个具体数据库的功能

中文翻译过来，叫 窗口函数 ，或者 开窗函数，在 Oracle 中也称 分析函数；与 聚合函数 一样，也是对集合进行聚合计算，但和 聚合函数 又不一样，使用 聚合函数 时，每组只返回一个值，但 开窗函数 可以为组中的每一行返回一个值，你们懂我说的意思吧

目前不懂也没关系哈，继续往下看，看完之后你肯定就懂了

虽说窗口函数是标准的 SQL 功能，各个数据库厂商理应都支持，但实际与理论有所差别，Oracle 11g、SQL Server 2008、DB2 9.7、PostgreSQL 8.4 开始支持窗口函数，MySQL 就比较任性了，从 MySQL 8 开始才支持窗口函数

关于对标准 SQL 的支持以及支持程度，还得看各个数据库厂商，有的支持的早、支持的全，也有的支持的晚、支持的少，但随着时间的推移，我相信标准 SQL 终将能在所有的 DBMS 中使用

窗口函数的语法

基本语法如下

看着很简单，实则很陌生，我们将其进行拆分下

1. 窗口函数，命名一般是见名知意，表明这个函数要实现的功能

2. OVER 子句，OVER 是约定好的固定写法，其内容是规则的指定，告诉窗口函数以怎样的规则去实现功能

   PARTITION BY 类似 GROUP BY ，指定分组规则，ORDER BY 就跟我们平时使用的 ORDER BY 一样，指定排序规则

看完这个语法介绍，我相信你们还是挺懵，我非常理解大家

因为我刚接触的时候也很懵，懵归懵，但你们不要慌，等看完后面的案例，你们就懂了

能够作为窗口函数使用的函数分两种

1. 专用窗口函数，如：RANK、ROW_NUMBER、DENSE_RANK 等等
2. 能够作为窗口函数的聚合函数，如：SUM、AVG、COUNT、MAX、MIN

专用窗口函数

因为对 MySQL 比较熟，所以后续案例基于 MySQL8.0.30 来演示，准备 tbl_ware 及数据

CREATE TABLE `tbl_ware` (
  `ware_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `ware_name` VARCHAR(100) NOT NULL COMMENT '商品名称',
  `ware_category` VARCHAR(100) NOT NULL COMMENT '商品类别',
    `sale_unit_price` INT COMMENT '销售单价',
    `purchase_unit_price` INT COMMENT '进货单价',
    `registration_date` DATE COMMENT '等级日期',
  PRIMARY KEY (`ware_id`) USING BTREE
) ENGINE=InnoDB COMMENT='产品';

INSERT INTO tbl_ware VALUES
(1,'T恤衫','衣服',100, 50,'2023-12-15'),
(2,'打孔器','办公用品',25, 10,'2023-12-15'),
(3,'运动T恤','衣服',150, 50,'2023-12-15'),
(4,'菜刀','厨房用具',75, 30,'2023-12-15'),
(5,'高压锅','厨房用具',600, 200,'2023-12-15'),
(6,'叉子','厨房用具',7, 3,'2023-12-15'),
(7,'菜板','厨房用具',98, 30,'2023-12-15'),
(8,'圆珠笔','办公用品',5, 2,'2023-12-15');

专用窗口函数是标准 SQL 定义的 OLAP 专用函数，通过函数名很容易看出其 OLAP 的用途

1. RANK

   对 RANK，我相信你们都接触过，比如 RANK 分，RANK 排名，熟悉的记忆是不是涌上来了？而该函数也正是用来排名、排序

   看个案例

   对 tbl_ware 按售价从高到低进行排名， SQL 该如何写

   我相信你们很容易就能想到

   SELECT * FROM tbl_ware ORDER BY sale_unit_price DESC;
   

   那除了这个方式，还有其他方式吗，RANK 也能实现

   SELECT *, RANK() OVER(ORDER BY sale_unit_price DESC) AS ranking FROM tbl_ware;
   

   

   这个案例不能够充分体现 RANK 的功能，我们再看个案例

   对 tbl_ware 按类别进行分组，然后组内按售价从高到低进行排名， SQL 又该如何写

   看到分组二字，你们第一反应肯定想到了 GROUP BY，不只是你们，我也 一样；但 GROUP BY 之后了，你们是不是开始迷茫了？因为 GROUP BY 往往结合 聚合函数 使用，分组后每组只能得到一个值，显然满足不了需求；此时就需要 RANK 登场了

   SELECT *, RANK() OVER(PARTITION BY ware_category ORDER BY sale_unit_price DESC) AS ranking FROM tbl_ware;
   

   

   这是不是完美实现了需求？

   PARTITION BY 对表进行横向分组，类似 GROUP BY，但不具备聚合功能；ORDER BY 则决定了纵向排序规则，与 SELECT 子句末尾的 ORDER BY 子句完全相同

   通过 PARTITION BY 分组后的记录集合称为 窗口，代表 范围；窗口函数 名称由此而来

2. DENSE_RANK

   一看名字就知道跟 RANK 有关系，为了对比它俩的区别，需要补充几条数据

   INSERT INTO tbl_ware VALUES
   (9,'带帽卫衣','衣服', 150, 90, '2023-12-15'),
   (10,'砍骨刀','厨房用具', 150, 69, '2023-12-15');
   

   

   RANK 排序时，如果存在相同位次的记录，会跳过之后的位次，如衣服类别：1,1,3，2 被跳过了

   DENSE_RANK 排序时，如果存在相同位次的记录，则不会跳过之后的位次，如衣服类别：1,1,2

3. ROW_NUMBER

   获取行数或者行号；请看案例

   对 tbl_ware 按售价从高到低进行排序后获取每一行的行号， SQL 该如何写

   用 RANK 肯定不行，存在相同位次时它的位次值类似：1,1,2；DENSE_RANK 类似

   此时就需要用到 ROW_NUMBER 了

   SELECT *, ROW_NUMBER() OVER(ORDER BY sale_unit_price DESC) AS row_num FROM tbl_ware;
   

   

   如果再按类别分组

   SELECT *, ROW_NUMBER() OVER(PARTITION BY ware_category ORDER BY sale_unit_price DESC) AS row_num FROM tbl_ware;
   

   

   此刻，你们应该想起那个工作中比较常见的需求了

   

   想不起来？那我再延申下案例

   对 tbl_ware 按类别分组后，取每组售价最高的 2 条记录，SQL 该如何写

   注意看上面分组案例图中的 row_num，发现什么了？所以 SQL 是不是可以这么写？

   SELECT * FROM (
   	SELECT *, ROW_NUMBER() OVER(PARTITION BY ware_category ORDER BY sale_unit_price DESC) AS row_num 
   	FROM tbl_ware
   ) t WHERE t.row_num <= 2;
   

   

   所以

   分组排序后取前N

   是不是都可以用 ROW_NUMBER 来实现？

还有其他的 专用窗口函数 就不一一做介绍了，大家可以去各个数据库的官网进行查阅

聚合参数的窗口化

所有的 聚合函数 都能用作窗口函数，其语法和 专用窗口函数 完全相同；作为窗口化使用后，聚合函数实现的效果就发生了很大的变化，我们来看具体案例

1. SUM

   作为聚合函数，SUM 的作用想必大家都很清楚了，但是窗口化之后了，我们来看看效果

   SELECT ware_id,ware_name,sale_unit_price,
   	SUM(sale_unit_price) OVER(ORDER BY ware_id) AS current_sum 
   FROM tbl_ware;
   

   

   发现什么了？并不是一个单独的汇总值，而是逐行汇总，是不是有点意思？如果再加上分组

   SELECT ware_id,ware_name,ware_category,sale_unit_price,
   	SUM(sale_unit_price) OVER(PARTITION BY ware_category ORDER BY ware_id) AS current_sum 
   FROM tbl_ware;
   

   

   分组后，对每一组进行逐行汇总

2. AVG

   类比 SUM，我们直接看分组的情况

   SELECT ware_id,ware_name,ware_category,sale_unit_price,
   	AVG(sale_unit_price) OVER(PARTITION BY ware_category ORDER BY ware_id) AS current_avg
   FROM tbl_ware;
   

   

   分组后，对每一组的每一行求历史平均值

3. MAX

   同样直接看分组情况

   SELECT ware_id,ware_name,ware_category,sale_unit_price,
   	MAX(sale_unit_price) OVER(PARTITION BY ware_category ORDER BY ware_id) AS current_max
   FROM tbl_ware;
   

   

   分组后，对每一组的每一行求历史最大值

其他 聚合函数 的窗口化就不一一演示了，相信你们也都摸清套路了

适用范围

通过上述的几个案例，相信你们对这个问题已经有了一个大致的答案

窗口函数只能在 SELECT 子句中使用，不能在 WHERE 子句或者 GROUP BY 子句中使用

为什么呢？因为窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的 结果 进行的逐行操作，我们换个角度来看， 窗口函数是不会改变结果行数的，而 WHERE 是会改变结果行数的，那把窗口函数放到 WHERE 子句的意义何在？所以一不做二不休，直接在语法上做了这样的限制

窗口函数只能在 SELECT 子句中使用

总结

1. 窗口函数 是标准的 SQL 功能，而非特定数据库的功能

   SQL 功能的落地还得依赖各个数据库厂商；提供了标准，数据库厂商不一定实现，或者说暂时不实现

2. 窗口函数 与 聚合函数 并非矛盾，二者是互补关系

3. 我们之所以对 窗口函数 这么陌生，主要是使用太少，但是其在报表分析方面还是非常有作用的

4. 窗口函数 的使用范围很有限，你可以随意使用，报语法错误了再调整呗