– 查询指定日期的沉默用户
启动一次,以后没有在使用,有时间长度限制(一周)
use big12_umeng ;
SELECT
t1.appid ,
t1.appplatform ,
t1.brand ,
t1.devicestyle,
t1.ostype ,
t1.appversion ,
count(t1.deviceid)
FROM
(
SELECT
t.appid appid ,
t.appplatform appplatform,
t.brand brand,
t.devicestyle devicestyle,
t.ostype ostype,
t.appversion appversion,
t.deviceid deviceid
FROM
(
SELECT
appid ,
appplatform,
brand ,
devicestyle,
ostype ,
appversion,
deviceid,
count(createdatms) cnt , #访问的次数
min(createdatms) firsttime #第一次访问的时刻
FROM
appstartuplogs
WHERE
#指定的时间
concat(ym,day) <= '${ymd}'
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 t.cnt = 1
#时间是在7天前 小于指定的时间片的7天
and formatbyday(t.firsttime , 0 ,'yyyyMMdd') <= formatbyday('${ymd}' ,'yyyyMMdd' , -7 , 'yyyyMMdd')
)t1
group BY
t1.appid ,
t1.appplatform ,
t1.brand ,
t1.devicestyle,
t1.ostype ,
t1.appversion
order BY
t1.appid ,
t1.appplatform ,
t1.brand ,
t1.devicestyle,
t1.ostype ,
t1.appversion