查询一段时间内的DAU / MAU（每天）

问题描述

我有一个每日会话表，其中包含user_id和date列。我想每天绘制出DAU / MAU（每日活跃用户/每月活跃用户）的图表。例如：

 日期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。

解决方案

假设每天都有值，则可以使用子查询和介于$code之间的范围来获取总数：

 ，dau为（
选择日期，count（userid）为dau 
，来自每日会话ds 
按日期
）
选择日期dau，
 sum（dau）结束（按日期行在-29之前和当前行之间排序）为dau中的mau 
； 
  

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

 ，dau为（
选择日期，count（distinct userid）为dau 
来自dailysessions ds 
按日期
组）
选择日期，dau，
（select计数（来自distinctsessions ds 
的
，其中ds）。日期-29 *时间间隔 1天和日期
）之间的日期为dau的mau 
； 
  

I have a daily sessions table with columns user_id and date. I'd like to graph out DAU/MAU (daily active users / monthly active users) on a daily basis. For example:

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%
...          ...      ...     ...

Calculating daily actives is straightforward to calculate, but calculating the monthly actives e.g. the number of users that logged in the date-30 days, is causing problems. How is this achieved without a left join for each day?

Edit: I'm using Postgres.

解决方案

Assuming you have values for each day, you can get the total counts using a subquery and 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;

Unfortunately, I think you want distinct users rather than just user counts. That makes the problem much more difficult, especially because Postgres doesn't support count(distinct) as a window function.

I think you have to do some sort of self join for this. Here is one method:

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;

这篇关于查询一段时间内的DAU / MAU（每天）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！