Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ibt2f-0002Lb-4O for pgsql-sql@arkaria.postgresql.org; Mon, 02 Dec 2019 21:15:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ibt1f-0002Pa-8N for pgsql-sql@arkaria.postgresql.org; Mon, 02 Dec 2019 21:14:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ibt1e-0002P5-TJ for pgsql-sql@lists.postgresql.org; Mon, 02 Dec 2019 21:14:35 +0000 Received: from mail-pj1-x1041.google.com ([2607:f8b0:4864:20::1041]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ibt1b-0004af-Vy for pgsql-sql@lists.postgresql.org; Mon, 02 Dec 2019 21:14:34 +0000 Received: by mail-pj1-x1041.google.com with SMTP id ep17so349404pjb.4 for ; Mon, 02 Dec 2019 13:14:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=EwjhWKLLcj8iZlqQF04mQf1LVmnvuYozwsKTdHE4/Tc=; b=oq+VdCeZjHCeWddVDDOxT44DwmOTcapPszJQzWkwQ4U1+zcgIxkaqqrF7rx/J0UmiB WScHeYQY6M4hNUBhEYhoVfT3hgLhxcP1eUkXbqgEiYp72u5pUDMnk9fRzQ1CZX4UTWaE Cc9Nk1jbPugLox77k+ChizSuvZmZvSX7pjxDm/1izCE3V2J4aEQ18TW6hLSutYOQc1aT UxKPTDjS8K6n71Pt8qfnOmtnUFkeg5oLI0CRJyc+Zqe2vNXtL+795xvC+elx7yLoH+B1 e6MWhinhlm5Vt+k6MS0ksNtYUApFP7coB68WORZldrCWG17kRBWEHHO9YwvOqqWhXA/5 RFEw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=EwjhWKLLcj8iZlqQF04mQf1LVmnvuYozwsKTdHE4/Tc=; b=s7Uya/74+cDlTsqDpnZ0k8Q77yd8XRPsviMnv16qpER1mS+a55off39ZckL39RkejT tXdwsV146B+MQ3gL9cYwB11a6OHu+n3CZX8YR9BX0wO2pHZWJTvhXhSSt8PsgYtSJ+gs ygTjz/xPXBMwYIXmYfhOitAnryVd/jeGR8Hzh8oc9+/ot/y1Ji+4HtF23QuxCGrbVqjI rGxqdu6Irrlh3cGR0YhzGr7Ku4yTZLncPzMt8ejYVeguX0/W/if3wGJnuO+2IFnCaKXF MJZr2dObVrV4CtCnN4Dd7E4ysKmEJ5ae26RX08xJctvACcsZtkj9JVLmlm8acC7re2P+ hSOw== X-Gm-Message-State: APjAAAWt90unDze8NCd6Orydr1Mub+OLaolHfuK2Cot1XfTZyuimnoNo wFXcZPo8HcLSceh5733CQkI= X-Google-Smtp-Source: APXvYqz9bh+0HFA3pmDD32hdgVSHkMjjBiLPXt2k+gvX1LqShSbglBX9+jkRy5TphzBsGJQS4rc7xg== X-Received: by 2002:a17:90a:2710:: with SMTP id o16mr1267141pje.110.1575321268667; Mon, 02 Dec 2019 13:14:28 -0800 (PST) Received: from [10.106.128.50] ([155.100.47.2]) by smtp.gmail.com with ESMTPSA id z1sm423729pfk.61.2019.12.02.13.14.27 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 02 Dec 2019 13:14:28 -0800 (PST) From: Rob Sargent Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_96DA600B-6918-4411-8A19-987746916EA5" Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.11\)) Subject: Re: Solving my query needs with Rank and may be CrossTab Date: Mon, 2 Dec 2019 14:14:26 -0700 In-Reply-To: Cc: pgsql-sql@lists.postgresql.org To: Iaam Onkara References: <20558BC2-9439-4449-A9A4-542141811003@gmail.com> X-Mailer: Apple Mail (2.3445.104.11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_96DA600B-6918-4411-8A19-987746916EA5 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On Dec 2, 2019, at 9:55 AM, Iaam Onkara wrote: >=20 > @Rob. What your referring to sounds like Materialized views, isn't it? = An example query would be helpful in understand your = recommendation/approach better. >=20 > On Mon, Dec 2, 2019 at 7:42 AM Rob Sargent > wrote: >=20 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 =3D last_value=20 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 =3D 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. --Apple-Mail=_96DA600B-6918-4411-8A19-987746916EA5 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii

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 =3D = 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 =3D 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.



= --Apple-Mail=_96DA600B-6918-4411-8A19-987746916EA5--