给定的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流失)|
1条答案
按热度按时间8yoxcaq71#
诀窍是将
COUNT(DISTINCT userid)
除以表中的总非重复用户数;子查询可以执行以下操作:SELECT COUNT(DISTINCT userid) FROM eventLog
。将该语句与IF
语句结合起来,检查2022-01-01
是否作为返回100%
的值存在(如果我理解的逻辑正确的话)。结果:
**注意:**我使用
FLOOR()
函数是因为您为2022-02-01
返回了66%
而不是67%
。如果需要,您可以使用FORMAT(column, 0)
(或您想要的任何小数位)将2022-02-01
向上舍入为67%
。"小提琴“