1. Reads is constantly inserted upon. It should never be updated or deleted.
2. I suppose I can, but that will make my insertion logic very complicated. I cannot guarantee the order of any of this data, so I might get reads at any time and also get assignments at any time (historical as well). I suppose I could do that, but I'd like to avoid it if at all possible.
3. 2 measurements can have the same date, and that is fine. The problem arises when the same device produces 2 reads at the same time and that isn't possible.
4. I agree that a lot of this is a mess, however MAX(max_read) OVER(PARTITION BY patient_id) AS latest_read seems necessary as using a group by clause forces me to group by all elements I'm selecting, which I don't want to do.
On Thu, May 23, 2013 at 12:23 PM, Vladimir Sitnikov <sitnikov.vladimir@xxxxxxxxx> wrote:
>>This leads to the WHERE clause, WHERE read_datetime = max_read, and hence I'm only summing the last read for each device for each patient.Is "reads" table insert-only? Do you have updates/deletes of the "historical" rows?Can you have a second "reads" table that stores only up to date values?
>>3. Can I modify my tables to make this query (which is the crux of my application) run faster?That will eliminate max-over completely, enable efficient usage in other queries, and make your queries much easier to understand by humans and computers.PS. read_datetime = max_read is prone to "what if two measurements have same date" errors.PPS. distinct MAX(max_read) OVER(PARTITION BY patient_id) AS latest_read looks like a complete mess. Why don't you just use group by?
Regards,Vladimir
