hive 沉默用户

– 查询指定日期的沉默用户

启动一次，以后没有在使用，有时间长度限制(一周)

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