目录
0 问题描述
SQL 测试一组数据中是否存在某个值，你想根据某一组行记录里是否包含某个特定值来生成一个布尔值。试想这样一个例子，一个学生在一段时间内会参加若干场考试。假设他每 3 个月会参加 3 场考试。只要他通过了任何一场，将返回一个标志（flag）以表示考试通过。如果 3 场都没有通过，也会返回一个标志以表示考试未通过。
如下数据：
create table v
as
select 1 student_id,
1 test_id,
2 grade_id,
1 period_id,
'2005-02-01' test_date,
0 pass_fail
union all
select 1, 2, 2, 1, '2005-03-01', 1
union all
select 1, 3, 2, 1, '2005-04-01', 0
union all
select 1, 4, 2, 2, '2005-05-01', 0
union all
select 1, 5, 2, 2, '2005-06-01', 0
union all
select 1, 6, 2, 2, '2005-07-01', 0 查看数据
hive> select * from v;
OK
1 1 2 1 2005-02-01 0
1 2 2 1 2005-03-01 1
1 3 2 1 2005-04-01 0
1 4 2 2 2005-05-01 0
1 5 2 2 2005-06-01 0
1 6 2 2 2005-07-01 0
Time taken: 0.275 seconds, Fetched: 6 row(s)
仔细观察以上结果集，可以看到这个学生在两个学期里共参加了 6 场考试。他通过了其中一场（1 表示“通过”，0 表示“未通过”），因此他第一个学期的学习成绩算是过关了。他在第二个学期（接下来的 3 个月）没有通过任何一场考试，因此 3 场考试的 PASS_FAIL 列都是 0。你希望返回一个结果集表示这个学生某个学期是否通过了考试。最终你希望得到如下所示的结果集。
STUDENT_ID TEST_ID GRADE_ID PERIOD_ID TEST_DATE METREQ IN_PROGRESS
---------- ------- -------- --------- ----------- ------ -----------
1 1 2 1 2005-02-01 + 0
1 2 2 1 2005-03-01 + 0
1 3 2 1 2005-04-01 + 0
1 4 2 2 2005-05-01 - 0
1 5 2 2 2005-06-01 - 0
1 6 2 2 2005-07-01 - 1METREQ（表示是否通过）的值是“+”和“-”，表示学生在一个学期（3 个月）内是否通过了至少一场考试。如果一个学生在一个学期内通过了至少一场考试，则 IN_PROGRESS 值为 0。如果没有通过任何一场考试，那么他参加的最后一场考试对应的 IN_PROGRESS 值应该是 1。
要求：METQER中的值与该字段最后一个字符对齐，同样IN_PROGRESS字段值也与最后一个字符对齐
1 问题解决
通过问题描述我们知道本题的实质是在原表的基础上添加两列，作为辅助判断。因此凭借以前的经验，我们需要借助窗口函数构来完成。
第一列METQER:表示学生在一个学期（3 个月）内是否通过了至少一场考试,其中关键字为至少，意思即为只要有一门考试通过即可，由于pass_fail只有0和1，所以我们只需要求max(pass_fail) over(partition by studen_id,grade_id,period_id)值即可，只要最大的值为1就代表通过，此时METQER的值全部置为+，如果为0则置为-
第二列IN_PROGRESS：如果一个学生在一个学期内通过了至少一场考试，则 IN_PROGRESS 值为 0。如果没有通过任何一场考试，那么他参加的最后一场考试对应的 IN_PROGRESS 值应该是 1。这里考试通过与否还是用max() over(）的方法来解决，但是比较麻烦的是此处为最后一次考试才置为1，那么如何判断是最后一次考试呢？通过时间来判断，max(test_date) over(partition by studen_id,grade_id,period_id) 则表示该考生同一时期的最后一次考试。
有了上述分析，我们借助窗口函数分析如下：
（1）获取判断条件
select *
,max(pass_fail) over(partition by student_id,grade_id,period_id)
,max(test_date) over(partition by student_id,grade_id,period_id)
from vOK
1 2 2 1 2005-03-01 1 1 2005-04-01
1 1 2 1 2005-02-01 0 1 2005-04-01
1 3 2 1 2005-04-01 0 1 2005-04-01
1 4 2 2 2005-05-01 0 0 2005-07-01
1 5 2 2 2005-06-01 0 0 2005-07-01
1 6 2 2 2005-07-01 0 0 2005-07-01
（2）根据（1）结果集进行条件判断，得出最终的结果。
对于题目中最后的要求，对齐字段最后一个字符，我们采用左补足函数lpad()完成。具体SQL如下
select student_id
,test_id
,grade_id
,period_id
,test_date
,if(max_p=1,lpad('+',6,' '),lpad('-',6,' ')) as metreq
,if( max_p=1,lpad('0',11,' ')
,if(datediff(last_d,test_date)=0
,lpad('1',11,' '),lpad('0',11,' '))) as in_progress
from(
select *
,max(pass_fail) over(partition by student_id,grade_id,period_id) as max_p --条件1
,max(test_date) over(partition by student_id,grade_id,period_id) as last_d --条件2
from v
) m+-------------+----------+-----------+------------+-------------+---------+--------------+--+
| student_id | test_id | grade_id | period_id | test_date | metreq | in_progress |
+-------------+----------+-----------+------------+-------------+---------+--------------+--+
| 1 | 1 | 2 | 1 | 2005-02-01 | + | 0 |
| 1 | 2 | 2 | 1 | 2005-03-01 | + | 0 |
| 1 | 3 | 2 | 1 | 2005-04-01 | + | 0 |
| 1 | 4 | 2 | 2 | 2005-05-01 | - | 0 |
| 1 | 5 | 2 | 2 | 2005-06-01 | - | 0 |
| 1 | 6 | 2 | 2 | 2005-07-01 | - | 1 |
+-------------+----------+-----------+------------+-------------+---------+--------------+--+2 小 结
本文通过窗口函数对一组数据中是否存在某个值进行了分析，利用窗口函数构造辅助判断条件这一技巧解决此类问题，其中数值对齐采用了lpad()函数技巧。
欢迎关注石榴姐公众号"我的SQL呀"，关注我不迷路
