MySQL - 根据几周的登录历史记录获取用户数量

Question

有两个表 - users 和 login_history。

----------------------------------------------
-- user_id -- | -- created -------------------
--  1  ------ | -- 2018-11-01 13:07:24 -------
--  2  ------ | -- 2018-12-01 13:08:24 -------
--  3  ------ | -- 2019-01-01 13:09:24 -------
--  4  ------ | -- 2019-02-01 13:10:24 -------
--  5  ------ | -- 2019-02-01 13:11:24 -------
----------------------------------------------

以下查询根据创建时间的 YEAR 和 MONTH 返回上述用户的计数。

SELECT MONTH(created) as monthly, YEAR(created) as yearly, 
       count(*) as new_users FROM users
GROUP BY YEAR(created),MONTH(created)
ORDER BY YEAR(created) DESC, MONTH(created) DESC

返回以下数据，显示 11 月、2018 年 12 月、2019 年 1 月创建的各 1 个用户以及 2019 年 2 月创建的 2 个用户:

------------------------------------
----monthly---yearly----new_users---
----11--------2018-------1----------
----12--------2018-------1----------
----1---------2019-------1----------
----2---------2019-------2----------
------------------------------------

另一个表login_history保存了系统中用户登录的记录，显示具有user_id的用户在2018年11月登录了5次，在12月各登录了2次2018年、2019年1月和2019年2月:

----------------------------------------------
-- user_id -- | -- login_time ----------------
--  1  ------ | -- 2018-11-01 13:07:24 -------
--  1  ------ | -- 2018-11-02 13:07:24 -------
--  1  ------ | -- 2018-11-09 13:07:24 -------
--  1  ------ | -- 2018-11-10 13:07:24 -------
--  1  ------ | -- 2018-11-21 13:07:24 -------
--  1  ------ | -- 2018-12-01 13:07:24 -------
--  1  ------ | -- 2018-12-11 13:07:24 -------
--  1  ------ | -- 2019-01-01 13:07:24 -------
--  1  ------ | -- 2018-01-05 13:07:24 -------
--  1  ------ | -- 2018-02-01 13:07:24 -------
--  1  ------ | -- 2018-02-07 13:07:24 -------
----------------------------------------------

现在，基于类似的数据集，我必须获取在一年中的特定月份创建时，在第一周、第二周、第三周等再次登录系统的用户数....直到第五周。

我已将示例数据集更改为更大，输出应如下所示，即 2018 年 11 月创建的 100 个新用户，第 1 周登录的 100 个用户中的 98 个，第 2 周登录的 100 个用户中的 80 个，依此类推...

------------------------------------------------------------------------------------------
----monthly---yearly----new_users-------week1------week2----week3------week4-----week5----
----11--------2018-------100-------------98---------80-------60---------70--------10------
----12--------2018-------200-------------190--------150------120--------100-------30------
----1---------2019-------300-------------288--------250------200--------100-------50------
----2---------2019-------400-------------360--------200-------100--------50-------5-------
------------------------------------------------------------------------------------------

到目前为止我尝试过的内容如下，但我无法获得周数:

SELECT t1.monthly, t1.yearly, t1.total as total_users, t2.total as logins from (
SELECT user_id, count(*) as total, YEAR(created) as yearly, MONTH(created) as monthly FROM users
GROUP BY user_id, YEAR(created),MONTH(created)
ORDER BY YEAR(created) DESC, MONTH(created) DESC) t1
join (SELECT user_id, COUNT(*) as total from login_history
GROUP BY user_id) t2
ON t1.user_id = t2.user_id;

Answer 1

下面的查询返回一个数据集，其中显示有多少用户在接下来的几周内登录。

