mysql8开窗函数(Window function)
<h3>MySql8开窗函数的基本使用：</h3>
<h6>测试数据表:</h6>
<pre><code>CREATE TABLE `school_score` (
`id` int NOT NULL AUTO_INCREMENT,
`name` char(1) DEFAULT NULL,
`course` char(10) DEFAULT NULL,
`score` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (1, 'A', 'Chinese', 80);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (2, 'B', 'Chinese', 90);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (3, 'C', 'Chinese', 70);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (4, 'A', 'Math', 70);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (5, 'B', 'Math', 100);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (6, 'C', 'Math', 80);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (7, 'A', 'English', 90);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (8, 'B', 'English', 85);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (9, 'C', 'English', 99);
</code></pre>
<ul>
<li>开窗函数排名 row_number () (partition by ) rank 作为关键字 不能用于别名</li>
</ul>
<pre><code> SELECT name,course,score score,
row_number( ) over (PARTITION by course order by score desc) as score_rank
from school_score;</code></pre>
<pre><code> B Chinese 90 1
A Chinese 80 2
C Chinese 70 3
C English 99 1
A English 90 2
B English 85 3
B Math 100 1
C Math 80 2
A Math 70 3
</code></pre>
<ul>
<li>查询各科成绩第一的人 或者前两名</li>
</ul>
<pre><code> SELECT * from (
select name,course,score ,row_number() over (PARTITION by course order by score) as score_rank from school_score
) as a where a.score_rank in (1,2);</code></pre>
<pre><code> C Chinese 70 1
A Chinese 80 2
B English 85 1
A English 90 2
A Math 70 1
C Math 80 2</code></pre>
<ul>
<li>不公平排序 会跳过重复的序号</li>
</ul>
<pre><code>select name,course,rank() over (order by score) as score_rank from school_score;</code></pre>
<pre><code> C Chinese 1
A Math 1
A Chinese 3
C Math 3
B English 5
B Chinese 6
A English 6
C English 8
B Math 9</code></pre>
<ul>
<li>公平排序 dense_rank over ( order by socore) 序号连续 但会出现并列序号 如 1 1 2 3 3 4</li>
</ul>
<pre><code> select name,course,score,dense_rank() over (order by score) as score_rank from school_score;</code></pre>
<pre><code> C Chinese 70 1
A Math 70 1
A Chinese 80 2
C Math 80 2
B English 85 3
B Chinese 90 4
A English 90 4
C English 99 5
B Math 100 6</code></pre>
<ul>
<li>row_number 排序 不会出现并列 1 2 3</li>
</ul>
<pre><code> select name,course,score, row_number() over (order by score) as score_rank from school_score;</code></pre>
<pre><code> C Chinese 70 1
A Math 70 2
A Chinese 80 3
C Math 80 4
B English 85 5
B Chinese 90 6
A English 90 7
C English 99 8
B Math 100 9</code></pre>
<ul>
<li>ntile() 分桶，为结果集按行数分成不同的桶，比如 ntile(2) 就对结果集分分两组 nitil(3) 分三组，以此类推，如果分的组或者桶数不均，第一组或第一桶则数量加1</li>
</ul>
<h5>不均等</h5>
<pre><code>select name,course,score, ntile(4) over(order by score desc) as score_rank from school_score;</code></pre>
<pre><code>
B Math 100 1
C English 99 1
B Chinese 90 1
A English 90 2
B English 85 2
A Chinese 80 3
C Math 80 3
C Chinese 70 4
A Math 70 4</code></pre>
<h5>均等</h5>
<pre><code>select name,course,score, ntile(3) over(order by score desc) as score_rank from school_score;</code></pre>
<pre><code>B Math 100 1
C English 99 1
B Chinese 90 1
A English 90 2
B English 85 2
A Chinese 80 2
C Math 80 3
C Chinese 70 3
A Math 70 3</code></pre>