1.数据 s1,201801,A s1,201802,A s1,201803,C s1,201804,A s1,201805,A s1,201806,A s2,201801,A s2,201802,B s2,201803,C s2,201804,A s2,201805,D s2,201806,A s3,201801,C s3,201802,A s3,201803,A s3,201804,A s3,201805,B s3,201806,A 2.建表 create table if not exists student(name string,month string,degree string) row format delimited fields terminated by ',' ; load data local inpath '/root/stu.txt' into table student; 3.现要查询表中连续三个月以上degree均为A的记录? select a1.name, a1.month, a1.degree from ( select name, month, degree, sum(if(degree = 'A', 1, 0)) OVER(PARTITION BY name ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS score1, sum(if(degree = 'A', 1, 0)) OVER(PARTITION BY name ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 following) AS score2, sum(if(degree = 'A', 1, 0)) OVER(PARTITION BY name ORDER BY month ROWS BETWEEN CURRENT ROW AND 2 following) AS score3 from student ) as a1 where a1.score1 = 3 or a1.score2 = 3 or a1.score3 = 3 结果: s1 201804 A s1 201805 A s1 201806 A s3 201802 A s3 201803 A s3 201804 A
项目实战从0到1之hive(15)hive实现连续三个月学生成绩都为A的记录
作者:大码王
-------------------------------------------
个性签名:独学而无友,则孤陋而寡闻。做一个灵魂有趣的人!
如果觉得这篇文章对你有小小的帮助的话,记得在右下角点个“推荐”哦,博主在此感谢!
万水千山总是情,打赏一分行不行,所以如果你心情还比较高兴,也是可以扫码打赏博主,哈哈哈(っ•?ω•?)っ???!