mysql窗口无法最大话_7、MySQL高级功能（窗口函数）

一、窗口函数

1、基本语法

over(partition by order by)

2、大概分类专用窗口函数

比如 rank，dense_rank， row_number等聚合函数

比如 sum，count，avg，max，min等

二、如何使用窗口函数(聚合函数作为窗口函数)

1、聚合函数 + over()函数问题：求每个店铺，每天的销量和全部销量的均值对比；店铺销量表——shop_sale

-- 一般的解决办法select shopname，

sales，

sale_date,

(select avg(sales) from shop_sales) avg_sales

form shop_sale;

-- 用窗口函数来解决select shopname，

sales，

sale_date,

avg(sales) over() as avg_sales

form shop_sale;

第一段代码虽然可以实现我们的需求，但代码略繁琐，窗口函数可以简化代码；

over()函数的作用，就是将聚合结果显示在每条单独的记录中。

2、partition by 子句

上面的例子，展示的是“每个店铺，每天销量和全部销量均值作比较”；

但如果店铺之间的差异不大，那么用全部销量的均值作为比较标准还可以，如果店铺之间的销量差异很大，那么就不具备可比性了，店铺销量自己和自己比；

这里的需求分成2部分，一是所有店铺的每日销量，二是店铺的销量均值；

-- 一般的解决方案select shopname，

sales，

sale_date,

b.avg_sales

form shop_sale a

left join

(select shopname, avg(sales) avg_sales

from shop_sale

group by shopname) b

on a.shopname = b.shopname

-- 用窗口函数select shopname，

sales，

sale_date,

avg(sales) over(partition by shopname) as avg_sales

form shop_sale

partition by 的作用类似于 group by，但确实在over()函数中使用，按照指定的列进行分组，聚合函数就会在分好的组内运算。

3、order by 子句

order by 子句比较好理解，即在over()函数中进行指定的排序；

为什么需要order by子句呢？还是用上面的例子。

上面的例子，考虑到了店铺之间销量的差异问题，所以店铺用自己的销量均值作比较，但还是不够严谨，因为我们不能用未来的信息和现在的信息进行比较；

比如 1月2日的销量，在求均值时就不应该包含1月3日的销量，而只应该包括1月2日及之前的销量，这种聚合方式也被叫做【顺序聚合】。

select shopname，

sales，

sale_date,

avg(sales) over(partition by shopname order by sale_date) as avg_sales

form shop_sale3

三、专用窗口函数

1、序列函数：rank，dense_4rank，row_number，以及3者的区别：

select * ，

rank() over(order by 成绩 desc) as ranking,

dense_rank() over(order by 成绩 desc) as dense_rank,

row_number() over(order by 成绩 desc) as row_num

from score

案例：如何使用排名函数解决 Top N 问题——每组最大/小的N条记录按课程号分组取成绩最大值所在行的数据笨办法 , 关联子查询

select 学号, 课程号, 成绩

from score a

where 成绩 = (

select max(成绩)

from score b

where a.课程号 = b.课程号)

order by 课程号;窗口函数的解题思路

select *

from (

select 学号, 课程号, 成绩, rank() over(partition by 课程号 order by 成绩 desc) '排名'

from score) c

where c.排名 = 1

2、ntile()函数

ntile()函数，是在默认不对表进行任何操作之前，进行切片分组。

select shopname, sales, sale_date,

ntile() over(partition by shopname order by sales) 'cut_group'

from shopsale

先按照 shopname 分组，然后在组内按照销量升序排列，最后切片分组

3、lag() 和 lead() 函数

lag()是指向后移动，lead()是指向前移动；怎么理解这2个函数？

lag()，就相当于在Excel中，某个单元格前面插入一行时，【活动单元格下移】；

lead()，则相当于【活动单元格上移】，只不过Excel中没有这个选项罢了；这2个函数有什么用？

比如说：按照shopname分组，求各分组内，前后2天的销量之差。

select shopname, sales, sale_date,

lag(sales, 1) over(partition by shopname order by sale_date) lag_value,

lead(sales, 1) over(partition by shopname order by sale_date) lead_value

from shopsale运行结果

4、移动平均

rows 行数 preceding，表示本行以及前2行的平均。

select *，

avg() over(order by rows 行数 preceding) as cur_avg

from table

四、总结

1、窗口函数的功能：1、同时具有分组和排序的功能；

2、不减少原表的行数，经常用于组内排名；

2、注意事项：窗口函数是对where 或 group by 子句处理后的结果进行操作，所以窗口函数理论上只能写在select子句中。

3、窗口函数的使用场景：1、Top N问题

2、排名问题

3、组内比较问题(聚合函数)

4、移动平均