oracle中sql分析函数(二)
al<10;
update test set name='c' where val>10;
SQL> select * from test;
NAME VAL
-------------------- ----------
a 1
a 2
b 3
b 5
b 7
b 9
c 12
c 15
已选择8行。
--按name连续求和
SQL> select name,val,sum(val)over(partition by name order by val desc) "按name连
续求和" from test;
NAME VAL 按name连续求和
-------------------- ---------- --------------
a 2 2
a 1 3
b 9 9
b 7 16
b 5 21
b 3 24
c 15 15
c 12 27
已选择8行。
--按name求和
SQL> select name,val,sum(val)over(partition by name ) "按name求和" from test;
NAME VAL 按name求和
-------------------- ---------- ----------
a 1 3
a 2 3
b 3 24
b 5 24
b 9 24
b 7 24
c 12 27
c 15 27
已选择8行。
5、使用rank()进行排序操作，rank()如果前面有并列则下一名会空出并列的此时，dense_rank()则会按顺序向下排名。
SQL> select name,val,dense_rank() over(partition by name order by val nulls las
) "在name列内部排序" ,
2 dense_rank()over(order by val nulls last) "整体排序" from test;
NAME VAL 在name列内部排序 整体排序
-------------------- ---------- ---------------- ----------
a 1 1 1
a 2 2 2
b 3 1 3
b 5 2 4
b 5 2 4
b 9 3 5
c 12 1 6
c 15 2 7
已选择8行。
6、求top n，使用row_number(),它与rank的不同是不会出现并列情况，会依据行的先后顺序给出top n
SQL> select name,val,row_number()over(partition by name order by val nulls last
) row_num from test;
NAME VAL ROW_NUM
-------------------- ---------- ----------
a 1 1
a 2 2
b 3 1
b 5 2
b 5 3
b 9 4
c 12 1
c 15 2
已选择8行。
7、窗口之行窗口（使用rows关键字）
--依据name分组，向前取两条（共三条）记录求平均值
SQL> select name ,val,avg(val)over(partition by name order by val rows 2 precedi
ng) x from test
2 ;
NAME VAL X
-------------------- ---------- ----------
a 1 1
a 2 1.5
b 3 3
b 5 4
b 5 4.33333333
b 9 6.33333333
c 12 12
c 15 13.5
已选择8行。
SQL> select name,val,sum(val)over( order by val rows between 1 preceding and 1
following ) x from test
2 ;
NAME VAL X
-------------------- ---------- ----------
a 1 3
a 2 6
b 3 10
b 5 15
a 7 19
b 7 23
b 9 28
c 12 36
c