【案例实操】面试必知必会的SQL窗口函数

什么是窗口函数？

1、定义

理解窗口函数，顾名思义，这是一个可以在滑动窗口上实现各种统计操作的函数。一个滑动窗口是一个移动变化的小区间，所以窗口函数可以在不断变化的小区间里实现各种复杂的统计分析，统计的数据范围灵活可变，在日常的数据分析中，也是一种非常重要且常用的分析(OLAP)函数。

2、窗口函数VS. 聚合函数

经过上面的描述，窗口函数好像和我们用的聚合函数sum、count、avg功能很像啊？聚合函数不也是在一个区间范围内实现各种不同的聚合操作么？

窗口函数与聚合函数的功能相似，两者都可以对指定数据窗口进行统计分析，但窗口函数与聚合函数又有所区别，窗口函数可以为每行数据进行一次计算，因为窗口函数指定了数据窗口大小，这个数据窗口大小可能会随着行的变化而滑动变化，可以在这个滑动窗口里进行计算并返回一个值。而聚合函数只返回一行，因为它只能对分组下的所有数据进行统计。另外，在使用聚合函数时，与分组列无关的列不可以出现在SELECT关键字下，如果想要把除了分组列之外的其他明细数据和聚合值同时提取，聚合函数是实现不了的 ，而窗口函数就可以方便地实现这一点。比如我想在一行同时呈现某个产品的销量和所有产品的销量汇总sum，以便于计算某个产品占所有销量的百分比，这个时候窗口函数就可以非常完美地支持。所以，综合以上的问题，相对于传统死板固定的聚合函数，以更灵活的设置小区间的方式来计算统计值的窗口函数应运而生。

3、窗口函数的语法

窗口函数出现在 SELECT 子句的表达式列表中，它最显著的特点就是 OVER 关键字。语法定义如下：

