--rank() over(order by column)不连续排名
sql : select t.STUDENTNO ,t.STUDENTCOURSEGRADE, rank() over(order by studentcoursegrade desc nulls last) as ranknum from student t;
result:
1 1001005 67 1
2 1001006 54 2
3 1001004 45 3
4 1001002 34 4
5 1001003 34 4
6 1001001 34 4
7 1001007 32 7
8 1001008 23 8
9 1001009 9 这里的null值,排名最后,由nulls last来控制,默认null是排在最前面的
--dense_rank() over(order by column)连续排名
select t.STUDENTNO,t.STUDENTCOURSEGRADE , dense_rank() over(order by studentcoursegrade desc nulls last) as ranknum from student t;
result:
1 1001005 67 1
2 1001006 54 2
3 1001004 45 3
4 1001002 34 4
5 1001003 34 4
6 1001001 34 4
7 1001007 32 5
8 1001008 23 6
9 1001009 7 同上
--rank() over(partition by column1 order by column2)分区不连续排名
select t.studentno,t.STUDENTCLASSID,t.STUDENTCOURSEGRADE,rank() over(PARTITION by t.STUDENTCLASSID order by t.STUDENTCOURSEGRADE desc nulls last) from student t;
result:
1 1001006 12 54 1
2 1001001 12 34 2
3 1001002 12 34 2
4 1001007 12 32 4
5 1001008 12 23 5
6 1001009 12 6 同上,在组内排名
7 1001005 45 67 1
8 1001004 45 45 2
9 1001003 45 34 3
--dense_rank() over(partition by column1 order by column2)分区连续排名
select t.studentno,t.STUDENTCLASSID,t.STUDENTCOURSEGRADE,dense_rank() over(PARTITION by t.STUDENTCLASSID order by t.STUDENTCOURSEGRADE desc nulls last) from student t;
result:
1 1001006 12 54 1
2 1001001 12 34 2
3 1001002 12 34 2
4 1001007 12 32 3
5 1001008 12 23 4
6 1001009 12 5 同上,组内排名
7 1001005 45 67 1
8 1001004 45 45 2
9 1001003 45 34 3
--sum(column) over(order by column)累加求和
select t.STUDENTNO, sum(t.STUDENTCOURSEGRADE) over(order by t.STUDENTNO) from student t;
result:
1 1001001 34
2 1001002 68
3 1001003 102
4 1001004 147
5 1001005 214
6 1001006 268
7 1001007 300
8 1001008 323
9 1001009 323 --这里的9的值为null,故还是323
计算方式就是2=1+2,3=1+2+3,。。。这样累加求和