# 首页卡片点击PV/首页卡片精准曝光PV

select *,ROUND(cast(card_click as double) /cast(card_exposure as double),5) as card_click_divide_card_exposure 
from pm.tl_pm_recommend_strategy_d where 
device_os_type = 'android' and active_type = '新增' and recommend_type = 'ctr预估' and recommend_type = 'ctr预估' and card_content_type = 'diary'
order by day_id desc



spark-shell --master yarn --total-executor-cores 1 --executor-memory 1g

SELECT t1.query as query
            ,t1.device_os_type as device_type
            ,t1.active_type as active_type
            ,t1.channel as channel_type
            ,NVL(t1.search_pv,0) as 30_pv
            ,NVL(t1.search_uv,0) as 30_uv
            ,if(NVL(t3.search_uv,0) <> 0 ,concat(cast((NVL(t4.hexin_card_click_pv,0)/NVL(t3.search_uv,0)) as decimal(18,2)),'') , '-') as 1_core_pv_division_uv
            ,if(NVL(t3.search_uv,0) <> 0 ,concat(cast((NVL(t4.neirong_card_click_pv,0)/NVL(t3.search_uv,0)) as decimal(18,2)),'') , '-') as 1_pv_division_uv
    
            ,if(NVL(t5.search_uv,0) <> 0 ,concat(cast((NVL(t6.hexin_card_click_pv,0)/NVL(t5.search_uv,0)) as decimal(18,2)),'') , '-') as 7_core_pv_division_uv
            ,if(NVL(t5.search_uv,0) <> 0 ,concat(cast((NVL(t6.neirong_card_click_pv,0)/NVL(t5.search_uv,0)) as decimal(18,2)),'') , '-') as 7_pv_division_uv
    
            ,if(NVL(t1.search_uv,0) <> 0 ,concat(cast((NVL(t2.hexin_card_click_pv,0)/NVL(t1.search_uv,0)) as decimal(18,2)),'') , '-') as 30_core_pv_division_uv
            ,if(NVL(t1.search_uv,0) <> 0 ,concat(cast((NVL(t2.neirong_card_click_pv,0)/NVL(t1.search_uv,0)) as decimal(18,2)),'') , '-') as 30_pv_division_uv
    
    
            ,NVL(t2.hexin_card_click_pv,0) as 30_search_core_pv
            ,NVL(t2.neirong_card_click_pv,0) as 30_search_pv
    
            ,NVL(t3.search_pv,0) as 1_pv
            ,NVL(t3.search_uv,0) as 1_uv
    
            ,NVL(t4.hexin_card_click_pv,0) as 1_search_core_pv
            ,NVL(t4.neirong_card_click_pv,0) as 1_search_pv
    
            ,NVL(t5.search_pv,0) as 7_pv
            ,NVL(t5.search_uv,0) as 7_uv
    
            ,NVL(t6.hexin_card_click_pv,0) as 7_search_core_pv
            ,NVL(t6.neirong_card_click_pv,0) as 7_search_pv
			
	query









