mysql8 窗口函数

窗口函数

mysql8增加的一个极其强大的统计函数

窗口函数加排序

// 1,2,3,4,5,6
row_number()over()
row_number()over(partition by cloum1 order by cloum2)

// 1,2,2,3,3,4
dense_rank()over()
dense_rank()over(partition by cloum1 order by cloum2)

// 1,2,2,4,4,6
rank()over()
rank()over(partition by cloum1 order by cloum2)

sum(cloum1) over(order by cloum2)
sum(cloum1)over(partition by cloum1 order by cloum2)

 

 

案例

构建数据表

drop table if exists passing_number;
CREATE TABLE `passing_number` (
`id` int(4) NOT NULL,
`number` int(4) NOT NULL,
PRIMARY KEY (`id`));
 
INSERT INTO passing_number VALUES
(1,4),
(2,3),
(3,3),
(4,2),
(6,4),
(5,5);

测试sql

SELECT
	pn.id,
	pn.`number`,
	RANK() OVER(ORDER BY pn.number DESC) `RANK`,
	DENSE_RANK() OVER(ORDER BY pn.number DESC) `DENSE_RANK`,
	ROW_NUMBER() OVER(ORDER BY pn.number DESC) `row_number`
FROM
	passing_number pn

mysql分析指令：

• DESC
• EXPLAIN