高效大数据开发之 bitmap 思想的应用

作者：xmxiong，PCG 运营开发工程师

数据仓库的数据统计，可以归纳为三类：增量类、累计类、留存类。而累计类又分为历史至今的累计与最近一段时间内的累计(比如滚动月活跃天，滚动周活跃天，最近 N 天消费情况等)，借助 bitmap 思想统计的模型表可以快速统计最近一段时间内的累计类与留存类。

一、背景

数据仓库的数据统计，可以归纳为三类：增量类、累计类、留存类。而累计类又分为历史至今的累计与最近一段时间内的累计(比如滚动月活跃天，滚动周活跃天，最近 N 天消费情况等)，借助 bitmap 思想统计的模型表可以快速统计最近一段时间内的累计类与留存类。

二、业务场景

我们先来看几个最近一段时间内的累计类与留存类的具体业务问题，作为做大数据的你建议先不要急着往下阅读，认真思考一下你的实现方案：

1.统计最近 30 天用户的累计活跃天(每个用户在 30 天里有 N 天使用微视 app，N 为 1-30，然后将月活跃用户的 N 天加总)？

2.统计最近 7 天的用户累计使用时长？

3.统计最近 30 天有播放的累计用户数？

4.统计最近 30 天活跃用户有多少在最近 30 天里有连续 3 天及以上活跃？

5.统计 28 天前活跃用户的 1、3、7、14、28 天留存率？

三、传统解决方案

在进入本文真正主题之前，我们先来看看常规的解决思路：1.统计最近 30 天用户的累计活跃天？

--用dau表(用户ID唯一)，取最近30天分区，sum(活跃日期)。
select
    sum(imp_date) active_date
  from
    weishi_dau_active_table
  where
    imp_date>=20200701
    and imp_date<=20200730

2.统计最近 7 天的用户累计使用时长？

--用dau表(用户ID唯一)，取最近7天分区，sum(使用时长)。
select
    sum(log_time) log_time
  from
    weishi_dau_active_table
  where
    imp_date>=20200701
    and imp_date<=20200707

3.统计最近 30 天有播放的累计用户数？

--用用户播放表(用户ID唯一)，取最近30天分区，count(distinct if(播放次数>0,用户ID,null))。
select
    count(distinct if(play_vv_begin>0,qimei,null)) play_user
  from
    weishi_play_active_table
  where
    imp_date>=20200701
    and imp_date<=20200730

4.统计最近 30 天活跃用户有多少在最近 30 天里有连续 3 天及以上活跃？

--用dau表(用户ID唯一)，取最近30天分区，关联两次最近30天分区，关联条件右表分别为imp_date-1，imp_date-2。
select
    count(distinct a.qimei) active_num
  from
  ( select
        imp_date
        ,qimei
      from
        weishi_dau_active_table
      where
        imp_date>=20200701
        and imp_date<=20200730
   )a
  join --第一次join，先取出连续2天的用户，因为7月1日用户与7月2号-1天关联得上，表示一个用户在1号和2号都活跃
  ( select
        date_sub(imp_date,1) imp_date
        ,qimei
      from
        weishi_dau_active_table
      where
        imp_date>=20200701
        and imp_date<=20200730
   )b
   on
    a.imp_date=b.imp_date
    and a.qimei=b.qimei
  join --第二次join，取出连续3天的用户，因为第一次join已经取出连续两天活跃的用户了，再拿这些7月1日用户关联7月3日-2天关联得上，表示一个用户在1号和3号都活跃，结合第一步join得出用户至少3天连续活跃了
  ( select
        date_sub(imp_date,2) imp_date
        ,qimei
      from
        weishi_dau_active_table
      where
        imp_date>=