高效SQL之分析函数:row_number()/rank()/dense_rank()/sum(column)

ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)

表示根据COL1分组，在分组内部根据 COL2排序
而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）

rank() 类似，不过rank排序的时候跟派名次一样，可以并列2个第一名之后是第3名
row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
dense_rank()是连续排序，有两个第二名时仍然跟着第三名
相比之下row_number是没有重复值的

sum(column)是对每一分组内的column进行汇总求和

Table中数据如下：
name  subject  score
A      语文    85
A      数学    90
A      英语    80
B      语文    75
B      数学    78
B      英语    90
C      语文    95
C      数学    88
C      英语    90

select t.name,t.subject,t.score, rank() over(partition by t.subject order by t.score desc) rank from table t

A 数学 90 1
C 数学 88 2
B 数学 78 3
C 英语 90 1
B 英语 90 1
A 英语 80 3
C 语文 95 1
A 语文 85 2
B 语文 75 3

select t.name,t.subject,t.score, row_number() over(partition by t.subject order by t.score desc) row_number from table t

A 数学 90 1
C 数学 88 2
B 数学 78 3
C 英语 90 1
B 英语 90 2
A 英语 80 3
C 语文 95 1
A 语文 85 2
B 语文 75 3

select t.name,t.subject,t.score, dense_rank() over(partition by t.subject order by t.score desc) dense_rank from table t

A 数学 90 1
C 数学 88 2
B 数学 78 3
C 英语 90 1
B 英语 90 1
A 英语 80 2
C 语文 95 1
A 语文 85 2
B 语文 75 3

select t.name,t.subject,t.score, sum(t.score) over(partition by t.name) rank from table t

A 语文 85 255
A 数学 90 255
A 英语 80 255
B 语文 75 243
B 数学 78 243
B 英语 90 243
C 英语 90 273
C 数学 88 273
C 语文 95 273

select t.name,t.subject,t.score, sum(t.score) over(partition by t.subject) rank from table t

B 数学 78 256
A 数学 90 256
C 数学 88 256
A 英语 80 260
C 英语 90 260
B 英语 90 260
A 语文 85 255
B 语文 75 255
C 语文 95 255

评论

1 楼 领悟人生 2010-06-22