假设一个场景:存在表user_score,该表的数据如下
id | rate | score |
1 | '0-4' | 10 |
2 | '0-4' | 40 |
3 | '0-4' | 30 |
4 | '0-4' | 20 |
5 | '5-10' | 10 |
6 | '5-10' | 40 |
7 | '5-10' | 30 |
8 | '5-10' | 20 |
9 | '11-20' | 10 |
10 | '11-20' | 40 |
11 | '11-20' | 30 |
12 | '11-20' | 20 |
现在要求用一条查询语句取出每种rate下score最大的两条记录,也就算取出id为:2,3,6,7,10,11的记录,要求分别给出SQL和HIVESQL的查询语句
以下是我的想到的方案:
SQL:对user_score根据rate进行分区后根据score进行倒序排序,然后应用row_number函数于每个分区从而筛选出每个分区的前两条记录。
WITH pus AS (
SELECT *, rid = ROW_NUMBER() OVER
(PARTITION BY rete ORDER BY score DESC) FROM user_score
)
SELECT id, rate, score FROM pus WHERE rid <= 2
HIVESQL: HIVE中不存在以上SQL的那种实现方式,ROW_NUMBER()在HIVE中是一个函数,必须带一个或者多个列参数,如ROW_NUMBER(col1, ....),它的作用是按指定的列进行分组生成行序列,在ROW_NUMBER(a,b) 时,若两条记录的a,b列相同,则行序列+1,否则重新计数。因为HIVE是基于MAPREADUCE的,必须保证ROW_NUMBER执行是在REDUCE中,并且ROW_NUMBER中使用的列中,列值相同的记录要再同一个reduce中,否则ROW_NUMBER的行为是无意义的。
SELECT id, rate, score FROM(SELECT * FROM user_score DISTRIBUTE BY rate SORT BY rete ASC, score DESC) WHERE ROW_NUMBER(rete) <= 2
这里有两点需要说明:
- 使用子查询保证ROW_NUMBER在reduce端执行。
- 使用BY rate SORT BY rete ASC, score DESC 来保证rate相同的记录被分配到相同的REDUCE中。
分享到:
相关推荐
02.hive内置函数--窗口分析函数--row_number_over.mp4
hive中分组取topN、row_number、rank和dense_rank使用介绍
大数据分析工具 hive 高级分析函数的使用与优化,笔记整理!
hive_jdbc_2.6.2.1002
flink-sql-connector-hive-2.3.6_2.11-1.11.0.jar
flink-sql-connector-hive-3.1.2_2.11-1.11.6.jar 已经解决guava冲突亲测可以
由于jar包的下载速度很慢,我想也有很多下载慢的同学,分享下
Hive用户(Hive_user_guide)_中文版pdf
基于hadoop,进行hive客户端安装与使用
Hive用户指南,使用手册,简明扼要。内容包括架构、基本操作、参数设置、UDF,以及优化及使用技巧等等。
一个基于thrift的进行使用php操作hive的一个类库的封装。
hive_jdbc_2.6.1.1001,hive_jdbc_2.6.1.1001,hive_jdbc_2.6.1.1001
hive JDBC连接实例 maven工程
Apache Hive 是基于Hadoop的一个数据仓库工具,用来进行数据提取、转化、加载,这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表,并提供...
1_hive函数大全.pdf
hbase_hive_thrift_php
大数据_Hive_视频_含有笔记_详细_给力_强大_好好学_链接3个月有效期
hive用户手册_0.11_cn.chm
基于Hive和Apache_Kylin_大数据聚合与管理系统的设计与实现
Hive编程指南_PDF_中文高清版.pdf