18 友盟项目--统计---日活、周活、月活--创建表并插入选择出的数据

定位分区-->with cube维度组合 -->  去null过滤 --->

 

日活跃用户

spark执行代码  ---  调用工具类

    执行sql工具类：ExecSQLUtil

public class StatDayActJava{
    public static void main(String[] args) throw Exception{
        SparkConf conf = new SparkConf();
        conf.setAppName("statNew");
        conf.setMaster("local[4]");
        //spark链接
        SparkSession sess = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate();
        //注册执函数
        ExecSQLUtil.execRegisterFuncs(sess);    
        //执行sql
        ExecSQLUtil.execSQLScript(sess, "stat_act_day.sql");
            }
}

 

 

日活跃量  sql语句 启动日志下appstartuplogs  

stat_act_day.sql

use big12_umeng ;
create table if not exists stat_act_day(
  day string ,
  appid string,
  appplatform string,
  brand string ,
  devicestyle string,
  ostype string ,
  appversion string ,
  cnt int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
lines terminated by '\n';

insert overwrite table stat_act_day
select
  formatbyday(-5, 'yyyyMMdd'),
  tt.appid ,
  tt.appplatform,
  tt.brand ,
  tt.devicestyle,
  tt.ostype ,
  tt.appversion ,
  count(tt.deviceid)
FROM
(
  select
    t.appid ,
    t.appplatform,
    t.brand ,
    t.devicestyle,
    t.ostype ,
    t.appversion ,
    t.deviceid
  FROM
  (
    select
      appid ,
      appplatform,
      brand ,
      devicestyle,
      ostype ,
      appversion ,
      deviceid
    from
      appstartuplogs
    WHERE
      concat(ym,day) = formatbyday(-5, 'yyyyMMdd')
    group BY
      appid ,
      appplatform,
      brand ,
      devicestyle,
      ostype ,
      appversion,
      deviceid
    with cube
  )t
  where
    t.appid is not null
    and t.deviceid is not null
)tt
group BY
  tt.appid ,
  tt.appplatform,
  tt.brand ,
  tt.devicestyle,
  tt.ostype ,
  tt.appversion
order by
  tt.appid ,
  tt.appplatform,
  tt.brand ,
  tt.devicestyle,
  tt.ostype ,
  tt.appversion

 

周活跃量---每天算本周活跃

concat()  连接函数

返回结果为连接参数产生的字符串

stat_act_week.sql

use big12_umeng ;
create table if not exists stat_act_week(
  day string ,
  appid string,
  appplatform string,
  brand string ,
  devicestyle string,
  ostype string ,
  appversion string ,
  cnt int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
lines terminated by '\n';

insert overwrite table stat_act_week
select
  formatbyday(-5, 'yyyyMMdd'),
  tt.appid ,
  tt.appplatform,
  tt.brand ,
  tt.devicestyle,
  tt.ostype ,
  tt.appversion ,
  count(tt.deviceid)
FROM
(
  select
    t.appid ,
    t.appplatform,
    t.brand ,
    t.devicestyle,
    t.ostype ,
    t.appversion ,
    t.deviceid
  FROM
  (
    select
      appid ,
      appplatform,
      brand ,
      devicestyle,
      ostype ,
      appversion ,
      deviceid
    from
      appstartuplogs
    WHERE
      formatbyweek(concat(ym,day) , 'yyyyMMdd' , 0 , 'yyyyMMdd') = formatbyweek(-1 , 'yyyyMMdd')
    group BY
      appid ,
      appplatform,
      brand ,
      devicestyle,
      ostype ,
      appversion,
      deviceid
    with cube
  )t
  where
    t.appid is not null
    and t.deviceid is not null
)tt
group BY
  tt.appid ,
  tt.appplatform,
  tt.brand ,
  tt.devicestyle,
  tt.ostype ,
  tt.appversion
order by
  tt.appid ,
  tt.appplatform,
  tt.brand ,
  tt.devicestyle,
  tt.ostype ,
  tt.appversion

 

 

月活跃量

stat_act_month.sql

 

use big12_umeng ;
create table if not exists stat_act_month(
  month string ,
  appid string,
  appplatform string,
  brand string ,
  devicestyle string,
  ostype string ,
  appversion string ,
  cnt int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
lines terminated by '\n';

insert into table stat_act_month
select
  '${ym}' ,
  tt.appid ,
  tt.appplatform,
  tt.brand ,
  tt.devicestyle,
  tt.ostype ,
  tt.appversion ,
  count(tt.deviceid)
FROM
(
  select
    t.appid ,
    t.appplatform,
    t.brand ,
    t.devicestyle,
    t.ostype ,
    t.appversion ,
    t.deviceid
  FROM
  (
    select
      appid ,
      appplatform,
      brand ,
      devicestyle,
      ostype ,
      appversion ,
      deviceid
    from
      appstartuplogs
    WHERE
      ym = '${ym}'
    group BY
      appid ,
      appplatform,
      brand ,
      devicestyle,
      ostype ,
      appversion,
      deviceid
    with cube
  )t
  where
    t.appid is not null
    and t.deviceid is not null
)tt
group BY
  tt.appid ,
  tt.appplatform,
  tt.brand ,
  tt.devicestyle,
  tt.ostype ,
  tt.appversion
order by
  tt.appid ,
  tt.appplatform,
  tt.brand ,
  tt.devicestyle,
  tt.ostype ,
  tt.appversion