我正在使用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语句中添加更多的行以获得更多周。