Re: Solving my query needs with Rank and may be CrossTab
Rob Sargent <[email protected]> Mon, 2 Dec 2019 14:14:26 -0700
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <[email protected]> |
--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 <[email protected]> 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 <[email protected] = <mailto:[email protected]>> 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 <html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; = charset=3Dus-ascii"></head><body style=3D"word-wrap: break-word; = -webkit-nbsp-mode: space; line-break: after-white-space;" class=3D""><br = class=3D""><div><br class=3D""><blockquote type=3D"cite" class=3D""><div = class=3D"">On Dec 2, 2019, at 9:55 AM, Iaam Onkara <<a = href=3D"mailto:[email protected]" class=3D"">[email protected]</a>>= wrote:</div><br class=3D"Apple-interchange-newline"><div class=3D""><div = dir=3D"ltr" class=3D"">@Rob. What your referring to sounds like = Materialized views, isn't it? An example query would be helpful in = understand your recommendation/approach better.</div><br class=3D""><div = class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Dec = 2, 2019 at 7:42 AM Rob Sargent <<a = href=3D"mailto:[email protected]" = class=3D"">[email protected]</a>> wrote:<br = class=3D""></div><blockquote class=3D"gmail_quote" style=3D"margin:0px = 0px 0px 0.8ex;border-left:1px solid = rgb(204,204,204);padding-left:1ex"><div dir=3D"auto" class=3D""><div = dir=3D"ltr" class=3D""><br = class=3D""></div></div></blockquote></div></div></blockquote></div><br = class=3D""><div class=3D"">Using the update-fixed-table style:</div><div = class=3D""><div class=3D"">-- Get all possible people, null their = values</div><div class=3D"">create table report as</div><div = class=3D"">select distinct patient, null::float as bmi, null::float as = sysbp, null::float as diabp, null::int as height</div><div class=3D"">from= source_table;</div><div class=3D"">create index on = report(patient);</div><div class=3D"">-- get the height code (8302-2 = using tilde operator because the import included leading = blanks)</div><div class=3D"">update report r set height =3D = last_value </div><div class=3D"">from (select distinct patient, = last_value(measurement) over</div><div class=3D""> = (partition by patient, code</div><div class=3D""> = order by sampletime)</div><div class=3D"">from = source_table</div><div class=3D"">where code ~ '8302-2') as m where = r.patient =3D m.patient</div><div class=3D"">;</div></div><div = class=3D"">-- then similar for other codes. You may want to format the = results, as in combining sys/dia bp readings after the update = operations</div><div class=3D"">-- 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.</div><div class=3D""><br class=3D""></div><div class=3D""><br = class=3D""></div><div class=3D""><br class=3D""></div></body></html>= --Apple-Mail=_96DA600B-6918-4411-8A19-987746916EA5--