From: | Rob Sargent <robjsargent(at)gmail(dot)com> |
---|---|
To: | Iaam Onkara <iamonkara(at)gmail(dot)com> |
Cc: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Re: Solving my query needs with Rank and may be CrossTab |
Date: | 2019-12-02 21:14:26 |
Message-ID: | C4F63576-4927-4AB9-95C3-0D215E14DDA0@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> On Dec 2, 2019, at 9:55 AM, Iaam Onkara <iamonkara(at)gmail(dot)com> wrote:
>
> @Rob. What your referring to sounds like Materialized views, isn't it? An example query would be helpful in understand your recommendation/approach better.
>
> On Mon, Dec 2, 2019 at 7:42 AM Rob Sargent <robjsargent(at)gmail(dot)com <mailto:robjsargent(at)gmail(dot)com>> wrote:
>
Using the update-fixed-table style:
-- Get all possible people, null their values
create table report as
select distinct patient, null::float as bmi, null::float as sysbp, null::float as diabp, null::int as height
from source_table;
create index on report(patient);
-- get the height code (8302-2 using tilde operator because the import included leading blanks)
update report r set height = last_value
from (select distinct patient, last_value(measurement) over
(partition by patient, code
order by sampletime)
from source_table
where code ~ '8302-2') as m where r.patient = m.patient
;
-- then similar for other codes. You may want to format the results, as in combining sys/dia bp readings after the update operations
-- the time drag of course is forever finding max(measurement time). A composite index might help; indeed the unique key on the source is patient,code,timestamp I think.
From | Date | Subject | |
---|---|---|---|
Next Message | ROS Didier | 2019-12-20 13:01:50 | problem with read-only user |
Previous Message | Iaam Onkara | 2019-12-02 16:55:27 | Re: Solving my query needs with Rank and may be CrossTab |