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

最新推荐文章于 2023-06-06 22:59:42 发布

转载最新推荐文章于 2023-06-06 22:59:42 发布 · 732 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/star521/p/9943518.html

本文介绍了一种通过全表扫描及立方体聚合的方式，统计不同维度下应用的每日、每周及每月新增用户数量的方法。具体包括：1）通过查找每个设备ID的首次启动时间来确定昨日新增用户；2）从日新增表汇总本周及本月的用户增长情况。

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

各个维度下---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_day.sql 昨日新增用户

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

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_week.sql 周新增用户数

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

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