Hive SQL之如何在row | 您所在的位置:网站首页 › 函数添加字段序号是什么 › Hive SQL之如何在row |
前言
能翻到这篇博文的,想必大家对窗口函数的基本使用已经有了一定的了解,这里就不废话再去多说了。 这篇博文主要讲的是,如果在窗口函数中加入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 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我们看到,虽然没有把 |
CopyRight 2018-2019 实验室设备网 版权所有 |