前言
能翻到這篇博文的，想必大家對視窗函式的基本使用已經有了一定的了解，這里就不廢話再去多說了，
這篇博文主要講的是，如果在視窗函式中加入where條件，
為了方便理解，以下案例使用排名函式：row_number()來講解，其他視窗函式同理，大家舉一反三，
本文你可以學習到：
- 使用UNION ALL分之而治解決本問題
- 使用視窗函式中的一個小技巧解決本問題 （簡單高效）
案例
我們如果要對一組資料根據某個列去排名，一般會使用row_number()，但是如果我們要對某個列符合條件的才去row_number()進行排名，
舉個例子，現有一組資料：
spark-sql (default)> select score from math_test;
30
50
101
300
456
768
999
130
350
1130
1350
1131
1150
我們想對score列大于300的資料進行排名（從1開始），小于等于300資料則填充默認值null，
解決方案
-
UNION ALL分之而治
該方案的思路很簡單，就是把符合條件的提取出來，通過row_number()進行排名，然后再將不符合條件的填充默認值null，最后將兩個結果union all起來，
為了方便看效果，在處理完對score進行了一次排序（這一步可以去掉，只是演示使用），
SELECT score,rk FROM ( SELECT score,ROW_NUMBER() OVER(ORDER BY score) AS rk FROM MATH_TEST WHERE score > 300 UNION SELECT score,NULL AS rk FROM MATH_TEST WHERE score <= 300 )T ORDER BY score -- 結果如下： score rk 30 NULL 50 NULL 101 NULL 130 NULL 300 NULL 350 1 456 2 768 3 999 4 1130 5 1131 6 1150 7 1350 8UNION ALL的方式很簡單，也很好理解，但是寫起來還是太長了，而且有點啰嗦也不高效，需要掃描2次表，
-
視窗函式小技巧
嘿嘿，所以有需要的同學可以看看這種方式，利用視窗函式的一個小技巧，
這里先放入完整SQL
SELECT score ,CASE WHEN score > 300 THEN ROW_NUMBER() OVER(PARTITION BY (CASE WHEN score > 300 THEN 1 ELSE 2 END) ORDER BY score) ELSE NULL END AS rk FROM math_test -- 結果如下： score rk 350 1 456 2 768 3 999 4 1130 5 1131 6 1150 7 1350 8 30 NULL 50 NULL 101 NULL 130 NULL 300 NULL這種方式我們拆開來的解釋一下：
CASE WHEN score > 300 THEN <排名處理> ELSE NULL END AS rk在這一層我們判斷了 score > 300 我們需要做排名處理，反之填充null
在<排名處理>：
ROW_NUMBER() OVER(PARTITION BY (CASE WHEN score > 300 THEN 1 ELSE 2 END) ORDER BY score)我們利用PARTITION BY 傳入
CASE WHEN score > 300 THEN 1 ELSE 2 END,將score > 300設定為組1，反而設定為組2，這樣我們就在能夠“看到”的是score > 300的資料的條件下，只對score > 300的資料使用row_number()排序了，
-
如果沒有在parition by去分組會怎樣？
首先，結果是這樣的：
SELECT score ,CASE WHEN score > 300 THEN ROW_NUMBER() OVER( ORDER BY score) ELSE NULL END AS rk FROM math_test -- 結果如下： score rk 30 NULL 50 NULL 101 NULL 130 NULL 300 NULL 350 6 456 7 768 8 999 9 1130 10 1131 11 1150 12 1350 13我們看到，雖然沒有把 <= 300的資料填充為默認null，但是，由于視窗函式是能夠看到“全部”的資料，所以在over(order by score)后，排名結果并不是我們想到的，
總結
本文通過一個排名案例說明并解釋了如何在視窗函式中添加where條件達到我們預期的效果，大家可以舉一反三，
兩種方法都可以，但是我還是熱衷于第二種方法，簡單高效，
這么寫SQL真是的太妙妙妙~~~~
– by 倆只猴
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/254462.html
標籤：其他