PHP 怎么留存分析

wen PHP项目 2026-08-09 2

本文目录导读：

PHP 怎么留存分析

核心概念：留存的定义
数据表设计（两表方案）
PHP 数据采集（埋点）代码
PHP 计算留存率（核心算法 SQL）
PHP 查询并格式化输出（JSON API）
优化与进阶（重要！）
前端展示（可选）

在 PHP 中实现“留存分析”（Retention Analysis），通常涉及数据采集、存储、计算和展示四个环节。

由于 PHP 是服务端脚本语言，它不适合也不应该直接做大数据量的 OLAP 计算（即用户量大时，用 PHP 做 GROUP BY 会很慢），核心思路是：PHP 负责埋点入库，数据库（如 MySQL/ClickHouse）负责计算，PHP 只负责查询结果并渲染图表。

以下是 PHP 实现留存分析的标准流程与代码实战：

核心概念：留存的定义

在写代码前,必须明确留存的计算口径。

新增日（D0）：用户首次注册或首次启动的日期。
次日留存（D+1）：在 D0 新增的用户中，有多少人在 D0+1 天再次活跃。
7日留存（D+7）：在 D0 新增的用户中，有多少人在 D0+7 天再次活跃（通常指“第7天”，也有指“7日内”）。

数据表设计（两表方案）

为了计算留存,至少需要两张核心表：用户表（确认新增日期）和活跃日志表（确认活跃日期）。（也可以直接用一张全量事件表）

-- 1. 用户主表（用于记录首次注册时间，作为 D0）
CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` VARCHAR(64) NOT NULL COMMENT '业务用户ID',
  `first_active_date` DATE NOT NULL COMMENT '首次活跃日期（D0）',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id` (`user_id`),
  KEY `idx_first_date` (`first_active_date`)
) ENGINE=InnoDB;
-- 2. 活跃日志表（每日一行，记录当天活跃的用户，常用于计算留存）
CREATE TABLE `user_active_daily` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` VARCHAR(64) NOT NULL,
  `active_date` DATE NOT NULL COMMENT '活跃日期',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_date` (`user_id`, `active_date`),
  KEY `idx_active_date` (`active_date`)
) ENGINE=InnoDB;

注意：如果用户量极大（如百万级以上），推荐数据库用 ClickHouse，PHP 只做查询代理。

PHP 数据采集（埋点）代码

当用户登录或启动 APP 时，PHP 接口写入日志。注意使用“去重键”防止重复写入。

