group_recommend_evaluation.sql

--!connect jdbc:hive2://hadoop015.dx.momo.com:10000 ${hivevar:user} ${hivevar:passwd}
!connect jdbc:hive2://hadoop015.dx.momo.com:10000 dm dm
 
--beeline hive -f filename --hivevar dbname=online --hivevar partition_date=****
 
USE ${hivevar:dbname};
--use offline
 
SET mapreduce.job.queuename=data;
SET mapreduce.job.name="group_reocommend_evaluation_${hivevar:partition_date}";
 
select recommend_log.partition_date, recommend_log.recd_type, count(1) as recd_cnt,
       sum(case when click_log.momoid is not null then 1 else 0 end) as click_cnt,
       sum(case when click_log.momoid is not null then 1 else 0 end) / count(1) as ctr,
       sum(case when apply_log.momoid is not null 
                     and (apply_log.timestamp-click_log.timestamp) > 0
                     and (apply_log.timestamp-click_log.timestamp) < 1200000
                then 1 else 0 end ) as apply_cnt,
       sum(case when apply_log.momoid is not null 
                     and (apply_log.timestamp-click_log.timestamp) > 0
                     and (apply_log.timestamp-click_log.timestamp) < 1200000
                then 1 else 0 end ) / sum(case when click_log.momoid is not null 
                                               then 1 else 0 end)
                                      as atr
from
(
    select momoid, timestamp, type,
    get_json_object(result_json, '$.groupId') as groupid,
    get_json_object(result_json, '$.type') as recd_type,
    partition_date
    from tl_hdfs_group_recd_log
    LATERAL VIEW EXPLODE(array(get_json_object(result, '$.rst\[0]'),
                               get_json_object(result, '$.rst\[1]'))) l
    as result_json
    where partition_date=${hivevar:partition_date} and type=1
    and get_json_object(result_json, '$.type') is not null
) recommend_log
left outer join
(
    select momoid, timestamp, groupid, action, type, partition_date
    from ml_group_apply_click_log
    where partition_date=${hivevar:partition_date} and action='site_nearby_recommend'
) click_log
on (recommend_log.momoid=click_log.momoid and recommend_log.groupid=click_log.groupid 
    and recommend_log.recd_type=click_log.type)
left outer join
(
    select momoid, timestamp, groupid, action, type, partition_date
    from ml_group_apply_click_log
    where partition_date=${hivevar:partition_date} and action='apply'
) apply_log
on (click_log.momoid=apply_log.momoid and click_log.groupid=apply_log.groupid)
group by recommend_log.partition_date, recommend_log.recd_type;