Using standard SQL in hive for intra-group sorting

In hive, standard SQL is used to implement intra-group sorting in hive. To implement intra-group sorting, you generally write your own udf to implement the analysis function row_number () over (partition) in oracle, if you do not use a user-defined udf and only use standard SQL for implementation, there is no performance, just experiment. For convenience, the following statements are in oracle

In hive, standard SQL is used to implement intra-group sorting in hive. To implement intra-group sorting, you generally write your own udf to implement the analysis function row_number () over (partition) in oracle, if you do not use a user-defined udf and only use standard SQL for implementation, there is no performance, just experiment. For convenience, the following statements are in oracle

Using standard SQL in hive for intra-group sorting

In hive, to sort data in a group, you generally write a udf to implement the row_number () over (partition) function in oracle. If you do not use a udf, if you only use standard SQL, there is no performance at all, just experiment.

For convenience, the following statements are in oracle, but all of them belong to standard SQL, and can also be used in hive:

SQL code

Create table lxw_t (user_id VARCHAR2 (20 ),

Class VARCHAR2 (20 ),

Score NUMBER

);

SQL code

Insert into liuxiaowen. lxw_t VALUES ('user _ 1', 'China', '90 ');

Insert into liuxiaowen. lxw_t VALUES ('user _ 1', 'mat', '86 ');

Insert into liuxiaowen. lxw_t VALUES ('user _ 1', 'English ', '96 ');

Insert into liuxiaowen. lxw_t VALUES ('user _ 1', 'Sports ', '77 ');

Insert into liuxiaowen. lxw_t VALUES ('user _ 2', 'China', '88 ');

Insert into liuxiaowen. lxw_t VALUES ('user _ 2', 'mat', '65 ');

Insert into liuxiaowen. lxw_t VALUES ('user _ 2', 'English ', '67 ');

Insert into liuxiaowen. lxw_t VALUES ('user _ 2', 'Sports ', '98 ');

Commit;

SQL code

SELECT x. user_id, x. class, x. score, COUNT (1) AS seq

FROM (

SELECT a. *, B. score score2 FROM

Liuxiaowen. lxw_t,

Liuxiaowen. lxw_t B

WHERE a. user_id = B. user_id (+)

) X

WHERE x. score2> = x. score-sort by score from high to low

-- WHERE x. score> = x. score2 -- sort by score from low to high

Group by x. user_id, x. class, x. score

Order BY 1, 4

The results are listed from high to low as follows:

SQL code

USER_ID CLASS SCORE SEQ

User_1 English 96 1

User_1 language 90 2

User_1 mathematics 86 3

User_1 sports 77 4

User_2 sports 98 1

User_2 language 88 2

User_2 English 67 3

User_2 mathematics 65 4

The results are listed as follows:

SQL code

USER_ID CLASS SCORE SEQ

User_1 sports 77 1

User_1 mathematics 86 2

User_1 language 90 3

User_1 English 96 4

User_2 mathematics 65 1

User_2 English 67 2

User_2 language 88 3

User_2 sports 98 4