Re: Solving my query needs with Rank and may be CrossTab
| От | Rob Sargent |
|---|---|
| Тема | Re: Solving my query needs with Rank and may be CrossTab |
| Дата | |
| Msg-id | C4F63576-4927-4AB9-95C3-0D215E14DDA0@gmail.com обсуждение исходный текст |
| Ответ на | Re: Solving my query needs with Rank and may be CrossTab (Iaam Onkara <iamonkara@gmail.com>) |
| Список | pgsql-sql |
On Dec 2, 2019, at 9:55 AM, Iaam Onkara <iamonkara@gmail.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@gmail.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.
В списке pgsql-sql по дате отправления: