2024sql开窗函数详解

详解SQL操作的窗口函数 　　摘要：窗口函数是聚集函数的延伸，是更高级的SQL语言操作，主要用于AP场景下对数据进行一些分析、汇总、排序的功能。 　　本文分享自华为云社区《GaussDB(DWS) SQL进阶之SQL操作之窗口函数》，作者： 两杯咖啡。 　　窗口函数是聚集函数的延伸，是更高级的SQL语言操作，主要用于AP场景下对数据进行一些分析、汇总、排序的功能。本文将从窗口函数的基本概念入手，介绍其常用SQL语法和主要的应用场景，同时会讲到在GaussDB(DWS)中使用窗口函数需要考虑的一些因素。 　　一. 窗口函数的作用 　　窗口函数(Window Function)，又被叫做分析函数(Analytics Function)，通常在需要对数据进行分组汇总计算时使用，因此与聚集函数有一定的相似性。但与聚集函数不同的是，聚集函数通过对数据进行分组，仅能够输出分组汇总结果，而原始数据则无法展现在结果中。而窗口函数则可以同时将原始数据和聚集分析结果同时显示出来。通过下例，大家可以体会一下区别。 　　给定表score(id, classid, score)，每行表示学生id，所在班级id以及考试成绩，数据如下图所示：

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

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

　　通过以上信息，我们可以很方便地进行进一步的查询，例如：查询每个班超过平均分的学生id，排名前5的学生id等。 　　可以看出，窗口函数其实是对查询，聚集等多个操作所做的一个组合操作，但相对于多个操作而言，使用窗口函数在完成功能的情况下，书写也更加简洁。同时，窗口函数还提供了更多的函数、更多的聚集方式以支持多样化的功能，而且支持分组中的排序功能。通过与聚集结果比较，可以方便地提取符合一定统计要求的记录信息。 　　二. 窗口函数的SQL语法介绍 　　窗口函数的功能这么强大，那支持的语法一定也非常复杂吧。别急，通过系统性地分析，你也可以快速掌握窗口函数语法的精髓！ 　　单个窗口函数表达式的主要语法为： 　　该表达式主要由以下部分组成（下图为图解）：

窗口函数表达式（紫色部分）：指定该窗口函数进行计算的聚集函数，可以是SUM(), COUNT(), AVG(), MIN(), MAX()等聚集函数，可以通过以下语句在GaussDB(DWS)中查到： 　　也可以是其它专有的窗口函数，可以通过如下语句在GaussDB(DWS)系统表中查到： 　　GaussDB(DWS)目前支持的专有窗口函数有15个：

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

