一道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

 

转载于:https://www.cnblogs.com/wang-bing/p/10579422.html