#mysql
#mysql
Вопрос:
Есть две таблицы — 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
который возвращает приведенные ниже данные, которые показывают, что каждый пользователь по 1 был создан в ноябре, декабре 2018 года, январе 2019 года и 2 пользователя, созданные в феврале 2019 года :
------------------------------------
----monthly---yearly----new_users---
----11--------2018-------1----------
----12--------2018-------1----------
----1---------2019-------1----------
----2---------2019-------2----------
------------------------------------
Другая таблица login_history хранит запись о входах пользователей в систему, которая показывает, что пользователь user_id входил в систему 5 раз в ноябре 2018 года, по 2 раза в декабре 2018 года, январе 2019 года и феврале 2019 года:
----------------------------------------------
-- 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 -------
----------------------------------------------
Теперь, основываясь на аналогичном наборе данных, я должен получить количество пользователей, которые при создании в определенном месяце года снова вошли в систему на первой неделе, затем на второй неделе, затем на третьей и так далее…. до 5-й недели.
Я изменил выборку данных, чтобы она была больше, и результат должен быть таким, как показано ниже, т. е. 100 новых пользователей, которые были созданы в ноябре 2018 года, 98 из 100 вошли в систему на 1-й неделе, 80 из 100 на 2-й неделе и так далее…
------------------------------------------------------------------------------------------
----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;
Ответ №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