上一篇介紹過數(shù)據(jù)差距與數(shù)據(jù)島的背景，這里不再贅述，請翻閱上一文。此篇在Sqlserver上給大家演示1000萬條記錄的計算性能。

測試電腦軟硬件說明

一般般的筆記本電腦，2017年7月，價格：4500+。

電腦配置

數(shù)據(jù)構(gòu)造

1000萬行數(shù)據(jù)，由10萬個用戶+每用戶100條記錄組成，同樣使用書中所提及的構(gòu)造序列的表值函數(shù)輕松構(gòu)造完成。

同樣使用窗口函數(shù)完成的表值函數(shù)

生成1000萬條數(shù)據(jù)記錄

數(shù)據(jù)源結(jié)構(gòu)

使用循環(huán)和隨機函數(shù)，實現(xiàn)刪除10萬條數(shù)據(jù)，因測試時先建了索引再刪除數(shù)據(jù)，慢得一塌糊涂，最終中途中止了，沒有實際刪除這么多數(shù)據(jù)。

隨機刪除10萬條記錄，用于實現(xiàn)數(shù)據(jù)差距和數(shù)據(jù)島效果

關(guān)系型數(shù)據(jù)庫，性能優(yōu)化的核心是適當(dāng)?shù)乃饕?#xff0c;此次肯定要加上索引才能客觀地表現(xiàn)出該有的合理效果。

增加聚集索引

算法代碼演示

數(shù)據(jù)差距范圍

此部分計算的邏輯是將每個用戶分組下本來連續(xù)的序號中，缺失了某些記錄，這些缺失的部分對應(yīng)的區(qū)間范圍，若缺失的為連續(xù)的，返回連續(xù)的區(qū)間，若缺失為單個記錄，返回首尾相同的序號。

數(shù)據(jù)差距的SQL代碼及結(jié)果

原理：關(guān)鍵思路是使用LEAD函數(shù)，使用“用戶”列作分區(qū)，按序號的升序排列，取當(dāng)前用戶組的當(dāng)前行序號為cur列,其下一行內(nèi)容作為nxt列，最終構(gòu)造結(jié)構(gòu)表是，將當(dāng)前行的cur列值+1構(gòu)造出差距的首范圍，當(dāng)前行的nxt列值-1作為結(jié)束范圍。

而最終的結(jié)果只會取nxt和cur之差大于1的記錄，即開始有缺失產(chǎn)生差距的行記錄。

總記錄1000萬條，10萬個用戶，分組計算后，返回數(shù)據(jù)產(chǎn)距90899條記錄，用時27秒

分解下步驟，將CTE虛擬表C給大家看下效果，可以看到97和100之間是缺失了98、99兩值，最終在97序號上，cur為97、nxt為100，此行記錄是我們后面where條件要篩選出來的記錄行(模擬刪除數(shù)據(jù)過程中，盡量刪除連續(xù)的兩條記錄，讓差距結(jié)果更清晰)。

將cur+1,nxt-1后，就拿到98-99這樣的差距區(qū)間。

分步驟演示

數(shù)據(jù)島范圍

這個就是一般來說連續(xù)記錄的區(qū)間，如現(xiàn)實場景中的用戶連續(xù)打卡天區(qū)間。10萬個用戶測試，100天打卡天數(shù)，足夠滿足一般互聯(lián)網(wǎng)中等規(guī)模的活動場景使用。

數(shù)據(jù)島范圍的SQL代碼及結(jié)果

原理：使用排名窗口函數(shù)，對用戶進行分組計算。若有數(shù)據(jù)缺失時，排名的序號和正常序號之差會有跳躍性的差距(正常無缺失時序號和排名是相同，且差異為0)。

下一步對排名產(chǎn)生的相同的數(shù)值進行分組匯總，連同用戶字段，最終可統(tǒng)計出某用戶在某個差異值grp下的連續(xù)區(qū)間。

分解步驟后可知，在用戶1中，58后缺失了59、60兩個值，最終在61的排名與序號差grp為2，直到下一次有缺失時，此2的值再更新為下一個缺失的值區(qū)間長度2+2=4。

分步驟演示

下一個缺失值為98和99兩值

結(jié)語

Sqlserver的窗口函數(shù)，非常多的應(yīng)用場景，對傳統(tǒng)的SQL的查詢進行了極大的簡化，在PowerBI的DAX查詢語言中，暫時還缺少其在集合的基礎(chǔ)上進行窗口的處理，致使同樣都是對數(shù)據(jù)集合進行運算，但因為缺失窗口函數(shù)特性支持，性能上仍然和SQL中的窗口函數(shù)處理有非常大的差距。

想必有人好奇地問，這些內(nèi)容在其他數(shù)據(jù)庫中是否同樣可以？窗口函數(shù)在其他關(guān)系型數(shù)據(jù)庫中是否也一樣支持？

據(jù)筆者知識范圍所知，MySQL和Sqlite是沒有的，Oracle是有的，并且比Sqlserver更強大(為何不推薦，因為筆者是微軟系的信徒唄，Oracle人家是法務(wù)團隊強大，你懂得)。

總結(jié)

以上是生活随笔為你收集整理的mysql缺少函数_Sqlserver的窗口函数的精彩应用之数据差距与数据岛-答案篇的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。