基于Laravel Query Builder/Eloquent计算MAU的查询需求

我拥有users和login_histories两张数据表，表结构及示例数据如下：

users表 #

login_histories表 #

期望统计结果 #

字段定义 #

• user_count：对应年月的用户总数（用户创建时间≤当月最后一天，且删除时间为空或>当月最后一天）
• login_user_count：对应年月有登录记录的唯一用户数
• mau：登录用户数占总用户数的比例（login_user_count/user_count，结果保留两位小数）

Laravel Query Builder 实现代码 #

use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

// 获取统计时间范围的边界
$minDate = DB::table('users')->min('created_at');
$maxDate = max(
    DB::table('users')->whereNotNull('deleted_at')->max('deleted_at') ?? now(),
    DB::table('login_histories')->max('login_at') ?? now()
);

// 生成需要统计的所有年月数据
$years = range(Carbon::parse($minDate)->year, Carbon::parse($maxDate)->year);
$dateRange = collect();

foreach ($years as $year) {
    // 确定当前年份需要统计的月份范围
    $startMonth = $year == Carbon::parse($minDate)->year ? Carbon::parse($minDate)->month : 1;
    $endMonth = $year == Carbon::parse($maxDate)->year ? Carbon::parse($maxDate)->month : 12;
    
    for ($month = $startMonth; $month <= $endMonth; $month++) {
        $monthEnd = Carbon::create($year, $month, 1)->endOfMonth()->toDateTimeString();
        $dateRange->push([
            'year' => $year,
            'month' => $month,
            'month_end' => $monthEnd
        ]);
    }
}

// 构建统计查询
$stats = DB::table(DB::raw('(' . $dateRange->map(function ($item) {
    return "SELECT {$item['year']} AS year, {$item['month']} AS month, '{$item['month_end']}' AS month_end";
})->implode(' UNION ALL ') . ') AS date_range'))
    ->selectRaw('
        date_range.year,
        LPAD(date_range.month, 2, "0") AS month,
        -- 计算当月有效用户总数
        (SELECT COUNT(id) 
         FROM users 
         WHERE created_at <= date_range.month_end 
         AND (deleted_at IS NULL OR deleted_at > date_range.month_end)) AS user_count,
        -- 计算当月登录的唯一用户数
        (SELECT COUNT(DISTINCT user_id) 
         FROM login_histories 
         WHERE YEAR(login_at) = date_range.year AND MONTH(login_at) = date_range.month) AS login_user_count,
        -- 计算MAU，处理除数为0的情况避免报错
        ROUND(
            COALESCE((SELECT COUNT(DISTINCT user_id) 
                      FROM login_histories 
                      WHERE YEAR(login_at) = date_range.year AND MONTH(login_at) = date_range.month), 0) / 
            NULLIF((SELECT COUNT(id) 
                    FROM users 
                    WHERE created_at <= date_range.month_end 
                    AND (deleted_at IS NULL OR deleted_at > date_range.month_end)), 0),
            2
        ) AS mau
    ')
    ->orderBy('date_range.year', 'date_range.month')
    ->get();

代码说明 #

1. 先统计出需要覆盖的时间范围，从最早的用户创建时间到最晚的用户删除/登录时间
2. 生成该范围内的所有年月节点，每个节点附带当月最后一天的时间戳
3. 通过关联子查询分别计算每个年月的有效用户数、登录用户数
4. 计算MAU时用NULLIF处理用户总数为0的情况，避免数据库报错，用ROUND保留两位小数

内容的提问来源于stack exchange，提问作者bluestar0505