Oracle Analytic functions

First, the application scenario

Student Score Table:

Create TableS_score (ID Number( -)Primary Key  not NULL, StudentID Number( -) not NULL, Subjectvarchar( +) not NULL, Test_time datedefaultSysdate not NULL, score Number( -,2)); Comment on TableS_score is 'Student Score'; Comment on columnS_score.id is 'Record ID'; Comment on columnS_score.studentid is 'Student ID'; Comment on columnS_score.subject is 'Subjects'; Comment on columnS_score.test_time is 'Test Date'; Comment on columnS_score.score is 'score';Createsequence S_score_seqminvalue1MaxValue9999999999999999999999999999Start with 1Increment by 1Cache -;

The data are as follows:

Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,1,'A', Sysdate, -);Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,2,'A', Sysdate, -);Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,3,'A', Sysdate, -);Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,4,'A', Sysdate, the);Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,5,'A', Sysdate, the);Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,6,'A', Sysdate, the);Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,7,'A', Sysdate, the);Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,1,'B', Sysdate, -);Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,2,'B', Sysdate, -);Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,3,'B', Sysdate, -);Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,4,'B', Sysdate, -);Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,5,'B', Sysdate, -);Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,6,'B', Sysdate, +);Insert  intoS_score (Id,studentid,subject,test_time,score)Values(S_score_seq.nextval,7,'B', Sysdate, the);

Second, ranking function rank () Over (Order by column) | Dense_rank () Over (Order by column)

1. No grouping

Explain:

Sequencing has continuity: Dense_rank () Over (Order by column), for example: 1, 2, 2, 3, 3, 3, 4

Sort without continuity: rank () Over (Order by column), for example: 1, 2, 2, 4, 4, 4, 4, 8

Query a subject for all student grades, from high to Low: note: The order by default is sorted in ascending order, and Desc is descending from high to low

Dense_rank () Over (Order by column):

Select  Over (orderbydesc) Ranknum,s.studentid,s.score  from S_score S where = ' A ';

　　

Rank () Over (Order by column):

Select  Over (orderbydesc) Ranknum,s.studentid,s.score  from S_score S where = ' A ';

　　

2. Sorting by groups

Application: Query the top 3 of A/b disciplines

Select *  from (Selectover byorderbydesc) Ranknum, S.studentid,s.score from s_score s)where<=3;

　　

Third, Row_number

Select  Over  by Order  by desc ) Ranknum,s.studentid,s.score from S_score s;

　　

Oracle Analytic functions