Querying sporadic time series type data.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Querying sporadic time series type data.

Tim Uckun
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?


Reply | Threaded
Open this post in threaded view
|

Re: Querying sporadic time series type data.

David G Johnston
On Fri, Oct 2, 2020 at 10:45 PM Tim Uckun <[hidden email]> wrote:
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.

My first impression is that you should use a timeseries database for this, not PostgreSQL.

If you do try to use SQL to do this you indeed run into the problem of handling SQL's requirement that every column be explicitly defined.  You can probably work around this somewhat by writing a query generator instead of hard-coding queries.  Though for your current level of knowledge I'd suggest putting forth working queries for the current model and forget about how easy or hard dealing with change would be.  Or at least be more convincing that change adaption is an important requirement.

Your data type issue is self-inflicted.  Create a composite data type for blood pressure.  Expanding upon that, you should have a table for each metric - or at least be willing to find middle-ground where some are grouped on one table (either as rows or columns) and some are separate tables.

None of this is impossible in PostgreSQL.  Its a matter of development effort and performance.  Put forth something that works - or at least kinda works and note where specifically in the code you are stuck - if you want non-conceptual help overcoming a specific problem.

David J.
Reply | Threaded
Open this post in threaded view
|

Re: Querying sporadic time series type data.

Tim Uckun
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.


On Sun, Oct 4, 2020 at 5:11 AM David G. Johnston
<[hidden email]> wrote:

>
> On Fri, Oct 2, 2020 at 10:45 PM Tim Uckun <[hidden email]> wrote:
>>
>> 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.
>
>
> My first impression is that you should use a timeseries database for this, not PostgreSQL.
>
> If you do try to use SQL to do this you indeed run into the problem of handling SQL's requirement that every column be explicitly defined.  You can probably work around this somewhat by writing a query generator instead of hard-coding queries.  Though for your current level of knowledge I'd suggest putting forth working queries for the current model and forget about how easy or hard dealing with change would be.  Or at least be more convincing that change adaption is an important requirement.
>
> Your data type issue is self-inflicted.  Create a composite data type for blood pressure.  Expanding upon that, you should have a table for each metric - or at least be willing to find middle-ground where some are grouped on one table (either as rows or columns) and some are separate tables.
>
> None of this is impossible in PostgreSQL.  Its a matter of development effort and performance.  Put forth something that works - or at least kinda works and note where specifically in the code you are stuck - if you want non-conceptual help overcoming a specific problem.
>
> David J.


Reply | Threaded
Open this post in threaded view
|

Re: Querying sporadic time series type data.

David G Johnston
The convention here is to inline or bottom-post.

On Saturday, October 3, 2020, Tim Uckun <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Querying sporadic time series type data.

Steven Pousty
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.

On Sat, Oct 3, 2020 at 5:10 PM David G. Johnston <[hidden email]> wrote:
The convention here is to inline or bottom-post.

On Saturday, October 3, 2020, Tim Uckun <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Querying sporadic time series type data.

Tim Uckun
> select... from table where [9,xdate::date]::daterange @:> datecolumn and metric =  'measure you want' order by date desc limit 1

In the correlated subquery a simple select  date, value from metrics
where metric_id=x order by datetime desc limit 1 would get you the
figure you want. but of course this has to be repeated for every
metric you collect and if you wanted to do something fancy like plot
some measure over a year based on monthly figures it's massively
inefficient.


> 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 problem with this is that most of the rows will only have one
column filled in. In fact probably all of them are.

I am starting to think the best way to handle this is by creating some
time slot tables with all the columns and populating with batch jobs.
A daily table, weekly table, monthly table etc.  Another option might
be to go ahead and write that slow and io heavy query and save it as a
materialized view.