MYSQL-窗口函数学习总结

窗口函数不是将数据汇总成单个结果，而是为每一行数据都返回一个结果。

完整的窗口函数的定义：

window_function over(partition by...-- 分区参数 order by... -- 排序参数 frame_clause -- 窗口大小 );

以下代码所用数据集在之前的文章中可以找到。

select name , dept_id,sum(salary) over(partition by dept_id) from emp; select dept_id ,sum(salary) from emp group by dept_id; -- order by 用于指定分区内的数据的排序方式，作用类似于查询语句中的order by 子句。 select name ,salary, dept_id,sum(salary) over(partition by dept_id order by salary) from emp;

聚合窗口函数

移动平均值：avg 函数作为窗口函数使用时，可以用于计算随着当前行移动的窗口内数据行的平均值。例，查找不同产品截止每个月，最近三个月的平均销量。

create table sales_monthly( product varchar(10), ym int, amount int ); insert into sales_monthly values ('橘子',201801,10128),('橘子',201802,10221),('橘子',201803,10257),('橘子',201804,10386),('橘子',201805,11400),('橘子',201806,10566),('橘子',201807,10623),('橘子',201808,10697)，('苹果',201801,10118),('苹果',201802,10211),('苹果',201803,10247),('苹果',201804,10286),('苹果',201805,11410),('苹果',201806,10565),('苹果',201807,10633),('苹果',201808,10687); select product as '产品', ym '年月', amount '销量', avg(amount) over (partition by product order by ym rows between 2 preceding and current row) as '平均销量 ' from sales_monthly order by product, ym; -- 当前平均值取的是当前日期和前两天的平均值。

也可更改为，当前日期为中间值

select product as '产品', ym '年月', amount '销量', avg(amount) over (partition by product order by ym rows between 1 preceding and 1 following) as '平均销量 ' from sales_monthly order by product, ym;

当不指定时，就是累计结果。

select product as '产品', ym '年月', amount '销量', avg(amount) over(partition by product order by ym) as '平均销量 ' from sales_monthly order by product,ym;

range关键字为单位指定窗口的偏移量。比如 查找短期内（5天）累计转账超过100万元的账号：

select log_ts,from_user,total_amount from ( select log_ts,from_user,sum(amount) over ( partition by from_user order by log_ts range interval '5' day preceding) as total_amount from transfer_log where type = '转账') t where total_amount >=1000000;

排名窗口函数

可以用来获取数据的分类排名。 常见的排名窗口函数如下：
row_number ：函数可以为分区中的每行数据分配一个序列号，序列号从1开始。
rank ：函数返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃。
dense_rank： 函数返回当前行在分区中的名次。即使存在名次相同的数据，后续排名也是连续值。
percent_rank ：函数以百分比的形式返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃。
cume_dist ：函数计算当前行在分区内的积累分布。
ntile：函数将分区内的数据分为N等分，并返回当前行所在分片的位置。
排名窗口函数不支持动态的窗口大小选项，而是以整个分区作为分析的窗口。

排名窗口函数不支持动态的窗口大小选项，而是以整个分区作为分析的窗口。

select name,dept_id,salary, row_number() over (partition by dept_id order by salary desc), rank() over (partition by dept_id order by salary desc), dense_rank() over (partition by dept_id order by salary desc), percent_rank() over (partition by dept_id order by salary desc) from emp; -- 可以用Window 封装 为 w window w as (partition by dept_id order by salary desc); (要在后面定义。） select name,dept_id,salary, row_number() over w, rank() over w, dense_rank() over w, percent_rank() over w from emp window w as (partition by dept_id order by salary desc);

cume_dist 函数返回当前行在分区内的累积分布，也就是排名在当前行之前（包含当前行）所有数据所占的比率，取值范围大于0且小于或等于1.

-- ；例如 查询返回所有员工按照月薪排名的累积分布情况： select name '姓名',salary, cume_dist() over( order by salary) '累计占比' from emp;

ntile 函数用于将分区内的数据分成N等分，并计算当前行所在的分片位置。

-- 例，以下语句将员工按照入职先后顺序分为5组，并计算每个员工所在的分组； select name '姓名',entrydate , ntile(5) over (order by entrydate) from emp;

取值窗口函数

lag函数返回窗口内当前行之前的第N行数据；
lead函数可以返回窗口内当前行之后的第N行数据。
first_value 返回窗口内第一行数据。
last_value 返回窗口内最后一行数据。

nth_value 返回窗口内第N行数据。

-- 案例分析：环比、同比分析 -- 环比是本期与上期数据相比的增长，例，产品6月的销量与五月的销量相比增加的部分； select product,ym,(amount-pre_amount)/pre_amount delta from( select * ,lag(amount,1) over (partition by product order by ym) pre_amount from sales_monthly) t; -- 同比增长 今年6月和 上一年6 月的销售相比增长的部分。lag(amount,12)当数据中每个月份的数据都有时，该字段表示当前月份之前第12期的销量，即去年同月份的销量 -- 复合增长率 每一期和第一期作比较

LAG(column, n, default_value)中的n表示向前第n行，default_value指定无数据时的默认值（如0或NULL）。

三类核心窗口函数对比

总结

1. 灵魂在于OVER()子句：定义PARTITION BY（分组）和ORDER BY（排序）是写出正确窗口函数的关键。

2. 性能关键：在PARTITION BY和ORDER BY涉及的列上建立索引，能极大提升执行效率。

3. 组合使用：窗口函数可以组合。例如，可以先计算累计值（聚合函数），再对累计值进行排名。

   SELECT *, RANK() OVER (ORDER BY cumulative_amount DESC) FROM ( SELECT *, SUM(amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS cumulative_amount FROM sales ) t;