Как выполнить туже операцию 1 запросом?

Question

[Миха Панкратов]

Привет, есть задача для тех кто хочет побороть коронавирус и повысить свои знания. Я честно говоря уже мозг сломал, но знаю что можно сделать то что я делаю без вложенных запросов.

Задача:
Есть таблица `course` (`currency` enum(‘usd’, ‘eur’, ...), `price` float, `created_at` datetime) с курсами валют. В таблицу идет запись, как только изменился курс, путем добавления новой записи в конец. Курс по одной валюте может не меняться несколько дней, а по другой изменяться каждую минуту. Необходимо вывести актуальный курс валют. Список валют заранее не известен.

Попытки
sqlfiddle.com/#!9/172f58/1

Код который делает что мне нужно

SELECT n.* 
FROM course n 
INNER JOIN (
SELECT id, MAX(created_at) AS created_at
  FROM course GROUP BY currency  
) AS max USING (created_at)
;

Структура

CREATE TABLE IF NOT EXISTS `course` (
  `id` int(6) unsigned NOT NULL,
  `price` int(3) unsigned NOT NULL,
  `currency` enum('usd', 'rub') NOT NULL,
  `created_at` datetime,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `course` (`id`, `price`, `currency`, `created_at`) VALUES
  (1, 1, 'usd', CURDATE()),
  (2, 6, 'usd', date('2020-11-11')),
  (3, 3, 'usd', date('2020-12-11')),
  (4, 2, 'rub', date('2019-11-11')),
  (5, 66, 'rub', date('2020-11-14')),
  (6, 3, 'rub', now());

Есть идеи?

Answer 1

[Дмитрий Шицков]

Только уже указанный вариант с подзапросом возможен, так как нет возможности использовать группировку и не участвующие в группировке или неаггрегированные поля. Единственное что, выгоднее будет использовать первичный ключ для JOIN

SELECT n.* FROM course n
JOIN (SELECT MAX(created_at) created_at FROM course GROUP BY currency) m
USING(created_at)

Одним запросом, судя по explain, значительно дороже будет такой запрос

SELECT n.* FROM course n
LEFT JOIN course m
ON n.currency = m.currency AND n.created_at < m.created_at
WHERE m.id IS NULL

Comments

[Миха Панкратов]

Без вложенных запросов можешь придумать?

[Дмитрий Шицков]

Миха Панкратов, это олимпиадная задача или что? Зачем необходимо придумать что-то, отличное от общепринятого?
Одним запросом будет дороже, чем подзапросом

[Максим]

MAX(id) не совсем верно, т.к. для максимальной даты не обязательно максимальный id =( . Хотя зачастую так и есть =)

[Миха Панкратов]

Тут к id совсем не надо привязываться

[Дмитрий Шицков]

Миха Панкратов, ну, это уже на ваше усмотрение.

[Миха Панкратов]

К тому же

SELECT n.* FROM course n
LEFT JOIN currency m
ON n.currency = m.currency AND n.id < m.id
WHERE m.id IS NULL;

Таблицы currency у меня нет это все в одной табличке тут надо данные на данные как-то скрестить

[Дмитрий Шицков]

Миха Панкратов, исправил ошибку

[Миха Панкратов]

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

sqlfiddle.com/#!9/172f58/12

[Дмитрий Шицков]

Миха Панкратов, ну, если id не автоинкремент, то конечно не будет работать. Исправил на дату

[Максим]

SELECT n.* FROM course n
LEFT JOIN course m
ON n.currency = m.currency AND n.created_at < m.created_at
WHERE m.id IS NULL;

P.S. уже поправил автор

[Миха Панкратов]

Максим, да вот это кстати решение мне нравиться

Кто как думает?

[Максим]

Миха Панкратов, Я думаю что задача решена, НО. Это как ехать на авто без двух колес, доехать можно но не практично, медленно и с большими нагрузками. Как и отмечал в своем ответе Дмитрий Шицков. Есди позволяет версия, Я бы рассмотрел еще мой вариант, он должен быть менее трудоемкий для системы, НО проще и лучше - подзапрос.

Answer 2

[Максим]

Это больше на изврат похоже, условие без подзапросов ...
но вот:

select distinct
FIRST_VALUE(currency) OVER (PARTITION BY currency ORDER BY created_at desc) currency,
FIRST_VALUE(price)    OVER (PARTITION BY currency ORDER BY created_at desc) price
       from course

Данных нет, не проверял, но должно работать

Comments

[Миха Панкратов]

Добавил данные для проверки, не работает ( но суть идеи я понял

[Максим]

странно MySQL
8.0.19

currency	price
usd	3
rub	66

[Дмитрий Шицков]

Под рукой у меня только Potgresql. Там такой запрос планировщик считает очень дорогим. Больше всего из-за необходимости сортировок

[Максим]

Дмитрий Шицков, Дороже чем таблицы саму на себя крестить?

[Дмитрий Шицков]

Максим,
стоимость для этого запроса 116.37 - 118.37
саму на себя скрестить 37.00 - 79.12

Полагаю, это потому что последовательное сканирование всегда дешевле сортировки

[Миха Панкратов]

Дмитрий Шицков, согласен

[Максим]

Дмитрий Шицков, Миха Панкратов, По результатам теста, считаю этот вариант не оптимальным. Миха Панкратов предлагаю использовать вариант предложенный Дмитрий Шицков. Он более универсален, но задача поставленна из разряда: напишите запрос так как писать не надо, но чтобы работал.

[Миха Панкратов]

Максим, нет задачу я так не ставил) если есть доказательства что мой запрос оптимальный или тот что был поставлен выше лучше то я ж только рад

[Максим]

Миха Панкратов, Ни Ваш запрос, ни первый запрос Дмитрий Шицков я бы на прод не пропустил, скажу так =). В чем их фундаментальная ошибка, Вы находите max(created_at) для каждой currency, а потом по нему делаете соединение. Это неправильно, потому как если эта дата присутствует в 2 (и более) валютах, а у Вас в постановке такое возможно, то выведет по какой-то валюте более 1 записи. А это может быть критично для логики.
Свой вариант я написал как альтернативу т.к. вариант с 2 таблицами был (с ошибками, но я решил его откоментить). Ну и та постановка, как написать одним селектом без вложенных запросов не корректна. Спрашивайте какие еще могут быть варианты, как можно написать оптимальнее. Я могу подкинуть еще вариант с row_number(), но там вложенность. В общев Вы сильно и себя и нас ограничили условием "без подзапросов".