Hi all.
I am warehousing my health data in PG. Some of this data comes from my
phone/watch but some I enter in manually. Basically this is similar to
a time series metric collection type of scenario. The table looks
like this
timestamp, measure, value, unit
So for example the data could look like
Insert into metrics (timestamp, measure, value, unit) values (now(),
"blood glucose", 6.0, 'mmol/L')
Some metrics have a lot of data such as steps and others such as blood
pressure readings, weight etc are much rarer.
I have two problems I am trying to solve. Question one is
"what was the state of my health on XXXX date" This would be based on
"last known data as of that date". The problem is that for some
metrics the value will be a sum (step count for example) but for
others it will be an average (blood glucose for example) for still
others it might be min and/or max (blood pressure). Also it might be
wise to report null for metrics that haven't been measured for a long
time (weight for example).
I tried to do this with CTEs but I am not sure how to write one
without having to include every metric ahead of time. This means I
would have to rewrite the query every time I add a metric.
I get the feeling this might be possible with some kind of correlated
subquery but I can't come up with one that works.
The other issue is how to deal with metrics with dual values like
blood pressure. It's easy enough to log systolic and diastolic as
separate rows but how do I consolidate them?