sql求平均日活_18 友盟项目--统计---日活、周活、月活--创建表并插入选择出的数据...

use big12_umeng ;

create tableifnot exists stat_act_day(

day string ,

appid string,

appplatform string,

brand string ,

devicestyle string,

ostype string ,

appversion string ,

cntint)

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 notnulland t.deviceid is notnull)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