PowerBI技巧之「数据分析」Sqlserver中的窗口函数的精彩应用之数据差距与数据岛（含答案）....pdfVIP

上一篇介绍过数据差距与数据岛的背景，这里不再赘述，请翻阅上一文。此篇在Sqlserver上给大 演示1000万条记录的计算性能。 测测试试电电脑脑软软硬硬件件说说明明 一般般的笔记本电脑，2017年7月，价格：4500+。 电脑配置 数数据据构构造造 1000万行数据，由10万个用户+每用户100条记录组成，同样使用书中所提及的构造序列的表值函数轻松构造完成。 同样使用窗口函数完成的表值函数 生成1000万条数据记录 数据源结构 使用循环和随机函数，实现删除10万条数据，因测试时先建了索引再删除数据，慢得一塌糊涂，最终中途中止了，没有实际 删除这么多数据。 随机删除10万条记录，用于实现数据差距和数据岛效果 关系型数据库，性能优化的核心是适当的索引，此次肯定要加上索引才能客观地表现出该有的合理效果。 增加聚集索引 算算法法代代码码演演示示 数数据据差差距距范范围围 此部分计算的逻辑是将每个用户分组下本来连续的序号中，缺失了某些记录，这些缺失的部分对应的区间范围，若缺失的为连 续的，返回连续的区间，若缺失为单个记录，返回首尾相同的序号。 数据差距的SQL代码及结果 原理：关键思路是使用LEA D函数，使用“用户”列作分区，按序号的升序排列，取当前用户组的当前行序号为cur列,其下一行内 容作为nxt列，最终构造结构表是，将当前行的cur列值+1构造出差距的首范围，当前行的nxt列值-1作为结束范围。 而最终的结果只会取nxt和cur之差大于1的记录，即开始有缺失产生差距的行记录。 总总记记录录1000万万条条，，10万万个个用用户户，，分分组组计计算算后后，，返返回回数数据据产产距距90899条条记记录录，，用用时时27秒秒 分解下步骤，将CTE虚拟表C给大 看下效果，可以看到 7和100之间是缺失了 8、 两值，最终在 7序号上，cur为 7、nxt 为100，此行记录是我们后面where条件要筛选出来的记录行(模拟删除数据过程中，尽量删除连续的两条记录，让差距结果更 清晰)。 将cur+1,nxt-1后，就拿到 8- 这样的差距区间。 分步骤演示 数数据据岛岛范范围围 这个就是一般来说连续记录的区间，如现实场景中的用户连续打卡天区间。10万个用户测试，100天打卡天数，足够满足一般 互联网中等规模的活动场景使用。 数据岛范围的SQL代码及结果 原理：使用排名窗口函数，对用户进行分组计算。若有数据缺失时，排名的序号和正常序号之差会有跳跃性的差距 （正常无缺 失时序号和排名是相同，且差异为0 ）。 下一步对排名产生的相同的数值进行分组汇总，连同用户字段，最终可统计出某用户在某个差异值grp下的连续区间。 分解步骤后可知，在用户1中，58后缺失了5 、60两个值，最终在61的排名与序号差grp为2 ，直到下一次有缺失时，此2 的值 再更新为下一个缺失的值区间长度2+2=4 。 分步骤演示 下一个缺失值为 8和 两值 结结语语 Sqlserver的窗口函数，非常多的应用场景，对传统的SQL的查询进行了极大的简化，在PowerBI的DAX查询语言中，暂时还缺 少其在集合的基础上进行窗口的处理，致使同样都是对数据集合进行运算，但因为缺失窗口函数特性支持，性能上仍然和SQL 中的窗口函数处理有非常大的差距。 现实场景更靠谱的是日期维度的数据序列，日期和序列原理一样，只需使用DateAdd函数处理下即可，书中也有相应的例子， 有兴趣可自行翻阅。 想必有人好奇地问，这些内容在其他数据库中是否同样可以？窗口函数在其他关系型数据库中是否也一样支持？ 据笔者知识范围所知，MySQL和Sqlite是没有的，Oracle是有的，并且比Sqlserver更强大 （为何不推荐，因为笔者是