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.vladi...@gmail.com> 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? > > >>3. Can I modify my tables to make this query (which is the crux of my > application) run faster? > Can you have a second "reads" table that stores only up to date values? > 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 >
