HIVE_HIVE函数_窗口函数_LAG()/LEAD() 详解

 

参考文章：

1.ORALCE函数：LAG()和LEAD() 分析函数详解、

https://blog.csdn.net/pelifymeng2/article/details/70313943

 

 

LAG, LEAD 函数简单介绍

 Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。

  在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是 LAG 和 LEAD 与 left join、right join 等自连接相比，效率更高，SQL更简洁。下面我就对这两个函数做一个简单的介绍。 
 

 

函数语法

lag(exp_str,offset,defval) over(partion by ..order by …)

lead(exp_str,offset,defval) over(partion by ..order by …)

其中exp_str是字段名

     Offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset 为3，则表示我们所要找的数据行就是表中的第2行（即5-3=2）。

     Defval默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag（）函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。
 

例子 

构建表，插入测试数据

use data_warehouse_test;

CREATE TABLE IF NOT EXISTS user_old_salary_info (
	user_name STRING
	,salary_vaild_date STRING
	,salary BIGINT
)
;


INSERT OVERWRITE TABLE user_old_salary_info VALUES
('szh', '2011-11-06', 1000)
,('sx', '2011-11-07', 2000)
,('szh', '2015-06-11', 4000)
,('sx', '2016-07-12', 5000)
,('szh', '2017-08-20', 10000)
,('sg', '2017-08-20', 30000)
,('szh', '2020-06-20', 25000)
;

进行相关查询

use data_warehouse_test;

SELECT * 
FROM user_old_salary_info
;

SELECT user_name, salary, LAG(salary, 1, 0) OVER(PARTITION BY user_name ORDER BY salary_vaild_date) AS last_salary
FROM user_old_salary_info
;

SELECT user_name, salary, LEAD(salary, 1, 0) OVER() AS next_salary
FROM user_old_salary_info
;

 

 

查询表中所有的记录：

SELECT * 
FROM user_old_salary_info
;

+---------------------------------+-----------------------------------------+------------------------------+
| user_old_salary_info.user_name  | user_old_salary_info.salary_vaild_date  | user_old_salary_info.salary  |
+---------------------------------+-----------------------------------------+------------------------------+
| szh                             | 2011-11-06                              | 1000                         |
| sx                              | 2011-11-07                              | 2000                         |
| szh                             | 2015-06-11                              | 4000                         |
| sx                              | 2016-07-12                              | 5000                         |
| szh                             | 2017-08-20                              | 10000                        |
| sg                              | 2017-08-20                              | 30000                        |
| szh                             | 2020-06-20                              | 25000                        |
+---------------------------------+-----------------------------------------+------------------------------+

 

=============================

 

某一个员工 本次的薪水 和 上一次的薪水

SELECT user_name, salary, LAG(salary, 1, 0) OVER(PARTITION BY user_name ORDER BY salary_vaild_date) AS last_salary
FROM user_old_salary_info
;

 

+------------+---------+--------------+
| user_name  | salary  | last_salary  |
+------------+---------+--------------+
| sg         | 30000   | 0            |
| sx         | 2000    | 0            |
| sx         | 5000    | 2000         |
| szh        | 1000    | 0            |
| szh        | 4000    | 1000         |
| szh        | 10000   | 4000         |
| szh        | 25000   | 10000        |
+------------+---------+--------------+

 

=============================

 

顺序查看 本次的薪水 和 上一次的薪水

SELECT user_name, salary, LEAD(salary, 1, 0) OVER() AS next_salary
FROM user_old_salary_info
;

 

+------------+---------+--------------+
| user_name  | salary  | next_salary  |
+------------+---------+--------------+
| szh        | 25000   | 30000        |
| sg         | 30000   | 10000        |
| szh        | 10000   | 5000         |
| sx         | 5000    | 4000         |
| szh        | 4000    | 2000         |
| sx         | 2000    | 1000         |
| szh        | 1000    | 0            |
+------------+---------+--------------+