窗口函数如何开多个窗口
1 背景
最近实习的时候,负责提数的同事SQL写的贼6,偷瞄了一眼,又看到了窗口函数,而这个窗口函数,之前的博客中笔者也有涉及到,具体见:
但上面两个例子中都只是开了单个窗口,什么叫窗口?
上面红色部分就是叫“窗口”,可以理解为根据这个字段进行分窗口!然后在窗口中进行相关后续统计操作!那问题来了,能不能开多个窗口呢?答案:必须阔以!
2 窗口函数如何开多个窗口
2.1 具体需求和数据长啥样?
- 需求1:得到不同id和type下的score前两名
- 需求2:得到不同id和type下的随机抽两个
2.2 需求1
2.2.1 思路
- 窗口函数中间开两个变量 即窗口,变量之间以逗号连接
2.2.2 SQL实现
-- 分区后按照score降序排列取前两名
SELECT *
from
(
SELECT
app_id,type,score,
row_number() over(partition by type,app_id order by cast(score as float) desc) rank
from tmp.tmp_test_0424
)a
where rank <= 2
2.2.3 SQL实现结果
2.2.4 遇到的坑
当时建立临时表的时候,score这一列也是设成了string类型,而不是float型,所以一开始的排名结果是:
可以看到上述红色的两条记录明显有bug,这是什么原因呢?
- 针对降序排列,因为如果是string,就是按照字符进行排序,所以就是9开头的排名靠前,先看点之前的,点之前越多排名越靠前。
- 那出现了这个问题我们怎么去处理呢?两种方案:
- 重新建表,把score那一列改成int类型
- 使用cast(score as float) 即将其变成float类型!这样也可以实现了!完美!
2.3 需求2
2.3.1 思路
- 主要是随机取,如何随机?一个骚操作来了,order by后面使用rand()函数!
2.3.2 SQL实现
-- 分区后随机取两个
SELECT *
from
(
SELECT *,
row_number() over(partition by app_id, type order by rand()) rank
from tmp.tmp_test_0424
)a
where rank <= 2