Представление записей с истекающим сроком действия

Мне нужно хранить тарифы, подключенные к порту. Итак, таблица может выглядеть так:

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 для «текущей» записи. Это заставит запрос работать независимо. Но... это кажется неправильным.

Кто-то рекомендовал использовать ДВА поля для каждого тарифа, «от» и «до», сказав мне, что в противном случае запросы будут кошмаром. Я начинаю понимать, что они имеют в виду... но тогда я опасаюсь, что операторы могут невольно иметь "дыры" в сроках для тарифов, которые будет трудно предотвратить.

Как я должен организовать свою таблицу и как я должен запрашивать ее? Каковы лучшие практики здесь?

Я бы предпочел использовать даты от и до, а для открытого периода использовать дату, например «9999-12-31».

Sergey 02.05.2022 16:07

вы можете начать с установки столбца срока действия в качестве типа данных даты, а не временной метки. И вам нужен только один столбец, потому что SQL может проверять другие более ранние или более поздние значения в том же столбце....

Martin 02.05.2022 17:03
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
1
2
77
4
Перейти к ответу Данный вопрос помечен как решенный

Ответы 4

Ответ принят как подходящий
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.

Хорошо, я отредактировал свой ответ и понял, что у меня все равно есть ошибка. :)

Bill Karwin 02.05.2022 17:21

Разве в запросе не требуется оператор ORDER BY, чтобы убедиться, что вы действительно получаете последний возможный тариф?

Merc 03.05.2022 00:57

Мне было непонятно, что вы хотели последний тариф. Я отредактировал ответ и dbfiddle, чтобы учесть это.

Bill Karwin 03.05.2022 01:48

Вы можете оставить окончательную цену с нулевым сроком действия, и мы можем использовать объединение, чтобы присвоить значение в соответствии с логикой, необходимой в то время.
Здесь мы начинаем только с тарифа с истекшим сроком действия и тарифом с истекшим сроком действия = нулевой. Мы создаем представление, которое дает нам срок действия как 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

дб <> рабочий пример здесь

Есть ли способ сделать это без использования представлений? Я бы предпочел, чтобы решение было одним запросом. Могу ли я создать подмножество, а затем запросить его на лету?

Merc 03.05.2022 01:45

Что-то вроде этого совершенно ужасно? dbfiddle.uk/… SELECT * FROM (SELECT *, COALESCE(expiry, 2051273200) as expiry2 FROM тарифы) as temp WHERE 1648594700 < temp.expiry2 ORDER BY expiry2 LIMIT 1

Merc 03.05.2022 01:56

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

user18098820 03.05.2022 06:25

Создавайте даты и временные метки срока действия и срока действия по ходу работы, используя функции 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().

Bill Karwin 02.05.2022 17:30

Вы правы, что NVL() не понимается MySQL. COALESCE(), однако, является функцией с несколькими параметрами, которая возвращает первый параметр, отличный от NULL, а NVL() и ее синонимы IFNULL() и ISNULL() являются функциями с двумя параметрами, возвращающими второй параметр, если первый равен NULL. Функции параметров с фиксированным списком немного более эффективны, чем функции параметров со списком переменных. Попробуйте IFNULL()- это работает - как я здесь: dbfiddle.uk/….

marcothesane 03.05.2022 00:01

Вы можете написать оператор 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()?

Merc 03.05.2022 00:59

Однако, похоже, он не дает правильного ответа. Если я попытаюсь выполнить запрос на 16 марта (1647408000), я должен получить строку с идентификатором 3 (последний тариф на тот момент).

Merc 03.05.2022 01:14

@Merc Я думал, что тебе нужно получить максимально допустимое значение. Я изменил ответ, и теперь вы получите ближайшее значение по отношению к DATE_APPLY. Попробуйте, это может решить вашу проблему)

Harvey Dent 03.05.2022 07:57

Другие вопросы по теме