`

ORACLE中row_number over(partition by col1 order by col2)用法

 
阅读更多
文章转自:

[url]
http://space.itpub.net/7366728/viewspace-594893
[/url]

row_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
这个只是个模板,根据具体情况再改!

分享到:
评论

相关推荐

    Oracle row_number()over

    row_number() over ([partition by col1] order by col2) as 别名 ``` 其中,`partition by col1` 用于指定分区的列,`order by col2` 用于指定排序的列。 在使用 row_number()over 函数时,需要注意以下几点: * ...

    深入探讨:oracle中row_number() over()分析函数用法

    首先,`row_number() over(partition by col1 order by col2)` 这个表达式说明了row_number()函数的工作原理。`partition by col1`将数据集分成多个分区,每个分区由col1的相同值组成。然后,在每个分区内部,`order...

    oracle中rownum和row_number()

    row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。 与rownum的区别在于:使用rownum进行排序的...

    Oracle 分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法

    ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序 而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的) RANK() 类似,不过RANK 排序的时候跟派名次一样,...

    Oracle单条SQL语句可以做什么.pdf

    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY ROWNUM) AS rn2, col3, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3 ORDER BY ROWNUM) AS rn3 FROM t ); ``` 这里通过`ROW_NUMBER()`函数实现了列...

    Oracle到mysql转换的问题总结.docx

    SELECT row_number() over(partition by col1 order by col2) as num FROM table1 MySQL: SET @rank = 0; SET @pdept = ''; SELECT num1 AS num FROM ( SELECT IF(@pdept=col1, @rank:=@rank+1, @rank:=1) AS num1...

    Oracle行转列

    ROW_NUMBER() OVER (ORDER BY 1) AS id FROM DUAL; ``` Oracle行转列操作可以使用多种方法来实现,包括UNION ALL、MODEL、COLLECTION、AGGREGATE FUNCTION、LISTAGG、REGEXP_SPLIT等方法。这些方法可以满足不同的...

    Oracle到mysql转换的问题总结.doc

    - `ROW_NUMBER()`窗口函数:MySQL无此功能,但可以利用用户变量模拟实现,如Oracle的`row_number() over(partition by col1 order by col2)`。 - `ROWNUM`行号:MySQL中没有直接等价的行号,需用用户变量模拟,如...

    Oracle事例

    20.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。 &lt;1&gt;下面的语句可以进行总计 select region_code,count(*) from aicbs.acc_woff_notify group by rollup(region_code); &lt;2&gt; 对第1个...

    oracle行转列较精典的解决方案

    ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) seq FROM emp ) WHERE seq GROUP BY deptno; ``` 在这段代码中,我们首先使用`ROW_NUMBER()`窗口函数为每个部门的员工按照薪资降序分配行号,然后...

    Oracle 设计高级编码

    - **ROW_NUMBER() OVER (PARTITION BY col ORDER BY col)**: 对每个分区中的行进行编号。 - **MIN(mc) KEEP (DENSE_RANK FIRST ORDER BY sl) OVER (PARTITION BY id)**: 按`id`分组后,根据`sl`排序,取每个分组的...

    oracle函数大全

    6. **ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)**:为每个分组内的行编号。 - 示例:`SELECT ROW_NUMBER() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS RNK, EMPLOYEE_ID FROM EMPLOYEES...

    Oracle实现行列转换的方法分析

    LEAD (rn) OVER (PARTITION BY c1 ORDER BY rn) rn1 FROM ( SELECT c1, c2, ROW_NUMBER () OVER (ORDER BY c2) rn FROM t ) ) START WITH rn1 IS NULL CONNECT BY rn1 = PRIOR rn GROUP BY c1; ``` 3. **...

    sql删除重复数据的详细方法

    select *, ROW_NUMBER() OVER(PARTITION BY c1, c2, c3 ORDER BY (getdate())) as num from duplicate_all where c1 = 1 ) delete tmp where num &gt; 1 ``` **二、删除部分重复的记录** 部分字段值重复的记录...

    SQL语法教程

    3. 分区(PARTITION BY)和窗口函数:在聚合函数中,`OVER()`配合`PARTITION BY`可以对数据进行分区处理,窗口函数如`RANK()`, `ROW_NUMBER()`, `LEAD()`, `LAG()`等提供了更复杂的分析能力。 4. 视图(VIEW):...

Global site tag (gtag.js) - Google Analytics