一道hive面试题（窗口函数）
表student中的数据格式如下：
name month degree
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
现要查询表中连续三个月以上degree均为A的记录
1 select 2 a1.name, 3 a1.month, 4 a1.degree 5 from 6 ( 7 select 8 name, 9 month, 10 degree, 11 sum(if(degree = 'A', 1, 0)) OVER(PARTITION BY name ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS score1, 12 sum(if(degree = 'A', 1, 0)) OVER(PARTITION BY name ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 following) AS score2, 13 sum(if(degree = 'A', 1, 0)) OVER(PARTITION BY name ORDER BY month ROWS BETWEEN CURRENT ROW AND 2 following) AS score3 14 from student 15 ) as a1 16 where 17 a1.score1 = 3 or 18 a1.score2 = 3 or 19 a1.score3 = 3
