本文分享自华为云社区《GaussDB(DWS) SQL进阶之SQL操作之窗口函数》，作者： 两杯咖啡。

窗口函数是聚集函数的延伸，是更高级的 SQL 语言操作，主要用于 AP 场景下对数据进行一些分析、汇总、排序的功能。本文将从窗口函数的基本概念入手，介绍其常用 SQL 语法和主要的应用场景，同时会讲到在 GaussDB(DWS)中使用窗口函数需要考虑的一些因素。

一. 窗口函数的作用

窗口函数(Window Function)，又被叫做分析函数(Analytics Function)，通常在需要对数据进行分组汇总计算时使用，因此与聚集函数有一定的相似性。但与聚集函数不同的是，聚集函数通过对数据进行分组，仅能够输出分组汇总结果，而原始数据则无法展现在结果中。而窗口函数则可以同时将原始数据和聚集分析结果同时显示出来。通过下例，大家可以体会一下区别。

给定表 score(id, classid, score)，每行表示学生 id，所在班级 id 以及考试成绩，数据如下图所示：

如果我们想获取每个班的总分、平均分及学生数量，可以通过对 classid 进行聚集，查询语句为：

select classid, sum(score), avg(score), count(*) from score group by classid order by classid;

结果如下：

通过这个结果，我们了解了班级 1 和 2 的基本信息，但是此时丢掉了学生信息，也不知道每个学生在班级中的排名如何。如果想查询这些信息，当然可以通过将聚集结果和原表进行 Join 得出，但显然更繁琐。而通过窗口函数的语句，我们可以轻而易举地将所需要的信息查询出来。

select classid, id, score,sum(score) over(partition by classid),avg(score) over(partition by classid),count(*) over(partition by classid),rank() over(partition by classid order by score desc)from scoreorder by classid;

结果如下：

通过以上信息，我们可以很方便地进行进一步的查询，例如：查询每个班超过平均分的学生 id，排名前 5 的学生 id 等。

可以看出，窗口函数其实是对查询，聚集等多个操作所做的一个组合操作，但相对于多个操作而言，使用窗口函数在完成功能的情况下，书写也更加简洁。同时，窗口函数还提供了更多的函数、更多的聚集方式以支持多样化的功能，而且支持分组中的排序功能。通过与聚集结果比较，可以方便地提取符合一定统计要求的记录信息。

二. 窗口函数的 SQL 语法介绍

窗口函数的功能这么强大，那支持的语法一定也非常复杂吧。别急，通过系统性地分析，你也可以快速掌握窗口函数语法的精髓！

单个窗口函数表达式的主要语法为：

