您好,登錄后才能下訂單哦!
小編給大家分享一下SQL中NTILE函數怎么用,相信大部分人都還不怎么了解,因此分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后大有收獲,下面讓我們一起去了解一下吧!
ntile函數可以對序號進行分組處理,將有序分區中的行分發到指定數目的組中。 各個組有編號,編號從一開始。 對于每一個行,ntile 將返回此行所屬的組的編號。這就相當于將查詢出來的記錄集放到指定長度的數組中,每一個數組元素存放一定數量的記錄。ntile函數為每條記錄生成的序號就是這條記錄所有的數組元素的索引(從1開始)。也可以將每一個分配記錄的數組元素稱為“桶”。ntile函數有一個參數,用來指定桶數。下面的SQL語句使用ntile函數對Order表進行了裝桶處理:
select NTILE(4) OVER(order by [SubTime] desc) as ntile,* from [Order]
查詢結果如下圖所示:
Order表的總記錄數是6條,而上面的Sql語句ntile函數指定的組數是4,那么Sql Server2005是怎么來決定每一組應該分多少條記錄呢?這里我們就需要了解ntile函數的分組依據(約定)。
ntile函數的分組依據(約定):
1、每組的記錄數不能大于它上一組的記錄數,即編號小的桶放的記錄數不能小于編號大的桶。也就是說,第1組中的記錄數只能大于等于第2組及以后各組中的記錄數。
2、所有組中的記錄數要么都相同,要么從某一個記錄較少的組(命名為X)開始后面所有組的記錄數都與該組(X組)的記錄數相同。也就是說,如果有個組,前三組的記錄數都是9,而第四組的記錄數是8,那么第五組和第六組的記錄數也必須是8。
這里對約定2進行詳細說明一下,以便于更好的理解。
首先系統會去檢查能不能對所有滿足條件的記錄進行平均分組,若能則直接平均分配就完成分組了;若不能,則會先分出一個組,這個組分多少條記錄呢?就是 (總記錄數/總組數)+1 條,之所以分配 (總記錄數/總組數)+1 條是因為當不能進行平均分組時,總記錄數%總組數肯定是有余的,又因為分組約定1,所以先分出去的組需要+1條。
分完之后系統會繼續去比較余下的記錄數和未分配的組數能不能進行平均分配,若能,則平均分配余下的記錄;若不能,則再分出去一組,這個組的記錄數也是(總記錄數/總組數)+1條。
然后系統繼續去比較余下的記錄數和未分配的組數能不能進行平均分配,若能,則平均分配余下的記錄;若還是不能,則再分配出去一組,繼續比較余下的......這樣一直進行下去,直至分組完成。
舉個例子,將51條記錄分配成5組,51%5==1不能平均分配,則先分出去一組(51/5)+1=11條記錄,然后比較余下的 51-11=40 條記錄能否平均分配給未分配的4組,能平均分配,則剩下的4組,每組各40/4=10 條記錄,分配完成,分配結果為:11,10,10,10,10,曉菜鳥我開始就錯誤的以為他會分配成 11,11,11,11,7。
根據上面的兩個約定,可以得出如下的算法:
//mod表示取余,p表示取整.if(記錄總數 mod 桶數==0) { recordCount=記錄總數 p 桶數; //將每桶的記錄數都設為recordCount.}else{ recordCount1=記錄總數 p 桶數+1; int n=1;//n表示桶中記錄數為recordCount1的最大桶數. m=recordCount1*n; while(((記錄總數-m) mod (桶數- n)) !=0) { n++; m=recordCount1*n; } recordCount2=(記錄總數-m) p (桶數-n); //將前n個桶的記錄數設為recordCount1. //將n+1個至后面所有桶的記錄數設為recordCount2.}
根據上面的算法,如果總記錄數為59,總組數為5,則 n=4 , recordCount1=12 , recordCount2=11,分組結果為 :12,12,12,12,11。
如果總記錄數為53,總組數為5,則 n=3 , recordCount1=11 , recordCount2=10,分組結果為:11,11,11,10,10。
就拿上面的例子來說,總記錄數為6,總組數為4,通過算法得到 n=2 , recordCount1=2 , recordCount2=1,分組結果為:2,2,1,1。
select ntile,COUNT([ID]) recordCount from ( select NTILE(4) OVER(order by [SubTime] desc) as ntile,* from [Order]) as tgroup by t.ntile
運行Sql,分組結果如圖:
比對算法與Sql Server的分組結果是一致的,說明算法沒錯。
以上是“SQL中NTILE函數怎么用”這篇文章的所有內容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內容對大家有所幫助,如果還想學習更多知識,歡迎關注億速云行業資訊頻道!
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。