四个排名函数:
1.row_number
2.rank
3.dense_rank
4.ntile
1. ROW_NUMBER(排名场景推荐)
1.1 介绍
在 SQL 中,ROW_NUMBER() 是一个窗口函数,它为结果集中的每一行分配一个唯一的序号。该函数的语法如下:
ROW_NUMBER() OVER (ORDER BY column1 [, column2, ...])
其中,ORDER BY 子句指定了 ROW_NUMBER() 函数排列行的顺序。如果省略 ORDER BY,则序号将按照结果集中的任意顺序进行分配。
1.2 使用场景及例子
1.2.1 排名
通过order by进行排序得到排名。
SELECT
id,
name,
score,
row_number() OVER(
ORDER BY score DESC) AS ranks
FROM
student_info
1.2.2 去重
可以使用 ROW_NUMBER() 函数去除重复记录。例如,在一个表中,有重复记录,想要保留每个记录的唯一编号。
SELECT id, column1, column2, ...
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY column1, column2, ... ORDER BY id) AS row_num
FROM table_name
) subquery
WHERE row_num = 1;
1.2.3 分页查询
大可不必,不建议使用。
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (ORDER BY id) AS row_num
FROM table_name
) subquery
WHERE row_num BETWEEN 11 AND 20;
2. RANK
2.1 介绍
在SQL中,RANK()是一个窗口函数,用于根据指定的ORDER BY子句给结果集中的每一行分配排名值。在排序过程中,如果有多个行具有相同的排序值,则它们将被分配相同的排名值。RANK()可以在SELECT语句的SELECT子句、ORDER BY子句或OVER()子句中使用。
以下是RANK()的语法:
RANK() OVER ( [PARTITION BY partition_expression, ... [n]]
ORDER BY sort_expression [ASC|DESC], ... [n] )
在这个语法中,PARTITION BY子句可选,它用于将结果集分割成不同的分区,然后对每个分区的行进行排名。如果省略PARTITION BY,则所有行将合并成一个分区,然后进行排名。
排序表达式是必需的,可以指定一个或多个表达式以排序行。SORT_EXPRESSION可以是列名称、函数、常量或表达式。
2.2 使用场景及例子
2.2.1 排名
通过order by进行排序得到排名。
SELECT
id,
name,
score,
RANK() OVER(
ORDER BY score DESC) AS ranks
FROM
student_info
2.2.2 分组排名
SELECT
id,
name,
score,
RANK() OVER(PARTITION BY name
ORDER BY score DESC) AS ranks
FROM
student_info
PARTITION BY子句指定了name列作为分区表达式,ORDER BY子句按score列降序排序。
但使用RANK排名有个问题,即字段内容相同时,会得到相同名次,并且后续会跳过几个名次,如图:
3. DENSE_RANK
3.1 介绍
DENSE_RANK()是SQL中的一个窗口函数(window function),用于查询结果中的排名。与RANK()类似,不同之处在于DENSE_RANK()不会出现相同的排名。即使有多个值排名相同,紧随其后的排名也会按照定义进行递增。解决RANK的问题。
3.2 使用场景及例子
3.2.1 排名
通过order by进行排序得到排名。
SELECT
id,
name,
score,
DENSE_RANK() OVER(
ORDER BY score DESC) AS ranks
FROM
student_info
4. NTILE
4.1 介绍
NTILE函数用于将一组数据按照数量均匀地分成若干份,并给每份数据编号。常见的用途是将数据进行分组或划分。
语法:
NTILE(n) OVER (ORDER BY expr)
其中n:指定将数据分成的份数。ORDER BY expr:指定按照哪个表达式来进行排序。
4.2 使用场景及例子
4.2.1 排名
通过order by进行排序得到排名。
SELECT
id,
name,
score,
NTILE(4) OVER(
ORDER BY score DESC) AS ranks
FROM
student_info