Задача
Хотим: купить товар на одной планете и продать на другой. Прибыль =
sell (там) −
buy (тут).
Обе цены лежат в одной таблице prices, но в разных строках. Чтобы сравнить две строки — берём таблицу дважды.
Что такое Self-JOIN?
Self-JOIN — это JOIN таблицы с самой собой.
В SELF JOIN одна и та же таблица используется два раза в одном запросе через псевдонимы (алиасы). При этом строки одной части таблицы соединяются со строками той же таблицы по какому‑то условию в ON или WHERE.
FROM prices AS buy_p -- «копия для покупки»
JOIN prices AS sell_p -- «копия для продажи»
ON buy_p.product_type = sell_p.product_type -- тот же товар!
Обязательно:
buy_p.planet_id != sell_p.planet_id — иначе «продадим сами себе».
Пример
1. Для каждой пары планет и каждого товара — какая прибыль за тонну?
SELECT
buy_p.planet_id AS buy_planet,
sell_p.planet_id AS sell_planet,
buy_p.product_type,
sell_p.sell_price - buy_p.buy_price AS profit_per_ton
FROM prices AS buy_p
JOIN prices AS sell_p
ON buy_p.product_type = sell_p.product_type
AND buy_p.planet_id != sell_p.planet_id
WHERE sell_p.sell_price > buy_p.buy_price -- только прибыльные
ORDER BY profit_per_ton DESC
LIMIT 5;
Зачем
WHERE sell > buy? Без этого фильтра будут и убыточные пары. Мы ищем прибыль — нужны только те, где sell на планете назначения больше buy на планете отправления.
2. Общая прибыль с учётом грузоподъёмности
Формула:
profit = (sell_price - buy_price) × MIN(amount, грузоподъёмность)
Почему MIN? Нельзя купить больше, чем есть на планете (amount), и больше, чем влезет в корабль.
-- Прибыль с учётом того, что грузоподъёмность = 80 тонн
(sell_p.sell_price - buy_p.buy_price)
* MIN(buy_p.amount, 80) AS total_profit