查询一段时间内的DAU / MAU

Question

我有一个带有session_user_id和date的每日会话表。我想每天绘制出DAU / MAU（每日活跃用户/每月活跃用户）的图表。例如：

Date         MAU      DAU     DAU/MAU
2014-06-01   20,000   5,000   20%
2014-06-02   21,000   4,000   19%
2014-06-03   20,050   3,050   17%
...          ...      ...     ...

计算每日活动量很容易直接计算，但是计算每月活动量（例如，登录日期为30天的用户数）会引起问题。每天没有左加入怎么办？

编辑：我正在使用Postgres。

Answer 1

假设您每天都有值，则可以使用子查询和获得总计数range between：

with dau as (
      select date, count(userid) as dau
      from dailysessions ds
      group by date
     )
select date, dau,
       sum(dau) over (order by date rows between -29 preceding and current row) as mau
from dau;

不幸的是，我认为您想要的是不同的用户，而不仅仅是用户数。这使问题变得更加困难，尤其是因为Postgres不支持count(distinct)作为窗口函数。

我认为您必须为此进行某种形式的自我加入。这是一种方法：

with dau as (
      select date, count(distinct userid) as dau
      from dailysessions ds
      group by date
     )
select date, dau,
       (select count(distinct user_id)
        from dailysessions ds
        where ds.date between date - 29 * interval '1 day' and date
       ) as mau
from dau;