Мне нужно хранить тарифы, подключенные к порту. Итак, таблица может выглядеть так:
create table tariffs(
int NOT NULL AUTO_INCREMENT,
price decimal(12,2),
expiry bigint(11)
)
expiry
представляет собой отметку времени, когда истечет срок действия этого конкретного тарифа.
Итак, у меня могут быть такие данные:
id | price | expiry
1 | 11.00 | 30/Jan/2022
2 | 12.00 | 30/Feb/2022
3 | 13.00 | 30/Mar/2022
4 | 14.00 | 30/Apr/2022
5 | 15.00 | null
В этом случае срок действия идентификатора 5 еще не истек, что означает, что он является текущим. (Я понимаю, что поставил даты, а не временные метки; я сделал так, чтобы их было легче читать)
Проблема у меня в логике, чтобы выяснить, какой тариф использовать с учетом конкретной даты.
В идеальном мире, если бы 5 было «Бесконечно», я мог бы просто сделать WHERE expiry > date_apply limit 1
— однако у меня нет такой роскоши, так как date_apply
вообще не будет возвращено.
Я МОГУ присвоить очень большое число expiry
для «текущей» записи. Это заставит запрос работать независимо. Но... это кажется неправильным.
Кто-то рекомендовал использовать ДВА поля для каждого тарифа, «от» и «до», сказав мне, что в противном случае запросы будут кошмаром. Я начинаю понимать, что они имеют в виду... но тогда я опасаюсь, что операторы могут невольно иметь "дыры" в сроках для тарифов, которые будет трудно предотвратить.
Как я должен организовать свою таблицу и как я должен запрашивать ее? Каковы лучшие практики здесь?
вы можете начать с установки столбца срока действия в качестве типа данных даты, а не временной метки. И вам нужен только один столбец, потому что SQL может проверять другие более ранние или более поздние значения в том же столбце....
SELECT COALESCE(t2.price, t1.price) AS price
FROM (SELECT price FROM tariffs WHERE expiry IS NULL LIMIT 1) AS t1
LEFT OUTER JOIN (SELECT price FROM tariffs WHERE expiry > ? ORDER BY expiry DESC LIMIT 1) AS t2
Демо: https://www.db-fiddle.com/f/wykqR5X7B9S424AWkA4aQy/1
Первый подзапрос обязательно вернет 1 строку, если у вас есть хотя бы один неистекший тариф.
Второй подзапрос может не вернуть 1 строку, если вы указали дату слишком поздно. Поэтому я меняю это соединение на LEFT OUTER JOIN. Если нет строки, соответствующей условию по истечении срока действия, подзапрос не вернет строк, и внешнее соединение заменит их на NULL.
Итак, если t2.*
равно NULL, то COALESCE() по умолчанию использует неистекшее значение в t1.price
.
Хорошо, я отредактировал свой ответ и понял, что у меня все равно есть ошибка. :)
Разве в запросе не требуется оператор ORDER BY, чтобы убедиться, что вы действительно получаете последний возможный тариф?
Мне было непонятно, что вы хотели последний тариф. Я отредактировал ответ и dbfiddle, чтобы учесть это.
Вы можете оставить окончательную цену с нулевым сроком действия, и мы можем использовать объединение, чтобы присвоить значение в соответствии с логикой, необходимой в то время.
Здесь мы начинаем только с тарифа с истекшим сроком действия и тарифом с истекшим сроком действия = нулевой. Мы создаем представление, которое дает нам срок действия как undefined
. Затем мы добавляем действительный тариф, и он правильно возвращается тем же представлением.
create table tariffs( id int NOT NULL PRIMARY KEY AUTO_INCREMENT, price decimal(12,2), expiry date); insert into tariffs (price,expiry) values (11,'2022-01-30'),(12,null);
create view current_tarif as select id, price, coalesce(expiry,'undefined') expiry from tariffs where coalesce(expiry,'3000-12-31') > curdate() order by coalesce(expiry,'3000-12-31') limit 1;
select * from current_tarif;
id | price | expiry -: | ----: | :-------- 2 | 12.00 | undefined
insert into tariffs (price,expiry) values (15,'2022-12-30');
select * from current_tarif;
id | price | expiry -: | ----: | :--------- 3 | 15.00 | 2022-12-30
дб <> рабочий пример здесь
Есть ли способ сделать это без использования представлений? Я бы предпочел, чтобы решение было одним запросом. Могу ли я создать подмножество, а затем запросить его на лету?
Что-то вроде этого совершенно ужасно? dbfiddle.uk/… SELECT * FROM (SELECT *, COALESCE(expiry, 2051273200) as expiry2 FROM тарифы) as temp WHERE 1648594700 < temp.expiry2 ORDER BY expiry2 LIMIT 1
Вы можете использовать тот же запрос, что и в представлении, самостоятельно. Просто удалите первую строку.
Создавайте даты и временные метки срока действия и срока действия по ходу работы, используя функции OLAP.
WITH
indata(id,price,expiry) AS (
SELECT 1,11.00,DATE '30-Jan-2022'
UNION ALL SELECT 2,12.00,DATE '28-Feb-2022'
UNION ALL SELECT 3,13.00,DATE '30-Mar-2022'
UNION ALL SELECT 4,14.00,DATE '30-Apr-2022'
UNION ALL SELECT 5,15.00,NULL
)
,
enriched AS (
SELECT
id
, price
, LAG(NVL(expiry, '9999-12-31'),1,'0001-01-01') OVER(ORDER BY id) AS validity
, NVL(expiry, '9999-12-31') AS expiry
FROM indata
-- chk id | price | validity | expiry
-- chk ----+-------+------------+------------
-- chk 1 | 11.00 | 0001-01-01 | 2022-01-30
-- chk 2 | 12.00 | 2022-01-30 | 2022-02-28
-- chk 3 | 13.00 | 2022-02-28 | 2022-03-30
-- chk 4 | 14.00 | 2022-03-30 | 2022-04-30
-- chk 5 | 15.00 | 2022-04-30 | 9999-12-31
)
SELECT
price
FROM enriched
WHERE '2022-04-22' >= validity
AND '2022-04-22 < expiry
;
Примечание NVL()
— это функция Oracle. Стандартный эквивалент, поддерживаемый MySQL, — COALESCE()
.
Вы правы, что NVL()
не понимается MySQL. COALESCE()
, однако, является функцией с несколькими параметрами, которая возвращает первый параметр, отличный от NULL, а NVL()
и ее синонимы IFNULL()
и ISNULL()
являются функциями с двумя параметрами, возвращающими второй параметр, если первый равен NULL. Функции параметров с фиксированным списком немного более эффективны, чем функции параметров со списком переменных. Попробуйте IFNULL()
- это работает - как я здесь: dbfiddle.uk/….
Вы можете написать оператор CASE WHEN
и изменить столбец expiry
на максимальное значение 999-12-31 (253402270022), если expiry is null
, то отсортируйте и получите максимальный срок действия. Затем вы можете выполнить условие expiry > date_apply
WITH maxTariffs AS
(SELECT id,
(CASE
WHEN expiry IS NULL
THEN 253402270022
ELSE expiry
END) AS expiry
FROM tariffs)
SELECT * FROM tariffs WHERE id IN (SELECT id FROM maxTariffs WHERE expiry > DATE_APPLY ORDER BY expiry ASC ) LIMIT 1
Демо в DBfiddle
Это самое читаемое решение. Является ли производительность такой же хорошей, как COALESCE()?
Однако, похоже, он не дает правильного ответа. Если я попытаюсь выполнить запрос на 16 марта (1647408000), я должен получить строку с идентификатором 3 (последний тариф на тот момент).
@Merc Я думал, что тебе нужно получить максимально допустимое значение. Я изменил ответ, и теперь вы получите ближайшее значение по отношению к DATE_APPLY. Попробуйте, это может решить вашу проблему)
Я бы предпочел использовать даты от и до, а для открытого периода использовать дату, например «9999-12-31».