求指导：留存时间的R语言/SQL计算方法（附周分组SQL示例）

SQL实现 #

场景1：计算用户首次下单到后续订单的时间间隔 #

假设sales表包含user_id、order_date、quantity字段，以下SQL会计算每个用户后续订单与首次订单的间隔天数/周数：

WITH user_orders AS (
    SELECT 
        user_id,
        order_date,
        MIN(order_date) OVER (PARTITION BY user_id) AS first_order_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_seq
    FROM sales
)
SELECT 
    user_id,
    order_date,
    first_order_date,
    DATEDIFF(day, first_order_date, order_date) AS retention_days,
    DATEDIFF(week, first_order_date, order_date) AS retention_weeks
FROM user_orders
WHERE order_seq > 1;

场景2：按周统计新用户留存率 #

如果需要统计每周新增用户在后续周的留存情况（比如第0周、第1周留存率），可以用以下代码：

WITH new_users AS (
    SELECT 
        user_id,
        DATEPART(week, MIN(order_date)) AS first_order_week
    FROM sales
    GROUP BY user_id
),
user_weekly_orders AS (
    SELECT 
        s.user_id,
        DATEPART(week, s.order_date) AS order_week
    FROM sales s
    GROUP BY s.user_id, DATEPART(week, s.order_date)
)
SELECT 
    nu.first_order_week AS signup_week,
    uwo.order_week AS retention_week,
    uwo.order_week - nu.first_order_week AS weeks_since_signup,
    COUNT(DISTINCT uwo.user_id) AS retained_users,
    (SELECT COUNT(DISTINCT user_id) FROM new_users WHERE first_order_week = nu.first_order_week) AS total_new_users,
    ROUND(COUNT(DISTINCT uwo.user_id) * 100.0 / (SELECT COUNT(DISTINCT user_id) FROM new_users WHERE first_order_week = nu.first_order_week), 2) AS retention_rate
FROM new_users nu
JOIN user_weekly_orders uwo ON nu.user_id = uwo.user_id
WHERE uwo.order_week >= nu.first_order_week
GROUP BY nu.first_order_week, uwo.order_week
ORDER BY nu.first_order_week, uwo.order_week;

R语言实现 #

假设已将sales数据导入为R数据框，包含user_id、order_date（需为日期类型）、quantity字段。

场景1：计算用户首次下单到后续订单的时间间隔 #

library(dplyr)
library(lubridate)

# 转换日期格式（若未转换）
sales$order_date <- ymd(sales$order_date)

user_retention <- sales %>%
  group_by(user_id) %>%
  mutate(
    first_order_date = min(order_date),
    order_seq = row_number(order_date),
    retention_days = as.numeric(order_date - first_order_date),
    retention_weeks = floor(retention_days / 7)
  ) %>%
  filter(order_seq > 1) %>%
  ungroup()

# 查看结果
head(user_retention)

场景2：按周统计新用户留存率 #

library(dplyr)
library(lubridate)

sales$order_date <- ymd(sales$order_date)
sales$order_week <- week(sales$order_date)

# 确定用户首次下单周
new_users <- sales %>%
  group_by(user_id) %>%
  summarise(first_order_week = min(order_week)) %>%
  ungroup()

# 去重用户每周下单记录
user_weekly_orders <- sales %>%
  distinct(user_id, order_week)

# 计算周留存率
weekly_retention <- new_users %>%
  inner_join(user_weekly_orders, by = "user_id") %>%
  filter(order_week >= first_order_week) %>%
  group_by(first_order_week, order_week) %>%
  summarise(retained_users = n_distinct(user_id)) %>%
  ungroup() %>%
  left_join(
    new_users %>%
      group_by(first_order_week) %>%
      summarise(total_new_users = n_distinct(user_id)),
    by = "first_order_week"
  ) %>%
  mutate(
    weeks_since_signup = order_week - first_order_week,
    retention_rate = round(retained_users / total_new_users * 100, 2)
  ) %>%
  arrange(first_order_week, order_week)

# 查看结果
print(weekly_retention)

内容的提问来源于stack exchange，提问作者Tasya Fauzia Fitriasari