Push拉起率评估【含SQL源码】

/*4.0 push漏斗数据计算*/SELECT time,show_count/arrive_count AS show_rate,click_count/show_count AS click_rate,push_from_background_count/click_count AS push_from_background_rate,push_from_appstart_count/click_count AS push_from_appstart_rate,ai_push_from_background_count/push_from_background_count AS ai_push_from_background_rate,ai_push_from_appstart_count/push_from_appstart_count AS ai_push_from_appstart_rate,artificial_push_from_background_count/push_from_background_count AS artificial_push_from_background_rate,artificial_push_from_appstart_count/push_from_appstart_count AS artificial_push_from_appstart_rate,not_ai_art_push_from_background_count/push_from_background_count AS not_ai_art_push_from_background_rate,not_ai_art_push_from_appstart_count/push_from_appstart_count AS not_ai_art_push_from_appstart_rate,send_count,arrive_count,show_count,click_count,push_from_background_count,push_from_appstart_count,ai_push_from_background_count,ai_push_from_appstart_count,artificial_push_from_background_count,artificial_push_from_appstart_count,not_ai_art_push_from_background_count,not_ai_art_push_from_appstart_countFROM(/*3.0 合并push全流程*/SELECT e.time,e.send_count,e.arrive_count,e.show_count,e.click_count,f.push_from_background_count,f.push_from_appstart_count,f.ai_push_from_background_count,f.ai_push_from_appstart_count,f.artificial_push_from_background_count,f.artificial_push_from_appstart_count,f.not_ai_art_push_from_background_count,f.not_ai_art_push_from_appstart_count FROM(/*2.0 按天统计push发送，到达，展示，点击以及各占发送的比例*/SELECT TO_DATE(time) AS time,SUM(CASE WHEN action='send' THEN 1 ELSE 0 END) AS send_count,SUM(CASE WHEN action='arrive' THEN 1 ELSE 0 END) AS arrive_count,SUM(CASE WHEN action='show' THEN 1 ELSE 0 END) AS show_count,SUM(CASE WHEN action='click' THEN 1 ELSE 0 END) AS click_countFROM events WHERE time>='2020-05-03 00:00:00' AND time<='2020-06-01 23:59:59'AND event='Push'AND region='BR'AND package_name='com.vstatus.premium'AND version_code>2050000AND action IN ('send','arrive','show','click')GROUP BY time/*2.0 结尾*/  )e LEFT JOIN(/*1.0 区分时间的push开启率*/SELECT time,SUM(CASE WHEN $resume_from_background=1 THEN 1 ELSE 0 END)AS push_from_background_count,SUM(CASE WHEN $resume_from_background=0 THEN 1 ELSE 0 END)AS push_from_appstart_count,SUM(CASE WHEN position_source='reco_push_ai'AND $resume_from_background=1 THEN 1 ELSE 0 END)AS ai_push_from_background_count, SUM(CASE WHEN position_source='reco_push_ai'AND $resume_from_background=0 THEN 1 ELSE 0 END)AS ai_push_from_appstart_count,SUM(CASE WHEN position_source='reco_push'AND $resume_from_background=1 THEN 1 ELSE 0 END)AS artificial_push_from_background_count,SUM(CASE WHEN position_source='reco_push'AND $resume_from_background=0 THEN 1 ELSE 0 END)AS artificial_push_from_appstart_count,SUM(CASE WHEN position_source<>'reco_push' AND position_source<>'reco_push_ai'AND $resume_from_background=1 THEN 1 ELSE 0 END)AS not_ai_art_push_from_background_count,SUM(CASE WHEN position_source<>'reco_push' AND position_source<>'reco_push_ai'AND $resume_from_background=0 THEN 1 ELSE 0 END)AS not_ai_art_push_from_appstart_countFROM(  SELECT TO_DATE(push_time)AS time,position_source,$resume_from_background FROM(SELECT push_userid,push_time,position_source,min(appstart_time),$resume_from_background FROM( SELECT a.user_id AS push_userid,a.time AS push_time,a.position_source,b.user_id AS appstart_userid,b.time AS appstart_time,b.$resume_from_background FROM (/*1.0 筛选出所有的push事件*/SELECT user_id,time,position_source FROM events WHERE time>='2020-05-03 00:00:00' AND time<='2020-06-01 23:59:59'AND event='Push' AND action='click'AND region='BR'AND package_name='com.vstatus.premium'AND version_code>2050000GROUP BY user_id,time,position_sourceORDER BY time/*1.0 结尾*/)a RIGHT JOIN(/*1.0 筛选出所有的$AppStart事件*/SELECT user_id,time,$resume_from_background FROM events WHERE time>='2020-05-03 00:00:00' AND time<='2020-06-01 23:59:59'AND event='$AppStart'AND region='BR'AND package_name='com.vstatus.premium'AND version_code>2050000GROUP BY user_id,time,$resume_from_backgroundORDER BY time/*1.0 结尾*/)b ON a.user_id=b.user_id AND b.time>a.time AND b.time<SECONDS_ADD(a.time,10))c GROUP BY push_userid,push_time,position_source,$resume_from_background)d WHERE push_time IS NOT NULL)a GROUP BY time/*1.0 结尾*/)f ON e.time=f.time/*3.0 结尾*/)g ORDER BY time/*4.0 结尾*/