MySQL 窗口函数OVER实现均线计算

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批量计算均线，也很方便