Hive SQL数据分析实战（三）

有以下几张数据表，请写出Hive SQL语句，实现以下需求。

注：分区字段为dt，代表日期。

1、对2018年公司的支付总额按月度累计进行分析

select a.month,
		a.pay_amount,
		sum(a.pay_amount) over(order by a.month)
from
    (select month(dt) month,
            sum(pay_amount) as pay_amount
    from user_trade
    where year(dt)=2018
    group by month(dt)) a;

2、对2017年和2018年公司的支付总额按月度累计进行分析，按年度进行汇总。

select a.year,
		a.month,
		a.pay_amount,
		sum(a.pay_amount) over(partition by a.year order by a.month)
from
    (select year(dt) year,
    		month(dt) month,
            sum(pay_amount) as pay_amount
    from user_trade
    where year(dt) in (2017, 2018)
    group by year(dt),
    		month(dt)) a;

3、对2018年每个月的近三个月进行移动地求平均支付金额

select a.month,
		a.pay_amount,
		avg(a.pay_amount) over(order by a.month rows between 2 preceding and current row)
from
	 (select month