sql 计算2日、3日、7日、15日、30日留存率 语句

假设有一个用户行为表 user_behavior，包含以下字段：

- user_id：用户ID
- behavior_time：行为发生时间

可以使用以下SQL语句计算2日、3日、7日、15日、30日留存率：

-- 2日留存率
SELECT
  COUNT(DISTINCT b1.user_id) / COUNT(DISTINCT b2.user_id) AS `2_day_retention_rate`
FROM
  user_behavior b1
  JOIN user_behavior b2 ON b1.user_id = b2.user_id AND DATEDIFF(b1.behavior_time, b2.behavior_time) = 2
WHERE
  b1.behavior_time >= '2022-01-01' AND b1.behavior_time < '2022-02-01'
  AND b2.behavior_time >= '2022-01-01' AND b2.behavior_time < '2022-02-01';

-- 3日留存率
SELECT
  COUNT(DISTINCT b1.user_id) / COUNT(DISTINCT b2.user_id) AS `3_day_retention_rate`
FROM
  user_behavior b1
  JOIN user_behavior b2 ON b1.user_id = b2.user_id AND DATEDIFF(b1.behavior_time, b2.behavior_time) = 3
WHERE
  b1.behavior_time >= '2022-01-01' AND b1.behavior_time < '2022-02-01'
  AND b2.behavior_time >= '2022-01-01' AND b2.behavior_time < '2022-02-01';

-- 7日留存率
SELECT
  COUNT(DISTINCT b1.user_id) / COUNT(DISTINCT b2.user_id) AS `7_day_retention_rate`
FROM
  user_behavior b1
  JOIN user_behavior b2 ON b1.user_id = b2.user_id AND DATEDIFF(b1.behavior_time, b2.behavior_time) = 7
WHERE
  b1.behavior_time >= '2022-01-01' AND b1.behavior_time < '2022-02-01'
  AND b2.behavior_time >= '2022-01-01' AND b2.behavior_time < '2022-02-01';

-- 15日留存率
SELECT
  COUNT(DISTINCT b1.user_id) / COUNT(DISTINCT b2.user_id) AS `15_day_retention_rate`
FROM
  user_behavior b1
  JOIN user_behavior b2 ON b1.user_id = b2.user_id AND DATEDIFF(b1.behavior_time, b2.behavior_time) = 15
WHERE
  b1.behavior_time >= '2022-01-01' AND b1.behavior_time < '2022-02-01'
  AND b2.behavior_time >= '2022-01-01' AND b2.behavior_time < '2022-02-01';

-- 30日留存率
SELECT
  COUNT(DISTINCT b1.user_id) / COUNT(DISTINCT b2.user_id) AS `30_day_retention_rate`
FROM
  user_behavior b1
  JOIN user_behavior b2 ON b1.user_id = b2.user_id AND DATEDIFF(b1.behavior_time, b2.behavior_time) = 30
WHERE
  b1.behavior_time >= '2022-01-01' AND b1.behavior_time < '2022-02-01'
  AND b2.behavior_time >= '2022-01-01' AND b2.behavior_time < '2022-02-01';

其中，日期范围可以根据实际情况进行修改。这些SQL语句可以分别计算出2日、3日、7日、15日、30日留存率。