Записи, наиболее близкие по времени
Мне нужно получить 2 записи для каждой даты, время которых ближе всего к определенному времени.
Например, это таблица (реальная таблица содержит больше данных)
CREATE TABLE t1 (time DATE, some_data NUMBER);
И в этой таблице будут такие записи
INSERT INTO t1 VALUES(TO_DATE('1.1.2024 08:08:08', 'dd.mm.yyyy hh24:mi:ss'), 1);
INSERT INTO t1 VALUES(TO_DATE('1.1.2024 10:10:10', 'dd.mm.yyyy hh24:mi:ss'), 2);
INSERT INTO t1 VALUES(TO_DATE('1.1.2024 15:15:15', 'dd.mm.yyyy hh24:mi:ss'), 3);
INSERT INTO t1 VALUES(TO_DATE('1.1.2024 20:20:20', 'dd.mm.yyyy hh24:mi:ss'), 4);
INSERT INTO t1 VALUES(TO_DATE('2.1.2024 09:09:09', 'dd.mm.yyyy hh24:mi:ss'), 5);
INSERT INTO t1 VALUES(TO_DATE('2.1.2024 12:12:12', 'dd.mm.yyyy hh24:mi:ss'), 6);
INSERT INTO t1 VALUES(TO_DATE('2.1.2024 16:16:16', 'dd.mm.yyyy hh24:mi:ss'), 7);
Я хотел бы получить две записи для каждой даты: первую, ближайшую к 8, а вторую, ближайшую к 20 часам. В этом примере данные результата будут такими:
1.1.2024 08:08:08 1
1.1.2024 20:20:20 4
2.1.2024 09:09:09 5
2.1.2024 16:16:16 7
Я пытался сделать это с помощью подзапросов, но поскольку в реальной жизни у меня сложный запрос (с соединениями, подзапросами и т. д.), я довольно быстро в нем запутался.
Есть ли какой-нибудь хороший и чистый способ сделать это?
заранее спасибо
Ответы 2
Вы можете определить разницу между каждым временем и 08:00 и 20:00 в один и тот же день:
trunc(t1.time) + interval '8' hour - time
trunc(t1.time) + interval '20' hour - time
который может быть положительным или отрицательным, поэтому получите абсолютное значение:
abs(trunc(t1.time) + interval '8' hour - time)
abs(trunc(t1.time) + interval '20' hour - time)
затем проранжируйте их по порядку для каждого дня:
rank() over (partition by trunc(time)
order by abs(trunc(t1.time) + interval '8' hour - time))
rank() over (partition by trunc(time)
order by abs(trunc(t1.time) + interval '20' hour - time))
что дает вам:
select t1.time, t1.some_data,
rank() over (partition by trunc(time)
order by abs(trunc(t1.time) + interval '8' hour - time)) as rnk8,
rank() over (partition by trunc(time)
order by abs(trunc(t1.time) + interval '20' hour - time)) as rnk20
from t1
и, наконец, выберите только те строки, которые имеют наивысший рейтинг:
select time, some_data
from (
select t1.time, t1.some_data,
rank() over (partition by trunc(time)
order by abs(trunc(t1.time) + interval '8' hour - time)) as rnk8,
rank() over (partition by trunc(time)
order by abs(trunc(t1.time) + interval '20' hour - time)) as rnk20
from t1
)
where rnk8 = 1
or rnk20 = 1
order by time
Если у вас есть две строки с одинаковой (абсолютной) разницей, вы увидите обе, потому что rank() даст один и тот же ответ. Если вы не хотите, чтобы это произошло, вам нужно будет решить, как разорвать связи, добавив условия заказа (например, разница без пресса, а затем, возможно, какой-то другой столбец). Или вы можете использовать dense_rank() или row_number(), которые оба будут иметь значение 1 только для одной строки, но какая из строк будет возвращена, будет неопределенным. Лучше быть явным, чтобы получить последовательные результаты.
Если вы хотите сохранить простоту, вы можете сделать это без математических вычислений интервального типа. Вот как это сделать простым способом:
select dt, time, some_data
from (select q.*, rank() over(partition by q.dt, q.target_time order by q.dist asc) rnk
from (select 8 / 24 target_time
,abs(time - trunc(time) - 8 / 24) dist
,trunc(time) dt
,time
,some_data
from t1
union all
select 20 / 24 target_time
,abs(time - trunc(time) - 20 / 24) dist
,trunc(time) dt
,time
,some_data
from t1) q)
where rnk = 1
order by dt, time;