window_function (expression) OVER (   [ PARTITION BY part_list ]   [ ORDER BY order_list ]   [ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )

其中包括以下选项：1、PARTITION BY 表示将数据先按 part_list 进行分组, 如果不指定 PARTITION BY，则不对数据进行分组，换句话说，所有数据看作同一个分组，和聚合函数就很像了。2、ORDER BY 表示将各个分组内的数据按 order_list 进行排序。一般情况下都要指定，如果不指定 ORDER BY，则不对各分区做排序，通常用于那些顺序无关的窗口函数，例如SUM

3、ROWS/RANGE BETWEEN表示窗口范围的定义，即：当前窗口包含哪些数据ROWS 选择前/后几行，例如 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING 表示当前行往前数3行到往后 3 行，一共 7 行数据（或小于 7 行，如果碰到了边界）。所以ROWS是通过排序后的前后位置选取窗口范围。RANGE 选择数据范围，例如 RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING 表示选取取值在 [c−3,c+3]这个范围内的行，其中c 为当前行的值。所以RANGE是通过数值的大小选取窗口范围。若不指定 ORDER BY，则默认使用PARTITION BY分组内所有行，即等价于：ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。若指定了 ORDER BY，使用PARTITION BY分组内第一行到当前值，即等价于：ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。参考下图看看ROWS 和 RANGE的区别，当前行val的值是2，ROWS BETWEEN 1 PRECEDING AND CURRENT ROW指定的窗口就是前一行和当前行，共两行；RANGE BETWEEN 1 PRECEDING AND CURRENT ROW指定的窗口是当前行val值2减去1到2这个范围内的数据，共包括1,1.2,2三行。

4、窗口函数的分类

按照使用功能场景，窗口函数可以分为以下 5类：

1. 聚合（Aggregate）：AVG(), COUNT(), MIN(), MAX(), SUM()…
2. 排序（Ranking）：RANK(), DENSE_RANK(), ROW_NUMBER()…
3. 极值（Value）：FIRST_VALUE(), LAST_VALUE()…
4. 位移（Shift）：LEAD(), LAG()…
5. 分箱（Binning）：NTILE()…

接下来我们将通过实际案例对每一类的窗口函数进行讲解，进一步了解窗口函数的和用法和使用场景。

5、构造演示数据

为方便进行说明，我们构建了以下的数据表product_sale_all，该表记录了每年、各个品牌的销售额，建表和数据填充语句如下：

-- 1、创建产品销售明细表Product，用于记录每天各个商品的销售情况USE Sales; -- 转到Sales数据库下执行以下操作CREATE TABLE product_sale_all(      year     CHAR(25)      NOT NULL,-- 年份字段，字符类型CHAR            product_product_name   VARCHAR(100) NOT NULL,-- 产品名称，字符类型VARCHAR            product_category   VARCHAR(32)  NOT NULL,-- 产品所属类别，字符类型VARCHAR            sale_sum     INT-- 产品销售额总和，整数类型INT      );       -- 2、插入一些用于演示的数据，只是用来演示说明，并无实际意义和真实性。 INSERT INTO product_sale_all VALUES ('2017', 'iPhone', '手机', 110000); INSERT INTO product_sale_all VALUES ('2018', 'iPhone', '手机', 115000); INSERT INTO product_sale_all VALUES ('2018', 'HuaWei', '手机', 138000); INSERT INTO product_sale_all VALUES ('2019', 'HuaWei', '手机', 160000); INSERT INTO product_sale_all VALUES ('2018', 'Canon', '相机', 100000); INSERT INTO product_sale_all VALUES ('2019', 'Canon', '相机', 200000); INSERT INTO product_sale_all VALUES ('2020', 'Canon', '相机', 180000); INSERT INTO product_sale_all VALUES ('2017', 'DELL', '笔记本电脑', 155000); INSERT INTO product_sale_all VALUES ('2018', 'DELL', '笔记本电脑', 130000); INSERT INTO product_sale_all VALUES ('2019', 'DELL', '笔记本电脑', 155000); INSERT INTO product_sale_all VALUES ('2020', 'DELL', '笔记本电脑', 140000);

将所有的数据查询出来结果如下所示。

-- 3、查看数据select * from product_sale_all;

02窗口函数之聚合

1、计算总销售额

如果要在每一行后面加上所有产品的总销售额total_sale_sum可以使用如下的窗口函数：

SELECT *,SUM(sale_sum) OVER() as total_sale_sumfrom product_sale_all;

其中sum是求和，over()中不添加参数，则默认对所有数据进行求和，可以看到，新加的一列total_sale_sum，每一行的结果都是1583000。

2、各产品类别的总销售额

如果要在每一行后面加上整体的销售额total_sale_sum，以及当前产品所在类别的总销售额category_sale_sum可以使用如下窗口函数：

SELECT *,SUM(sale_sum) OVER() as total_sale_sum,SUM(sale_sum) OVER(PARTITION BY product_category) as category_sale_sumfrom product_sale_all;

计算各产品类别总销售额就要对各个产品类别分组，这里分组使用的是PARTITION BY，PARTITION BY的功能与GROUP BY的功能类似，指定按照哪一列进行分组，用product_category分组求和，则同属于一个产品类别的产品每个product_category的输出结果一致。

3、各产品类别的累计销售额

如果要按照产品类别进行分组，按照销售额降序，求累计至当前产品的销售额order_sale_sum。使用窗口函数实现如下：

SELECT *,SUM(sale_sum) OVER() as total_sale_sum,SUM(sale_sum) OVER(PARTITION by product_category) as category_sale_sum,SUM(sale_sum) OVER(PARTITION by product_category ORDER BY sale_sum DESC) as order_sale_sumfrom product_sale_all;

这里使用SQL中经常使用的一种计算累计的方法，先按照product_category进行分组，分组后每组内使用ORDER BY按照销售额sale_sum降序排列，使用ORDERBY时没有使用ROWS BETWEEN则意味着窗口是从该分组的起始行到当前行，进而可以对不同产品类别进行累加求和操作。

4、各产品类别产品数

如果要在每一行后面加上所有的产品数量(不去重) total_products，以及当前所属产品类别下的产品数量category_products，可以用窗口函数实现如下：

SELECT *,COUNT(product_name) OVER() as total_products,COUNT(product_name) OVER(PARTITION BY product_category) as category_productsfrom product_sale_all;

类似的，count()用于计数，与前面sum的用法基本一致，可以用count(distinct product_name)进行去重，但是目前MySQL窗口函数暂不支持countdistinct， over()中不添加参数，则默认对所有数据进行计数，可以看到，新加的2列：产品总数total_products和每类产品的数量category_products如下图所示。

5、各产品类别的平均销售额

如果要在每一行后面加上整体的平均销售额avg_sale_sum，以及当前所属产品类别下的平均销售额category_ayg_sale_sum，可以用窗口函数实现如下：

SELECT *,AVG(sale_sum) OVER() as avg_sale_sum,AVG(sale_sum) OVER(PARTITION BY product_category) as category_ayg_sale_sumfrom product_sale_all;

AVG聚合函数的用法与前面的聚合运算用法一致，PARTITION BY同样用来分组，只是这里分组后是求平均值。

6、各产品类别的最低销售额

如果要在每一行后面加上整体的最高销售额max_sale_sum，以及当前所属产品类别下的最低销售额category_min_sale_sum，可以用窗口函数实现如下：

SELECT *,MAX(sale_sum) OVER() as max_sale_sum,MIN(sale_sum) OVER(PARTITION by product_category) as category_min_sale_sumfrom product_sale_all;

这里MAX(sale_sum)函数对整个数据计算最大值，使用PARTITION BY对不同的产品类别进行分组，然后计算各个分组的最小值。

03窗口函数之排序使用窗口函数可以方便地进行排序，常用的排序函数有：row_number,rank,dense_rank，它们的使用区别如下：

• row_number从1开始，按照ORDER BY的顺序，值相等时排名不出现并列；
• rank与row_number类似，只是值相等时，排名会并列，并会在名次中跳过并列排名继续排名；
• dense_rank与row_number类似，只是值相等时，排名会并列，并会在名次中紧接着并列的排名继续排名。

通过一个例子说明，在每一行后面，使用三种不同的排名函数，按照销售额降序的方式对各产品进行排序。

SELECT *,ROW_NUMBER()OVER(ORDER BY sale_sum DESC) as '顺序排序',RANK()OVER(ORDER BY sale_sum DESC) as '秩排序',DENSE_RANK()over(ORDER BY sale_sum DESC) as '数据排序'from product_sale_all;

row_number函数，按照行记录的顺序来排序，此处从1到11按顺序排列；rank函数，在排名相等会在名次中留下空位，此处共同排名为第4名，同时忽略第5名，继续往下排列，下一行直接排到第6名；dense_rank排名相等会在名词中不会留下空位，此处共同排名为第4名，不忽略第5名，继续往下排列，下一位为第5名。

04窗口函数之极值如果我们要获取分组内排序第一或者最后一位的值，可以使用first_value和last_value。first_value是该分组内截止到当前行的第一条记录，last_value是该分组内截止到当前行的最后一条记录。例如，我们如果想看整体上销售额最高/最低的产品all_max_product/all_min_product，以及各产品类别中销售额最低的产品category_min_product，窗口函数实现如下：

select *,first_value(product_name)over(order by sale_sum desc) as all_max_product,first_value(product_name)over(order by sale_sum asc) as all_min_product,last_value(product_name)over(partition by product_category order by sale_sum desc rows between unbounded preceding and unbounded following) as category_min_productfrom product_sale_all;

first_value按分组排序后取范围内第1个值，因为没有使用partition by，所以默认是所有数据，分别案找sale_num进行逆序/升序排列，可以获得整体上销售额最高的产品all_max_product和最低的产品all_min_product
last_value取最后1个值，因为默认窗口的关系，last_value会随着窗口的改变而改变，所以用last_value的时候尤其需要注意，这里使用unbounded preceding and unbounded following来限定分组内的所有记录，以此来查询每个产品类别里销售额最小的产品。

05窗口函数之位移如果我们要取当前记录的前/后几位的数据，需要用到位移函数lag/lead。lag和lead是按照排序规则，取前多少位和后多少位，参数有3个，第1个是要取出来的列，第2个移动多少位，第3个是如果取不到，赋予的值，默认取不到是NULL。如果我们按产品类别进行分组，组内按照销售额进行逆序排列，针对每一行，计算销售额排名前1位lag_product和后1位产品lead_product，窗口函数实现如下

select *,lag(product_name,1,null)over(partition by product_category order by sale_sum desc) as lag_product,lead(product_name,1,'0')over(partition by product_category order by sale_sum desc) as lead_productfrom product_sale_all;

每个组内按照销售额逆序排列后，每一行最后会出现该分组内、该产品的前一位和后一位产品的名称，由于每个分组内第一行的前一行和最后一行的后一行不存在，系统会默认置为NULL,我们也可以在函数中对NULL进行替换处理，如lead中将NULL替换成0，结果如下所示：

06窗口函数之分箱如果我们要对记录进行分箱，也就是把记录切分为几组，就要用分箱函数ntile。ntile(n)可以将数据按照顺序划分成N组，返各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号，ntile(3)表示将表切分为3组，ntile也可以在PARTITION BY分组后分箱，表示对当前的组内进行分箱。例如，我们要对整体的销售额分为3组，每个产品类别内分为2组，窗口函数实现如下：

select *,ntile(3) over(order by sale_sum desc) as total_part,ntile(2)over(partition by product_category order by sale_sum desc) as category_partfrom product_sale_all;

以上就是数据分析工具—SQL窗口函数部分的内容，部分数据分析工具请翻看历史文章，更多数据分析工具的文章持续更新中，敬请期待，如果觉得不错，也欢迎分享、点赞和点在看哈~