SELECT
 MONTH(u.created) as monthly, YEAR(u.created) as yearly, count(DISTINCT u.user_id) as new_users,
 COUNT(DISTINCT CASE WHEN lh.login_time BETWEEN u.created AND (u.created + INTERVAL 1 WEEK) THEN  u.user_id END) AS week1,
 COUNT(DISTINCT CASE WHEN lh.login_time BETWEEN DATE_ADD(u.created, INTERVAL 1 WEEK) AND ( DATE_ADD(u.created, INTERVAL 1 WEEK) + INTERVAL 1 WEEK) THEN u.user_id END) AS week2,
 COUNT(DISTINCT CASE WHEN lh.login_time BETWEEN DATE_ADD(u.created, INTERVAL 2 WEEK) AND ( DATE_ADD(u.created, INTERVAL 2 WEEK) + INTERVAL 1 WEEK) THEN u.user_id END) AS week3,
 COUNT(DISTINCT CASE WHEN lh.login_time BETWEEN DATE_ADD(u.created, INTERVAL 3 WEEK) AND ( DATE_ADD(u.created, INTERVAL 3 WEEK) + INTERVAL 1 WEEK) THEN u.user_id END) AS week4,
 COUNT(DISTINCT CASE WHEN lh.login_time BETWEEN DATE_ADD(u.created, INTERVAL 4 WEEK) AND ( DATE_ADD(u.created, INTERVAL 4 WEEK) + INTERVAL 1 WEEK) THEN u.user_id END) AS week5,
 COUNT(DISTINCT CASE WHEN lh.login_time BETWEEN DATE_ADD(u.created, INTERVAL 5 WEEK) AND ( DATE_ADD(u.created, INTERVAL 5 WEEK) + INTERVAL 1 WEEK) THEN u.user_id END) AS week6,
 COUNT(DISTINCT CASE WHEN lh.login_time BETWEEN DATE_ADD(u.created, INTERVAL 6 WEEK) AND ( DATE_ADD(u.created, INTERVAL 6 WEEK) + INTERVAL 1 WEEK) THEN u.user_id END) AS week7,
 COUNT(DISTINCT CASE WHEN lh.login_time BETWEEN DATE_ADD(u.created, INTERVAL 7 WEEK) AND ( DATE_ADD(u.created, INTERVAL 7 WEEK) + INTERVAL 1 WEEK) THEN u.user_id END) AS week8,
 COUNT(DISTINCT CASE WHEN lh.login_time BETWEEN DATE_ADD(u.created, INTERVAL 8 WEEK) AND ( DATE_ADD(u.created, INTERVAL 8 WEEK) + INTERVAL 1 WEEK) THEN u.user_id END) AS week9,
 COUNT(DISTINCT CASE WHEN lh.login_time BETWEEN DATE_ADD(u.created, INTERVAL 9 WEEK) AND ( DATE_ADD(u.created, INTERVAL 9 WEEK) + INTERVAL 1 WEEK) THEN u.user_id END) AS week10 
 FROM
  users as u
LEFT JOIN
  login_history as lh
    ON u.user_id = lh.user_id
GROUP BY 
    YEAR(u.created), MONTH(u.created)
ORDER BY 
    YEAR(u.created) DESC, MONTH(u.created) DESC
LIMIT 10;

输出为:

monthly, yearly, new_users, week1, week2, week3, week4, week5, week6, week7, week8, week9, week10
3, 2019, 1418, 676, 131, 107, 55, 8, 0, 0, 0, 0, 0
2, 2019, 1452, 707, 241, 234, 239, 202, 157, 93, 32, 2, 0
1, 2019, 2664, 1178, 469, 404, 359, 332, 316, 284, 321, 321, 214
12, 2018, 1574, 340, 50, 88, 53, 17, 47, 50, 29, 48, 19
11, 2018, 1608, 689, 138, 97, 84, 87, 76, 46, 55, 51, 54
10, 2018, 5349, 1817, 763, 691, 656, 585, 531, 478, 465, 455, 422
9, 2018, 5318, 2959, 1295, 1330, 1172, 1101, 1137, 1099, 971, 913, 917
8, 2018, 209, 73, 11, 9, 8, 8, 8, 9, 12, 10, 8
7, 2018, 223, 57, 7, 4, 1, 1, 0, 0, 0, 1, 1
6, 2018, 172, 62, 11, 6, 9, 6, 5, 4, 3, 1, 0