本文详细介绍了SQL中的几种窗口函数：rank(), dense_rank(), row_number(), cume_dist(), max()及avg()的使用方法，并通过具体实例展示了这些函数如何应用于数据查询和分析中。
环境准备:
create table students
(id number(15,0),
area varchar2(10),
stu_type varchar2(2),
score number(20,2));
insert into students values(1, '111', 'g', 80 );
insert into students values(1, '111', 'j', 80 );
insert into students values(1, '222', 'g', 89 );
insert into students values(1, '222', 'g', 68 );
insert into students values(2, '111', 'g', 80 );
insert into students values(2, '111', 'j', 70 );
insert into students values(2, '222', 'g', 60 );
insert into students values(2, '222', 'j', 65 );
insert into students values(3, '111', 'g', 75 );
insert into students values(3, '111', 'j', 58 );
insert into students values(3, '222', 'g', 58 );
insert into students values(3, '222', 'j', 90 );
insert into students values(4, '111', 'g', 89 );
insert into students values(4, '111', 'j', 90 );
insert into students values(4, '222', 'g', 90 );
insert into students values(4, '222', 'j', 89 );
commit;
1.rank(),DENSE_RANK()
A）求各个id中排名情况，允许并列名次、名次间断
SQL> select id,rank() over(partition by id order by score desc) rk,score from students;
ID RK SCORE
---------------- ---------- ----------------------
1 1 89.00
1 2 80.00
1 2 80.00
1 4 68.00
2 1 80.00
2 2 70.00
2 3 65.00
2 4 60.00
3 1 90.00
3 2 75.00
3 3 58.00
3 3 58.00
4 1 90.00
4 1 90.00
4 3 89.00
4 3 89.00
16 rows selected
B）求各个id中排名情况，允许并列名次，名次不间断
SQL> select id,dense_rank() over(partition by id order by score desc) rk,score from students;
ID RK SCORE
---------------- ---------- ----------------------
1 1 89.00
1 2 80.00
1 2 80.00
1 3 68.00
2 1 80.00
2 2 70.00
2 3 65.00
2 4 60.00
3 1 90.00
3 2 75.00
3 3 58.00
3 3 58.00
4 1 90.00
4 1 90.00
4 2 89.00
4 2 89.00
16 rows selected
2.row_number
求各个id内的score排名
SQL> select id,row_number() over(partition by ID order by SCORE desc) rn,score from students;
ID RN SCORE
---------------- ---------- ----------------------
1 1 89.00
1 2 80.00
1 3 80.00
1 4 68.00
2 1 80.00
2 2 70.00
2 3 65.00
2 4 60.00
3 1 90.00
3 2 75.00
3 3 58.00
3 4 58.00
4 1 90.00
4 2 90.00
4 3 89.00
4 4 89.00
16 rows selected
3.cume_dist
该组最大row_number/所有记录row_number
SQL> select cume_dist() over(order by id) a, --该组最大row_number/所有记录row_number
2 row_number() over (order by id) rn,id,area,score from students;
A RN ID AREA SCORE
---------- ---------- ---------------- ---------- ----------------------
0.25 1 1 222 89.00 =4/16
0.25 2 1 111 80.00 =4/16
0.25 3 1 111 80.00 =4/16
0.25 4 1 222 68.00 =4/16
0.5 5 2 222 60.00 =8/16
0.5 6 2 111 70.00 =8/16
0.5 7 2 111 80.00 =8/16
0.5 8 2 222 65.00 =8/16
0.75 9 3 111 75.00 =12/16
0.75 10 3 111 58.00 =12/16
0.75 11 3 222 58.00 =12/16
0.75 12 3 222 90.00 =12/16
1 13 4 111 89.00 =16/16
1 14 4 111 90.00 =16/16
1 15 4 222 90.00 =16/16
1 16 4 222 89.00 =16/16
16 rows selected
这里很难理解 。 我们再举例说明
SQL>conn scott/tiger
SQL>insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7935, 'MILLER', 'CLERK1', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1340.00, null, 10);
SQL>COMMIT;
SQL> SELECT deptno, job, SUM(sal),
2 CUME_DIST() OVER(PARTITION BY deptno ORDER BY
3 SUM(sal) )
4 AS cume_dist_per_dep
5 FROM emp
6 GROUP BY deptno, job
7 ORDER BY deptno, SUM(sal);
DEPTNO JOB SUM(SAL) CUME_DIST_PER_DEP
------ --------- ---------- -----------------
10 CLERK 1300 0.25 =1/4
10 CLERK1 1340 0.5 =2/4
10 MANAGER 2450 0.75 =3/4
10 PRESIDENT 5000 1 =4/4
20 CLERK 1900 0.333333333333333 =1/3
20 MANAGER 2975 0.666666666666667 =2/3
20 ANALYST 6000 1 =3/3
30 CLERK 950 0.333333333333333 =1/3
30 MANAGER 2850 0.666666666666667 =2/3
30 SALESMAN 5600 1 =3/3
10 rows selected
4.max
求相同id的最大值。
SQL> conn scott/sa
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> select id,max(score) over(partition by id order by score desc) as mx,score from students;
ID MX SCORE
---------------- ---------- ----------------------
1 89 89.00
1 89 80.00
1 89 80.00
1 89 68.00
2 80 80.00
2 80 70.00
2 80 65.00
2 80 60.00
3 90 90.00
3 90 75.00
3 90 58.00
3 90 58.00
4 90 90.00
4 90 90.00
4 90 89.00
4 90 89.00
16 rows selected
SQL>
5.avg
SQL> select id,area,avg(score) over(partition by id order by area) as avg,score from students;
ID AREA AVG SCORE
---------------- ---------- ---------- ----------------------
1 111 80 80.00
1 111 80 80.00
1 222 79.25 89.00
1 222 79.25 68.00
2 111 75 80.00
2 111 75 70.00
2 222 68.75 60.00
2 222 68.75 65.00
3 111 66.5 75.00
3 111 66.5 58.00
3 222 70.25 58.00
3 222 70.25 90.00
4 111 89.5 89.00
4 111 89.5 90.00
4 222 89.5 90.00
4 222 89.5 89.00
16 rows selected
SQL> select id,area,avg(score) over(partition by id /*order by area*/) as avg,score from students; --注意有无order by的区别
ID AREA AVG SCORE
---------------- ---------- ---------- ----------------------
1 222 79.25 89.00
1 111 79.25 80.00
1 111 79.25 80.00
1 222 79.25 68.00
2 222 68.75 60.00
2 111 68.75 70.00
2 111 68.75 80.00
2 222 68.75 65.00
3 111 70.25 75.00
3 111 70.25 58.00
3 222 70.25 58.00
3 222 70.25 90.00
4 111 89.5 89.00
4 111 89.5 90.00
4 222 89.5 90.00
4 222 89.5 89.00
16 rows selected
SQL>
SQL> select id,avg(score) over(partition by id order by score desc rows between unbounded preceding
2 and unbounded following ) as ag,score from students;
ID AG SCORE
---------------- ---------- ----------------------
1 79.25 89.00
1 79.25 80.00
1 79.25 80.00
1 79.25 68.00
2 68.75 80.00
2 68.75 70.00
2 68.75 65.00
2 68.75 60.00
3 70.25 90.00
3 70.25 75.00
3 70.25 58.00
3 70.25 58.00
4 89.5 90.00
4 89.5 90.00
4 89.5 89.00
4 89.5 89.00
16 rows selected
SQL>
扫一扫
1825
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
