HiveSQL基础之窗口函数_sql中substr(dt,1,7)-优快云博客

本文链接：https://blog.youkuaiyun.com/FDFCJBAFSA/article/details/99711236

本文介绍了HiveSQL中的窗口函数，包括累计窗口函数、avg() over()、语法总结、分区排序窗口函数、分组窗口函数、偏移窗口函数及综合应用。详细讲解了sum() over()用于计算累计支付总额，avg() over()用于计算移动平均，以及如何进行分区、排序、分组和偏移操作，最后提供了实践练习案例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 累计窗口函数

sum(…) over(…)
2018年每月支付总额和当年累积支付总额

SELECT a.month,
	   a.pay_amount,
	   sum(a.pay_amount) over(order by a.month)
FROM
	(SELECT month(dt) as month,
		   sum(pay_amount) as pay_amount
	FROM user_trade
	WHERE year(dt)='2018'
	GROUP BY month(dt)) as a;

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) as year,
		    month(dt) as month,
		    sum(pay_amount) as pay_amount
	FROM user_trade
	WHERE year(dt) in (2017,2018)
	GROUP BY year(dt),
		     month(dt)) as a;

partition by 起到分组的作用
order by 按照什么顺序进行累加，升序ASC，降序DESC，默认升序

2. avg(…) over(…)

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(dt) as month,
		    sum(pay_amount) as pay_amount
	FROM user_trade
	WHERE year(dt)='2018'
	GROUP BY month(dt)) as a;

3. 语法总结

sum(...A...) over(partition by ...B... order by ...C... rows between ...D1... and ...D2...)
avg(...A...) over(partition by ...B... order by ...C... rows between ...D1... and ...D2...)

A:需要被加工的字段名称
B:分组的字段名称