MYSQL窗口函数用法

1,顿号 插入数据

insert into order_table VALUES(1,001,100,'2018-01-01 00:00:00');
insert into order_table VALUES(2,001,300,'2018-01-02 00:00:00');
insert into order_table VALUES(3,001,500,'2018-01-02 00:00:00');
insert into order_table VALUES(4,001,800,'2018-01-03 00:00:00');
insert into order_table VALUES(5,001,900,'2018-01-04 00:00:00');
insert into order_table VALUES(6,002,500,'2018-01-03 00:00:00');
insert into order_table VALUES(7,002,600,'2018-01-04 00:00:00');
insert into order_table VALUES(8,002,300,'2018-01-10 00:00:00');
insert into order_table VALUES(9,002,800,'2018-01-16 00:00:00');
insert into order_table VALUES(10,002,800,'2018-01-22 00:00:00');

　　

按照功能划分，可以把MySQL支持的窗口函数分为如下几类：

• 序号函数：row_number() / rank() / dense_rank()
• 分布函数：percent_rank() / cume_dist()
• 前后函数：lag() / lead()
• 头尾函数：first_val() / last_val()
• 其他函数：nth_value() / nfile()

  select order_id,user_no,amout,creat_date,row_number()over(partition by user_no order by amout desc) as rank_no from order_table
  

  　　

  三、窗口函数如何使用

  窗口函数的基本用法如下：

  函数名（[expr]） over子句

  其中，over是关键字，用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下四种语法来设置窗口：

  • window_name：给窗口指定一个别名，如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读。上面例子中如果指定一个别名w，则改写如下：

  select * from

  (

  select row_number()over w as row_num,

  order_id,user_no,amount,create_date

  from order_tab

  WINDOW w AS (partition by user_no order by amount desc)

  )t ;

  • partition子句：窗口按照那些字段进行分组，窗口函数在不同的分组上分别执行。上面的例子就按照用户id进行了分组。在每个用户id上，按照order by的顺序分别生成从1开始的顺序编号。
  
  
  • order by子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition子句配合使用，也可以单独使用。上例中二者同时使用，如果没有partition子句，则会按照所有用户的订单金额排序来生成序号。
  • frame子句：frame是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的平均订单金额，则可以设置如下frame子句来创建滑动窗口：