3.窗口函数()-其他

发表于： 4月 1, 2023 4月 1, 2023
分类：未分类

3.窗口函数()

语法：

　　select 排序函数/聚合函数 over (<partition by …> 分区字段 order by 排序字段) #说明：注意over后面有一个空格

说明：　　

　　partiton by是可选的。如果不使用partition by，那么就是将整张表作为一个集合，最后使用排序函数得到的就是每一条记录根据排序列的排序编号。排序函数主要有rank()、dense_rank、row_number，他们主要区别：

rank(): 对同一个字段排序，出现相同时，会并列排名，并且会出现排名间隙。
dense_rank() : 对同一个字段排序，出现相同时，会出现并列排名，排名连续的
row_number(): 对同一个字段排序，排名是联系的，即使出现相同，不会并列排名次

示例：

1) 建表　

CREATE TABLE s_score (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(20),
  score int NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

2）插入数据

INSERT INTO s_score  VALUES(1,'张三', 80),(2,'小明', 90),(3,'小红', 60),(4,'李四', 70),(5,'赵武', 80);

3）查看

root@mysqldb 21:43:  [test]> select * from s_score;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | 张三   |    80 |
|  2 | 小明   |    90 |
|  3 | 小红   |    60 |
|  4 | 李四   |    70 |
|  5 | 赵武   |    80 |
+----+--------+-------+
5 rows in set (0.00 sec)

这里我们看看用上面三个函数进行排名的显示，

SELECT NAME,score,
    RANK () over (ORDER BY score DESC) `rank`,
    ROW_NUMBER () over (ORDER BY score DESC) `row`,
    DENSE_RANK () over (ORDER BY score DESC) `dense`
FROM s_score;

+--------+-------+------+-----+-------+
| NAME   | score | rank | row | dense |
+--------+-------+------+-----+-------+
| 小明   |    90 |    1 |   1 |     1 |
| 张三   |    80 |    2 |   2 |     2 |
| 赵武   |    80 |    2 |   3 |     2 |
| 李四   |    70 |    4 |   4 |     3 |
| 小红   |    60 |    5 |   5 |     4 |
+--------+-------+------+-----+-------+
5 rows in set (0.01 sec)

rank 并列排名会出现排名空隙，dense_rank也会并列排名，但不会出现空隙，row_number是不会并列排名。

————————

语法：