【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