　　可以看出，三个函数都是用于进行行排序的，且参数个数为0。通过①可以看出ROW_NUMBER()和RANK()的区别：前者顾名思义，对行从1开始进行编号，无论数据是否重复，结果不重；而后者对于相同的数据，给出的排序结果是相同的。通过②可以看出RANK()和DENSE_RANK()的区别：前者在重复值后，编号和ROW_NUMBER()是相同的，虽然相同的数据编号相同，但仍然占用多个编号位置；而后者对于重复值只占用一个编号，重复后紧接着进行编号。 　　在实际应用着，前两个函数应用较多，ROW_NUMBER()主要用于行编号，用于分页展示等应用中；而RANK()主要用于对结果进行排序后展示。窗口函数分区列（红色部分）：表示根据哪一列进行分组计算，类似于聚集语句中的GROUP BY子句。该部分可以没有，类似于聚集语句，表示对所有语句划分同一组处理。窗口函数排序列（绿色部分）：表示数组划分到同一组后，在进行窗口函数计算前排序的顺序，可以指定多列，语法与ORDER BY类似。当聚集函数计算结果与顺序无关时，此子句可以省略。窗口函数移动窗口选项（蓝色部分）：该选项也称为Window Frame Option，默认可以省略，表示对每个分组内所有行进行聚集计算（无排序列时）或对每个分组内起始行到当前行进行聚集计算（有排序列时）。但指定该选项后，仅针对指定的窗口内的组进行聚集计算。 　　对分组内所有行结果，当需要指定一个窗口时，我们需要指定开始的行和结束的行，则聚集函数将针对窗口之内的所有行的结果进行计算。因此，移动窗口选项的主要语法为： 　　第一种语法同时指定开始行和结束行，第二种语法仅指定开始行，结束行默认为当前行。 　　<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、窗口函数仅能出现在输出列中。如果需要使用窗口函数的值进行条件过滤，需要将窗口函数嵌套在子查询中，在外层使用窗口函数表达式的别名进行条件过滤。例如： 　　3、窗口函数所在查询块中支持使用GROUP BY表达式进行分组去重，但要求窗口函数中的PARTITION BY子句中必须是GROUP BY表达式的子集，以保证窗口函数在GROUP BY列去重后的结果上进行窗口运算，同时ORDER BY子句的表达式也需要是GROUP BY表达式的子集，或聚集运算的聚集函数。例如： 　　标准bench-mark TPC-DS中有多个语句使用到了窗口函数，以TPC-DS Q51为例： 　　上面查询中使用了四个窗口函数，以CTE web_v1的定义为例： 　　该语句块计算了在一定时间内，网上销售的货物的累计销售额。 　　该语句块在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的主语句可以写成： 　　在GaussDB(DWS)中使用window子句有以下需要注意的：window子句仅能在相同查询块语句中复用，跨查询块需要定义不同的window子句。window子句中仅能包含OVER之后的窗口子句。窗口子句可以包含：PARTITION BY子句、ORDER BY子句和移动窗口选项，而window不支持移动窗口选项。窗口函数在复用window子句时，可以在window子句的基础上增加ORDER BY子句和移动窗口子句，类似于继承。 　　例如下面的例子： 　　查询结果为：

　　四. GaussDB(DWS)使用窗口函数注意事项 　　与Teradata等主流数据库不同的是，GaussDB(DWS)中窗口函数的PARTITION BY后的表达式如果是数字，表示常量值，而不是输出列的序号，这点与GROUP BY的语法也有出入。例如： 　　此处会将所有数据分为一组。由于窗口函数一般需要对数据进行排序之后进行窗口计算，因此在GaussDB(DWS)中使用Sort + WindowAgg算子来实现其功能。如果有多个不同的窗口子句，则每个窗口子句对应一对Sort + WindowAgg算子，示例语句及计划如下： 　　

　　此查询中包含两类窗口子句，(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仅作前向兼容对外提供。）此种场景示例语句及计划如下图所示： 　　

　　第4号算子Broadcast，将各DN排序后的数据（第5号算子）广播到某一个DN上，该DN上进行ROW_NUMBER()的计算并进行过滤、输出结果。 　　(2) 当存在PARTITION BY子句时，会根据PARTITION BY的列进行重分布，将数据分布到各个DN上进行各自的分组计算。此时如果PARTITION BY的值较少，则数据会重分布到少数几个DN上，导致数据倾斜。因此，如果使用排序类的专有窗口函数，且仅关于排序靠前的若干行时，可以增加相应的过滤条件，GaussDB(DWS)会根据过滤条件进行双层WindowAgg计算，以TPC-DS的Q67为例： 　　该查询中包含如下窗口函数：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级数仓黑科技，后台还可众多学习资料哦~ 　　华为伙伴暨开发者大会2022火热来袭，重磅内容不容错过！ 　　【精彩活动】 　　勇往直前·做全能开发者→12场技术直播前瞻，8大技术宝典高能输出，还有代码密室、知识竞赛等多轮神秘任务等你来挑战。即刻闯关，开启终极大奖！踏上全能开发者晋级之路吧！ 　　【技术专题】 　　未来已来，2022技术探秘→华为各领域的前沿技术、重磅开源项目、创新的应用实践，站在智能世界的入口，探索未来如何照进现实，干货满满了解 　　，第一时间了解华为云新鲜技术~