Laravel Eloquent实现同表当前与上一日期值差值查询

我已查阅相关问题但未找到类似内容，若存在重复请告知，我会按规则删除问题以避免重复，谢谢。

我在Laravel中使用Eloquent实现需求时遇到了瓶颈，希望能得到帮助或指引。以下将详细说明需求并附上示例，以便清晰呈现问题。

我的问题分为两部分：第一部分是按日期分组查询差值；第二部分是指定具体日期，不分组查询每条记录的差值。

提前感谢您的帮助，若需更多信息请随时告知，顺颂时祺。

问题第一部分 #

我希望通过单条Eloquent语句完成查询，无需后续处理。

我拥有如下数据表：

Schema::create('readings', function (Blueprint $table) {
    $table->id();
    $table->foreignId('counter_id')
        ->constrained()
        ->cascadeOnUpdate()
        ->restrictOnDelete();
    $table->date('date');
    $table->unsignedInteger('value');
});

表中存储的数据示例如下：

我希望得到按日期分组的查询结果（示例）：

? 此处为0是因为没有更早的读数可用于计算差值。

我已尝试用MySQL实现该查询，但无法转换为Laravel Eloquent语句：

SELECT r_current.date,
       count(r_current.id) AS readings_in_group,
       SUM(r_current.value -
           (
                SELECT value
                FROM readings
                WHERE r_current.counter_id = counter_id
                  AND r_current.date > date
                ORDER BY date DESC
                LIMIT 1
           )
       ) AS difference
FROM `readings` AS r_current
GROUP by r_current.date
;

问题第一部分已解决，感谢。

第一部分通过@Masunulla提供的方法示例解决，我将MySQL查询转换为了Eloquent语句：

$subQuery = 'SELECT value FROM readings 
                        WHERE r_current.counter_id = counter_id 
                        AND r_current.date > date 
                        ORDER BY date DESC 
                        LIMIT 1';

$readings = Reading::from('readings AS r_current')
    ->select('r_current.date',
        DB::raw('COUNT(r_current.id) AS readings_in_group'),
        DB::raw('SUM(r_current.value - ('.$subQuery.')) AS total_difference')
    )
    ->groupBy('r_current.date')
    ->get();

问题第二部分 #

我希望通过单条Eloquent语句完成查询，无需后续处理。

第二部分需求是指定具体日期，不分组查询每条记录与上一日期对应counter_id的读数差值。我目前通过循环内嵌套查询实现，但希望优化为单条查询，同样遇到了无法转换为Eloquent的问题。

例如，指定日期2022-12-1时，我希望得到如下结果：

指定日期2022-11-1时，希望得到：

指定日期2022-10-1时，希望得到：

? 此处为0和null是因为没有更早的读数可用于计算差值。

我目前通过循环内嵌套查询实现，但这样会产生大量查询，不够优化：

控制器代码：

$query = Reading::query()
              ->with('counter')
              ->where('date', '=', $this->date) //示例日期
              ->get();

模型代码：

public function previous(): Model|null
{
    return $this->query()
        ->where('counter_id', '=', $this->counter_id)
        ->whereDate('date', '<', Carbon::parse($this->date)->format('Y-m-d'))
        ->orderBy('date', 'desc')
        ->first();
}

public function difference(): int
{
    $previous = $this->previous();
    
    if ($previous) {
        $difference = $this->value - $previous->value;
    } else {
        $difference = 0;
    }

    return $difference;
}

Blade视图代码：

@foreach($readings as $reading)
...
$reading->date
$reading->value
$reading->previous()->date
$reading->previous()->value
$reading->difference()
...
@endforeach

问题第二部分已解决，感谢。

基于第一部分的解决方案，我尝试了类似的方法，最终实现了需求，响应速度较快。若有更优方案欢迎提出，非常感谢。

$concatSubQuery = 'FROM readings 
                    WHERE r_current.counter_id = counter_id 
                    AND r_current.date > date 
                    ORDER BY date DESC 
                    LIMIT 1';

$query = Reading::from('readings AS r_current')
    ->select('r_current.id', 'r_current.counter_id', 'r_current.date', 'r_current.value',
        DB::raw('(SELECT date '.$concatSubQuery.')  AS previous_date'),
        DB::raw('(SELECT value '.$concatSubQuery.')  AS previous_value'),
        DB::raw('SUM(r_current.value - (SELECT value '.$concatSubQuery.')) AS difference')
    )
    ->with('counter')
    ->where('date', '=', $this->date)
    ->groupBy('r_current.id', 'r_current.counter_id', 'r_current.date', 'r_current.value')
    ->get();

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