【Oracle】求历史表以往记录中最接近给定时间的一批记录

需求：某系统将诸多服务器状态定时存到在一张表里，查询时会给出一个时间，要求取出最接近于给定时间，又不可大于的一批记录。

比如，有gp、dws、etl三台服务器，8：00，9：00，10：00，11：00都有记录存储了它们的状态，当给定10：30时，就该取出10：00的那批记录。

存储数据的表结构如下：

create table gpcc_disk_history(
    id number(8),
    hostname nvarchar2(10),
    disksize number(8),
    used number(8),
    ctime date,
    primary key(id)
)

测试数据如下：

insert into gpcc_disk_history values(1,'gp','100','50',to_timestamp('22-10-2021 12:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into gpcc_disk_history values(2,'dws','100','50',to_timestamp('22-10-2021 12:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into gpcc_disk_history values(3,'etl','100','50',to_timestamp('22-10-2021 12:00:00','dd-mm-yyyy hh24:mi:ss'));

insert into gpcc_disk_history values(4,'gp','110','51',to_timestamp('22-10-2021 11:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into gpcc_disk_history values(5,'dws','110','51',to_timestamp('22-10-2021 11:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into gpcc_disk_history values(6,'etl','110','51',to_timestamp('22-10-2021 11:00:00','dd-mm-yyyy hh24:mi:ss'));

insert into gpcc_disk_history values(7,'gp','120','52',to_timestamp('22-10-2021 10:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into gpcc_disk_history values(8,'dws','120','52',to_timestamp('22-10-2021 10:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into gpcc_disk_history values(9,'etl','120','52',to_timestamp('22-10-2021 10:00:00','dd-mm-yyyy hh24:mi:ss'));

insert into gpcc_disk_history values(10,'gp','130','53',to_timestamp('22-10-2021 09:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into gpcc_disk_history values(11,'dws','130','53',to_timestamp('22-10-2021 09:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into gpcc_disk_history values(12,'etl','130','53',to_timestamp('22-10-2021 09:00:00','dd-mm-yyyy hh24:mi:ss'));

insert into gpcc_disk_history values(13,'gp','140','54',to_timestamp('22-10-2021 08:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into gpcc_disk_history values(14,'dws','140','54',to_timestamp('22-10-2021 08:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into gpcc_disk_history values(15,'etl','140','54',to_timestamp('22-10-2021 08:00:00','dd-mm-yyyy hh24:mi:ss'));

现在如果要写SQL去查询不大于今天10：30的最新记录，方案是不止一种的，比如：

--max(ctime)方案  Cost=6
select id,hostname,disksize,used,to_char(ctime,'hh24:mi:ss') as nctime from gpcc_disk_history where ctime=(select max(ctime) from gpcc_disk_history where ctime<to_timestamp('22-10-2021 10:30:00','dd-mm-yyyy hh24:mi:ss'))

-- with as max(ctime)方案 Cost=7
with tmp as (
select * from gpcc_disk_history where ctime<to_timestamp('22-10-2021 10:30:00','dd-mm-yyyy hh24:mi:ss')
)
select id,hostname,disksize,used,to_char(ctime,'hh24:mi:ss') as nctime
from tmp where ctime=(select max(ctime) as maxtime from tmp)

--left join方案 Cost=9
with tmp as (
select * from gpcc_disk_history where ctime<to_timestamp('22-10-2021 10:30:00','dd-mm-yyyy hh24:mi:ss')
)
select a.id,a.hostname,a.disksize,a.used,to_char(a.ctime,'hh24:mi:ss') as nctime
from tmp a
left join tmp b
on a.ctime<b.ctime
where b.ctime is NULL

--not exists方案 Cost=9
with tmp as (
select * from gpcc_disk_history where ctime<to_timestamp('22-10-2021 10:30:00','dd-mm-yyyy hh24:mi:ss')
)
select a.id,a.hostname,a.disksize,a.used,to_char(a.ctime,'hh24:mi:ss') as nctime
from tmp a
where not exists (select NULL from tmp b where b.ctime>a.ctime)

它们执行的结果都是：

        ID HOSTNAME               DISKSIZE       USED NCTIME
---------- -------------------- ---------- ---------- ----------------
         8 dws                         120         52 10:00:00
         7 gp                          120         52 10:00:00
         9 etl                         120         52 10:00:00

 从效率和可读性来说，都是第一种方案最优。

END