MYSQL Выбрать записи с ближайшей датой
В моей базе данных есть следующие 2 таблицы:
цена продукта
CREATE TABLE `product_price` (
`asin` varchar(10) NOT NULL,
`date` date NOT NULL,
`price` decimal(7,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`asin`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
информация о продукте
CREATE TABLE `product_info` (
`asin` varchar(10) NOT NULL,
`name` varchar(200) DEFAULT NULL,
`brand` varchar(50) DEFAULT NULL,
`part_number` varchar(50) DEFAULT NULL,
`url` text,
`image` text,
PRIMARY KEY (`asin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Я хочу найти продукты, цена которых была снижена между датой начала и датой окончания.
Сейчас я использую следующий запрос, который работает:
$query = "SELECT pi.*, prev.price AS 'old_price', curr.price, ROUND((100.0*(curr.price - prev.price) / prev.price),0) As PercentDiff FROM product_price As curr
JOIN product_price As prev ON curr.date = '".$end_date."' AND prev.date = '".$start_date."'
JOIN product_info pi ON curr.asin = pi.asin WHERE curr.asin = prev.asin HAVING PercentDiff < 0 ORDER BY PercentDiff";
Но проблема в том, что в таблице product_price нет записи, если цена не изменилась.
Например например
asin date price
AAAAAAAAA 2018-07-17 7
AAAAAAAAA 2018-07-15 6
start_date = "2018-07-16" end_date = "2018-07-17"
Я хочу, чтобы это было в моих результатах, так как цена была снижена.
Как мне пришлось изменить свой запрос ??
Я попытался изменить prev.date = '". $ Start_date."' На prev.date <= '". $ Start_date."'
Но возвращает все записи, а не самую близкую, как хочу.
Вот несколько примеров данных из product_price:
+------------+------------+--------+
| asin | date | price |
+------------+------------+--------+
| B000GBKDB4 | 2018-07-02 | 38.66 |
| B000GBKDCI | 2018-07-02 | 72.98 |
| B000GBKFLW | 2018-07-02 | 33.27 |
| B000GBKFMG | 2018-07-02 | 63.45 |
| B000GBLZEI | 2018-07-02 | 34.90 |
| B000GBLZEI | 2018-07-04 | 21.31 |
| B000GBLZEI | 2018-07-05 | 20.24 |
| B000GBLZEI | 2018-07-06 | 18.41 |
| B000GBLZEI | 2018-07-08 | 17.49 |
| B000GBLZEI | 2018-07-09 | 15.79 |
| B000GBLZEI | 2018-07-11 | 14.84 |
| B000GBLZEI | 2018-07-16 | 14.29 |
Я добавил данные из таблицы product_price. Итак, в моем примере данные, когда дата начала 2018-07-15 и 2018-07-16 для asin B000GBLZEI должна показать падение цены с 2018-07-11 до 2018-07-16. Но с запросом, который я предоставил, он будет только показать, когда вы установили дату начала = 2018-07-11 и дату окончания 2018-07-16
Может ли цена упасть, а затем снова подняться в течение периода? Если да, то что ты хочешь делать? Вы действительно имеете в виду, что хотите те, у которых цена на последнюю дату меньше, чем цена на дату начала?
Ответы 1
Все еще не очень понятно, что вам нужно, но это должно выбрать правильные цены, применимые к паре дат. От 2018-07-03 до 2018-07-07, которые не отображаются в таблице product_price.
Надеюсь, это поможет вам встать на правильный путь.
SELECT *,
(SELECT price
FROM product_price
WHERE product_price.asin = product_info.asin AND
product_price.date <= '2018-07-03'
ORDER BY product_price.date DESC
LIMIT 1) AS start_price,
(SELECT price
FROM product_price
WHERE product_price.asin = product_info.asin AND
product_price.date <= '2018-07-07'
ORDER BY product_price.date DESC
LIMIT 1) AS end_price
FROM product_info
WHERE asin = 'B000GBLZEI'
Следуя вашему комментарию, вы можете использовать временную таблицу, подобную этой
CREATE TEMPORARY TABLE IF NOT EXISTS price_changes (
asin VARCHAR(10),
start_price DECIMAL(7,2),
end_price DECIMAL(7,2));
TRUNCATE price_changes;
INSERT INTO price_changes
SELECT asin,
(SELECT price
FROM product_price
WHERE product_price.asin = product_info.asin AND
product_price.date <= '2018-07-03'
ORDER BY product_price.date DESC
LIMIT 1) AS start_price,
(SELECT price
FROM product_price
WHERE product_price.asin = product_info.asin AND
product_price.date <= '2018-07-07'
ORDER BY product_price.date DESC
LIMIT 1) AS end_price
FROM product_info;
SELECT *,
ROUND((100.0 * (end_price - start_price) / start_price), 0) AS price_difference
FROM product_info
JOIN price_changes ON
price_changes.asin = product_info.asin;
Хорошо, это в правильном направлении. Но мне нужно посчитать разницу (в процентах) сверх этого.
Я добавил код для расчета разницы в процентах.
Кажется немного сложным для достижения этого, но я думаю, что другого варианта нет. Я попробую и дам вам знать. Спасибо :)
Было бы полезно, если бы вы поместили немного данных образца и требуемые результаты в виде таблицы