Laravel 10数据库层实现多层级统计数据差值与占比计算

针对你遇到的嵌套循环性能问题，直接在数据库层面完成双周期对比计算是最优解。以下是基于三层嵌套结构（Company->Affiliate->Campaign）的具体实现方案：

前提假设 #

假设你有一张campaign_stats统计表，结构如下：

id, company_id, affiliate_id, campaign_id, period_start, period_end, total_sessions, commission, ...其他统计字段

其中period_start/period_end用于标识统计周期（如当月/上月）。

1. 基础对比查询（Campaign层级） #

通过LEFT JOIN关联当前周期与历史周期的统计数据，直接在查询中计算差值和百分比：

use Carbon\Carbon;

// 定义对比周期
$currentStart = Carbon::parse('2024-05-01')->startOfDay();
$currentEnd = Carbon::parse('2024-05-31')->endOfDay();
$historicalStart = Carbon::parse('2024-04-01')->startOfDay();
$historicalEnd = Carbon::parse('2024-04-30')->endOfDay();

// 构建Campaign级别的对比统计
$campaignStats = DB::table('campaign_stats as current')
    ->select([
        // 关联字段
        'current.company_id',
        'current.affiliate_id',
        'current.campaign_id',
        // 当前周期数据
        'current.total_sessions as current_total_sessions',
        'current.commission as current_commission',
        // 历史周期数据（兼容空值）
        DB::raw('COALESCE(historical.total_sessions, 0) as historical_total_sessions'),
        DB::raw('COALESCE(historical.commission, 0) as historical_commission'),
        // 差值计算
        DB::raw('current.total_sessions - COALESCE(historical.total_sessions, 0) as diff_total_sessions'),
        DB::raw('current.commission - COALESCE(historical.commission, 0) as diff_commission'),
        // 百分比变化（处理除数为0的情况）
        DB::raw('CASE WHEN COALESCE(historical.total_sessions, 0) != 0 THEN ROUND(((current.total_sessions - COALESCE(historical.total_sessions, 0))/COALESCE(historical.total_sessions, 0))*100, 2) ELSE 0 END as pct_change_total_sessions'),
        DB::raw('CASE WHEN COALESCE(historical.commission, 0) != 0 THEN ROUND(((current.commission - COALESCE(historical.commission, 0))/COALESCE(historical.commission, 0))*100, 2) ELSE 0 END as pct_change_commission'),
    ])
    ->leftJoin('campaign_stats as historical', function ($join) use ($historicalStart, $historicalEnd) {
        $join->on('current.company_id', '=', 'historical.company_id')
             ->on('current.affiliate_id', '=', 'historical.affiliate_id')
             ->on('current.campaign_id', '=', 'historical.campaign_id')
             ->whereBetween('historical.period_start', [$historicalStart, $historicalEnd]);
    })
    ->whereBetween('current.period_start', [$currentStart, $currentEnd])
    ->get();

2. 上层层级汇总（Affiliate/Company） #

如果需要按Affiliate或Company层级汇总数据，只需在查询中添加GROUP BY并聚合统计字段：

Affiliate层级汇总 #

$affiliateStats = DB::table('campaign_stats as current')
    ->select([
        'current.company_id',
        'current.affiliate_id',
        DB::raw('SUM(current.total_sessions) as current_total_sessions'),
        DB::raw('SUM(current.commission) as current_commission'),
        DB::raw('SUM(COALESCE(historical.total_sessions, 0)) as historical_total_sessions'),
        DB::raw('SUM(COALESCE(historical.commission, 0)) as historical_commission'),
        DB::raw('SUM(current.total_sessions) - SUM(COALESCE(historical.total_sessions, 0)) as diff_total_sessions'),
        DB::raw('SUM(current.commission) - SUM(COALESCE(historical.commission, 0)) as diff_commission'),
        DB::raw('CASE WHEN SUM(COALESCE(historical.total_sessions, 0)) != 0 THEN ROUND(((SUM(current.total_sessions) - SUM(COALESCE(historical.total_sessions, 0)))/SUM(COALESCE(historical.total_sessions, 0)))*100, 2) ELSE 0 END as pct_change_total_sessions'),
        DB::raw('CASE WHEN SUM(COALESCE(historical.commission, 0)) != 0 THEN ROUND(((SUM(current.commission) - SUM(COALESCE(historical.commission, 0)))/SUM(COALESCE(historical.commission, 0)))*100, 2) ELSE 0 END as pct_change_commission'),
    ])
    ->leftJoin('campaign_stats as historical', function ($join) use ($historicalStart, $historicalEnd) {
        $join->on('current.company_id', '=', 'historical.company_id')
             ->on('current.affiliate_id', '=', 'historical.affiliate_id')
             ->whereBetween('historical.period_start', [$historicalStart, $historicalEnd]);
    })
    ->whereBetween('current.period_start', [$currentStart, $currentEnd])
    ->groupBy('current.company_id', 'current.affiliate_id')
    ->get();

Company层级汇总 #

