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

时间：2024-09-29 17:53:14

 前言

本篇将通过示例讲解：偏移分析函数+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. 

  2. 

    

    	user_name,
    

    3. 

  3. 

    

    	pay_time,
    

    4. 

  4. 

    

    	lag( pay_time, 1, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lag1,
    

    5. 

  5. 

    

    	lag( pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lag2,
    

    6. 

  6. 

    

    	lag( pay_time, 2, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lag3,
    

    7. 

  7. 

    

    	lag( pay_time, 2 ) over ( PARTITION BY user_name ORDER BY pay_time ) lag4 
    

    8. 

  8. 

    

    FROM
    

    9. 

  9. 

    

    	user_order 
    

    10. 

  10. 

    

    WHERE
    

    11. 

  11. 

    

    	user_name IN ( 'lisi', 'yantian' )
    

    12. 

  12. 

    

    复制代码
    

    13.

运行结果

高级应用窗口函数（四）

2.2 案例

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

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

    

  1. 

    

    SELECT
    

    2. 

  2. 

    

    	user_name,
    

    3. 

  3. 

    

    	pay_time,
    

    4. 

  4. 

    

    	lead( pay_time, 1, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lead1,
    

    5. 

  5. 

    

    	lead( pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lead2,
    

    6. 

  6. 

    

    	lead( pay_time, 2, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lead3,
    

    7. 

  7. 

    

    	lead( pay_time, 2 ) over ( PARTITION BY user_name ORDER BY pay_time ) lead4 
    

    8. 

  8. 

    

    FROM
    

    9. 

  9. 

    

    	user_order 
    

    10. 

  10. 

    

    WHERE
    

    11. 

  11. 

    

    	user_name IN ( 'lisi', 'yantian' );
    

    12. 

  12. 

    

    复制代码
    

    13.

运行结果

高级应用窗口函数（四）

2.3 案例

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

分析：

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

实现步骤

    

  1. 

    

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

    2. 

  2. 

    

    (2)通过上一步将支付时间做差,然后对用户去重，再进行count操作
    

    3. 

  3. 

    

    复制代码
    

    4.

实现

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

    

  1. 

    

    SELECT
    

    2. 

  2. 

    

    	user_name,
    

    3. 

  3. 

    

    	pay_time,
    

    4. 

  4. 

    

    	lead( pay_time, 1 ) over ( PARTITION BY user_name ORDER BY pay_time ) lead_time 
    

    5. 

  5. 

    

    FROM
    

    6. 

  6. 

    

    	user_order;
    

    7. 

  7. 

    

    复制代码
    

    8.

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

    

  1. 

    

    SELECT
    

    2. 

  2. 

    

    	count( DISTINCT user_name ) 
    

    3. 

  3. 

    

    FROM
    

    4. 

  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. 

  5. 

    

    WHERE
    

    6. 

  6. 

    

    	datediff( a.lead_time, a.pay_time )> 10;
    

    7. 

  7. 

    

    复制代码
    

    8.

高级应用窗口函数（四）

2.4 案例

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

实现步骤

    

  1. 

    

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

    2. 

  2. 

    

    (2)计算订单时间间隔,在年度内按照各自的订单时间间隔进行排名
    

    3. 

  3. 

    

    (3)将上方查询结果作为表进行子查询,取得排名结果为1的值
    

    4. 

  4. 

    

    复制代码
    

    5.

实现

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

    

  1. 

    

    
SELECT YEAR
    

    2. 

  2. 

    

    	( pay_time ) year_time,
    

    3. 

  3. 

    

    	user_name,
    

    4. 

  4. 

    

    	pay_time,
    

    5. 

  5. 

    

    	lag( pay_time ) over ( PARTITION BY user_name, YEAR ( pay_time ) ORDER BY pay_time ASC ) lag_time 
    

    6. 

  6. 

    

    FROM
    

    7. 

  7. 

    

    	user_order;
    

    8. 

  8. 

    

    复制代码
    

    9.

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

    

  1. 

    

    SELECT
    

    2. 

  2. 

    

    	a.year_time,
    

    3. 

  3. 

    

    	a.user_name,
    

    4. 

  4. 

    

    	datediff( a.pay_time, a.lag_time ) interval_days,
    

    5. 

  5. 

    

    	rank() over ( PARTITION BY a.year_time ORDER BY datediff( a.pay_time, a.lag_time ) DESC ) rank1 
    

    6. 

  6. 

    

    FROM
    

    7. 

  7. 

    

    	(
    

    8. 

  8. 

    

    	SELECT YEAR
    

    9. 

  9. 

    

    		( pay_time ) year_time,
    

    10. 

  10. 

    

    		user_name,
    

    11. 

  11. 

    

    		pay_time,
    

    12. 

  12. 

    

    		lag( pay_time ) over ( PARTITION BY user_name, YEAR ( pay_time ) ORDER BY pay_time ASC ) lag_time 
    

    13. 

  13. 

    

    	FROM

    

    14. 

  14. 

    

    		user_order 
    

    15. 

  15. 

    

    	) a;
    

    16. 

  16. 

    

    复制代码
    

    17.

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

    

  1. 

    

    SELECT
    

    2. 

  2. 

    

    	b.year_time,
    

    3. 

  3. 

    

    	b.user_name,
    

    4. 

  4. 

    

    	b.interval_days 
    

    5. 

  5. 

    

    FROM
    

    6. 

  6. 

    

    	(
    

    7. 

  7. 

    

    	SELECT

    

    8. 

  8. 

    

    		a.year_time,
    

    9. 

  9. 

    

    		a.user_name,
    

    10. 

  10. 

    

    		datediff( a.pay_time, a.lag_time ) interval_days,
    

    11. 

  11. 

    

    		rank() over ( PARTITION BY a.year_time ORDER BY datediff( a.pay_time, a.lag_time ) DESC ) rank1 
    

    12. 

  12. 

    

    	FROM

    

    13. 

  13. 

    

    		(
    

    14. 

  14. 

    

    		SELECT YEAR
    

    15. 

  15. 

    

    			( pay_time ) year_time,
    

    16. 

  16. 

    

    			user_name,
    

    17. 

  17. 

    

    			pay_time,
    

    18. 

  18. 

    

    			lag( pay_time ) over ( PARTITION BY user_name, YEAR ( pay_time ) ORDER BY pay_time ASC ) lag_time 
    

    19. 

  19. 

    

    		FROM

    

    20. 

  20. 

    

    			user_order 
    

    21. 

  21. 

    

    		) a 
    

    22. 

  22. 

    

    	) b
    

    23. 

  23. 

    

    	where 1 = 1;
    

    24. 

  24. 

    

    复制代码
    

    25.

运行结果

高级应用窗口函数（四）



链接：/post/7035169694529110024

相关文章