I'm not sure I understand your proposed solution. There is also the case to consider where the same patient can be assigned the same device multiple times. In this case, the value may be reset at each assignment (hence the line value - issued_value AS value from the original query).
On Thu, May 23, 2013 at 1:01 PM, Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> wrote:
On 05/23/2013 10:57 AM, Jonathan Morra wrote:If the reads are always incremented - that is the read you want is always the largest read - then something along these lines might work well and be more readable (untested code);
Ultimately I'm going to deploy this to Heroku on a Linux machine (my tests have so far indicated that Heroku is MUCH slower than my machine), but I wanted to get it fast on my local machine first. I agree with your role partitioning, however, this is only a dev machine.
For the sum vs. last, the idea is that each patient is issued a device and reads are recorded. The nature of the reads are that they are incremental, so if a patient never changes devices there is no need for a sum. However, patients will be changing devices, and the patient_device table records when each patient had a given device. What I want to sum up is the total value for a patient regardless of how many times they changed devices
-- distill out max value for each device
with device_maxreads as (
select
device_id,
max(value) as max_read
from
reads
group by
device_id)
-- then sum into a totals for each patient
patient_value as (
select
p.patient_id,
sum(max_read) patient_value
from
device_maxreads d
join patient_devices p on p.device_id = d.device_id
group by
p.patient_id
)
select
...whatever...
from
...your tables.
join patient_value p on p.patient_id = ...
;
If the values increment and decrement or patients are issued devices at overlapping times (i.e. using two devices at one time) then the query gets more complicated but "with..." is still a likely usable construct.
Cheers,
Steve
