mysql - SQL 中的队列分析

Question

希望对用户群进行一些同期群分析。我们有 2 个表“users”和“session”，其中 users 和 session 都有一个“created_at”字段。我正在寻找一个查询来生成一个 7 x 7 的数字表(带有一些空白)，该表向我显示:在特定日期创建的用户数也创建了一个 session y = (0..6几天前)，说明他那天回来了。

created_at  d2  d3  d4
today       *   *   *
today-1     49  *   *
today-2     45  30  *
today-3     47  48  18
...

在这种情况下，在 today-3 创建的 47 个用户在 today-2 返回。

我可以在单个 MySQL 查询中执行此操作吗？我可以像这样单独执行查询，但将所有查询都包含在一个查询中真的很棒。

SELECT `users`.* FROM `users` INNER JOIN `sessions` ON `sessions`.`user_id` = `users`.`id` WHERE `users`.`os` = 'ios' AND (`sessions`.`updated_at` BETWEEN '2013-01-16 08:00:00' AND '2013-01-17 08:00:00')

Answer 1

这似乎是一个复杂的问题。不管它在您看来是否也是一个困难的问题，从一个较小的问题开始解决它从来都不是一个坏主意。

例如，您可以根据您的要求，从返回上周注册的所有用户(仅用户)开始，即从现在六天后的那一天开始:

SELECT *
FROM users
WHERE created_at >= CURDATE() - INTERVAL 6 DAY

下一步可能是按日期对结果进行分组并计算每组中的行数:

SELECT
  created_at,
  COUNT(*) AS user_count
FROM users
WHERE created_at >= CURDATE() - INTERVAL 6 DAY
GROUP BY created_at

如果 created_at 是 datetime 或 timestamp，则使用 DATE(created_at) 作为分组标准:

SELECT
  DATE(created_at) AS created_at,
  COUNT(*) AS user_count
FROM users
WHERE created_at >= CURDATE() - INTERVAL 6 DAY
GROUP BY DATE(created_at)

但是，您似乎不希望在输出中显示绝对 日期，而只希望输出相对 日期，例如今天、今天 - 1 天 等。在这种情况下，您可以使用 DATEDIFF()函数，它返回两个日期之间的天数，以生成从今天开始的(数字)偏移量并按这些值进行分组:

SELECT
  DATEDIFF(CURDATE(), created_at) AS created_at,
  COUNT(*) AS user_count
FROM users
WHERE created_at >= CURDATE() - INTERVAL 6 DAY
GROUP BY DATE(created_at)

您的 created_at 列将包含“日期”，例如 0、1 等等，直到 6。将它们转换为 today、today-1 等是微不足道的，您将在最终查询中看到。然而，到目前为止，我们已经到了需要后退一步的地步(或者，也许是向右退了半步)，因为我们真的不需要计算用户数量，而是他们的 < em>返回。因此，目前需要的 users 的实际工作数据集是这样的:

SELECT
  id,
  DATEDIFF(CURDATE(), created_at) AS day_offset
FROM users
WHERE created_at >= CURDATE() - INTERVAL 6 DAY

我们需要用户 ID 将此行集加入(将从中派生的行集)sessions，并且我们需要 day_offset 作为分组标准。

接下来，需要对 sessions 表执行类似的转换，我不会详细介绍。可以说生成的查询与上一个查询非常相同，只有两个异常(exception):

• id 被替换为 user_id；

• DISTINCT 应​​用于整个子集。

DISTINCT 的原因是每个用户每天返回不超过一行:据我了解，无论用户在特定日期可能有多少 session ，您都希望将它们计为一次返回。所以，下面是从 sessions 派生的内容:

SELECT DISTINCT
  user_id,
  DATEDIFF(CURDATE(), created_at) AS day_offset
FROM sessions
WHERE created_at >= CURDATE() - INTERVAL 6 DAY

现在只剩下连接两个派生表，应用分组和使用条件聚合来获得所需的结果:

SELECT
  CONCAT('today', IFNULL(CONCAT('-', NULLIF(u.DayOffset, 0)), '')) AS created_at,
  SUM(s.DayOffset = 0) AS d0,
  SUM(s.DayOffset = 1) AS d1,
  SUM(s.DayOffset = 2) AS d2,
  SUM(s.DayOffset = 3) AS d3,
  SUM(s.DayOffset = 4) AS d4,
  SUM(s.DayOffset = 5) AS d5,
  SUM(s.DayOffset = 6) AS d6
FROM (
  SELECT
    id,
    DATEDIFF(CURDATE(), created_at) AS DayOffset
  FROM users
  WHERE created_at >= CURDATE() - INTERVAL 6 DAY
) u
LEFT JOIN (
  SELECT DISTINCT
    user_id,
    DATEDIFF(CURDATE(), created_at) AS DayOffset
  FROM sessions
  WHERE created_at >= CURDATE() - INTERVAL 6 DAY
) s
ON u.id = s.user_id
GROUP BY u.DayOffset
;

我必须承认我没有对此进行测试/调试，但是，如果需要，一旦您提供了数据样本，我将很乐意使用它们。 :)