有些人对oracle分析函数中select over(partition by col1 order by col2) from test order by ...关于partition by 和 组内order by以及最后的order by的执行顺序产生疑惑。
over 中的partition为分组, order by是视窗内排序, 先执行 partition 然后order by 如 partition by col_a order by col_b 的执行排序效果类似于order by col_a, col_b 这样的排序效果,如果再在最后加order by,是在前边分组排序的结果基础上进行排序。
SQL> create table test(id varchar2(20));
Table created
SQL> insert into test values('1');
1 row inserted
SQL> insert into test values('1');
1 row inserted
SQL> insert into test values('8');
1 row inserted
SQL> insert into test values('5');
1 row inserted
SQL> insert into test values('5');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
ID
--------------------
1
1
8
5
5
1.按照id排序:
SQL> select row_number() over(order by id),id,rownum from test;
ROW_NUMBER()OVER(ORDERBYID) ID ROWNUM
--------------------------- -------------------- ----------
1 1 1
2 1 2
3 5 5
4 5 4
5 8 3
2.组内(没有分组就是所有数据1组)按照id排序,最后order by在组内排序基础上按照rownum排序:
SQL> select row_number() over(order by id),id,rownum from test order by rownum;
ROW_NUMBER()OVER(ORDERBYID) ID ROWNUM
--------------------------- -------------------- ----------
1 1 1
2 1 2
5 8 3
4 5 4
3 5 5
3.按照rownum排序:
SQL> select row_number() over(order by rownum),id,rownum from test;
ROW_NUMBER()OVER(ORDERBYROWNUM ID ROWNUM
------------------------------ -------------------- ----------
1 1 1
2 1 2
3 8 3
4 5 4
5 5 5
4.按照id分组,组内按照id排序
SQL> select row_number() over(partition by id order by id),id,rownum from test;
ROW_NUMBER()OVER(PARTITIONBYID ID ROWNUM
------------------------------ -------------------- ----------
1 1 1
2 1 2
1 5 5
2 5 4
1 8 3
5.按照id分组,组内按照rownum(这个是早已经出来的结构)排序:
SQL> select row_number() over(partition by id order by rownum),id,rownum from test;
ROW_NUMBER()OVER(PARTITIONBYID ID ROWNUM
------------------------------ -------------------- ----------
1 1 1
2 1 2
1 5 4
2 5 5
1 8 3
oracle在提取数据库的时候是按over(partition by ... order by ...)这个里边的order by后边的字段的一个个distinct值取出数据的。
SQL> select * from t;
A B C D
---------- ---------- ---------- ----------
1 111 G 87
1 111 G 87
1 222 G 85
1 222 G 86
2 111 G 80
2 111 G 80
2 222 G 81
2 222 G 80
8 rows selected
只有partition by a,distinct a有2个值1和2:分2次提取数据
为1的提取一次,4条a值相同,4条平均86.25
为2的提取一次,4条a值相同,4条平均80.25
SQL> select a,b,c,avg(d) over(partition by a ),d from t;
A B C AVG(D)OVER(PARTITIONBYA) D
---------- ---------- ---------- ------------------------ ----------
1 111 G 86.25 87
1 111 G 86.25 87
1 222 G 86.25 85
1 222 G 86.25 86
2 111 G 80.25 80
2 111 G 80.25 80
2 222 G 80.25 81
2 222 G 80.25 80
8 rows selected
partition by a,order by b,distinct a,b有4个值:
1---111
1---222
2---111
2---222
分四次提取数据:
1---111:取出2条,a=1的2条取平均87
1---222:取出2条,a=1的4条取平均86.25
2---111:取出2条,a=2的2条取平均80
2---222:取出2条,a=2的4条取平均80.25
SQL> select a,b,c,avg(d) over(partition by a order by b ),d from t;
A B C AVG(D)OVER(PARTITIONBYAORDERBY D
---------- ---------- ---------- ------------------------------ ----------
1 111 G 87 87
1 111 G 87 87
1 222 G 86.25 85
1 222 G 86.25 86
2 111 G 80 80
2 111 G 80 80
2 222 G 80.25 81
2 222 G 80.25 80
8 rows selected
SQL>