Oracle分析函数的应用

create table v_test(id int,group_id varchar2(10),name varchar2(10),num int);
comment on table v_test is '分析函数学习专用表';
comment on column v_test.id is '序列号';
comment on column v_test.group_id is '组号';
comment on column v_test.name  is '名子';
comment on column v_test.num  is '数字';

insert into v_test values(1,001,'ok' ,13);
insert into v_test values(2,001,'yes',25);
insert into v_test values(3,001,'no' ,25);
insert into v_test values(4,001,'hi' ,25);
insert into v_test values(5,001,'no' ,17);
insert into v_test values(6,001,'no' ,34);
insert into v_test values(7,002,'no' ,26);
insert into v_test values(8,002,'oh' ,19);

------------------------------------ 测试最后一个函数用
insert into v_test values(11,001,'ok' ,17);
insert into v_test values(12,001,'yes',25);
insert into v_test values(13,001,'no' ,25);
insert into v_test values(14,001,'hi' ,25);
insert into v_test values(15,001,'no' ,13);
insert into v_test values(16,001,'no' ,34);
insert into v_test values(17,002,'no' ,26);
insert into v_test values(18,002,'oh' ,19);

-----------
--1 rank() over(partition by ... order by ...nulls last/first)
--  dense_rank() over(partition by ... order by ... nulls last/first)
--  row_number() over(partition by ... order by ... nulls last/first)

select id,group_id,name,num,
       rank() over(partition by group_id order by name asc nulls last) rk,
       dense_rank() over(partition by group_id order by name asc nulls last) dk,
       row_number() over(partition by group_id order by name asc nulls last) rn
from v_test;

--2 lag(...,...) over(partition by ... order by ... nulls last/first)
--  lead(...,...) over(partition by ... order by ... nulls last/first)

select id,group_id,name,num,
       lag(name,1) over(partition by group_id order by name asc nulls last) lg,
       lead(name,1) over(partition by group_id order by name asc nulls last) ld
from v_test;

select id,group_id,name,num,
       lag(name,3) over(partition by group_id order by name asc nulls last) lg,
       lead(name,3) over(partition by group_id order by name asc nulls last) ld
from v_test;

--3 aggregate_function : min,max,sum,avg,count,varlance,stddev
--  aggregate_function over(partition by ... order by ... nulls last/first)
--按 partition by 分组后,按 order by 排序后,到当前行的最小值,最大值,平均值,和

select id,group_id,name,num,
       min(num) over(partition by group_id order by id asc nulls last) mn,
       max(num) over(partition by group_id order by id asc nulls last) mx,
       avg(num) over(partition by group_id order by id asc nulls last) ag,
       sum(num) over(partition by group_id order by id asc nulls last) sm
from v_test;

--4 last/first
--  aggregate_function(...) keep(dense_rank first/last order by ... nulls last/first) over(partition by ...)

select id,group_id,name,num,
       min(num) keep(dense_rank first order by id) over(partition by group_id) mnf,
       max(num) keep(dense_rank last order by id) over(partition by group_id) mxl
from v_test
where id < 10;

select id,group_id,name,num,
       min(num) keep(dense_rank first order by name) over(partition by group_id) mnf,
       max(num) keep(dense_rank last order by name) over(partition by group_id) mxl
from v_test
where name = 'no'
      and id < 10;

select id,group_id,name,num,
       min(num) keep(dense_rank first order by 1) over(partition by group_id) mnf,
       max(num) keep(dense_rank last order by 1) over(partition by group_id) mxl
from v_test
where name = 'no'
      and id < 10;

---     
select id,group_id,name,num,
       min(num) keep(dense_rank first order by 1 asc) over(partition by group_id) mnf,
       max(num) keep(dense_rank last order by 1 asc) over(partition by group_id) mxl
from v_test
where id > 10;

select id,group_id,name,num,
       min(num) keep(dense_rank last order by id) over(partition by group_id) mnl,
       max(num) keep(dense_rank first order by id) over(partition by group_id) mxf
from v_test;