<?php
// 文件：api/login.php （登录接口）
function recordUserActive(string $userId): void {
    $date = date('Y-m-d');
    $pdo = getPdo(); // 获取PDO连接
    // 1. 检查是否为新增用户（如果不是，就插入 only 活跃表）
    $stmt = $pdo->prepare('INSERT INTO users (user_id, first_active_date) VALUES (?, ?) 
                            ON DUPLICATE KEY UPDATE user_id = VALUES(user_id)');
    $stmt->execute([$userId, $date]);
    // 2. 写入今日活跃（使用 INSERT IGNORE 防重复）
    $stmt = $pdo->prepare('INSERT IGNORE INTO user_active_daily (user_id, active_date) VALUES (?, ?)');
    $stmt->execute([$userId, $date]);
}
// 调用
recordUserActive($_POST['user_id']);

PHP 计算留存率（核心算法 SQL）

留存分析最推荐用 交叉表（Crosstab） 输出：行 = 新增日期，列 = 留存周期（D+1, D+3...）。

推荐用 SQL 联表 + 条件聚合，PHP 只取结果，这样性能最高：

SELECT 
    u.first_active_date AS `新增日期`,
    COUNT(DISTINCT u.user_id) AS `新增用户数`,
    -- 次日留存
    COUNT(DISTINCT CASE 
        WHEN a1.active_date = DATE_ADD(u.first_active_date, INTERVAL 1 DAY) 
        THEN u.user_id END) AS `次日留存人数`,
    -- 3日留存
    COUNT(DISTINCT CASE 
        WHEN a3.active_date = DATE_ADD(u.first_active_date, INTERVAL 3 DAY) 
        THEN u.user_id END) AS `3日留存人数`,
    -- 7日留存
    COUNT(DISTINCT CASE 
        WHEN a7.active_date = DATE_ADD(u.first_active_date, INTERVAL 7 DAY) 
        THEN u.user_id END) AS `7日留存人数`
FROM users u
LEFT JOIN user_active_daily a1 ON u.user_id = a1.user_id 
LEFT JOIN user_active_daily a3 ON u.user_id = a3.user_id 
LEFT JOIN user_active_daily a7 ON u.user_id = a7.user_id 
-- 筛选最近10天的新增用户作为观察窗口
WHERE u.first_active_date BETWEEN ? AND ? 
GROUP BY u.first_active_date
ORDER BY u.first_active_date DESC;

PHP 查询并格式化输出（JSON API）

<?php
// 文件：api/retention.php
header('Content-Type: application/json');
$pdo = getPdo();
$days = 10; // 查看最近10天的留存
$startDate = date('Y-m-d', strtotime("-{$days} days"));
$endDate = date('Y-m-d'); // 
$sql = "
    SELECT 
        u.first_active_date AS date,
        COUNT(DISTINCT u.user_id) AS total_d0,
        -- 次日留存
        COALESCE(SUM(CASE WHEN a1.active_date = DATE_ADD(u.first_active_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END), 0) AS d1_users,
        -- 3日
        COALESCE(SUM(CASE WHEN a3.active_date = DATE_ADD(u.first_active_date, INTERVAL 3 DAY) THEN 1 ELSE 0 END), 0) AS d3_users,
        -- 7日
        COALESCE(SUM(CASE WHEN a7.active_date = DATE_ADD(u.first_active_date, INTERVAL 7 DAY) THEN 1 ELSE 0 END), 0) AS d7_users
    FROM users u
    LEFT JOIN user_active_daily a1 ON u.user_id = a1.user_id
    LEFT JOIN user_active_daily a3 ON u.user_id = a3.user_id
    LEFT JOIN user_active_daily a7 ON u.user_id = a7.user_id
    WHERE u.first_active_date BETWEEN :start_date AND :end_date
    GROUP BY u.first_active_date
    ORDER BY u.first_active_date DESC
";
$stmt = $pdo->prepare($sql);
$stmt->execute([':start_date' => $startDate, ':end_date' => $endDate]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 计算百分比并组装前端需要的格式
$response = [];
foreach ($rows as $row) {
    $d0 = $row['total_d0'];
    $response[] = [
        'date' => $row['date'],
        'd0' => (int)$d0,
        'd1_rate' => $d0 > 0 ? round($row['d1_users'] / $d0 * 100, 2) : 0,
        'd3_rate' => $d0 > 0 ? round($row['d3_users'] / $d0 * 100, 2) : 0,
        'd7_rate' => $d0 > 0 ? round($row['d7_users'] / $d0 * 100, 2) : 0,
    ];
}
echo json_encode(['success' => true, 'data' => $response]);

优化与进阶（重要！）

方案 A：使用 Python/Go 定时脚本计算（推荐中大型项目）

PHP 不适合做重型聚合，建议数据入库后，用定时任务（如 Python+Spark，或纯 SQL 定时跑批）计算好结果，预先存入一张 retention_result 汇总表，PHP 接口只 SELECT 这张汇总表，响应速度极快。

方案 B：分桶留存（区间计算）

如果只需要看“整体留存”，而非“按日留存”，可以直接用单表查询：

-- 整体次日留存：
SELECT 
  (SELECT COUNT(DISTINCT user_id) FROM user_active_daily WHERE active_date = '2023-10-01') AS total_new,
  (SELECT COUNT(DISTINCT b.user_id) 
   FROM user_active_daily AS a 
   JOIN user_active_daily AS b ON a.user_id = b.user_id 
   WHERE a.active_date = '2023-10-01' 
     AND b.active_date = '2023-10-02') AS retained_users;

方案 C：窗口函数（MySQL 8.0+ / PostgreSQL）

对于计算“N日留存”，窗口函数可以一步到位，代码更简洁，也支持 PHP 直接调用。

前端展示（可选）

PHP 返回 JSON 后，前端用 ECharts 的 “柱状图 + 折线图” 展示：

// 前端拉取数据并渲染（伪代码）
fetch('/api/retention.php')
  .then(res => res.json())
  .then(data => {
    const dates = data.data.map(i => i.date);
    const rates = data.data.map(i => i.d1_rate);
    // 配置 ECharts option...
  });

核心原则：PHP 做埋点采集和轻查询展示，数据库负责聚合计算。
防止并发重复：入库使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE。
性能瓶颈：如果每天数据量过亿，请升级 MySQL 索引，或迁移至 ClickHouse；PHP 只做数据中转站，绝不循环去数据库查单条数据计算留存。