rank() over, dense_rank(), row_number()
时间: 2023-11-07 15:02:12 浏览: 35
rank() over、dense_rank()和row_number()都是窗口函数,用于在查询结果中添加排名信息。
rank() over函数用于计算结果集中每一行的排名,相同数值的行将被赋予相同的排名,并且紧随其后的排名将被跳过。例如,在班级成绩查询中,如果有两个学生得分相同并列第一名,则下一个学生将自动跳过并列为第三名。
dense_rank()函数也用于计算排名,但它不会跳过相同排名的行。即使有多个行具有相同的数值,它们也将被赋予不同的排名。在班级成绩查询中,如果有两个学生得分相同并列第一名,则下一个学生将被赋予第二名。
row_number()函数用于为结果集中的每一行分配一个唯一的行号。无论结果集中是否有相同的值,每一行都将有一个唯一的行号。在班级成绩查询中,无论得分如何,每个学生都将有一个独立的行号。
相关问题
row_number和rank和dense_rank
row_number、rank和dense_rank都是用于窗口函数中的排序函数。
- row_number:为每一行分配一个唯一的数字,按照指定的排序顺序(默认是升序)进行排序。
- rank:为相同的值分配相同的排名,不连续。例如,如果有两个值的排名为1,则下一个值的排名将为3(跳过2)。
- dense_rank:为相同的值分配相同的排名,连续。例如,如果有两个值的排名为1,则下一个值的排名将为2。
举个例子:
```
SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
RANK() OVER (ORDER BY score DESC) AS rank_num,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num
FROM students;
```
以上SQL语句将学生表按照成绩从高到低排序,并为每个学生分配row_number、rank和dense_rank。结果可能如下:
```
| name | score | row_num | rank_num | dense_rank_num |
|-------|-------|---------|----------|----------------|
| Tom | 90 | 1 | 1 | 1 |
| Jack | 85 | 2 | 2 | 2 |
| Alice | 85 | 3 | 2 | 2 |
| Lucy | 80 | 4 | 4 | 3 |
| Bob | 70 | 5 | 5 | 4 |
```
可以看到,Tom的成绩最高,所以他的row_number、rank和dense_rank都是1;Jack和Alice的成绩相同,所以他们的rank是2,但是dense_rank都是2;Lucy的成绩排在第4位,所以他的rank是4,但是dense_rank是3。Bob的成绩最低,所以他的row_number、rank和dense_rank都是5。
Hive sql rank dense_rank row_number
在 Hive SQL 中,可以使用窗口函数(Window Function)来实现类似于 rank、dense_rank 和 row_number 这样的排名函数。
1. RANK
RANK 函数用于计算排名,返回每个分组中按照指定的列排序后的排名。如果有多条记录的排序值相同,则它们的排名也相同,并且下一个排名会跳过相应的记录数。例如:
```sql
SELECT name, score, RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rank FROM mytable;
```
2. DENSE_RANK
DENSE_RANK 函数和 RANK 函数类似,但是它不会跳过相同排名的记录,而是按照相同排名的记录数进行计算排名。例如:
```sql
SELECT name, score, DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS dense_rank FROM mytable;
```
3. ROW_NUMBER
ROW_NUMBER 函数用于为每条记录分配一个唯一的行号,不考虑分组和排序。例如:
```sql
SELECT name, score, ROW_NUMBER() OVER () AS row_number FROM mytable;
```
这些窗口函数可以根据需要进行组合使用,以实现更加复杂的排名需求。需要注意的是,窗口函数只能在 SELECT 子句和 ORDER BY 子句中使用,不能用于 WHERE 子句和 HAVING 子句中。