我正在使用MySql和Symfony2。我需要建立队列分析表。我需要比较每个群组中有多少用户在注册后每周至少登录一次网站。我想做的是每周获取注册用户的数量,基本上这些是我的队列。

 SELECT DATE_FORMAT(date_added,'%d %b %y') as reg_date, COUNT(*) AS user_count
 FROM user
 WHERE date_added>='2016-02-01' AND date_added<=NOW()
 GROUP BY WEEK(date_added)

此查询按周获取不同的用户登录到网站。
 SELECT WEEK(login_date) AS week, COUNT(DISTINCT user_id) AS user_count
 FROM user_log
 WHERE login_date>='2016-02-01' AND login_date<=NOW()
 GROUP BY WEEK(login_date)

我的问题是:我不知道如何按队列对登录的用户进行分组,并按周比较队列。我希望我把问题说清楚了。英语不是我的母语。谢谢。
样本数据:
 user table

 id      | date_added (in WEEK() format)
 A       | 1
 B       | 1
 C       | 1
 D       | 2
 E       | 2
 F       | 2
 G       | 2
 ------------

 user_log table
 user_id | login_date (in WEEK() format)
 A       | 1
 B       | 1
 B       | 1
 A       | 2
 D       | 2
 A       | 2
 D       | 2
 E       | 2

应为表。队列1-在第1周注册的用户,队列2-在第1周等大小-注册用户的数量。第一周-注册后第一周有多少用户重新登录网站,第二周-注册后第二周有多少用户重新登录网站
 Cohort     size    Week1   Week2
 Cohort 1 |  3   |    2   |    1   |
 Cohort 2 |  4   |    2   |    -   |

最佳答案

这是从我修改@Andriy M对这个问题的回答:Cohort analysis in SQL
此查询在注册后按周获取唯一用户登录。

SELECT DISTINCT
    user_id,
    FLOOR(DATEDIFF(user_log.login_date, user.date_added)/7) AS Offset
    FROM user_log
    LEFT JOIN user ON (user.id = user_log.user_id)
    WHERE user_log.login_date >= CURDATE() - INTERVAL 14 DAY

此查询获取过去14天内创建的所有用户,并将日期格式化为他们注册的周:
 SELECT
    id,
    DATE_FORMAT(date_added, "%Y-%u") AS cohort
  FROM user
  WHERE date_added >= CURDATE() - INTERVAL 14 DAY

我们可以将这两个查询放在一起,得到一个表,其中包含注册后返回的人数:
SELECT STR_TO_DATE(CONCAT(u.cohort, ' Monday'), '%X-%V %W') as date,
  SUM(s.Offset = 0) AS size,
  SUM(s.Offset = 1) AS Week1,
  SUM(s.Offset = 2) AS Week2
FROM (
 SELECT
    id,
    DATE_FORMAT(date_added, "%Y-%u") AS cohort
  FROM user
  WHERE date_added >= CURDATE() - INTERVAL 21 DAY
) as u
LEFT JOIN (
    SELECT DISTINCT
    user_id,
    FLOOR(DATEDIFF(user_log.login_date, user.date_added)/7) AS Offset
    FROM user_log
    LEFT JOIN user ON (user.id = user_log.user_id)
    WHERE user_log.login_date >= CURDATE() - INTERVAL 21 DAY
) as s
ON s.user_id = u.id
GROUP BY u.cohort
ORDER BY u.cohort

由于我们没有计算在给定的一周内注册了多少人,因此我们假设他们在注册的一周内至少登录一次,以给出大小列的准确结果。
另外,你必须重新修改这个,以得到一个队列的数字,而不是日期,但我发现日期更有帮助。
您还可以将此时间延长到更多周-您必须更改两个子查询中间隔后的天数,并且可以在主select语句中添加更多的行以获得更多周。

10-06 05:39
查看更多