Как получить список актуальных цен?

Question

[aleksyglvnw]

Структура таблицы:

prices
- id
- related (laravel morph - не суть важно, через нее указываем на что цена)
- currency_id
- value
- relevant_from (дата с которой данная цена актуальна)

Пример данных (все для одного товара):

... currency_id - value - relevant_from
...      1         100     2020-06-10
...      1         200     2020-07-10
...      1         300     2020-08-10
...      2         100     2020-06-10
...      2         200     2020-07-10
...      2         300     2020-08-10

Описание запроса:

Нужно получить актуальный список цен для продукта во всех валютах.
Актуальной цена становится при условии если relevant_from меньше или равна текущей дате и ближе всего к текущему времени.
Результат запроса выглядит так (сегодня 2020-07-16):

... currency_id - value - relevant_from
...      1         200     2020-07-10
...      2         200     2020-07-10

Comments

[aleksyglvnw]

Когда мы выбираем валюту, это выглядит так:

SELECT * 
FROM prices
WHERE relevant_from <= NOW() AND currency_id = 2
ORDER BY relevant_from DESC
LIMIT 1

Не могу сообразить как мне получить все валюты

[Alams Stoyne]

Это то что должно тебе помочь GROUP BY

[aleksyglvnw]

Alams Stoyne, не разобрался как через GROUP BY это решить.
Получить, что требуется получилось:

SELECT *
FROM prices AS t1
WHERE relevant_from = (
	SELECT MAX(t2.relevant_from) 
	FROM prices AS t2 
	WHERE t2.currency_id = t1.currency_id AND t2.relevant_from <= NOW()
)
ORDER BY t1.currency_id

Но уверен, что должно быть более оптимизированное решение

[Максим]

aleksyglvnw, Я Вам написал как более оптимально =) и через group by

Answer 1

[Максим]

Можно оконными функциями, можно по старинке

select p.*
from prices p,
(select currency_id, max(relevant_from) max_relevant_from
  from price 
  where relevant_from <= NOW()
   group by currency_id) p2
where p.currency_id = p2.currency_id and p.relevant_from = p2.max_relevant_from

Comments

[aleksyglvnw]

Примерно то же самое у меня получилось:

SELECT *
FROM prices AS t1
WHERE relevant_from = (
  SELECT MAX(t2.relevant_from) 
  FROM prices AS t2 
  WHERE t2.currency_id = t1.currency_id AND t2.relevant_from <= NOW()
)

Но разве нет запроса более оптимизированного для этого?

Это еще в связи должно работать на ларе и у меня вот что вышло:

public function relevantPrices()
    {
        /** @var Model|AbstractModel $model */
        $model = $this;

        return $model->morphMany(Price::class, 'related')
            ->where('relevant_from', function (Builder $query) {
                $query->from(Price::getTableStatic())
                      ->where('currency_id', 'currency_id')
                      ->where('relevant_from', '<=', Carbon::now())
                      ->max('relevant_from');
            });
    }

[Максим]

aleksyglvnw, Ваш селект будет запускать subselect на КАЖДУЮ запись таблицы prices AS t1 что вызовет большие тормоза думаю от первого милиона записей (может и меньше). Мой вариант объединит два масива, что не вызовет особой нагрузки на сервер даже при больших объемах.