Re: Performance of complicated query

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(at)pinpointresearch(dot)com> wrote:

> On 05/23/2013 10:57 AM, Jonathan Morra wrote:
>
>> 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
>>
>
> 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);
>
> -- 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
>