Hive SQL之如何在row_number()等窗口函数中加where条件?

前言

能翻到这篇博文的，想必大家对窗口函数的基本使用已经有了一定的了解，这里就不废话再去多说了。

这篇博文主要讲的是，如果在窗口函数中加入where条件。

为了方便理解，以下案例使用排名函数：row_number()来讲解。其他窗口函数同理，大家举一反三。

本文你可以学习到：

1. 使用UNION ALL分之而治解决本问题
2. 使用窗口函数中的一个小技巧解决本问题 （简单高效）

案例

我们如果要对一组数据根据某个列去排名，一般会使用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	8
  

  UNION 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 俩只猴