Hive 内置函数之 ROW_NUMBER

转自：http://blog.csdn.net/u010080235/article/details/8971466

hkl：建议可以把这篇和另外一篇SQL函数之row_number()  over()  一起看，会哟更加深刻的理解。

hkl:完善一下:感觉下面的这篇博文写的还是很不错的。

• Hive-0.11.0中内置row_number函数

  org.apache.hadoop.hive.ql.exe.FunctionRegistry 
  
  
  registerHiveUDAFsAsWindowFunctions();
  registerWindowFunction("row_number", new GenericUDAFRowNumber());  --row_number实现类
  registerWindowFunction("rank", new GenericUDAFRank());
  registerWindowFunction("dense_rank", new GenericUDAFDenseRank());
  registerWindowFunction("percent_rank", new GenericUDAFPercentRank());
  registerWindowFunction("cume_dist", new GenericUDAFCumeDist());
  registerWindowFunction("ntile", new GenericUDAFNTile());
  registerWindowFunction("first_value", new GenericUDAFFirstValue());
  registerWindowFunction("last_value", new GenericUDAFLastValue());
  registerWindowFunction(LEAD_FUNC_NAME, new GenericUDAFLead(), false);
  registerWindowFunction(LAG_FUNC_NAME, new GenericUDAFLag(), false);
  

• 使用的DEMO

  • 从test_s表中根据c1字段除重，选取c2最大的值，导入test_c表.

• DEMO

    hive> 
    desc test_s;
    OK
    col_name data_type      comment
    c1                      int                     None                
    c2                      string                  None                
    Time taken: 1.87 seconds, Fetched: 2 row(s)

    hive> 
        > select * from test_s;
    OK
    c1      c2
    1       str1
    2       str2
    3       str3
    3       str31
    3       str33
    4       str41
    4       str42
    Time taken: 0.671 seconds, Fetched: 7 row(s)

    hive> select c1,c2,row_number() over (distribute by c1
        > sort by c2 desc) rownum
        >   from test_s ;

    OK
    c1      c2      rownum
    1       str1    1
    2       str2    1
    3       str33   1
    3       str31   2
    3       str3    3
    4       str42   1
    4       str41   2
    Time taken: 17.23 seconds, Fetched: 7 row(s)

    hive> 
        > 
        > desc test_c;
    OK
    col_name data_type      comment
    c1                      int                     None                
    c2                      string                  None                
    Time taken: 0.118 seconds, Fetched: 2 row(s)


    hive> 
        > insert into table test_c
        > select c1,c2
        > from (
        > select c1,c2,row_number() over (distribute by c1
        > sort by c2 desc) rownum
        >   from test_s 
        > ) aa
        > where aa.rownum = 1;
        OK
        Time taken: 19.609 seconds

    hive> 
        > 
        > 
        > 
        > select * from test_c;
        OK
        c1      c2
        1       str1
        2       str2
        3       str33
        4       str42
        Time taken: 0.144 seconds, Fetched: 4 row(s)

以上都是转发自http://blog.csdn.net/yangjun2/article/details/9339641

下面是我的感受：

我现在使用的hive的版本是0.12，但如果写成上面那样，还是报错，不会执行：如：

hive> select sale_ord_id,ivc_tm from 
    > (select sale_ord_id,ivc_tm,row_number() over (distribute by sale_ord_id sort by ivc_tm desc) rn 
    > from gdm_mXX_inv_actual_det_sum_da 
    > where dt='2014-12-09'
    > and valid_flag=1) a
    > where a.rn=1
    > limit 50
    > ;
FAILED: NullPointerException null

后来同事告诉我，这个版本还是不支持的，需要写在row_number()的括号里面，于是我改成了如下方式：

select sale_ord_id,ivc_title,row_number(ivc_tm) as rn 
from 
(select sale_ord_id,ivc_tm,ivc_title 
from gdm_mXX_inv_actual_det_sum_da 
where dt='2014-12-09'
and valid_flag=1
distribute by sale_ord_id 
sort by ivc_tm desc) a
where row_number(ivc_tm)=1
limit 50
;

这次可以了

原文的链接：http://zhaoyan0922-photo.blog.163.com/blog/static/262384412014111542856545/

假设一个场景：存在表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函数于每个分区从而筛选出每个分区的前两条记录。 

Sql代码  

1. WITH pus AS (  
2.  SELECT *, rid = ROW_NUMBER() OVER   
3.  (PARTITION BY rete ORDER BY score DESC) FROM user_score  
4. )  
5.   
6. 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的行为是无意义的。 

Sql代码  

1. 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中。

hkl:以后用到的时候会不断的完善的。