使用分析函数 first_value 以及 last_value :

select colldate, 
           max(last_success - first_success) as success,
           max(last_conmiss - first_conmiss) as conmiss,
           resourcename
    from
    (
    select 
         first_value(success) over(partition by resourcename, colldate order by colltime) first_success,
         last_value(success) over(partition by resourcename, colldate order by colltime) last_success,
         first_value(conmiss) over(partition by resourcename, colldate order by colltime) first_conmiss,
         last_value(conmiss) over(partition by resourcename, colldate order by colltime) last_conmiss,
    colldate, resourcename
    from (select s.*, to_date(s.colltime) colldate from table s) s
    )s
group by colldate, resourcename;

请参阅以下文档：https://cwiki.apache.org/confluence/display/hive/languagemanual+windowingandanalytics