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
