在Eloquent中计算两条记录之间的值差 [英] Calculating value differences between two records in 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.

有什么想法吗?

推荐答案

我不介意雄辩对性能的微小影响，而是不断循环 结果集来计算差异..我的意思是，如果我有 数千条记录，一个接一个地循环是一个粗略的想法

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列和1M行的表.

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

因此，口才对性能造成的小影响"是:

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()将其转换为Database\Query\Builder.

However you can try a mixed version - Use Eloquent to build the query, but convert it to Database\Query\Builder 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:

在Eloquent查询中使用会话变量转换为Database\Query\Builder:

Using session variables in an Eloquent query converted to Database\Query\Builder:

$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

使用转换后的口才查询的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

$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.

这篇关于在Eloquent中计算两条记录之间的值差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！