关注MySQL高级应用窗口函数之偏移分析函数

时间:2024-10-03 07:17:33

前言

本篇将通过示例讲解:偏移分析函数+over()

一、偏移分析函数概念

  • lag(col,n,default):用于统计分组内往上第n行值。

    • 第一个参数为列名
    • 第二个参数为往上第n行(可选,不填默认为1)
    • 第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)
  • lead(col,n,default):与lag相反,统计分组内往下第n行值。

    • 第一个参数为列名
    • 第二个参数为往下第n行(可选,不填默认为1)
    • 第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)
  • first_value(col):用于取分组内排序后,截止到当前行,第一个col的值。

  • last_value(col):用于取分组内排序后,截止到当前行,最后一个col的值。

二、示例讲解

2.1 案例

查询出用户【yantian】和【lisi】的时间偏移(前N行)

分析:通过lag()窗口函数进行实现

  1. SELECT
  2. user_name,
  3. pay_time,
  4. lag( pay_time, 1, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lag1,
  5. lag( pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lag2,
  6. lag( pay_time, 2, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lag3,
  7. lag( pay_time, 2 ) over ( PARTITION BY user_name ORDER BY pay_time ) lag4
  8. FROM
  9. user_order
  10. WHERE
  11. user_name IN ( 'lisi', 'yantian' )
  12. 复制代码

运行结果

高级应用窗口函数(四)

2.2 案例

查询出用户【yantian】和【lisi】的时间偏移(后N行)

分析:通过lead()窗口函数进行实现

  1. SELECT
  2. user_name,
  3. pay_time,
  4. lead( pay_time, 1, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lead1,
  5. lead( pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lead2,
  6. lead( pay_time, 2, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lead3,
  7. lead( pay_time, 2 ) over ( PARTITION BY user_name ORDER BY pay_time ) lead4
  8. FROM
  9. user_order
  10. WHERE
  11. user_name IN ( 'lisi', 'yantian' );
  12. 复制代码

运行结果

高级应用窗口函数(四)

2.3 案例

查询出支付时间间隔超过10天的用户数

分析:

  • 同一用户,相邻的订单进行下单时间比较,如果相邻订单下单时间间隔超过10天,那么这个用户就是需要统计的
  • 在这个过程中,需要进行相邻订单支付时间相减

实现步骤

  1. (1)通过lead窗口函数根据支付时间进行分组,将下一行支付时间移到上一行当中
  2. (2)通过上一步将支付时间做差,然后对用户去重,再进行count操作
  3. 复制代码

实现

步骤一:通过lead窗口函数根据支付时间进行分组,将下一行支付时间移到上一行当中

  1. SELECT
  2. user_name,
  3. pay_time,
  4. lead( pay_time, 1 ) over ( PARTITION BY user_name ORDER BY pay_time ) lead_time
  5. FROM
  6. user_order;
  7. 复制代码

步骤二:通过上一步将支付时间做差,然后对用户去重,再进行count操作

  1. SELECT
  2. count( DISTINCT user_name )
  3. FROM
  4. ( SELECT user_name, pay_time, lead( pay_time, 1 ) over ( PARTITION BY user_name ORDER BY pay_time ) lead_time FROM user_order ) a
  5. WHERE
  6. datediff( a.lead_time, a.pay_time )> 10;
  7. 复制代码

高级应用窗口函数(四)

2.4 案例

查询出每年支付时间间隔最长的用户

实现步骤

  1. (1)根据用户和支付年份进行分组,通过lag()窗口函数将上一行的支付时间合并到下一行
  2. (2)计算订单时间间隔,在年度内按照各自的订单时间间隔进行排名
  3. (3)将上方查询结果作为表进行子查询,取得排名结果为1的值
  4. 复制代码

实现

步骤一:根据用户和支付年份进行分组,通过lag()窗口函数将上一行的支付时间合并到下一行

  1. SELECT YEAR
  2. ( pay_time ) year_time,
  3. user_name,
  4. pay_time,
  5. lag( pay_time ) over ( PARTITION BY user_name, YEAR ( pay_time ) ORDER BY pay_time ASC ) lag_time
  6. FROM
  7. user_order;
  8. 复制代码

步骤二:计算订单时间间隔,在年度内按照各自的订单时间间隔进行排名

  1. SELECT
  2. a.year_time,
  3. a.user_name,
  4. datediff( a.pay_time, a.lag_time ) interval_days,
  5. rank() over ( PARTITION BY a.year_time ORDER BY datediff( a.pay_time, a.lag_time ) DESC ) rank1
  6. FROM
  7. (
  8. SELECT YEAR
  9. ( pay_time ) year_time,
  10. user_name,
  11. pay_time,
  12. lag( pay_time ) over ( PARTITION BY user_name, YEAR ( pay_time ) ORDER BY pay_time ASC ) lag_time
  13. FROM
  14. user_order
  15. ) a;
  16. 复制代码

步骤三:将上方查询结果作为表进行子查询,取得排名结果为1的值

  1. SELECT
  2. b.year_time,
  3. b.user_name,
  4. b.interval_days
  5. FROM
  6. (
  7. SELECT
  8. a.year_time,
  9. a.user_name,
  10. datediff( a.pay_time, a.lag_time ) interval_days,
  11. rank() over ( PARTITION BY a.year_time ORDER BY datediff( a.pay_time, a.lag_time ) DESC ) rank1
  12. FROM
  13. (
  14. SELECT YEAR
  15. ( pay_time ) year_time,
  16. user_name,
  17. pay_time,
  18. lag( pay_time ) over ( PARTITION BY user_name, YEAR ( pay_time ) ORDER BY pay_time ASC ) lag_time
  19. FROM
  20. user_order
  21. ) a
  22. ) b
  23. where 1 = 1;
  24. 复制代码

运行结果

高级应用窗口函数(四)