通信人家园

标题: sql 函数row_number() over [查看完整版帖子] [打印本页]

时间: 2013-1-31 15:27

作者: buddhast 标题: sql 函数row_number() over

select*from
(
selectanimal,age,id ,row_number()over(partitionbyanimalorderbyagedesc) row_num
fromzoon
)whererow_num<=5; number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的).

　　与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．

　　row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．

　　rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．

　　dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的．

　　lag（arg1,arg2,arg3):
arg1是从其他行返回的表达式
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。
arg3是在arg2表示的数目超出了分组的范围时返回的值。

看几个SQL语句:

语句一:

select row_number() over(order by sale/cnt desc) as sort, sale/cnt
from (
select -60 as sale,3 as cnt from dual union
select 24 as sale,6 as cnt from dual union
select 50 as sale,5 as cnt from dual union
select -20 as sale,2 as cnt from dual union
select 40 as sale,8 as cnt from dual);

执行结果:

      SORT    SALE/CNT
---------- ----------
         1          10
         2             5
         3             4
         4          -10
         5          -20

语句二:查询员工的工资,按部门排序

select ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order from scott.emp;

执行结果:

ENAME                         SAL    SAL_ORDER
-------------------- ---------- ----------
KING                         5000             1
CLARK                         2450             2
MILLER                      1300             3
SCOTT                         3000             1
FORD                         3000             2
JONES                         2975             3
ADAMS                         1100             4
SMITH                         800             5
BLAKE                         2850             1
ALLEN                         1600             2
TURNER                      1500             3
WARD                         1250             4
MARTIN                      1250             5
JAMES                         950             6

已选择14行。

语句三:查询每个部门的最高工资

select deptno,ename,sal from
   (select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order
      from scott.emp) where sal_order <2;

执行结果:

   DEPTNO ENAME                         SAL
---------- -------------------- ----------
         10 KING                         5000
         20 SCOTT                      3000
         30 BLAKE                      2850

已选择3行。

语句四：

select deptno,sal,rank() over (partition by deptno order by sal) as rank_order from scott.emp order by deptno;

执行结果:

   DEPTNO       SAL RANK_ORDER
---------- ---------- ----------
      10       1300          1
      10       2450          2
      10       5000          3
      20       800          1
      20       1100          2
      20       2975          3
      20       3000          4
      20       3000          4
      30       950          1
      30       1250          2
      30       1250          2
      30       1500          4
      30       1600          5
      30       2850          6

已选择14行。

语句五：

select deptno,sal,dense_rank() over(partition by deptno order by sal) as dense_rank_order from scott.emp order by deptn;
执行结果：

   DEPTNO       SAL DENSE_RANK_ORDER
---------- ---------- ----------------
      10       1300                1
      10       2450                2
      10       5000                3
      20       800                1
      20       1100                2
      20       2975                3
      20       3000                4
      20       3000                4
      30       950                1
      30       1250                2
      30       1250                2
      30       1500                3
      30       1600                4
      30       2850                5

已选择14行。

语句六：

select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) as lag_ from scott.emp order by deptno;

执行结果：

   DEPTNO ENAME                      SAL LAG_
---------- -------------------- ---------- --------------------
      10 CLARK                      2450
      10 KING                      5000 CLARK
      10 MILLER                   1300 KING
      20 ADAMS                      1100
      20 FORD                      3000 ADAMS
      20 JONES                      2975 FORD
      20 SCOTT                      3000 JONES
      20 SMITH                      800 SCOTT
      30 ALLEN                      1600
      30 BLAKE                      2850 ALLEN
      30 JAMES                      950 BLAKE
      30 MARTIN                   1250 JAMES
      30 TURNER                   1500 MARTIN
      30 WARD                      1250 TURNER

已选择14行。

有这样一个sql语句：我有一个视图，里面数据有2亿，现在我要根据某个字段排重后，计算一下还有多少，我是这样查询的，但是速度太慢，请问是否有更快的方式？

select count(distinct user_msisdn) from tab_day_test

优化后的语句：select count(1)
from
(
select row_number() over(partition by c1,c2,c3,c4,c5 order by  c1) rw
from t1
)
where rw=1
这个只是个模板，根据具体情况再改！

通信人家园 (https://www.txrjy.com/)