©2020-2024 ioDraw All rights reserved,
Privacy Policy
<>前言
能翻到这篇博文的，想必大家对窗口函数的基本使用已经有了一定的了解，这里就不废话再去多说了。
这篇博文主要讲的是，如果在窗口函数中加入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 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 俩只猴