mysql 窗口函数（二）

数据分析从0到1 2021-11-20

560

前言

上一章节我们讲了序号函数和分布函数，这一章节我们来讲一下前后函数，多用于看近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) dif1
FROM
  (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) dif2
FROM
  (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);