巴彦淖尔市网站制作,聚名网认证,企业网站系统排名,怎么上百度搜索本节书摘来自异步社区出版社《T-SQL性能调优秘笈——基于SQL Server 2012 窗口函数》一书中的第1章#xff0c;第1.2节#xff0c;作者#xff1a; 【美】Itzik Ben-Gan#xff0c;更多章节内容可以访问云栖社区“异步社区”公众号查看。 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首先用下面的代码来生成样本数据 在表T1中可以看到col1的序列号并不连续。我们的任务是找到现有值的连续区间也称为数据岛返回每个数据岛的开始值和结束值我们希望得到的结果如下 如果我们好奇这类问题在现实中的使用场景这里有很多实际例子。示例包括生成可用性报告识别某种活动的持续时间如销售活动找到满足一定要求的持续区间如股票高于或低于一定阈值的时间区间识别车牌的使用范围等。当前示例的目的非常简单我们可以专注在实现它的技术上。只须稍作调整应用在简单案例上的技术就可以应用在更加复杂的案例上所以请把它看成是一个基于集合的解决方案带来的挑战。首先请找出一个能解决问题的方案然后对表填充大量的数据行假设10 000 000行然后再试试刚才的解决方案看看它将如何进行。完成这些我们再来看我的解决方案。 在展示使用窗口函数的方案之前我先展示其中一个用传统语言结构实现的解决方案。特别地我会展示使用子查询的解决方案。为了解释第一种解决方案的策略先查看T1.col1序列的值我增加了一个目前尚不存在的概念化的列把它当做组标识符 grp列尚未存在从概念上来说它的值唯一地标识一个数据岛。这就意味着对同一个岛内的所有成员它们的grp值相同并与其他岛内的成员的grp值不同。如果我们能设法对这样的组标识符进行计算我们就能把结果根据grp特性值进行分组返回每组岛中最大和最小的col1值。传统语言结构中生成这个组标识符的方法是针对col1的每个当前值找到大于或等于当前值的最小的col1值并且要求这个值后面没有值。 举例说明按照上面的逻辑试着找到相对于值2col1的值大于或等于2的最小值且要求其后面的值没有连续答案是3。现在针对3做同样的查找结果还是3所以3是组标识符数据岛开始于2结束于3。对于开始于11结束于13的数据岛组成员的标识符都是13。从这里我们可以看到一个数据岛内的所有成员的组标识符实际上就是数据岛最后一个成员的值。 下面是实现这个概念的T-SQL代码。 代码执行后的输出结果如下 下面的部分就相当直观了——根据上面的查询定义表表达式在外部查询中根据组标识符进行分组返回每组的最大和最小的col值如下。 在这个解决方案里有两个主要问题。一、这里遵循的逻辑有点复杂。二、运行非常缓慢。我暂时不想开始讨论查询执行计划——后面的篇幅有很多这方面的讨论——在这儿可以告诉大家针对表中的每一行SQL Server都几乎执行了两次完整的数据扫描。现在可以想象对于10 000 000条记录组成的序列尝试转化它对应的工作量会有多少了。需要处理的行的数量只有一个词形容——巨大。 另一个解决方案也是计算组标识符不同之处是使用窗口函数进行。解决方案的第一步是使用ROW_NUMBER函数基于col1排序计算行号。本书后面会提供ROW_NUMBER的细节。目前只要知道它在分区中按照给定的顺序从1开始逐一递增产生唯一的整数就足够了。 牢记上面的知识下面的查询按照col1排序返回的col1的值和行号。 现在我把注意力集中到两个序列上。一个col1是不连贯的另一个rownum是连贯的。记住这一点然后尝试发现在一个数据岛内二者有什么独特的关系。在数据岛内两个序列都以固定间隔在增长因此二者的差异是一个常数。在另一个岛内col1的增量超过1而rownum的增量还是1所以差异在变大。换句话说二者的差异是常数不同数据岛内的数值不同。运行下面的查询来计算其差异。 我们可以看到这个差异满足我们对组标识符的两个需求因此可以把它当做组标识符使用。其他内容都与之前的解决方案相同即是说把行按组标识符进行分组返回每组中最小和最大的col1值如下所示。 请观察一下这样的解决方案多么清晰和简单。同时在代码上加上注释帮助那些第一次读代码的人更好地了解解决方案也是个不错的主意。 这个解决方案还很高效。与前一解决方案相比其所涉及的行的处理数量微不足道。它仅仅包含一个在col1上的排序索引扫描和一个持续递增计数器的迭代器。我测试过这条查询的性能它在10 000 000条记录组成的序列上仅运行了10秒钟。前一解决方案的运行时间就要长得多。 希望关于使用窗口函数的解决方案的简介能足以吸引你让你看到它们包含的强大功能。现在我们要返回继续学习窗口函数技术了在书的后面部分我们还有机会看到更多的示例。 本文仅用于学习和交流目的不代表异步社区观点。非商业转载请注明作译者、出处并保留本文的原始链接。
