Window Function, Function

Window Function, Function

1. Create a test table score

Create table score (class_no varchar2 (10), -- class student_name varchar2 (20), -- name score number -- score );

2. initialize data

insert into score(class_no,student_name,score) values('n001','park',99);insert into score(class_no,student_name,score) values('n001','ning',99);insert into score(class_no,student_name,score) values('n001','tom',79);insert into score(class_no,student_name,score) values('n001','cat',87);insert into score(class_no,student_name,score) values('n001','sandy',95);insert into score(class_no,student_name,score) values('n002','cake',85);insert into score(class_no,student_name,score) values('n002','mavom',69);insert into score(class_no,student_name,score) values('n002','tony',90);insert into score(class_no,student_name,score) values('n002','lisa',99);insert into score(class_no,student_name,score) values('n002','linda',67);insert into score(class_no,student_name,score) values('n003','versy',84);insert into score(class_no,student_name,score) values('n003','peter',97);insert into score(class_no,student_name,score) values('n003','train',83);insert into score(class_no,student_name,score) values('n003','rain',80);

3. Sort students in different classes in descending order of scores

select *  from (select class_no,               student_name,               score,               rank() over(partition by class_no order by score desc)          from score) t;

4. Execution result

5. Other analysis functions

row_number() over(partition by ... order by ...)rank() over(partition by ... order by ...)dense_rank() over(partition by ... order by ...)count() over(partition by ... order by ...)max() over(partition by ... order by ...)min() over(partition by ... order by ...)sum() over(partition by ... order by ...)avg() over(partition by ... order by ...)first_value() over(partition by ... order by ...)last_value() over(partition by ... order by ...)lag() over(partition by ... order by ...)lead() over(partition by ... order by ...)