1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# 首页卡片点击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