开发者社区> 异步社区> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

《T-SQL性能调优秘笈——基于SQL Server 2012 窗口函数》——1.2 使用窗口函数的解决方案简介

简介:
+关注继续查看

本节书摘来自异步社区出版社《T-SQL性能调优秘笈——基于SQL Server 2012 窗口函数》一书中的第1章,第1.2节,作者: 【美】Itzik Ben-Gan,更多章节内容可以访问云栖社区“异步社区”公众号查看。

1.2 使用窗口函数的解决方案简介

T-SQL性能调优秘笈——基于SQL Server 2012 窗口函数
本书前4章描述了窗口函数及其优化,所选素材偏重技术说明,虽然我自己觉得很吸引人,但可以想见,有些人会觉得有点沉闷。通常来说,人们在阅读用窗口函数解决现实问题的内容时,会觉得比较有趣,本书将在最后一章满足大家。只有当我们看到能如何用窗口函数解决难题时,才会真正认识到它们的价值。所以,我在思考如何说服你在读到有趣的章节之前,能坚持读完这些枯燥的技术说明而不中途放弃。也许我可以展示一个窗口函数解决方案示例。

这里演示的查询包含:对一个在列中包含序列号的表进行查询,在现有值中找到连贯的区间。这个难题也就是所谓的数据岛问题。序列号可以是数值类型的、时间类型的(比较常见)或支持全排序的任意数据类型。序列号可以是唯一值,也可以是重复值。间隔可以是任何符合列类型(如,整型数1、整型数7、时间间隔为1天、时间间隔为2周等)的固定间隔。第5章会讲述间隔的各种变化。这里只举个简单的案例,让大家感受一下窗口函数是如何工作的——所以用数值序列,间隔为1,首先用下面的代码来生成样本数据:

image

在表T1中,可以看到col1的序列号并不连续。我们的任务是找到现有值的连续区间(也称为数据岛,)返回每个数据岛的开始值和结束值,我们希望得到的结果如下:

image

如果我们好奇这类问题在现实中的使用场景,这里有很多实际例子。示例包括生成可用性报告,识别某种活动的持续时间(如,销售活动),找到满足一定要求的持续区间(如,股票高于或低于一定阈值的时间区间),识别车牌的使用范围等。当前示例的目的非常简单,我们可以专注在实现它的技术上。只须稍作调整,应用在简单案例上的技术就可以应用在更加复杂的案例上,所以请把它看成是一个基于集合的解决方案带来的挑战。首先请找出一个能解决问题的方案,然后对表填充大量的数据行(假设10 000 000行)然后再试试刚才的解决方案,看看它将如何进行。完成这些,我们再来看我的解决方案。

在展示使用窗口函数的方案之前,我先展示其中一个用传统语言结构实现的解决方案。特别地,我会展示使用子查询的解决方案。为了解释第一种解决方案的策略,先查看T1.col1序列的值,我增加了一个目前尚不存在的概念化的列,把它当做组标识符:

image

grp列尚未存在,从概念上来说,它的值唯一地标识一个数据岛。这就意味着,对同一个岛内的所有成员,它们的grp值相同,并与其他岛内的成员的grp值不同。如果我们能设法对这样的组标识符进行计算,我们就能把结果根据grp特性值进行分组,返回每组(岛)中最大和最小的col1值。传统语言结构中生成这个组标识符的方法是:针对col1的每个当前值,找到大于或等于当前值的最小的col1值,并且要求这个值后面没有值。

举例说明,按照上面的逻辑,试着找到相对于值2,col1的值大于或等于2的最小值,且要求其后面的值没有连续,答案是3。现在,针对3,做同样的查找,结果还是3,所以3是组标识符,数据岛开始于2,结束于3。对于开始于11,结束于13的数据岛,组成员的标识符都是13。从这里我们可以看到,一个数据岛内的所有成员的组标识符,实际上就是数据岛最后一个成员的值。

下面是实现这个概念的T-SQL代码。

image