$companyStats = DB::table('campaign_stats as current')
    ->select([
        'current.company_id',
        DB::raw('SUM(current.total_sessions) as current_total_sessions'),
        DB::raw('SUM(current.commission) as current_commission'),
        DB::raw('SUM(COALESCE(historical.total_sessions, 0)) as historical_total_sessions'),
        DB::raw('SUM(COALESCE(historical.commission, 0)) as historical_commission'),
        DB::raw('SUM(current.total_sessions) - SUM(COALESCE(historical.total_sessions, 0)) as diff_total_sessions'),
        DB::raw('SUM(current.commission) - SUM(COALESCE(historical.commission, 0)) as diff_commission'),
        DB::raw('CASE WHEN SUM(COALESCE(historical.total_sessions, 0)) != 0 THEN ROUND(((SUM(current.total_sessions) - SUM(COALESCE(historical.total_sessions, 0)))/SUM(COALESCE(historical.total_sessions, 0)))*100, 2) ELSE 0 END as pct_change_total_sessions'),
        DB::raw('CASE WHEN SUM(COALESCE(historical.commission, 0)) != 0 THEN ROUND(((SUM(current.commission) - SUM(COALESCE(historical.commission, 0)))/SUM(COALESCE(historical.commission, 0)))*100, 2) ELSE 0 END as pct_change_commission'),
    ])
    ->leftJoin('campaign_stats as historical', function ($join) use ($historicalStart, $historicalEnd) {
        $join->on('current.company_id', '=', 'historical.company_id')
             ->whereBetween('historical.period_start', [$historicalStart, $historicalEnd]);
    })
    ->whereBetween('current.period_start', [$currentStart, $currentEnd])
    ->groupBy('current.company_id')
    ->get();

3. 结合Eloquent关联获取嵌套结构 #

如果需要直接获取Company->Affiliate->Campaign的嵌套对比数据，可以在模型中定义关联方法：

// Company.php
public function affiliatesWithComparison($historicalStart, $historicalEnd, $currentStart, $currentEnd)
{
    return $this->hasMany(Affiliate::class)->with([
        // Campaign级别的对比数据
        'campaignsWithComparison' => function ($query) use ($historicalStart, $historicalEnd, $currentStart, $currentEnd) {
            $query->select([
                'campaigns.id',
                'campaigns.affiliate_id',
                'campaigns.name',
                'current.total_sessions as current_total_sessions',
                'current.commission as current_commission',
                DB::raw('COALESCE(historical.total_sessions, 0) as historical_total_sessions'),
                DB::raw('COALESCE(historical.commission, 0) as historical_commission'),
                DB::raw('current.total_sessions - COALESCE(historical.total_sessions, 0) as diff_total_sessions'),
                DB::raw('current.commission - COALESCE(historical.commission, 0) as diff_commission'),
                DB::raw('CASE WHEN COALESCE(historical.total_sessions, 0) != 0 THEN ROUND(((current.total_sessions - COALESCE(historical.total_sessions, 0))/COALESCE(historical.total_sessions, 0))*100, 2) ELSE 0 END as pct_change_total_sessions'),
                DB::raw('CASE WHEN COALESCE(historical.commission, 0) != 0 THEN ROUND(((current.commission - COALESCE(historical.commission, 0))/COALESCE(historical.commission, 0))*100, 2) ELSE 0 END as pct_change_commission'),
            ])
            ->join('campaign_stats as current', function ($join) use ($currentStart, $currentEnd) {
                $join->on('campaigns.id', '=', 'current.campaign_id')
                     ->whereBetween('current.period_start', [$currentStart, $currentEnd]);
            })
            ->leftJoin('campaign_stats as historical', function ($join) use ($historicalStart, $historicalEnd) {
                $join->on('campaigns.id', '=', 'historical.campaign_id')
                     ->whereBetween('historical.period_start', [$historicalStart, $historicalEnd]);
            });
        },
        // Affiliate级别的汇总对比数据
        'affiliateComparison' => function ($query) use ($historicalStart, $historicalEnd, $currentStart, $currentEnd) {
            $query->select([
                'affiliate_id',
                DB::raw('SUM(current.total_sessions) as current_total_sessions'),
                DB::raw('SUM(current.commission) as current_commission'),
                DB::raw('SUM(COALESCE(historical.total_sessions, 0)) as historical_total_sessions'),
                DB::raw('SUM(COALESCE(historical.commission, 0)) as historical_commission'),
                DB::raw('SUM(current.total_sessions) - SUM(COALESCE(historical.total_sessions, 0)) as diff_total_sessions'),
                DB::raw('SUM(current.commission) - SUM(COALESCE(historical.commission, 0)) as diff_commission'),
                DB::raw('CASE WHEN SUM(COALESCE(historical.total_sessions, 0)) != 0 THEN ROUND(((SUM(current.total_sessions) - SUM(COALESCE(historical.total_sessions, 0)))/SUM(COALESCE(historical.total_sessions, 0)))*100, 2) ELSE 0 END as pct_change_total_sessions'),
                DB::raw('CASE WHEN SUM(COALESCE(historical.commission, 0)) != 0 THEN ROUND(((SUM(current.commission) - SUM(COALESCE(historical.commission, 0)))/SUM(COALESCE(historical.commission, 0)))*100, 2) ELSE 0 END as pct_change_commission'),
            ])
            ->from('campaign_stats as current')
            ->leftJoin('campaign_stats as historical', function ($join) use ($historicalStart, $historicalEnd) {
                $join->on('current.affiliate_id', '=', 'historical.affiliate_id')
                     ->whereBetween('historical.period_start', [$historicalStart, $historicalEnd]);
            })
            ->whereBetween('current.period_start', [$currentStart, $currentEnd])
            ->groupBy('current.affiliate_id');
        }
    ]);
}

控制器中调用示例：

$companies = Company::with(['affiliatesWithComparison' => function ($query) use ($historicalStart, $historicalEnd, $currentStart, $currentEnd) {
    $query->setBindings([$historicalStart, $historicalEnd, $currentStart, $currentEnd]);
}])->get();

4. 性能优化建议 #

• 给campaign_stats表建立组合索引：

  CREATE INDEX idx_campaign_stats_period_entity ON campaign_stats(period_start, period_end, company_id, affiliate_id, campaign_id);
  

• 仅查询需要的字段，避免SELECT *减少数据传输量；
• 若周期固定（如月度），可预计算汇总数据到专用汇总表，进一步提升查询速度。

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