目录
数据
各个班级数据表
id | name | class | goal |
1 | 小明 | 一班 | 100 |
2 | 小东 | 一班 | 90 |
3 | 小红 | 二班 | 95 |
4 | 小西 | 二班 | 80 |
RANK()求排名
求两个班所有人的排名
SELECT
*,
rank() OVER(ORDER BY goal desc) RANK
FROM
"user"
结果(注意 goal类型因改为整型,如果为数字字符串类型需要转为整型比较)
id name class goal rank
1 小明 一班 100 1
3 小红 二班 95 2
2 小东 一班 90 3
4 小西 二班 80 4
此时存在相同分数,此时小红也为100分,则结果为
id name class goal rank
1 小明 一班 100 1
2 小东 一班 100 1
3 小红 二班 95 3
4 小西 二班 80 4
nulls last
当值为null,排序时会处于所有数据后
SELECT
*,
rank() OVER(ORDER BY goal desc nulls last) RANK
FROM
"user"
结果(多个null具有相同名次)
id name class goal rank
1 小明 一班 100 1
2 小东 一班 100 1
3 小红 二班 95 3
4 小西 二班 4
5 小南 二班 4
row_number()求排名
SELECT
*,
row_number() OVER(ORDER BY goal desc) RANK
FROM
"user"
结果
id name class goal rank
1 小明 一班 100 1
2 小东 一班 100 2
3 小红 二班 95 3
4 小西 二班 80 4
与RANK()区别在于如果两个分数相同则排名仍然递增,并不会出现1134这种更符合实际的排名
dense_rank()求排名
SELECT
*,
dense_rank() OVER(ORDER BY goal desc) RANK
FROM
"user"
结果
id name class goal rank
1 小明 一班 100 1
2 小东 一班 100 1
3 小红 二班 95 2
4 小西 二班 80 3
与与RANK()区别在于,同名词后面的名次
RANK() 1 1 3 4
dense_rank() 1 1 2 3
PARTITION BY分区函数
求两个班各自排名
SELECT
*,
rank() OVER(PARTITION BY class ORDER BY goal desc) RANK
FROM
"user"
结果为
id name class goal rank
3 小红 二班 95 1
4 小西 二班 80 2
1 小明 一班 100 1
2 小东 一班 100 1
PARTITION BY 与 GROUP BY区别
PARTITION BY 分区,根据相同条件分区,通过聚合函数,将结果合并至每一条数据后成为新的属性
如求每个人对应班级的总分
SELECT
*,
sum( goal ) over ( PARTITION BY class )
FROM
"user"
结果(sum相加后的字段跟随在每个人的数据后,根据分区进行相加)
3 小红 二班 95 175
4 小西 二班 80 175
1 小明 一班 100 200
2 小东 一班 100 200
GROUP BY 分组,对所有数据的分组后处理,分组后,除了分组条件字段,其他字段需要通过聚合函数合并成一个值
如,求每个班级总分
SELECT
sum(goal)
FROM
"user"
GROUP BY class
结果
一班 200
二班 175