日志中每天的每周活动用户

Question

我想知道是否有人可以通过一些SQL帮助我，以便在两天或更长时间（让我们以7天为参考）期间返回登录到数据库表中的唯一身份用户的数量。

我的日志表在每行中都包含一个时间戳（ts）和user_id，代表当时该用户的活动。

以下查询从该日志返回每日活动用户或DAU：

SELECT FLOOR(ts / 86400) AS day, COUNT(DISTINCT user_id) AS dau
FROM log
GROUP BY day ORDER BY day ASC

现在，让我们说我想在此单个查询中添加（或至少以最有效的方式进行检索）每周活动用户或7天期间记录的唯一身份用户总数。但是，我不想将时间分散在不重叠的几周中。我需要每天计算当天和前6天所看到的不同的user_id。

例如：

day users wau
1   1,2   2
4   1,3   3
7   3,4,5 5
8   5     4    (user_id 2 lost from count)
15  2     2    (user_ids 1,3,4 lost from count)

感谢您提供的任何帮助，如果需要进一步说明，请随时通过评论询问。

Answer 1

要获得“每周平均用户”计数（根据我对您的规范的了解…“每天，分别是当天和前六天中看到的不同user_id的计数”），请按照以下内容进行查询可用于。（查询还会返回“每日平均用户”计数。

SELECT d.day
     , COUNT(DISTINCT u.user_id) AS wau
     , COUNT(DISTINCT IF(u.day=d.day,u.user_id,NULL)) AS dau
  FROM ( SELECT FLOOR(k.ts/86400) AS `day`
           FROM `log` k
          GROUP BY `day`
       ) d
  JOIN ( SELECT FLOOR(l.ts/86400) AS `day`
              , l.user_id
           FROM `log` l
          GROUP BY `day`, l.user_id
       ) u
    ON u.day <= d.day
   AND u.day > d.day - 7
 GROUP BY d.day
 ORDER BY d.day

（我尚未对此进行测试；但是稍后，如果需要任何更正，我将更新此语句。）

此查询将给定日期（来自行u源）的用户列表与日志表（行d源）中的一组日期连接起来。注意连接谓词（ON子句）中出现的文字“
7”，这就是使用户列表与前6天“匹配”的原因。

请注意，例如，可以通过在SELECT列表中添加另一个表达式，将其扩展以获取过去3天的唯一用户数。

     , COUNT(DISTINCT IF(u.day<=d.day AND u.day>d.day-3,u.user_id,NULL)) AS 3day

字面量“ 7”可以增加到更大的范围。上面表达式中的字面3可以更改为任意天数…我们只需要确保已经有足够的前一天行（from
d）联接到from的每一行u。

性能说明：由于内联视图（或派生表，如MySQL所称的），此查询可能不会很快，因为必须将这些内联视图的结果集具体化为中间MyISAM表。

内联视图的别名u可能不是最佳的；直接连接到日志表可能更快。我在考虑在给定的一天中获得唯一的用户列表，这就是内联视图中的查询使我得到的东西。对我来说，将发生的事情概念化只是一件容易的事。我当时在想，如果您当天有数百个相同的用户输入，那么在加入其他日期之前，内联视图会淘汰掉一大堆重复项。最好在u和d内联视图中添加WHERE子句以限制返回的天数。（d内联视图需要在更早的6天内添加。）

另一方面，如果ts列是TIMESTAMP数据类型，我将更倾向于使用DATE(ts)表达式来提取日期部分。但这会在结果集中返回DATE数据类型，而不是整数，该数据类型将与您指定的结果集不同。）

SELECT d.day
     , COUNT(DISTINCT u.user_id) AS wau
     , COUNT(DISTINCT IF(u.day=d.day,u.user_id,NULL)) AS dau
  FROM ( SELECT DATE(k.ts) AS `day`
           FROM `log` k
          GROUP BY `day`
       ) d
  JOIN ( SELECT DATE(l.ts) AS `day`
              , l.user_id
           FROM `log` l
          GROUP BY `day`, l.user_id
       ) u
    ON u.day <= d.day
   AND u.day > DATE_ADD(d.day, INTERVAL -7 DAY)
 GROUP BY d.day
 ORDER BY d.day