[大数据]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']