玖叶教程网

mysql,hive函数

lead向上填充,

lag向下填充

原数据如下 -----

SELECT id,name,nums,
	sum(nums) over() as sum11
	,sum(nums) over(ORDER BY id) as sum12
	-- ,sum(nums) over(PARTITION by id ORDER BY nums desc) as sum13
	,sum(nums) over(PARTITION by id ORDER BY nums ) as sum123
	
	-- 1表示向下取一个，'00'表示为NULL时用'00'填充
	,lag(nums,1,'00') over(PARTITION by id ORDER BY nums) as lag1

	-- 1表示向下取一个，表示为NULL时用NULL填充
	,lag(nums,1) over(PARTITION by id ORDER BY nums) as lag1_

	-- 1表示向下取二个，nums表示为NULL时用nums列填充
	,lag(nums,2,nums) over(PARTITION by id ORDER BY nums) as lag2
	
	-- 1表示向上取一个，'dd'表示为NULL时用'dd'表示
	,lead(nums,1,'dd') over(PARTITION by id ORDER BY nums) as lead1
from aaaa1

查询结果如下图：

2021-12-11