您好,登錄后才能下訂單哦!
窗口函數(shù)的目的是以聲明的方式將業(yè)務報告需求轉(zhuǎn)換為SQL,從而使查詢性能和開發(fā)人員/業(yè)務分析師的效率得到顯著提高。 我看到現(xiàn)實世界的報告和儀表板在使用窗口功能后從幾小時到幾分鐘,幾分鐘到幾秒鐘。 查詢大小從40頁減少到幾頁。 早在上世紀90年代,Redbrick數(shù)據(jù)庫就真正理解了業(yè)務用例并創(chuàng)建了一個新的功能層來進行業(yè)務報告,包括排名,運行總計,根據(jù)子組,位置等計算傭金和庫存。這些都是在SQL標準中每個BI層(如Tableau,Looker,Cognos)都利用此功能。
想象一下,通過兩輪比賽你有六個高爾夫球手。 現(xiàn)在,您需要創(chuàng)建排行榜并對其進行排名。 使用SQL對它們進行排名
播放機 | 第1輪 | Round2 |
馬爾科 | 75 | 73 |
約翰 | 72 | 68 |
常 | 67 | 76 |
宵 | 74 | 71 |
Sitaram | 68 | 72 |
冰潔 | 71 | 67 |
將數(shù)據(jù)插入Couchbase。
INSERT INTO高爾夫
VALUES(“KP1”,{ “player”:“Marco”,“round1”:75,“round2”:73}),
VALUES(“KP2”,{ “player”:“Johan”,“round1”:72,“round2”:68}),
VALUES(“KP3”,{ “player”:“Chang”,“round1”:67,“round2”:76}),
VALUES(“KP4”,{ “player”:“Isha”,“round1”:74,“round2”:71}),
VALUES(“KP5”,{ “player”:“Sitaram”,“round1”:68,“round2”:72}),
VALUES(“KP6”,{ “玩家”:“冰潔”,“ROUND1”:71,“round2”:67});
沒有窗口功能(當前狀態(tài) - Couchbase 6.0)
要在不使用窗口函數(shù)的情況下編寫查詢,您需要一個子查詢來計算每個玩家的等級。 該子查詢必須掃描所有數(shù)據(jù),導致 O(N ^ 2) 的最差算法復雜度 , 這大大增加了執(zhí)行時間和吞吐量。
用g1 作為(選擇球員,第1輪,第2輪從高爾夫球場)
SELECT g3 .player AS player,
(g3 .round 1 + g3 .round 2) AS T,
((g3 .round 1 + g3 .round 2) - 144) AS ToPar,
(選擇原始1 + COUNT(*)
從 g1 作為 g2
其中(g2 .round 1 + g2 .round 2)<
(g3 .round 1 + g3 .round 2))[ 0 ] AS sqlrankR2
從 g1 到 g3
ORDER BY sqlrankR2
結(jié)果:
T ToPar播放器sqlrankR2
138 - 6 “冰潔” 1
140 - 4 “約翰” 2
140 - 4 “Sitaram” 2
143 - 1 “Chang” 4
145 1 “Isha” 5
148 4 “Marco” 6
使用Mad-Hatter中的窗口函數(shù)(即將發(fā)布)
此查詢返回玩家,兩輪后的總數(shù)(T),分數(shù)如何超過/低于標準(ToPar),然后 根據(jù)前兩輪的分數(shù)對它們 進行 排名 。 這是Mad-Hatter的新功能。 其時間復雜度為O(N),意味著執(zhí)行時間只會線性增加。
SELECT 播放器 AS播放器,
(round1 + round2) AS T,
((round1 + round2) - 144) AS ToPar,
RANK()OVER(ORDER BY(round1 + round2))AS rankR2
來自高爾夫;
T ToPar玩家等級R2
138 - 6 “冰潔” 1
140 - 4 “約翰” 2
140 - 4 “Sitaram” 2
143 - 1 “Chang” 4
145 1 “Isha” 5
148 4 “Marco” 6
觀察:
查詢簡單明了地表達了要求。
在真實場景中執(zhí)行此查詢的效果要好得多。 我們計劃衡量。
當排名要求依賴于多個文檔時,查詢變得非常復雜 - 編寫,優(yōu)化和運行。
所有這些都會影響總體TCO。
現(xiàn)在,讓我們創(chuàng)建一個擴展的儀表板。
顯示添加密集排名,行號,領先者以及領導者背后的筆畫數(shù)。 報告中的所有非常常見的事情。 只要看到OVER()子句,就會看到新的窗口函數(shù)。 下面的查詢有六個窗口函數(shù)。
SELECT 播放器 AS播放器,
(round1 + round2) AS T,
((round1 + round2) - 144) AS ToPar,
RANK()OVER(ORDER BY(round1 + round2))AS rankR2,
DENSE_RANK()OVER(ORDER BY(round1 + round2))AS rankR2Dense,
ROW_NUMBER()OVER()rownum,
((round1 + round2) -
FIRST_VALUE(ROUND1 + round2)
OVER(ORDER BY(round1 + round2)))AS strokebehind,
RANK()OVER(ORDER BY(round1)) AS rankR1,
LAG(播放器,1,“無”)OVER(ORDER BY ROUND1 + round2)
AS inFront
從高爾夫球場
ORDER BY rankR2
T ToPar inFront player rankR1 rankR2 rankR2Dense rownum stroke behind behind
138 - 6 “無” “冰潔” 3 1 1 3 0
140 - 4 “Johan” “Sitaram” 2 2 2 2 2
140 - 4 “冰潔” “約翰” 4 2 2 4 2
143 - 1 “Sitaram” “Chang” 1 4 3 1 5
145 1 “Chang” “Isha” 5 5 4 5 7
148 4 “Isha” “Marco” 6 6 5 6 10
正如您之前看到的, 使用 子查詢方法 使用六個窗口函數(shù) 執(zhí)行此查詢 將是一個更大的努力,昂貴,容易出錯的查詢。
除了將內(nèi)置聚合(COUNT,SUM,AVG等)作為窗口函數(shù),即將發(fā)布的版本將具有以下窗口函數(shù)。 它們中的每一個的語法和語義在標準中得到很好的定義,并在下面的參考部分的文章中進行了充分描述。
RANK()
DENSE_RANK()
PERCENT_RANK()
CUME_DIST()
NTILE()
RATIO_TO_REPORT()
ROW_NUMBER()
LAG()
FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()
LEAD()
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權內(nèi)容。