写一个查询留存率的MYSQL代码
时间: 2023-06-01 11:06:47 浏览: 124
以下是一些可能的MySQL代码,用于查询用户留存率(即在某段时间内连续访问您的应用程序的用户百分比):
-- 计算留存率
SELECT
DATE_FORMAT(t1.create_time, '%Y-%m-%d') AS day,
COUNT(DISTINCT t1.user_id) AS total_users,
SUM(t2.next_day IS NOT NULL) AS next_day_retention,
SUM(t3.next_day IS NOT NULL) AS third_day_retention,
SUM(t7.next_day IS NOT NULL) AS seventh_day_retention
FROM user_t AS t1
LEFT JOIN (
-- 第二天有访问记录的用户
SELECT DISTINCT user_id, create_time AS next_day
FROM user_t
WHERE create_time >= '2021-01-01' AND create_time < '2021-01-02'
) AS t2 ON t1.user_id = t2.user_id
LEFT JOIN (
-- 第三天有访问记录的用户
SELECT DISTINCT user_id, create_time AS next_day
FROM user_t
WHERE create_time >= '2021-01-02' AND create_time < '2021-01-03'
) AS t3 ON t1.user_id = t3.user_id
LEFT JOIN (
-- 第七天有访问记录的用户
SELECT DISTINCT user_id, create_time AS next_day
FROM user_t
WHERE create_time >= '2021-01-06' AND create_time < '2021-01-07'
) AS t7 ON t1.user_id = t7.user_id
WHERE t1.create_time >= '2021-01-01' AND t1.create_time < '2021-01-08'
GROUP BY day;
这里假设您有一个名为"user_t"的用户表,其中包含每个用户的ID和创建时间。该查询将计算从2021年1月1日到1月7日的留存率,并返回总用户数和每个查询日期的用户留存数。请注意,这只是一种可能的实现方式,具体取决于您的数据结构和查询需求。
阅读全文