多表关联分组，获取各ISP/Target/Sponsor下Top2高EPC的Offer

我有isps、offers、sponsors三张数据表，statistics表存储这三张表的外键。需求如下：

• 按isp、target、sponsor分组
• 计算每个isp-target-sponsor-offer分组的EPC，公式为：epc=sum(statistics.earns)/ nullif(sum(statistics.clicks)::numeric, 0)（若点击量为0则EPC取0）
• 获取每个isp-target-sponsor分组下EPC最高的前2个offer

当前实现的Laravel查询会返回所有offer，需要修改以达成目标。

数据表结构 #

Schema::create('isps', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});

Schema::create('sponsors', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});

Schema::create('offers', function (Blueprint $table) {
    $table->id();
    $table->string('offer_id')->nullable();
    $table->string('name');
    $table->foreignIdFor(Sponsor::class)->constrained()->cascadeOnUpdate()->cascadeOnDelete();
    $table->timestamps();
});

Schema::create('statistics', function (Blueprint $table) {
    $table->id();
    $table->date('at')->nullable();
    $table->foreignIdFor(Isp::class)->nullable();
    $table->foreignIdFor(Sponsor::class)->nullable();
    $table->foreignIdFor(Offer::class)->nullable();

    $table->string('target')->default('NA');
    $table->unsignedInteger('clicks')->default(0);
    $table->unsignedDecimal('earns')->default(0);

    $table->timestamps();
});

当前实现代码 #

$stats = Statistic::query()
    ->where('at', '>=', now()->subMonth())
    ->selectRaw('
        statistics.sponsor_id as sponsar_id,
        sponsors.name as sponsor_name,
        statistics.target,
        statistics.isp_id as isp_id,
        isps.name as isp_name,
        statistics.offer_id as offer_id,
        offers.name as offer_name, 
        coalesce(sum(statistics.earns)/ nullif(sum(statistics.clicks)::numeric, 0),0) as epc
    ')
    ->join('sponsors', 'sponsors.id', '=', 'statistics.sponsor_id')
    ->join('offers', 'offers.id', '=', 'statistics.offer_id')
    ->join('isps', 'isps.id', '=', 'statistics.isp_id')
    ->groupBy(
        'statistics.target', 'statistics.isp_id', 'isps.name', 'statistics.sponsor_id',
        'sponsors.name', 'statistics.offer_id', 'offers.name'
    )
    ->get();

if ($stats->isEmpty()) {
    return [];
}

return $stats->transform(fn($row) => [
    'isp' => $row->isp_name,
    'target' => $row->target,
    'sponsor' => $row->sponsor_name,
    'offer' => $row->offer_name,
    'epc' => $row->epc,
])->toArray();

示例数据 #

修改后的解决方案 #

要实现分组取前2的需求，需要使用窗口函数ROW_NUMBER()，在每个isp-target-sponsor分组内按EPC降序排名，再筛选排名≤2的记录。以下是修改后的Laravel代码：

$stats = Statistic::query()
    ->where('at', '>=', now()->subMonth())
    ->selectRaw('
        statistics.sponsor_id,
        sponsors.name as sponsor_name,
        statistics.target,
        statistics.isp_id,
        isps.name as isp_name,
        statistics.offer_id,
        offers.name as offer_name,
        coalesce(sum(statistics.earns)/ nullif(sum(statistics.clicks)::numeric, 0), 0) as epc
    ')
    ->join('sponsors', 'sponsors.id', '=', 'statistics.sponsor_id')
    ->join('offers', 'offers.id', '=', 'statistics.offer_id')
    ->join('isps', 'isps.id', '=', 'statistics.isp_id')
    ->groupBy(
        'statistics.target', 'statistics.isp_id', 'isps.name', 'statistics.sponsor_id',
        'sponsors.name', 'statistics.offer_id', 'offers.name'
    )
    ->selectSub(function ($query) {
        $query->selectRaw('ROW_NUMBER() OVER (
            PARTITION BY statistics.isp_id, statistics.target, statistics.sponsor_id 
            ORDER BY coalesce(sum(statistics.earns)/ nullif(sum(statistics.clicks)::numeric, 0), 0) DESC
        ) as row_num');
    }, 'row_num')
    ->having('row_num', '<=', 2)
    ->get();

if ($stats->isEmpty()) {
    return [];
}

return $stats->transform(fn($row) => [
    'isp' => $row->isp_name,
    'target' => $row->target,
    'sponsor' => $row->sponsor_name,
    'offer' => $row->offer_name,
    'epc' => $row->epc,
])->toArray();

说明 #

1. 使用ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)为每个isp-target-sponsor分组内的offer按EPC降序分配排名
2. 通过having('row_num', '<=', 2)筛选出每个分组内排名前2的offer
3. 保留原有的EPC计算逻辑，确保数值准确性

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