mysql 窗口函数（二）

作者 | lpl

来源 | lpl （公众号：数据分析从0到1)

前言

上一章节我们讲了序号函数和分布函数，这一章节我们来讲一下前后函数，多用于看近N次的数据对比和头尾函数，多数用于第一个和最后一个数据的对比。

测试数据在文章《mysql窗口函数（一）》

前后函数

前后函数——lead(n)/lag(n)。

• 用途：分区中位于当前行前n行（lead）/后n行(lag)的记录值。

• 使用场景：查询上一个订单距离当前订单的时间间隔。

SELECT order_id,       user_no,       amount,       create_date,       last_date,       datediff(create_date,last_date) dif1FROM  (SELECT order_id,          user_no,          amount,          create_date,          lag(create_date,1) over w AS last_date   FROM order_tab WINDOW w AS (PARTITION BY user_no                               ORDER BY create_date))a;

lag()函数，将user_no分组后的时间整理向下移动了一行，没有的记为null。

lead()函数则和lag()函数相反，将user_no分组后的时间整理向上移动了一行，没有的记为null。

SELECT order_id,       user_no,       amount,       create_date,       lead_date,       datediff(create_date,lead_date) dif2FROM  (SELECT order_id,          user_no,          amount,          create_date,          lead(create_date,1) OVER w AS lead_date   FROM order_tab WINDOW w AS (PARTITION BY user_no                               ORDER BY create_date))a;

头尾函数

头尾函数——first_val(expr)/last_val(expr)。

• 用途：得到分区中的第一个/最后一个指定参数的值。

• 使用场景：查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额。

SELECT order_id,          user_no,          amount,          create_date,          first_value(amount) OVER w as first_amount,          last_value(amount) OVER w as last_amount   FROM order_tab WINDOW w AS (PARTITION BY user_no                               ORDER BY create_date);

结果和预期一致，比如order_id为4的记录，first_amount和last_amount分别记录了用户‘001’截止到时间2018-01-03 00:00:00为止，第一条订单金额100和最后一条订单金额800，注意这里是按时间排序的最早订单和最晚订单，并不是最小金额和最大金额订单。

往期文章回顾

mysql 8.0 安装遇到哪些坑？

mysql 窗口函数（一）

mysql 工作中常用的技能（一）

mysql 工作中常用的技能（二）

mysql 工作中常用的技能（三）

mysql 工作常用的的技能（四）