19 友盟项目--统计新增用户---日新增、周新增、月新增--创建表并插入选择出的数据

新增用户---全表扫描---启动时间的最小值所在的天

各个维度下---with cube

 

昨日新增---第一次启动时间（启动时间最小值）在昨天的设备id

stat_new_day.sql

use big12_umeng ;
create table if not exists stat_new_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 into table stat_new_day
SELECT
  '2018/08/02' ,
  t.appid ,
  t.appversion ,
  t.appplatform,
  t.brand ,
  t.devicestyle,
  t.ostype ,
  count(t.deviceid) cnt
FROM
  (
    select
      appid ,
      appplatform,
      brand ,
      devicestyle,
      ostype ,
      appversion ,
      deviceid ,
      min(createdatms) firsttime
    from
      appstartuplogs
    group BY
      appid ,
      appplatform,
      brand ,
      devicestyle,
      ostype ,
      appversion,
      deviceid
    with cube
  )t
WHERE
  t.appid is not NULL
  and t.deviceid is not null
  and formatbyday(t.firsttime , 0 , 'yyyy/MM/dd')  = '2018/08/02'
group by
    t.appid ,
    t.appversion ,
    t.appplatform,
    t.brand ,
    t.devicestyle,
    t.ostype
order BY
    t.appid ,
    t.appversion ,
    t.appplatform,
    t.brand ,
    t.devicestyle,
    t.ostype

 

周增---从日新增表中 --> 这周内每天新增数  的总和

stat_new_week.sql

use big12_umeng ;
create table if not exists stat_new_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 into table stat_new_week
SELECT
  appid ,
  appversion ,
  appplatform,
  brand ,
  devicestyle,
  ostype ,
  sum(cnt) cnt
FROM
  stat_new_day
WHERE
  formatbyweek(day ,'yyyy/MM/dd' , 0 , 'yyyy/MM/dd') = formatbyweek('2018/07/31' ,'yyyy/MM/dd' , 0 , 'yyyy/MM/dd')
group by
  appid ,
  appversion ,
  appplatform,
  brand ,
  devicestyle,
  ostype

 

 

月增---从日新增表中 --> 这月内每天新增数  的总和

stat_new_month.sql

use big12_umeng ;
create table if not exists stat_new_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_new_month
SELECT
  '201808' ,
  appid ,
  appversion ,
  appplatform,
  brand ,
  devicestyle,
  ostype ,
  sum(cnt) cnt
FROM
  stat_new_day
WHERE
  formatbymonth(day ,'yyyy/MM' , 0 , 'yyyy/MM') = '2018/08'
group by
  appid ,
  appversion ,
  appplatform,
  brand ,
  devicestyle,
  ostype