mysql 如何在SQL中计算保留曲线？

1yjd4xko 于 2022-10-31 发布在 Mysql

关注(0)|答案(1)|浏览(158)

给定的eventLog表包含userid和ds列。这表示给定用户在“网站”上发生的所有事件。
示例数据：
| 用户标识|数据处理系统|
| - -|- -|
| 使用者1| 2022年1月1日|
| 用户2| 2022年2月11日|
| 用户2| 2022年3月21日|
| 用户3| 2022年1月11日|
| 用户3| 2022年2月27日|
| 用户3| 2022年4月6日|
我需要从表中最早的月份开始计算每月保留曲线。如果用户在给定月份没有执行操作（在eventLog中没有记录），则将其视为流失用户。
我试过了：

SELECT DATE_FORMAT(ds, '%Y-%m-01') as ds_month
      ,COUNT(DISTINCT userid) * 1.0 / COUNT(*) as retention_rate
FROM eventLog
GROUP BY DATE_FORMAT(ds, '%Y-%m-01')

这是小提琴：http://sqlfiddle.com/#!9/f6bdefc/4
我得到了以下输出：

预期结果为：
| 月份|保留率|推理|
| - -|- -|- -|
| 2022年1月1日|100%的|根据定义，这是100%-user 1和user 3在本月执行了第一个操作|
| 2022年2月1日|百分之六十六|保留2 / 3个用户（用户2执行第一个操作，用户3保留，用户1流失）|
| 2022年3月1日|百分之三十三|保留1 / 3个用户（保留用户2，用户1和用户3流失）|
| 2022年4月1日|百分之三十三|保留1 / 3个用户（保留用户3，用户1和用户2流失）|

mysql

来源：https://stackoverflow.com/questions/74213286/how-to-calculate-retention-curve-in-sql

1条答案

按热度按时间

8yoxcaq71#

诀窍是将COUNT(DISTINCT userid)除以表中的总非重复用户数;子查询可以执行以下操作：SELECT COUNT(DISTINCT userid) FROM eventLog。将该语句与IF语句结合起来，检查2022-01-01是否作为返回100%的值存在（如果我理解的逻辑正确的话）。

SELECT 
  DATE_FORMAT(ds, '%Y-%m-01') as ds_month,
  CONCAT(FLOOR(IF(DATE_FORMAT(ds, '%Y-%m-01') = DATE_FORMAT(ds, '%Y-01-01'),100,(COUNT(DISTINCT userid)*100.0)/(SELECT COUNT(DISTINCT userid) FROM eventLog))),'%') AS user_count
FROM eventLog
GROUP BY DATE_FORMAT(ds, '%Y-%m-01')

结果：

| ds_month   | user_count  |
|------------|-------------|
| 2022-01-01 | 100%        |
| 2022-02-01 | 66%         |
| 2022-03-01 | 33%         |
| 2022-04-01 | 33%         |

**注意：**我使用FLOOR()函数是因为您为2022-02-01返回了66%而不是67%。如果需要，您可以使用FORMAT(column, 0)（或您想要的任何小数位）将2022-02-01向上舍入为67%。

"小提琴“

赞(0）回复(0）举报 2022-10-31

我来回答

mysql 如何在SQL中计算保留曲线？

1条答案

相关问题

热门标签

最新问答