SUM(SCORE) OVER (PARTITION BY CLASSID ORDER BY SCORE ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

该表达式主要由以下部分组成（下图为图解）：

窗口函数表达式（紫色部分）：指定该窗口函数进行计算的聚集函数，可以是 SUM(), COUNT(), AVG(), MIN(), MAX()等聚集函数，可以通过以下语句在 GaussDB(DWS)中查到：

SELECT proname FROM pg_proc WHERE proisagg = TRUE;

也可以是其它专有的窗口函数，可以通过如下语句在 GaussDB(DWS)系统表中查到：

SELECT proname FROM pg_proc WHERE proiswindow = TRUE;

GaussDB(DWS)目前支持的专有窗口函数有 15 个：

下面将详细说明一下其中常用的窗口函数 ROW_NUMBER(), RANK(), DENSE_RANK()的区别。首先通过如下查询的结果来对比。

SELECT id, classid, score,ROW_NUMBER() OVER(ORDER BY score DESC),RANK() OVER(ORDER BY score DESC),DENSE_RANK() OVER(ORDER BY score DESC)FROM score;

可以看出，三个函数都是用于进行行排序的，且参数个数为 0。通过①可以看出 ROW_NUMBER()和 RANK()的区别：前者顾名思义，对行从 1 开始进行编号，无论数据是否重复，结果不重；而后者对于相同的数据，给出的排序结果是相同的。通过②可以看出 RANK()和 DENSE_RANK()的区别：前者在重复值后，编号和 ROW_NUMBER()是相同的，虽然相同的数据编号相同，但仍然占用多个编号位置；而后者对于重复值只占用一个编号，重复后紧接着进行编号。

在实际应用着，前两个函数应用较多，ROW_NUMBER()主要用于行编号，用于分页展示等应用中；而 RANK()主要用于对结果进行排序后展示。

窗口函数分区列（红色部分）：表示根据哪一列进行分组计算，类似于聚集语句中的 GROUP BY 子句。该部分可以没有，类似于聚集语句，表示对所有语句划分同一组处理。

窗口函数排序列（绿色部分）：表示数组划分到同一组后，在进行窗口函数计算前排序的顺序，可以指定多列，语法与 ORDER BY 类似。当聚集函数计算结果与顺序无关时，此子句可以省略。

窗口函数移动窗口选项（蓝色部分）：该选项也称为 Window Frame Option，默认可以省略，表示对每个分组内所有行进行聚集计算（无排序列时）或对每个分组内起始行到当前行进行聚集计算（有排序列时）。但指定该选项后，仅针对指定的窗口内的元组进行聚集计算。

对分组内所有行结果，当需要指定一个窗口时，我们需要指定开始的行和结束的行，则聚集函数将针对窗口之内的所有行的结果进行计算。因此，移动窗口选项的主要语法为：

RANGE|ROWS [BETWEEN] <rows_loc> [AND <rows_loc>]或 RANGE|ROWS <rows_loc>

第一种语法同时指定开始行和结束行，第二种语法仅指定开始行，结束行默认为当前行。

<rows_loc>用于指定某一行，支持以下五种用法：

UNBOUNDED PRECEDING

表示该分组的第一行

UNBOUNDED FOLLOWING

表示该分组的最后一行

CURRENT ROW

表示当前行。

<expression> PRECEDING

表示从当前行往前数<expression>数量的行，其中<expression>不能包含变量。RANGE 选项禁用。

<expression> FOLLOWING

表示从当前行往后数<expression>数量的行，其中<expression>不能包含变量。RANGE 选项禁用。

例如：

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 以该分组所有元组为窗口

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 以该分组起始行到当前行为窗口

ROWS BETWEEN 10 PRECEDING AND 5 FOLLOWING 以该分组当前行前 10 行到后 5 行为窗口（不能超过起始行和结束行）

示例：下图左边表含有列 x，计算的表达式值 SUM(x) OVER(ROWS 2 PRECEDING AND 2 FOLLOWING)的值见右表所示，中间箭头上的数字表示起始和结束窗口的行号。例如：第 1 行的窗口为[1-2, 1+2]=[1, 3]（不超过起始行）；第 4 行的窗口为[4-2, 4+2]=[2, 6]。

了解完单个窗口函数表达式的语法，我们来看下在 SQL 语句中的使用规范。

1、单个查询中可以包含一个或多个窗口函数表达式。

2、窗口函数仅能出现在输出列中。如果需要使用窗口函数的值进行条件过滤，需要将窗口函数嵌套在子查询中，在外层使用窗口函数表达式的别名进行条件过滤。例如：

select classid, id, scorefrom(select *, avg(score) over(partition by classid) as avg_score from score)where score >= avg_score;

3、窗口函数所在查询块中支持使用 GROUP BY 表达式进行分组去重，但要求窗口函数中的 PARTITION BY 子句中必须是 GROUP BY 表达式的子集，以保证窗口函数在 GROUP BY 列去重后的结果上进行窗口运算，同时 ORDER BY 子句的表达式也需要是 GROUP BY 表达式的子集，或聚集运算的聚集函数。例如：

select classid,rank() over(partition by classid order by sum(score)) as avg_scorefrom scoregroup by classid, id;

标准 bench-mark TPC-DS 中有多个语句使用到了窗口函数，以 TPC-DS Q51 为例：

WITH web_v1 as (selectws_item_sk item_sk, d_date,sum(sum(ws_sales_price))over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_salesfrom web_sales,date_dimwhere ws_sold_date_sk=d_date_skand d_month_seq between 1212 and 1212+11and ws_item_sk is not NULLgroup by ws_item_sk, d_date),store_v1 as (selectss_item_sk item_sk, d_date,sum(sum(ss_sales_price))over (partition by ss_item_sk order by d_date rows between unbounded preceding and current row) cume_salesfrom store_sales,date_dimwhere ss_sold_date_sk=d_date_skand d_month_seq between 1212 and 1212+11and ss_item_sk is not NULLgroup by ss_item_sk, d_date)select *from (select item_sk,d_date,web_sales,store_sales,max(web_sales)over (partition by item_sk order by d_date rows between unbounded preceding and current row) web_cumulative,max(store_sales)over (partition by item_sk order by d_date rows between unbounded preceding and current row) store_cumulativefrom (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk,case when web.d_date is not null then web.d_date else store.d_date end d_date,web.cume_sales web_sales,store.cume_sales store_salesfrom web_v1 web full outer join store_v1 store on (web.item_sk = store.item_skand web.d_date = store.d_date))x )ywhere web_cumulative > store_cumulativeorder by item_sk,d_datelimit 100;

上面查询中使用了四个窗口函数，以 CTE web_v1 的定义为例：

selectws_item_sk item_sk, d_date,sum(sum(ws_sales_price))over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_salesfrom web_sales,date_dimwhere ws_sold_date_sk=d_date_skand d_month_seq between 1212 and 1212+11and ws_item_sk is not NULLgroup by ws_item_sk, d_date;

该语句块计算了在一定时间内，网上销售的货物的累计销售额。

该语句块在 web_sales 和 date_dim 表 Join 后，使用 ws_item_sk, d_date 列进行 GROUP BY，计算 sum(ws_sales_price)，而后对聚集函数的结果进行窗口函数，对 sum(ws_sales_price)再进行窗口函数的求和。窗口函数 sum(sum(ws_sales_price)) over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row)的含义是：以 ws_item_sk 为分组，以 d_date 为顺序，计算从开始截止到当天累计销售额。

