用户留存数和留存率只要在3日有第二次登陆都算留存,7日内有第二次登陆也算7日留存,14日内有第二次登陆也算14日留存,30日内有第二次登陆也算30日留存,现在要求不同的日期,用户数和他的3日留存数,3留存率,7日留存数,7日留存率,14日留存数,14日留存率,30日留存数,30日留存率,这个sql要怎么写
时间: 2024-03-01 15:54:37 浏览: 19
可以使用以下SQL语句来实现:
```
SELECT
DATE(date) AS date,
COUNT(DISTINCT user_id) AS user_count,
SUM(CASE WHEN DATEDIFF(date, login_date) = 3 THEN 1 ELSE 0 END) AS day3_retention_count,
SUM(CASE WHEN DATEDIFF(date, login_date) = 3 THEN 1 ELSE 0 END) / COUNT(DISTINCT user_id) AS day3_retention_rate,
SUM(CASE WHEN DATEDIFF(date, login_date) = 7 THEN 1 ELSE 0 END) AS day7_retention_count,
SUM(CASE WHEN DATEDIFF(date, login_date) = 7 THEN 1 ELSE 0 END) / COUNT(DISTINCT user_id) AS day7_retention_rate,
SUM(CASE WHEN DATEDIFF(date, login_date) = 14 THEN 1 ELSE 0 END) AS day14_retention_count,
SUM(CASE WHEN DATEDIFF(date, login_date) = 14 THEN 1 ELSE 0 END) / COUNT(DISTINCT user_id) AS day14_retention_rate,
SUM(CASE WHEN DATEDIFF(date, login_date) = 30 THEN 1 ELSE 0 END) AS day30_retention_count,
SUM(CASE WHEN DATEDIFF(date, login_date) = 30 THEN 1 ELSE 0 END) / COUNT(DISTINCT user_id) AS day30_retention_rate
FROM
user_login
GROUP BY
DATE(date)
```
其中,`user_login`是用户登录数据表的表名,包含以下列:
- user_id: 用户ID
- date: 登录日期
- login_date: 用户第一次登录日期
SQL语句中的`SUM(CASE WHEN DATEDIFF(date, login_date) = x THEN 1 ELSE 0 END)`用于计算x日留存数,`SUM(CASE WHEN DATEDIFF(date, login_date) = x THEN 1 ELSE 0 END) / COUNT(DISTINCT user_id)`用于计算x日留存率。`DATE(date)`用于取出日期部分,`COUNT(DISTINCT user_id)`用于计算用户数。最后使用`GROUP BY DATE(date)`将结果按日期分组。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)