hive中使用标准sql实现分组内排序

在hive中，想要实现分组内排序，一般都是自己写udf实现oracle中分析函数row_number() over(partition)的功能，如果不使用自定义udf,仅使用标准sql实现的话，毫无性能可言，仅做实验而已。

 

方便起见，以下语句为oracle中语句，但都属于标准sql,在hive中亦可：

 

CREATE TABLE lxw_t (user_id VARCHAR2(20),
class VARCHAR2(20),
score NUMBER 
);

 

 

 

INSERT INTO liuxiaowen.lxw_t VALUES ('user_1','语文','90');
INSERT INTO liuxiaowen.lxw_t VALUES ('user_1','数学','86');
INSERT INTO liuxiaowen.lxw_t VALUES ('user_1','英语','96');
INSERT INTO liuxiaowen.lxw_t VALUES ('user_1','体育','77');
INSERT INTO liuxiaowen.lxw_t VALUES ('user_2','语文','88');
INSERT INTO liuxiaowen.lxw_t VALUES ('user_2','数学','65');
INSERT INTO liuxiaowen.lxw_t VALUES ('user_2','英语','67');
INSERT INTO liuxiaowen.lxw_t VALUES ('user_2','体育','98');


commit;

 

 

SELECT x.user_id,x.class,x.score,COUNT(1) AS seq 
FROM (
  SELECT a.*,b.score score2 FROM 
  liuxiaowen.lxw_t a ,
  liuxiaowen.lxw_t b 
  WHERE a.user_id = b.user_id (+) 
) x 
WHERE x.score2 >= x.score --按照成绩由高到低排列
--WHERE x.score >= x.score2  --按照成绩由低到高排列
GROUP BY x.user_id,x.class,x.score 
order BY 1,4 

 

按照成绩由高到低排列的结果如下：

 

USER_ID	CLASS	SCORE	SEQ
user_1	英语	96	1
user_1	语文	90	2
user_1	数学	86	3
user_1	体育	77	4
user_2	体育	98	1
user_2	语文	88	2
user_2	英语	67	3
user_2	数学	65	4

 

按照成绩由低到高排列的结果如下：

 

USER_ID	CLASS	SCORE	SEQ
user_1	体育	77	1
user_1	数学	86	2
user_1	语文	90	3
user_1	英语	96	4
user_2	数学	65	1
user_2	英语	67	2
user_2	语文	88	3
user_2	体育	98	4