三. Window 子句

通过这个语句，细心的读者可以发现，窗口函数基本都是相同的格式 partition by item_sk order by d_date rows between unbounded preceding and current row，存在冗余书写的问题。因此，SQL 语句中支持使用 window 子句，类似于 WITH 子句定义公共的分组排序窗口，这样使用时仅引用对应的窗口名称即可。例如 Q51 的主语句可以写成：

select *from (select item_sk,d_date,web_sales,store_sales,max(web_sales)over (s rows between unbounded preceding and current row) web_cumulative,max(store_sales)over (s rows between unbounded preceding and current row) store_cumulativefrom (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk,case when web.d_date is not null then web.d_date else store.d_date end d_date,web.cume_sales web_sales,store.cume_sales store_salesfrom web_v1 web full outer join store_v1 store on (web.item_sk = store.item_skand web.d_date = store.d_date))x window s as (partition by item_sk order by d_date))ywhere web_cumulative > store_cumulativeorder by item_sk,d_datelimit 100;

在 GaussDB(DWS)中使用 window 子句有以下需要注意的：

1.window 子句仅能在相同查询块语句中复用，跨查询块需要定义不同的 window 子句。

2.window 子句中仅能包含 OVER 之后的窗口子句。窗口子句可以包含：PARTITION BY 子句、ORDER BY 子句和移动窗口选项，而 window 不支持移动窗口选项。

3.窗口函数在复用 window 子句时，可以在 window 子句的基础上增加 ORDER BY 子句和移动窗口子句，类似于继承。

例如下面的例子：

select classid, id, score,sum(score) over(s),avg(score) over(s),count(*) over(s),sum(score) over(s rows between 1 preceding and current row)from scorewindow s as (partition by classid order by score, id desc)order by classid;

查询结果为：

四. GaussDB(DWS)使用窗口函数注意事项

与 Teradata 等主流数据库不同的是，GaussDB(DWS)中窗口函数的 PARTITION BY 后的表达式如果是数字，表示常量值，而不是输出列的序号，这点与 GROUP BY 的语法也有出入。例如：

select *, rank() over(partition by 1 order by id) as avg_score from score;

此处会将所有数据分为一组。

由于窗口函数一般需要对数据进行排序之后进行窗口计算，因此在 GaussDB(DWS)中使用 Sort + WindowAgg 算子来实现其功能。如果有多个不同的窗口子句，则每个窗口子句对应一对 Sort + WindowAgg 算子，示例语句及计划如下：

explainselect classid, id, score,sum(score) over(s),avg(score) over(s),count(*) over(s),sum(score) over(s rows between 1 preceding and current row)from scorewindow s as (partition by classid order by score, id desc)order by classid;

