I ran into a problem and maybe there are experienced guys here to help me figure it out: I have a table with rows:

This table has a million records, there are about 1000 ID instances, only the date of the value change and, therefore, the value itself changes in them. When the value of the ID changes is added to this table:

ID | Tme the value was changed (DATE) | VALUE

My task is to get the all id's values closest to the input date.

I mean: if I input date "20.07.2022 10:00:00" I want to get each ID (1-1000) with rows "value, date" with last date before "20.07.2022 10:00:00":

What query will be the most optimal and correct in this case?

CodePudding user response：

If you want the data for each ID with the latest change up to, but not after, your input date then you can just filter on that date, and use aggregate functions to get the most recent data in that filtered range:

select id,
  max(change_time) as change_time,
  max(value) keep (dense_rank last order by change_time) as value
from your_table
where change_time <= <your input date>
group by id

With your previous sample data, using midnight this morning as the input date would give:

select id,
  max(change_time) as change_time,
  max(value) keep (dense_rank last order by change_time) as value
from your_table
where change_time <= timestamp '2022-07-28 00:00:00'
group by id
order by id

and using midday today woudl give:

select id,
  max(change_time) as change_time,
  max(value) keep (dense_rank last order by change_time) as value
from your_table
where change_time <= timestamp '2022-07-28 12:00:00'
group by id
order by id

db<>fiddle with some other input dates to show the result set changing.