<>前言

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

这篇博文主要讲的是,如果在窗口函数中加入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 俩只猴

技术
©2020 ioDraw All rights reserved
谷歌称居家办公影响工作效率!2021 年将回归线下办公为什么保持代码整洁如此重要?python求和函数sum()详解字节跳动游戏官网正式上线:命名“朝夕光年”QCustomPlot系列(5)-实时动态曲线C语言简易学生成绩管理系统C语言结课设计:餐饮管理与点餐系统NOI2019 游记遇到数学问题《深度学习》“花书”读不下去了吗?给你支个招Python-Pandas库实现EXCEL数据拆分成不同的表