文章转自:
[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
这个只是个模板,根据具体情况再改!
分享到:
相关推荐
row_number() over ([partition by col1] order by col2) as 别名 ``` 其中,`partition by col1` 用于指定分区的列,`order by col2` 用于指定排序的列。 在使用 row_number()over 函数时,需要注意以下几点: * ...
首先,`row_number() over(partition by col1 order by col2)` 这个表达式说明了row_number()函数的工作原理。`partition by col1`将数据集分成多个分区,每个分区由col1的相同值组成。然后,在每个分区内部,`order...
row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。 与rownum的区别在于:使用rownum进行排序的...
ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序 而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的) RANK() 类似,不过RANK 排序的时候跟派名次一样,...
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()`函数实现了列...
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...
ROW_NUMBER() OVER (ORDER BY 1) AS id FROM DUAL; ``` Oracle行转列操作可以使用多种方法来实现,包括UNION ALL、MODEL、COLLECTION、AGGREGATE FUNCTION、LISTAGG、REGEXP_SPLIT等方法。这些方法可以满足不同的...
- `ROW_NUMBER()`窗口函数:MySQL无此功能,但可以利用用户变量模拟实现,如Oracle的`row_number() over(partition by col1 order by col2)`。 - `ROWNUM`行号:MySQL中没有直接等价的行号,需用用户变量模拟,如...
20.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。 <1>下面的语句可以进行总计 select region_code,count(*) from aicbs.acc_woff_notify group by rollup(region_code); <2> 对第1个...
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) seq FROM emp ) WHERE seq GROUP BY deptno; ``` 在这段代码中,我们首先使用`ROW_NUMBER()`窗口函数为每个部门的员工按照薪资降序分配行号,然后...
- **ROW_NUMBER() OVER (PARTITION BY col ORDER BY col)**: 对每个分区中的行进行编号。 - **MIN(mc) KEEP (DENSE_RANK FIRST ORDER BY sl) OVER (PARTITION BY id)**: 按`id`分组后,根据`sl`排序,取每个分组的...
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...
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. **...
select *, ROW_NUMBER() OVER(PARTITION BY c1, c2, c3 ORDER BY (getdate())) as num from duplicate_all where c1 = 1 ) delete tmp where num > 1 ``` **二、删除部分重复的记录** 部分字段值重复的记录...
3. 分区(PARTITION BY)和窗口函数:在聚合函数中,`OVER()`配合`PARTITION BY`可以对数据进行分区处理,窗口函数如`RANK()`, `ROW_NUMBER()`, `LEAD()`, `LAG()`等提供了更复杂的分析能力。 4. 视图(VIEW):...