ROW_NUMBER() OVER()函数基础用法

   
  前言：
         咳咳，出来工作这么久，发现自己一直用的都是很基本的sql，当然主要是业务。最近想想这不是个好事，要开始注意这个问题了。
  
        最近接触到需要将某表的某些数据抽出来，表中关键字按照时间排序，需要将最新的状态筛选出来。请教大神罗罗，知道有某个函数
  测试数据如下：
SQL> select * from b1;


        ID      STATE        DAT
---------- ---------- ----------
         1          3   20170511
         1          4   20170512
         1          5   20170513
         2          6   20170514
         3          7   20170515
         2          8   20170513
         3          8   20170516


7 rows selected.
SQL> select id,state,dat,row_number() over(partition by id order by dat desc) rr from b1;


        ID      STATE        DAT         RR
---------- ---------- ---------- ----------
         1          5   20170513          1
         1          4   20170512          2
         1          3   20170511          3
         2          6   20170514          1
         2          8   20170513          2
         3          8   20170516          1
         3          7   20170515          2


7 rows selected.

可以看出是按照id分组，每个分组里按照dat排序。接下来要选取最值就简单了。

SQL> select id,state,dat from
  2  (select id,state,dat,row_number() over(partition by id order by dat desc) rr from b1)
  3  where rr=1
  4  ;


        ID      STATE        DAT
---------- ---------- ----------
         1          5   20170513
         2          6   20170514
         3          8   20170516

感觉函数真的很强大（见识短，莫笑），按照正常的写法是这样：

SQL>  select a.id,a.state,a.dat from b1 a inner join(select id,max(dat) max_dat from b1 group by id) b on a.id=b.id and a.dat=b.max_dat;


        ID      STATE        DAT
---------- ---------- ----------
         1          5   20170513
         2          6   20170514
         3          8   20170516

貌似也差不多。。。。但在HIVE里不支持第一种写法。

百度上对这个函数的解释：盗用一下

1.简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。 

  



2. row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)


总结：九层之台，起于垒土；千里之行，始于足下。还是贵在点滴积累，不分大小啊！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30289795/viewspace-2139722/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30289795/viewspace-2139722/