Re: Solving my query needs with Rank and may be CrossTab

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

From: Rob Sargent <robjsargent@gmail.com>
To: Iaam Onkara <iamonkara@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Solving my query needs with Rank and may be CrossTab
Date: Mon, 2 Dec 2019 14:14:26 -0700
Message-ID: <C4F63576-4927-4AB9-95C3-0D215E14DDA0@gmail.com> (raw)
In-Reply-To: <CAMz9UCZvcmYE-5qZH8F=KWOM6-sxVA6F6A3zQUk3uY05p2XwGg@mail.gmail.com>
References: <CAMz9UCatx408iKUGt5RThWxu34gJU_Y8ge4pUBuFoGXZxWxLBg@mail.gmail.com>
	<20558BC2-9439-4449-A9A4-542141811003@gmail.com>
	<CAMz9UCZvcmYE-5qZH8F=KWOM6-sxVA6F6A3zQUk3uY05p2XwGg@mail.gmail.com>



> 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 <mailto: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.

view thread (8+ messages)

Message-ID: <C4F63576-4927-4AB9-95C3-0D215E14DDA0@gmail.com>
Permalink:  ../C4F63576-4927-4AB9-95C3-0D215E14DDA0@gmail.com/
Also on:    postgresql.org/message-id/C4F63576-4927-4AB9-95C3-0D215E14DDA0@gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: robjsargent@gmail.com, iamonkara@gmail.com, pgsql-sql@lists.postgresql.org
  Subject: Re: Solving my query needs with Rank and may be CrossTab
  In-Reply-To: <C4F63576-4927-4AB9-95C3-0D215E14DDA0@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox