Couldn't he also use a daterange (or tsrange) in the where clause? Something like:
select... from table where [9,xdate::date]::daterange @:> datecolumn and metric = 'measure you want' order by date desc limit 1
Even with queries of this form, based on how you describe your data I might make two stored procedures. One where you pass in the date, the metric of interest and it returns the single value. Then the second procedure would take the same input and then does the averaging you want. Then you can call them all in a single select query:
select singlemetric('date'::date, bloodpressure), avgmetric('date'::date, insulin)...
Back to the design of your db, it sounds like you have a fixed set of metrics you are recording. If this is so I think making them distinct columns in your data set is preferable than the scheme you are using. But if you are going to be adding many new metrics frequently then your design makes sense to me.
The convention here is to inline or bottom-post.
On Saturday, October 3, 2020, Tim Uckun <timuckun@gmail.com> wrote:
Given the scale I am working at I thought a specific time scale
database would be overkill but I'll look into it nevertheless. Even
if I do write the queries with the known metrics I am still trying to
figure out how to efficiently query "last known value of this metric
on or before X time" without a correlated subquery which would be a
massively inefficient query.
Yes, if the time is arbitrary you are stuck with a where clause, sort-and-limit. DISTINCT ON can be useful for returning multiple entities over the same metric.
Indexes, possibly partial (if you don’t normalize the model), should help.
Even massively inefficient can be efficient enough...
David J.