mysql - 日志中每天的每周活跃用户数

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_ids 的计数”)，查询如下可以使用下面的那个。 (该查询还返回“每日平均用户”计数。

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 天“匹配”的原因。

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

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

可以增加文字“7”以获得更大的范围。上面表达式中的文字 3 可以更改为任意天数...我们只需要确保我们有足够的前一天行(来自 d)连接到每一行你。

性能注意事项:由于内联 View (或派生表，如 MySQL 所称)，此查询可能不会很快，因为这些内联 View 的结果集必须具体化到中间 MyISAM 表中。

别名为 u 的内联 View 可能不是最优的；直接加入日志表可能会更快。我在考虑获取特定日期的唯一用户列表，这就是内联 View 中的查询让我得到的。我更容易将正在发生的事情概念化。而且我在想，如果一天有数百个相同的用户输入，那么在我们加入其他日子之前，内联 View 会清除一大堆重复项。最好在 u 和 d 内联 View 中添加一个 WHERE 子句来限制我们返回的天数。 (d 内嵌 View 需要额外提前 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

---