在 Eloquent 中计算两条记录之间的值差异

2021-12-26 00:00:00 php mysql laravel laravel-5.4 eloquent

我想用 eloquent 计算两条记录之间的差异.例如，我有下表:

I would like to calculate the difference between two records in eloquent. For example, I have following table:

----------------------------------
| Id | value | type              |
----------------------------------
| 1  | 100   | FOO               |
| 2  | 500   | FOO               |
| 3  | 800   | FOO               |
| 4  | 200   | BAR               |
| 5  | 600   | BAR               |
| 6  | 1000  | FOO               |
----------------------------------

假设模型名称是 FooBar，所以当我过滤表格时，例如，使用 FooBar::where('type', 'FOO')->get(); 我将得到以下结果:

Let's say the model name is FooBar, so when I filter the table, for example, using FooBar::where('type', 'FOO')->get(); I will get following result:

----------------------------------
| Id | value | type  | diff      |
----------------------------------
| 1  | 100   | FOO   | 0         |
| 2  | 500   | FOO   | 400       | (500 - 100)
| 3  | 800   | FOO   | 300       | (800 - 500)
| 6  | 1000  | FOO   | 200       | (1000 - 800)
----------------------------------

现在，也许我可以使用原始查询更轻松地实现这一点，例如声明变量来存储以前的记录(例如:SET @id := 0 并在 SELECT 语句中设置它).但在这种情况下，如果可能，我更喜欢使用 Eloquent.

Now, probably I could achieve this more easily with raw query, like declaring variable to store previous record (e.g: SET @id := 0 and set it in SELECT statement). But in this case I prefer to use Eloquent if possible.

我目前的解决方法是循环结果集并手动计算，我担心这会影响性能.

My current workaround is loop the result set and calculate manually which I'm afraid it will affect the performance.

有什么想法吗?

推荐答案

我不介意 eloquent 对性能的小影响，但会循环通过计算差异的结果集.. 我的意思是，如果我有数千条记录，一个一个循环是粗略的想法

I don't mind small performance impact from eloquent, but looping thru the result set to calculate difference.. I mean, cmon if I had thousands records, looping one by one is rough idea

那么我有一个惊喜给你 - 这是一个小的性能测试:

Then I have a surprise for you - Here is a small performance test:

class Seq extends Eloquent {
    protected $table = 'helper.seq';
    protected $primaryKey = 'i';
}

Route::get('/loop', function () {
    $limit = 10000;

    $st = microtime(true);
    $data = Seq::orderBy('i')->take($limit)->get();
    var_dump(microtime(true) - $st);

    $st = microtime(true);
    foreach ($data as $row) {
        $row->i;
    }
    var_dump(microtime(true) - $st);

    $pdo = DB::getPdo();
    $st = microtime(true);
    $data2 = $pdo
        ->query("select * from helper.seq order by i limit $limit")
        ->fetchAll(PDO::FETCH_OBJ);
    var_dump(microtime(true) - $st);

    $st = microtime(true);
    foreach ($data2 as $k => $row) {
        if ($k == 0) {
            $row->diff = 0;
        } else {
            $row->diff = $row->i - $data2[$k-1]->i;
        }
    }
    var_dump(microtime(true) - $st);
});

helper.seq 是一张只有一个 int 列和 100 万行的表.

helper.seq is a table with only one int column and 1M rows.

结果是:

0.779045s <- Fetch from DB with Eloquent

1.022058s <- Read Eloquent data (Only one column and do nothing with it)

0.020002s <- Fetch from DB with PDO

0.009999s <- Calculate all diffs in a loop

所以eloquent 对性能的影响很小"是:

So the "small performance impact from eloquent" is:

  • 从数据库中获取数据时，比使用普通 PDO 和 stdClass 慢近 20 倍.
  • 在循环中读取属性/属性时，至少比 stdClass 慢 100 倍.
  • Almost 20 times slower than using plain PDO and stdClass when fetching data from database.
  • At least 100 times slower than stdClass when reading properties/attributes in a loop.

因此，如果您想提高性能，请在处理大量数据时切换到普通 PDO，或者至少使用默认的 Builder.

So if you want to improve the peroformance, switch to plain PDO when dealing with big amounts of data or at least use the default Builder.

现在你仍然可以尝试在 MySQL 中完成这项工作，但要求使用 Eloquent 是没有意义的.

Now you can still try to do the job in MySQL, but the requirement to use Eloquent wouldn't make sence.

然而，您可以尝试混合版本 - 使用 Eloquent 构建查询，但使用 getQuery() 将其转换为 DatabaseQueryBuilder.

However you can try a mixed version - Use Eloquent to build the query, but convert it to DatabaseQueryBuilder with getQuery(). 

$fooBars = FooBar::where('type', 'FOO')->orderBy('id')
    ->getQuery()
    ->select(['*', DB::raw('coalesce(`value` - @last, 0)'), DB::raw('@last := `value`')])
    ->get();

但我总是避免在应用程序代码中以这种方式使用会话变量，因为我已经看到许多此类解决方案在版本升级后返回错误/意外的结果.

But I would always avoid using session variables this way in application code, because i've seen many of such solutions returning wrong/unexpected results after a version upgrade.

还是不相信?以下是一些其他测试:

Still not convinced? Here are some other tests:

在转换为 DatabaseQueryBuilder 的 Eloquent 查询中使用会话变量:

Using session variables in an Eloquent query converted to DatabaseQueryBuilder:

$st = microtime(true);
$data = Seq::getQuery()
    ->select(['*', DB::raw('coalesce(i - @last, 0)'), DB::raw('@last := i')])
    ->orderBy('i')->take($limit)->get();
var_dump(microtime(true) - $st);

// runtime: 0.045002s

使用转换后的 Eloquent 查询的 PHP 解决方案:

PHP solution using converted Eloquent query:

$st = microtime(true);
$data2 = Seq::getQuery()->orderBy('i')->take($limit)->get();
foreach ($data2 as $k => $row) {
    if ($k == 0) {
        $row->diff = 0;
    } else {
        $row->diff = $row->i - $data2[$k-1]->i;
    }
}
var_dump(microtime(true) - $st);

// runtime: 0.039002

带有普通 PDO 和 stdClass

PHP solution with plain PDO and stdClass

$st = microtime(true);
$data3 = $pdo
    ->query("select * from helper.seq s1 order by i limit $limit")
    ->fetchAll(PDO::FETCH_OBJ);
foreach ($data3 as $k => $row) {
    if ($k == 0) {
        $row->diff = 0;
    } else {
        $row->diff = $row->i - $data3[$k-1]->i;
    }
}
var_dump(microtime(true) - $st);

// runtime: 0.035001s

带有普通 PDO 和关联数组的 PHP 解决方案:

PHP solution with plain PDO and assotiative arrays:

$st = microtime(true);
$data4 = $pdo
    ->query("select * from helper.seq s1 order by i limit $limit")
    ->fetchAll(PDO::FETCH_ASSOC);
foreach ($data4 as $k => $row) {
    if ($k == 0) {
        $row['diff'] = 0;
    } else {
        $row['diff'] = $row['i'] - $data4[$k-1]['i'];
    }
}
var_dump(microtime(true) - $st);

// runtime: 0.027001s

您首选的解决方案是最慢且最不可靠的.所以你的问题的答案对你的问题来说是一个糟糕的解决方案.

Your prefered solution is the slowest and the least reliable. So the answer to your question is a bad solution for your problem.

相关文章