gambling_chess_RT.sql 5.14 KB
Newer Older
wang-jinfeng committed
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 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155
set  hive.exec.dynamic.partition.mode=nonstrict;
set  hive.exec.dynamic.partition=true;
set mapred.max.split.size=536870912;
set mapred.min.split.size.per.node=536870912;
set mapred.min.split.size.per.rack=536870912;
set mapreduce.map.memory.mb=3072;
set mapreduce.map.java.opts=-Xmx2458m;
set mapreduce.reduce.memory.mb=3072;
set mapreduce.reduce.java.opts=-Xmx2458m;



insert overwrite table dwh.gambling_chess_rt_data_weekly partition(dt='${dt_today}',business='gambling')
      select
      devices
      from
      (
      select
      devices,daleis,count(1) as number
      from
      (
      select
      t1.device_id as devices,t1.package_name as package,t2.dalei as daleis
      from
      (select
      device_id,package_name
      from
      dwh.dm_install_list_v2
      where
      dt ='${dt_today}'
      and
      platform = 'ios'
      group by
      device_id,package_name) as t1
      join
      (select
      package_name,platform,
      min(case when first_tag = 'hardcoregame' then 1
      when first_tag = 'Games' and second_tag = 'Card' then 2
      when first_tag = 'Games' and second_tag = 'Casino' then 3
      when first_tag = 'lightgame' and tag_type = 'rules' then 4
      when first_tag = 'hypercasual' and tag_type = 'rules' then 5
      when first_tag = 'relax' then 6
      when first_tag = 'Games' and second_tag = 'Word' then 7
      when first_tag = 'Games' and second_tag = 'Puzzle' then 8
      when first_tag = 'Games' and second_tag = 'Cleanup' then 9
      when first_tag = 'elimination' then 10
      when first_tag = 'moderategame' and tag_type = 'rules' then 11
      when first_tag = 'Social' and second_tag = 'Forums' then 12
      when first_tag = 'Video' then 13
      when first_tag = 'Books' and second_tag = 'Online Reading' then 14
      when first_tag = 'Tools' then 15
      when first_tag = 'Children' and tag_type = 'rules'  then 16
      else 17 end) as dalei
      from
      dwh.dim_package_tags
      where platform = 'ios'
      group by package_name,platform) t2
      on t1.package_name = t2.package_name
      group by t1.device_id,t1.package_name,t2.dalei) a
      group by devices,daleis) b
      where daleis = '3'
      and number >= 3
      group by devices;


insert overwrite table dwh.gambling_chess_rt_output_weekly partition(dt='${dt_today}')
     select
     device_id,
     'idfa' as device_type,
     'ios' as platform,
     '["202007020000"]' as package_name
     from dwh.gambling_chess_rt_data_weekly where dt='${dt_today}' and business='gambling';


insert overwrite table dwh.gambling_chess_rt_ods_other_device_weekly partition(dt='${dt_today}')
     select
     device_id,
     'idfa' as device_type,
     'ios' as platform,
     'UNKNOWN' as country
     from dwh.gambling_chess_rt_data_weekly where dt='${dt_today}' and business='gambling';


insert overwrite table dwh.gambling_chess_rt_data_weekly partition(dt='${dt_today}',business='chess')
      select
      devices
      from
      (
      select
      devices,daleis,count(1) as number
      from
      (
      select
      t1.device_id as devices,t1.package_name as package,t2.dalei as daleis
      from
      (select
      device_id,package_name
      from
      dwh.dm_install_list_v2
      where
      dt ='${dt_today}'
      and
      platform = 'ios'
      group by
      device_id,package_name) as t1
      join
      (select
      package_name,platform,
      min(case when first_tag = 'hardcoregame' then 1
      when first_tag = 'Games' and second_tag = 'Card' then 2
      when first_tag = 'Games' and second_tag = 'Casino' then 3
      when first_tag = 'lightgame' and tag_type = 'rules' then 4
      when first_tag = 'hypercasual' and tag_type = 'rules' then 5
      when first_tag = 'relax' then 6
      when first_tag = 'Games' and second_tag = 'Word' then 7
      when first_tag = 'Games' and second_tag = 'Puzzle' then 8
      when first_tag = 'Games' and second_tag = 'Cleanup' then 9
      when first_tag = 'elimination' then 10
      when first_tag = 'moderategame' and tag_type = 'rules' then 11
      when first_tag = 'Social' and second_tag = 'Forums' then 12
      when first_tag = 'Video' then 13
      when first_tag = 'Books' and second_tag = 'Online Reading' then 14
      when first_tag = 'Tools' then 15
      when first_tag = 'Children' and tag_type = 'rules'  then 16
      else 17 end) as dalei
      from
      dwh.dim_package_tags
      where platform = 'ios'
      group by package_name,platform) t2
      on t1.package_name = t2.package_name
      group by t1.device_id,t1.package_name,t2.dalei) a
      group by devices,daleis) b
      where daleis = '2'
      and number >= 5
      group by devices;



insert into table dwh.gambling_chess_rt_output_weekly partition(dt='${dt_today}')
     select
     device_id,
     'idfa' as device_type,
     'ios' as platform,
     '["202006220000"]' as package_name
     from dwh.gambling_chess_rt_data_weekly where dt='${dt_today}' and business='chess';


insert into table dwh.gambling_chess_rt_ods_other_device_weekly partition(dt='${dt_today}')
     select
     device_id,
     'idfa' as device_type,
     'ios' as platform,
     'UNKNOWN' as country
     from dwh.gambling_chess_rt_data_weekly where dt='${dt_today}' and business='chess';