表示即将过期的记录

Question

我需要存储连接到港口的关税。因此，表可以像这样:

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

在这种情况下，ID 5还没有过期，这意味着它是当前的。(我意识到我在那里写的是日期，而不是时间戳;我这样做是为了更容易阅读)

我遇到的问题是在给定特定日期的逻辑上找出使用哪种关税。在一个理想的世界里，如果5是"无穷大"，我可以只做WHERE expiry > date_apply limit 1——然而，我没有那个奢侈，因为date_apply根本不会返回。

我可以给expiry分配一个非常大的数字来表示"电流"。条目。无论如何，它都会使查询工作。但是…感觉不对。

有人建议为每个关税使用两个字段，一个"来自";和一个"to"，告诉我，否则查询将是一场噩梦。我开始明白他们的意思了……但我担心运营商可能不愿意有"漏洞"。在关税的时间框架内，这将很难防止。

我应该如何组织我的表，我应该如何查询它?这里的最佳实践是什么?

Answer 1

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中未过期的值。

Answer 2

您可以将最终价格保留为过期null，我们可以使用coalesce根据当时所需的逻辑分配值。
这里我们只从过期关税和过期=null关税开始。我们创建了一个视图，它将过期日期设置为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 |价格|过期-: | ----: |:--------2 | 12.00 | undefined

insert into tariffs (price,expiry) values (15,'2022-12-30');

select * from current_tarif;

id |价格|过期-: | ----: |:---------3 | 15.00 | 2022-12-30

db<此处小提琴>

Answer 3

使用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
;

Answer 4

您可以编写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