gpt4 book ai didi

mysql - 如何使用 SQL 计算留存率?

转载 作者:行者123 更新时间:2023-11-28 23:27:43 28 4
gpt4 key购买 nike

我正在尝试计算 1 天、7 天和 2 周的留存率,但我被卡住了。名为 users 的表包含首次登录的日期。名为 logins 的表包含所有后续登录的日期。

我的想法是从 logins 表中找到“最大”或最近登录,并找出自用户首次登录以来的天数。最终,我想按首次登录日期分组,并计算 1 天后、7 天后和 14 天后登录该应用程序的这个“队列”中的用户数量。我觉得我很接近,但还不完全。

以下是我目前所拥有的。

DESCRIBE users;
Field Type Null Key Default Extra
---------------- ----------- ---- --- ------------------- ---------------------------
uid int(11) YES MUL (null)
device_id varchar(64) YES (null)
install_ts timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
firstlogin_ts timestamp NO 0000-00-00 00:00:00
firstpurchase_ts timestamp NO 0000-00-00 00:00:00

DESCRIBE logins;
Field Type Null Key Default Extra
----------- ----------- ---- --- ----------------- ---------------------------
uid int(11) NO (null)
device_id varchar(64) NO (null)
login_ts timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
login_count int(11) NO (null)
level int(11) NO (null)


SELECT logins.uid,
MAX(CAST(login_ts AS Date)) AS login_dt,
CAST(firstlogin_ts AS Date) AS firstlogin_dt,
MAX(DATEDIFF(CAST(login_ts AS Date),
CAST(firstlogin_ts AS Date))) AS retentionDays
FROM logins
LEFT JOIN users
ON logins.uid = users.uid
GROUP BY logins.uid
ORDER BY logins.uid, login_dt

uid login_dt firstlogin_dt retentionDays
------ ---------- ------------- -------------
121043 2015-01-04 (null) (null)
121044 2015-01-04 (null) (null)
121045 2015-01-06 2015-01-01 5
121046 2015-01-05 2015-01-01 4
121047 2015-01-04 2015-01-01 3
121049 2015-01-05 2015-01-01 4
121050 2015-01-02 2015-01-01 1
121054 2015-01-04 2015-01-01 3
121055 2015-01-15 2015-01-01 14
121056 2015-01-07 2015-01-01 6
121057 2015-01-12 2015-01-01 11
121058 2015-01-02 2015-01-01 1
121060 2015-01-11 2015-01-01 10
121063 2015-01-07 2015-01-01 6
121065 2015-01-05 2015-01-01 4
121066 2015-01-07 2015-01-01 6
121067 2015-01-03 2015-01-01 2
121069 2015-01-03 2015-01-01 2
121070 2015-01-06 2015-01-01 5

如有任何提示,我们将不胜感激。

最佳答案

我假设您问题中的查询是正确的,您只需要帮助更进一步。

看起来您想要做的是从第一个查询的结果中进行选择,并计算 retentionDays >= 1 的用户数、retentionDays >= 7 的用户数和 retentionDays 的用户数 > = 14.

用 SQL 翻译,给出如下查询:

SELECT
SUM(IF(retentionDays >= 1, 1, 0)) as 1day
SUM(IF(retentionDays >= 7, 1, 0)) as 7days
SUM(IF(retentionDays >= 14, 1, 0)) as 14days
FROM (
/* your previous query */
) as computedRetentionDays;

如果您想要一个比率而不是值,您可以将每个总和除以 COUNT(uid)

话虽如此,我建议您在 users 上添加一列 laSTLogin_ts,这将使您的生活更轻松(并且此查询更快)。

关于mysql - 如何使用 SQL 计算留存率?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38577175/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com