sql(clickhouse) 如何计算用户的滚动mau

假定我们计算30天的活跃为mau

我们先来看一个例子，如果一个用户只活跃了2天，2020-01-01  和2020-05-01，

如果要计算mau的时候，2019-12-31的时候不算是我们的mau，2020-01-30的时候是我们的mau，2020-01-31的时候不算我们的mau,同理 2020-05-01的时候是我们的mau,2020-05-30的时候是我们的mau.

日期	uid
2020/1/1	1
2020/5/1	1

 

即当前的d+29，在这个范围的就是我们的mau.相当于用户的真实活跃日期只有2天，但是计算mau的时候我们把用户的活跃时间改成 我们把2020-01-01——2020-01-30；2020-05-01 ——2020-05-30 即该用户总共有60天活跃

此时我们需要引入arrayMap函数，该函数可以返回加工后的数组

步骤拆解1：我们现在定义uid为1 的用户活跃了2天

select toDate(arrayJoin(['2020-01-01','2020-05-01'])) d, 1 as  uid 

得到的结果

步骤拆解2：引入arrayMap() 函数，我们把用户的活跃时间改成 2020-01-2020-01-30；2020-05-01-2020-05-30 

select d,uid,arrayMap(x->d+x,range(30)) as dt from
(select toDate(arrayJoin(['2020-01-01','2020-05-01'])) d, 1 as  uid 
)

得到结果

步骤拆解3：对结果行专列

 

select uid,arrayJoin(virtual_d) as dt from
(select d,uid,arrayMap(x->d+x,range(30)) as virtual_d from
(select toDate(arrayJoin(['2020-01-01','2020-05-01'])) d, 1 as  uid 
)
)
得到结果

步骤拆解4：进行mau计算

select dt,uniqExact(uid) as mau from
(select uid,arrayJoin(virtual_d) as dt from
(select d,uid,arrayMap(x->d+x,range(30)) as virtual_d from
(select toDate(arrayJoin(['2020-01-01','2020-05-01'])) d, 1 as  uid 
)
)
)
group by dt

 

得到结果

步骤拆解5：所以最后我们可以把结果写成这样：如果你建立了一个dau的表,这个表存放用户的活跃信息

 

select dt,uniqExact(uid) as mau from
(select arrayJoin(arrayMap(x->d+x,range(30))) as dt,uid from
(select d, uid from dau where d>='2020-04-01')
where dt>='2020-04-30' and dt<'2020-05-18'  --见下面注释
)
group by dt

注释：今天是5月18，这里加限制条件是因为，我们把未来30天的都算进去了，所以如果不限制的，现实的结果会有5月18之后的数据，这个数据就不准确了，同样的，因为我们条件限制了登陆日期>='2020-04-01'，累加到30天后才是mau的数字，所以我们从4月30开始。

 

 

当然我们也可以写成这样，你品一品有什么差别

 

select dt,
uniqExactIf(uid,(d>=dt-29 and d<=dt)) as mau

from
        (select d,uid from dau  )
        array join arrayMap(x->yesterday()-x,range(30)) as dt
        where d>=today()-60
        
     group by dt
     order by dt desc