hive求近三个月数据的平均环比值

计算公式已在摘要给出
假设有表t1，用户id，金额amount，交易日期bill_date
首先要计算出每月的汇总金额

with t2 as 
select
id,
sum(amount) sum_amount,
date_format(bill_date,'yyyy-MM') date_month
from t1
group by id,
date_format(bill_date,'yyyy-MM')

然后，使用开窗函数row_number() over()和lead（）,以id分组，以时间倒序排序，lead分别取上个月，上上个月，上上上个月的汇总金额，最后count开窗，是为了统计有几个月发生交易

with t3 as
select
t2.id,
t2.date_month,
t2.sum_amount,
row_number() over(partition by t2.id order by t2.date_month desc) rk,
lead(t2.sum_amount,1,null) OVER(PARTITION BY t2.id ORDER BY t2.date_month desc) last_one,
lead(t2.sum_amount,2,null) OVER(PARTITION BY t2.id ORDER BY t2.date_month desc) last_two,
lead(t2.sum_amount,3,null) OVER(PARTITION BY t2.id ORDER BY t2.date_month desc) last_three,
count(t2.date_month) OVER(PARTITION BY t2.id) count_month
from t2

最后使用coalesce函数，此函数用法，将字段放入其中从前往后一直取到不为null的值
rk=1，为了祛去除多余数据，因为已经将前几个月数据已经取到

select
t3.id,
(CASE WHEN t3.count_month = 4 THEN (t3.sum_amount / t3.last_one + t3.last_one / t3.last_two + t3.last_two / t3.last_three) / (t3.count_month - 1)
when t3.count_month = 3 THEN (coalesce(t3.sum_amount,t3.last_one) / coalesce(t3.last_one,t3.last_two) + coalesce(t3.last_one,t3.last_two) /coalesce(t3.last_two,t3.last_three)) / (t3.count_month - 1)
when t3.count_month = 2 THEN  coalesce(t3.sum_amount,t3.last_one,t3.last_two) / coalesce(t3.last_one,t3.last_two,t3.last_three)
when t3.count_month = 1 then 999999
ELSE -999999
END) 
AS flag
from t3
where t3.rk = 1

结束，一直更新工作中所遇到我认为难写的sql，来给大家分享