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;