hive之偏移分析窗口函数

说明：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）。offset 默认值为1。 defval默认值，当这两个函数取上N/下N个值时，在 表中从当前行位置向前数N行已经超出了表的范围 时，lag()函数将defval这个参数值作为函数的返回 值，若没有指定默认值，则返回NULL。lead()函数也 是一样的道理。那么在数学运算中，总要给一个默认 值才不会出错。

lag例子

SELECT user_name,
       dt,
       lag(dt,1,dt) over(partition by user_name order by dt),
       lag(dt) over(partition by user_name order by dt),
       lag(dt,2,dt) over(partition by user_name order by dt),
       lag(dt,2) over(partition by user_name order by dt)
FROM user_trade
WHERE dt>0 and user_name=Alice;

– 需求8：支付时间间隔超过100天的用户数

select count(distinct a.user_name)
from
(select user_name,dt,
lead(dt,1,dt) over(partition by user_name order by dt)as lead_dt
from user_trade
where year(dt)>0)a
where datediff(a.lead_dt,a.dt)>100;

注意:lead是将往后推 将当前的dt往后推一个进行做差,datediff是将两个时间做差求出天数来

– 需求9：每个城市，不同性别，2018年支付金额最高的TOP3用户 /* 1.2018年用户支付总金额求出来 2.获取所需的用户信息及排序序号 3.筛选最终数据 */

select *
from
(select a.user_name,b.city,b.sex,a.total_amount,
row_number()over(partition by b.city,b.sex order by a.total_amount desc)as num_rank
from
(select user_name,sum(pay_amount)as total_amount
from user_trade
where year(dt)=2018
group by user_name)a
left join user_info b
on a.user_name=b.user_name)c
where c.num_rank<=3;

– 需求10：每个手机品牌退款金额前25%的用户 /* 1.每个用户的退款总金额 2.获取用户手机品牌进关联,然后将手机品牌进行分组,按退款金额进行排序 3.切片,取出前25%的用户 */

select *
from
(select a.user_name,b.extra2[phonebrand],a.total_refund,
ntile(4)over(partition by b.extra2[phonebrand] order by a.total_refund desc)rank_lv
from
(select user_name,sum(refund_amount) as total_refund
from user_refund
where year(dt)>0
group by user_name)a
left join user_info b
on a.user_name=b.user_name)c
where c.rank_lv=1;

– json字符串

get_json_object(extra1, $.phonebrand)

– map类型写法

extra2[phonebrand]