Hive实战—时间滑动窗口计算

select
     b.union_id,to_date(ds,'yyyymmdd') as dt,count(1) as cnt
  from
    ods_la_daily_record_di b
  where
    -- 驱动表的时间限制
    b.ds>='${bizdate}'
    and b.ds<=${bizdate2}'
  group by
    b.union_id,ds

select
  union_id,count(1) as 打卡天数, sum(cnt) as 打卡次数
from
(
  select
     b.union_id,ds,count(1) as cnt
  from
    ods_la_daily_record_di b
  where
    -- 驱动表的时间限制
    b.ds>='${bizdate}'
    and b.ds<='${bizdate2}'
  group by
    b.union_id,ds
)
group by
  union_id
having
  -- 时间分布在4天以上
  count(1)>=4
  -- 打卡次数在8次以上
  and sum(cnt)>=8
;

with tmp as(
   -- 每个人每天打卡的次数
  select
     b.union_id,to_date(ds,'yyyymmdd') as dt,count(1) as cnt
  from
    ods_la_daily_record_di b
  where
    -- 驱动表的时间限制
    b.ds>='${bizdate}'
  group by
    b.union_id,ds
)
select
  union_id
from (
  -- 满足条件的(用户-天)
  select
    a.union_id,a.dt,sum(b.cnt) as 打卡次数,count(1) as 打卡天数
  from
    tmp a
  inner join
   tmp b
  on
    a.union_id=b.union_id
    and DATEDIFF(b.dt,a.dt)>=0
    and DATEDIFF(b.dt,a.dt)<=6
  group by
    a.union_id,a.dt
  having
    -- 次数限制
    sum(b.cnt)>=8
    -- 天数限制
    and count(1)>=4
)group by
  -- 对用户去重
  union_id
;

select
   UNION_ID,time_weeknum,count(1) as 打卡天数, sum(cnt) as 打卡次数
from(
  select
     b.union_id,ds,count(1) as cnt
  from
    ods_la_daily_record_di b
  where
    -- 驱动表的时间限制
    b.ds>='${bizdate}'
    and b.ds<='${bizdate2}'
  group by
    b.union_id,ds
) a
left join
dim_date_time b
on
  a.ds=b.time_date
group by
  --  周的标识
  UNION_ID,time_weeknum
HAVING
  -- 时间分布在4天以上
  count(1)>=4
  -- 打卡次数在8次以上
  and sum(cnt)>=8
;