代码执行后的输出结果如下:

image

下面的部分就相当直观了——根据上面的查询定义表表达式,在外部查询中,根据组标识符进行分组,返回每组的最大和最小的col值,如下。

image

在这个解决方案里,有两个主要问题。一、这里遵循的逻辑有点复杂。二、运行非常缓慢。我暂时不想开始讨论查询执行计划——后面的篇幅有很多这方面的讨论——在这儿可以告诉大家,针对表中的每一行,SQL Server都几乎执行了两次完整的数据扫描。现在可以想象对于10 000 000条记录组成的序列,尝试转化它对应的工作量会有多少了。需要处理的行的数量只有一个词形容——巨大。

另一个解决方案也是计算组标识符,不同之处是,使用窗口函数进行。解决方案的第一步是使用ROW_NUMBER函数基于col1排序计算行号。本书后面会提供ROW_NUMBER的细节。目前,只要知道它在分区中,按照给定的顺序,从1开始,逐一递增,产生唯一的整数就足够了。

牢记上面的知识,下面的查询按照col1排序,返回的col1的值和行号。

image

现在,我把注意力集中到两个序列上。一个(col1)是不连贯的,另一个(rownum)是连贯的。记住这一点,然后尝试发现在一个数据岛内,二者有什么独特的关系。在数据岛内,两个序列都以固定间隔在增长,因此,二者的差异是一个常数。在另一个岛内,col1的增量超过1,而rownum的增量还是1,所以差异在变大。换句话说,二者的差异是常数,不同数据岛内的数值不同。运行下面的查询来计算其差异。

image

我们可以看到,这个差异满足我们对组标识符的两个需求,因此,可以把它当做组标识符使用。其他内容都与之前的解决方案相同,即是说,把行按组标识符进行分组,返回每组中最小和最大的col1值,如下所示。

image

请观察一下,这样的解决方案多么清晰和简单。同时,在代码上加上注释,帮助那些第一次读代码的人更好地了解解决方案,也是个不错的主意。

这个解决方案还很高效。与前一解决方案相比,其所涉及的行的处理数量微不足道。它仅仅包含一个在col1上的排序索引扫描和一个持续递增计数器的迭代器。我测试过这条查询的性能,它在10 000 000条记录组成的序列上仅运行了10秒钟。前一解决方案的运行时间就要长得多。

希望关于使用窗口函数的解决方案的简介,能足以吸引你,让你看到它们包含的强大功能。现在,我们要返回继续学习窗口函数技术了,在书的后面部分,我们还有机会看到更多的示例。

本文仅用于学习和交流目的,不代表异步社区观点。非商业转载请注明作译者、出处,并保留本文的原始链接。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server——T-SQL基础技术
SQL Server——T-SQL基础技术
0 0
【T-SQL基础】01.单表查询-几道sql查询题
【T-SQL基础】01.单表查询-几道sql查询题
0 0
SQL Server 2012 T-SQL 新特性
原文:SQL Server 2012 T-SQL 新特性 序列 Sequence SQL Server 现在将序列当成一个对象来实现,创建一个序列的例子语法如下: CREATE SEQUENCE DemoSequence START WITH 1 INCREMENT BY 1; 使用序列的方法如下所表达的: SELECT VALUE FOR DemoSequence 序列与以前的种子列(identity)的区别很明显,种子列只限于当前列,而序列是一个对象层面的实现,则可以在多个表之间共享。
858 0
+关注
异步社区
异步社区(www.epubit.com)是人民邮电出版社旗下IT专业图书旗舰社区,也是国内领先的IT专业图书社区,致力于优质学习内容的出版和分享,实现了纸书电子书的同步上架,于2015年8月上线运营。公众号【异步图书】,每日赠送异步新书。
文章
问答
文章排行榜
最热
最新
相关电子书
更多
用SQL做数据分析
立即下载
阿里云流计算 Flink SQL 核心功能解密
立即下载
Comparison of Spark SQL with Hive
立即下载