前言

能翻到這篇博文的，想必大家對窗口函數(shù)的基本使用已經(jīng)有了一定的了解，這里就不廢話再去多說了。

這篇博文主要講的是，如果在窗口函數(shù)中加入where條件。

為了方便理解，以下案例使用排名函數(shù)：row_number()來講解。其他窗口函數(shù)同理，大家舉一反三。

本文你可以學(xué)習(xí)到：

1. 使用UNION ALL分之而治解決本問題
2. 使用窗口函數(shù)中的一個(gè)小技巧解決本問題 （簡單高效）

案例

我們?nèi)绻獙σ唤M數(shù)據(jù)根據(jù)某個(gè)列去排名，一般會(huì)使用row_number()，但是如果我們要對某個(gè)列符合條件的才去row_number()進(jìn)行排名，

舉個(gè)例子，現(xiàn)有一組數(shù)據(jù)：

spark-sql (default)> select score from math_test;
30
50
101
300
456
768
999
130
350
1130
1350
1131
1150

我們想對score列大于300的數(shù)據(jù)進(jìn)行排名（從1開始），小于等于300數(shù)據(jù)則填充默認(rèn)值null。

解決方案

• UNION ALL分之而治

  該方案的思路很簡單，就是把符合條件的提取出來，通過row_number()進(jìn)行排名，然后再將不符合條件的填充默認(rèn)值null，最后將兩個(gè)結(jié)果union all起來。

  為了方便看效果，在處理完對score進(jìn)行了一次排序（這一步可以去掉，只是演示使用）。

  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
  
  -- 結(jié)果如下：
  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  8
  

  UNION ALL的方式很簡單，也很好理解，但是寫起來還是太長了，而且有點(diǎn)啰嗦也不高效，需要掃描2次表。

• 窗口函數(shù)小技巧

  嘿嘿，所以有需要的同學(xué)可以看看這種方式，利用窗口函數(shù)的一個(gè)小技巧。

  這里先放入完整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
  
  -- 結(jié)果如下：
  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設(shè)置為組1，反而設(shè)置為組2。

  這樣我們就在能夠“看到”的是score > 300的數(shù)據(jù)的條件下，只對score > 300的數(shù)據(jù)使用row_number()排序了。

• 如果沒有在parition by去分組會(huì)怎樣？

  首先，結(jié)果是這樣的：

  SELECT score 
         ,CASE 
              WHEN score > 300 
              THEN ROW_NUMBER() OVER(
                      ORDER BY score) 
              ELSE NULL 
          END AS rk
  FROM math_test
  
  -- 結(jié)果如下：
  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的數(shù)據(jù)填充為默認(rèn)null，但是，由于窗口函數(shù)是能夠看到“全部”的數(shù)據(jù)，所以在over(order by score)后，排名結(jié)果并不是我們想到的。

總結(jié)

本文通過一個(gè)排名案例說明并解釋了如何在窗口函數(shù)中添加where條件達(dá)到我們預(yù)期的效果，大家可以舉一反三。
兩種方法都可以，但是我還是熱衷于第二種方法，簡單高效。

這么寫SQL真是的太妙妙妙~~~~

-- by 倆只猴