此查询中包含两类窗口子句，(partition by classid order by score, id desc)以及(partition by classid order by score, id desc rows between 1 preceding and current row)，因此需要两对 Sort + WindowAgg 算子，见 2-4 号算子（为什么计划中仅出现了一个 SORT？请读者思考）。由于按 classid 做 partition，因此首先按其进行重分布，见 5 号算子。

对于分布式数据库 GaussDB(DWS)，数据需要在各 DN 执行以获得更好的性能。对于窗口函数，相同 PARTITION BY 的值的元组会在同一个 DN 上执行，因此对于缺少 PARTITION BY 子句，或 PARTITION BY 的值较少的场景，将无法完全发挥出分布式的效果。

(1) 当没有 PARTITION BY 子句时，Gauss(DWS)需要进行全局排序及窗口计算，因此需要先在各 DN 进行排序，然后将数据汇总到一个 DN 上进行合并排序及窗口计算，最终进行计算的 DN 将成为整个系统的瓶颈。这种场景需要尽量避免。（Teradata 早期支持的 MSUM 等函数其语义即进行全局排序并局部求和，就属于这种情况。后由于窗口函数可实现同样的功能，MSUM 仅作前向兼容对外提供。）此种场景示例语句及计划如下图所示：

SELECT * from (select id, classid, score, ROW_NUMBER() OVER(ORDER BY score DESC) rn from score) where rn <= 5;

第 4 号算子 Broadcast，将各 DN 排序后的数据（第 5 号算子）广播到某一个 DN 上，该 DN 上进行 ROW_NUMBER()的计算并进行过滤、输出结果。

(2) 当存在 PARTITION BY 子句时，会根据 PARTITION BY 的列进行重分布，将数据分布到各个 DN 上进行各自的分组计算。此时如果 PARTITION BY 的值较少，则数据会重分布到少数几个 DN 上，导致数据倾斜。因此，如果使用排序类的专有窗口函数，且仅关于排序靠前的若干行时，可以增加相应的过滤条件，GaussDB(DWS)会根据过滤条件进行双层 WindowAgg 计算，以 TPC-DS 的 Q67 为例：

select *from (select i_category,i_class,i_brand,i_product_name,d_year,d_qoy,d_moy,s_store_id,sumsales,rank() over (partition by i_category order by sumsales desc) rkfrom (select i_category,i_class,i_brand,i_product_name,d_year,d_qoy,d_moy,s_store_id,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsalesfrom store_sales,date_dim,store,itemwhere ss_sold_date_sk=d_date_skand ss_item_sk=i_item_skand ss_store_sk = s_store_skand d_month_seq between 1212 and 1212+11group by rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2where rk <= 100order by i_category,i_class,i_brand,i_product_name,d_year,d_qoy,d_moy,s_store_id,sumsales,rklimit 100;

该查询中包含如下窗口函数：rank() over (partition by i_category order by sumsales desc)，以 i_category 进行分组，求每个分组前 100 名的数据。在 TPC-DS 数据集中，i_category 表示商品种类，数量较少，因此我们采取双层 WindowAgg 策略，先在本地进行 WindowAgg，并过滤出前 100 名的数据，在数据量减少后，再进行全局重分布，以及全局 WindowAgg 运算。计划如下图所示：

第 10-11 号算子为各 DN 计算排序和 WindowAgg，第 9 号 Result 节点进行各 DN 的条件过滤，获取各 DN 前 100 名的数据，而后第 8 层算子根据 i_category 列进行重分布，这样各 DN 可以获取 i_category 值相同的数据进行全局计算。第 6-7 号算子计算全局的 WindowAgg 结果，由第 5 号算子进行结果过滤后输出。

五. 总结

窗口函数作为 SQL 的高级功能，用法比较灵活、复杂且比较难掌握，而驾驭了它却对我们 SQL 编写提供了更广阔的天地，实现各种有一定技术含量的功能。同时，GaussDB(DWS)支持比较完备的窗口函数及其实现，可以助各位读者实现理想中的应用！

想了解 GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的 PB 级数仓黑科技，后台还可获取众多学习资料哦~

​​点击关注，第一时间了解华为云新鲜技术~