php - 获取按天分组的行的最小值和最大值

Question

我想用 Laravel Eloquent 返回按 Day 分组的每条记录的最小值和最大值，所以这里是：

表结构：速率

id     buy_rate     sell_rate     market_currency_id     created_at
==     ========     =========     ==================     ==========
1      10           7             44                     2020-10-10
2      10           9             44                     2020-10-10
3      10           8             44                     2020-10-10
4      10           12            44                     2020-10-11
5      10           14            44                     2020-10-11
6      10           13            44                     2020-10-11

现在我得到这样的数据：

$rates = Rate::
select('sell_rate', \DB::raw("DATE_FORMAT(created_at, %Y-%m-%d) day))
->where('market_currency_id', '=', 44)
->whereBetween('created_at', [2020-10-10, 2020-10-11])
->orderBy('created_at', 'ASC')
->get()
->toArray();

上面的代码返回：

0 => array:2 [▼
    "sell_rate" => "8"
    "day" => "2010-10-10"
]
1 => array:2 [▼
    "sell_rate" => "12"
    "day" => "2010-10-11"
]

现在，我想要的是得到这样的东西：

0 => array:2 [▼
    "sell_rate" => "8"
    "day" => "2010-10-10"
    "min_rate" => "7"
    "max_rate" => "9"

]
1 => array:2 [▼
    "sell_rate" => "13"
    "day" => "2010-10-11"
    "min_rate" => "12"
    "max_rate" => "14"
]

我尝试了很多方法，但我无法得到我想要的，我尝试了这样的原始选择：

$rates = Rate::
select('sell_rate', \DB::raw("DATE_FORMAT(created_at, %Y-%m-%d) day))
->selectRaw("MIN(sell_rate) AS min_rate, MAX(sell_rate) AS max_rate")
->where('market_currency_id', '=', 44)
->whereBetween('created_at', [2020-10-10, 2020-10-11])
->orderBy('created_at', 'ASC')
->get()
->toArray();

但是无法得到我想要的，我也不能单独做，因为有数千行，提前谢谢！

标签： phpmysqllaraveleloquent

Answer 1

您还需要按天分组：

$rates = Rate::select(DB::raw("DATE_FORMAT(created_at, %Y-%m-%d) day"))
    ->selectRaw("MIN(sell_rate) AS min_rate, MAX(sell_rate) AS max_rate")
    ->where('market_currency_id', '=', 44)
    ->whereBetween('created_at', ['2020-10-10', '2020-10-11'])
    ->groupBy(DB::raw('day'))
    ->orderBy(DB::raw('day'))
    ->get()
    ->toArray();