神奇的 SQL 高级处理之 Window Functions → 打破我们的局限

什么是窗口函数

　　 Window Function 也称为 OLAP（Online Analytical Processing）函数 

　　对数据库数据进行实时分析处理，例如市场分析、财务报表等，是标准的 SQL 功能

　　中文翻译过来，叫 窗口函数 ，或者 开窗函数 ，在 Oracle 中也称 分析函数 

　　与 聚合函数 一样，也是对集合进行聚合计算，但和 聚合函数 又不一样，使用 聚合函数 时，每组只返回一个值，但 开窗函数 可以为组中的每一行返回一个值

　　你们懂我说的意思吧

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

　　支持情况

　　既然 窗口函数 是 标准 SQL 功能 ，那关系型数据库应该都支持吧

　　 Oracle 11g 、 SQL Server 2008 、 DB2 9.7 、 PostgreSQL 8.4 都支持窗口函数

　　但 MySQL 从 8 开始才支持， MySQL5.7 及之前的版本不支持 窗口函数 

　　关于对标准 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 

　　后续的案例演示我们基于 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 的用途

　　RANK

　　从名字可知，该函数用来排名、排序

　　1、假设我们对 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; 

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

　　有小伙伴一看到分组二字，第一反应肯定想到了 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 分组后的记录集合称为“窗口”，代表“范围”。这也是 窗口函数 名称的由来

　　DENSE_RANK

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

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

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

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

　　ROW_NUMBER

　　获取行数或者行号

　　如果我们想按售价从高到低排序后，获取每一行的行号， SQL 可写成： 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; 

　　此刻大家应该想起点什么了

　　分组取前N条，是不是很适合用这种方式实现？

　　我都跟你们实现好了：MySQL 分组排序后 → 如何取前N条或倒数N条

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

聚合函数的窗口化使用

　　所有的 聚合函数 都能用作窗口函数，其语法和 专用窗口函数 完全相同

　　作为窗口化使用后， 聚合函数 实现的效果就发生了很大的变化，我们来看具体案例

　　SUM

　　作为 聚合函数 ， SUM 的作用想必大家都很清楚了

　　但是窗口化之后了，我们来看看效果

　　发现什么了？

　　并不是一个单独的汇总值，而是逐行汇总，是不是有点意思？

　　如果再加上分组

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

　　AVG

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

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

　　其他 聚合函数 的窗口化就不一一演示了，相信大家也都明白了

窗口函数的适用范围

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

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

　　因为 窗口函数 是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的逐行操作

　　我们换个角度来看， 窗口函数 是不会改变结果行数的，而 WHERE 是会改变结果行数的，那把 窗口函数 放到 WHERE 子句的意义何在？

　　所以一不做二不休，直接在语法上做了这样的限制： 窗口函数 只能在 SELECT 子句中使用

总结

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

　　　　 SQL 功能的落地还得依赖各个数据库厂商

　　　　提供了标准，数据库厂商不一定实现，或者说暂时不实现

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

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

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

参考

　　《SQL 基础教程》

 

【出处】：https://www.cnblogs.com/youzhibing/p/17908962.html

=======================================================================================