如何计算Laravel查询获取的各公司最近两期销售数据的数值差异？

嘿，针对你这个需求，我来分享一个更清晰且高效的解决方案。你的原查询用了GROUP_CONCAT和FIND_IN_SET来筛选每个公司的最近两期记录，咱们可以优化这个逻辑，同时加入差异计算的步骤：

步骤1：用窗口函数标记每个公司的最近两期记录 #

首先，利用ROW_NUMBER()窗口函数，按公司分组并按periods倒序排序，给每条记录分配一个序号（最新的期数序号为1，上一期为2）。这样能更直观地筛选出需要的两条记录：

$latestTwoPeriods = App\Salesreport::select(
    'company_id',
    'periods',
    'sales_amount', // 替换成你实际需要计算差异的数值字段
    DB::raw('ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY periods DESC) as period_rank')
)
->having('period_rank', '<=', 2)
->get();

步骤2：计算两期数值的差异 #

接下来，我们可以把上面的查询结果作为子查询，通过自连接的方式，将同一公司的第1期（最新）和第2期（上一期）记录关联起来，然后计算差异：

$salesDifferences = DB::table(function($query) {
    $query->select(
        'company_id',
        'periods',
        'sales_amount',
        DB::raw('ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY periods DESC) as period_rank')
    )
    ->from('salesreport')
    ->having('period_rank', '<=', 2);
}, 'latest_periods')
->join('latest_periods as prev_period', function($join) {
    $join->on('latest_periods.company_id', '=', 'prev_period.company_id')
         ->where('latest_periods.period_rank', 1)
         ->where('prev_period.period_rank', 2);
})
->select(
    'latest_periods.company_id',
    'latest_periods.periods as current_period',
    'prev_period.periods as previous_period',
    'latest_periods.sales_amount as current_sales',
    'prev_period.sales_amount as previous_sales',
    DB::raw('latest_periods.sales_amount - prev_period.sales_amount as sales_difference')
)
->get();

关键说明 #

• 如果你有多个需要计算差异的数值字段（比如profit、customer_count等），只需要在select和差异计算的DB::raw中添加对应的字段即可。
• 窗口函数的方式比原GROUP_CONCAT的方法更直观，也更容易维护，尤其是当后续需要调整期数（比如取最近3期）时，只需要修改having里的数值就行。
• 确保你的MySQL版本支持窗口函数（MySQL 8.0及以上），如果是旧版本，我们可以换用另一种自连接的方式来实现：

兼容旧版MySQL的方案

$salesDifferences = DB::table('salesreport as current')
->join('salesreport as prev', function($join) {
    $join->on('current.company_id', '=', 'prev.company_id')
         ->whereRaw('current.periods > prev.periods')
         ->whereRaw('(SELECT COUNT(*) FROM salesreport WHERE company_id = current.company_id AND periods > prev.periods) = 1');
})
->select(
    'current.company_id',
    'current.periods as current_period',
    'prev.periods as previous_period',
    'current.sales_amount as current_sales',
    'prev.sales_amount as previous_sales',
    DB::raw('current.sales_amount - prev.sales_amount as sales_difference')
)
->get();

这个方案通过自连接和子查询来确保prev是current的上一期记录，同样能实现需求。

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