编程教育资源分享平台

网站首页 > 后端开发 正文

mysql,hive函数lead向上填充,lag向下填充

luoriw 2024-04-04 12:50:52 后端开发 12 ℃ 0 评论

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

本文暂时没有评论,来添加一个吧(●'◡'●)

欢迎 发表评论:

最近发表
标签列表
最新留言