MySQL 窗口函数OVER实现均线计算
1. 需求
在MySQL数据库中，需要计算均线数据，通过over窗口函数实现。
2. 语法
MySQL从8.0开始支持窗口函数，也叫分析函数。
窗口函数分为静态窗口和滑动窗口，静态窗口的大小是固定的，滑动窗口的大小可以根据设置进行变化，在当前窗口下生成子窗口。
（1）语法格式
通用的语法格式：
函数名([参数])
over(
partition by [分组字段]
order by [排序字段] asc/desc
rows/range between 起始位置 and 结束位置
)核心语句：
- 分区参数：partition by
- 排序参数：order by
- 框架参数：between … and …
（2）窗口汇聚函数
<1>静态窗口函数
三个排名函数：
- row_number()： 按照值排序时产生一个自增编号，不会重复(如:1、2、3、4、5、6)
- rank() :按照值排序时产生一个自增编号，值相等时会重复，会产生空位(如:1、2、3、3、3、6)
- dense_rank() : 照值排序时产生一个自增编号，值相等时会重复，不会产生空位(如:1、2、3、3、3、4)
<2>动态窗口函数
聚合函数
- sum() 求和
- avg() 求平均
- max() 求最大值
- min() 求最小值
- percent_rank() 累计百分比
- cume_dist() 累计分布值
取值函数
- first_value() 返回分组内第一个值
- last_value() 返回分组内最后一个值
- nth_value() 返回分组内第N行
- lag(列名,往前的行数,[行数为null时的默认值，不指定为nul]):可以计算用户上次购买时间，或者用户下次购买时间。
- lead(列名,往后的行数,[行数为null时的默认值，不指定为null])
- ntile()将有序分区中的行分发到指定数量的近似大小的组中，即“分区”。n 是你希望将分区中的行分成的组数。NTILE() 函数为每个组分配一个唯一的序号（从1到n），序号基于行在分区内的排名。
（3）窗口定义
在between and 语句中定义，分为两类 rows 和 range：
<1> rows
- rows: 表示按照行的范围进行定义框架，根据order by子句排序后，取的前n行及后n行的数据计算（与当前行的值无关，只与排序后的行号相关）。常用：rows n perceding表示从当前行到前n行（一共n+1行）
rows 的参数说明：- current row 当前行
- n preceding 前 n 行，n 为数字， 比如 2 preceding 表示前2行
- unbounded preceding 开头（默认值）
- n following 后n行，n 为数字， 比如 2 following 表示后2行
- unbounded following 结尾（默认值）
举例说明：
rows between 2 preceding and current row # 取当前行和前面两行
rows between unbounded preceding and current row # 包括本行和之前所有的行
rows between current row and unbounded following # 包括本行和之后所有的行
rows between 3 preceding and current row # 包括本行和前面三行
rows between 3 preceding and 1 following # 从前面三行和下面一行，总共五行说明：
当order by后面缺少窗口从句条件，窗口规范默认是rows between unbounded preceding and current row.
当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded preceding and unbounded following
<2> range
- range：表示按照值的范围进行定义框架，根据order by子句排序后，指定当前行对应值的范围取值，行数不固定，只要行值在范围内，对应行都包含在内。适用于对日期、时间、数值排序分组
示例：- range interval 7-1 day preceding 最近7天的值
- range between interval 1 day preceding and interval 1 day following 前后一天和当天的值
3. 计算均线
计算 5日，10日均线：
select code, date, `close`, round(avg(`close`) over (partition by code order by date rows between 4 preceding and current row),3) as avg_5_day, round(avg(`close`) over (partition by code order by date rows between 9 preceding and current row),3) as avg_10_day
from ts_stock_daily t where t.date>'2022-01-01' and t.date<'2024-01-01' ;
说明：
between 4 preceding and current row ，从当前行向前4行，5日均线。
用MySQL批量计算均线，也很方便