Re: Solving my query needs with Rank and may be CrossTab
Rob Sargent <[email protected]> Mon, 2 Dec 2019 06:42:28 -0700
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail-82EA7EEA-102E-4180-ADAE-784AEDEB90B6 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable > On Dec 1, 2019, at 11:09 PM, Iaam Onkara <[email protected]> wrote: >=20 > =EF=BB=BF > Yes indexes on Code and Timestamp column may also be needed even though Pa= tient_ID column will be indexed. >=20 I believe you will want a compound index covering both columns > By incremental tables do you mean tables with Auto Increment primary key f= or ID=20 No. I mean a series of intermediate tables each with one more report column.= These can be temporary and unlogged but the will need an index on patient. (= Again, you have the option of predefining the full report table and repeated= ly updating a single column.) >=20 > What I am having tough time figuring out is how to transform the result in= to this even after using multiple CTEs >=20 >> On Sun, Dec 1, 2019 at 10:58 PM Rob Sargent <[email protected]> wrote= : >>=20 >>=20 >>>> On Dec 1, 2019, at 4:38 PM, Iaam Onkara <[email protected]> wrote: >>>>=20 >>> =EF=BB=BF >>> @Rob: There is no time window. It is the latest values for given set of a= ttributes regardless of timestamp. If some attributes have multiple values t= hen multiple rows can be returned with other attributes having blank values.= >>>=20 >>> Creating one sub select for one column is an obvious approach but will n= ot be performant specially when the dataset grows, I am looking for a soluti= on which doesn't require one sub select per column. >>>=20 >>>> On Sun, Dec 1, 2019 at 5:28 PM Rob Sargent <[email protected]> wrot= e: >>>>=20 >>>>=20 >>>>> On Dec 1, 2019, at 3:54 PM, Iaam Onkara <[email protected]> wrote: >>>>>=20 >>>>> Hi Friends, >>>>>=20 >>>>> I have a table with data like this gist https://gist.github.com/daya/d= 0794efcd4278fc5dce6e7339d03a8fd and I want to fetch the latest values for a g= iven set of attributes so the result set looks like this gist https://gist.g= ithub.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6 >>>>>=20 >>>>> Please note in the desired result set=20 >>>>> There is an assumed mapping of Code to display i.e. code 39156-5 is BM= I. >>>>> "Oxygen Saturation" has only one value and "Pulse" has no value >>>>> In my attempts and with some help I have this query=20 >>>>>=20 >>>>> with v_max as=20 >>>>> (SELECT=20 >>>>> code, uom, val, created_on, dense_rank() over ( partition by code orde= r by created_on desc) as r=20 >>>>> FROM vitals v >>>>> where (v.code =3D '8480-6' or v.code=3D'8462-4' or v.code=3D'39156-5' o= r v.code=3D'8302-2') >>>>> )=20 >>>>> SELECT c.display, uom, val, created_on=20 >>>>> from v_max v inner join codes c on v.code=3Dc.code >>>>> where r =3D 1;=20 >>>>>=20 >>>>> which gives this result=20 >>>>>=20 >>>>> But the result set that I want I am unable to get. Or if is it even po= ssible to get? >>>>>=20 >>>>> Thanks for your help >>>>>=20 >>>>=20 >>>> I take it the last value by timestamp per code per patient is the one t= o be reported? Or is there a time window? >>>> Turning rows into columns can be done with sub-selects per derived colu= mn or (usually faster) temporary tables built up in separate selects with ea= ch adding usually one column (but possibly more). >>>>=20 >>=20 >> I agree the sub select per column can easily become slow. Incremental tab= les does not, in my experience, suffer the same problem. (One can also repe= atedly update a single table with the predefined structure.) There maybe a w= ay to get what you want with multiple CTEs but I suspect that approach would= be more akin to multiple sub selects than to incremental tables.=20 >> =46rom your further description of the problem it will be critical to hav= e an index on the code AND time stamp columns of the source table.=20 --Apple-Mail-82EA7EEA-102E-4180-ADAE-784AEDEB90B6 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D= utf-8"></head><body dir=3D"auto"><div dir=3D"ltr"><br></div><div dir=3D"ltr"= ><br><blockquote type=3D"cite">On Dec 1, 2019, at 11:09 PM, Iaam Onkara <= [email protected]> wrote:<br><br></blockquote></div><blockquote type=3D= "cite"><div dir=3D"ltr">=EF=BB=BF<div dir=3D"ltr">Yes indexes on Code and Ti= mestamp column may also be needed even though Patient_ID column will be inde= xed.<div><br></div></div></div></blockquote>I believe you will want a compou= nd index covering both columns<br><blockquote type=3D"cite"><div dir=3D"ltr"= ><div dir=3D"ltr"><div>By incremental tables do you mean tables with Auto In= crement primary key for ID </div></div></div></blockquote>No. I mean a s= eries of intermediate tables each with one more report column. These can be t= emporary and unlogged but the will need an index on patient. (Again, you hav= e the option of predefining the full report table and repeatedly updating a s= ingle column.)<br><blockquote type=3D"cite"><div dir=3D"ltr"><div dir=3D"ltr= "><div><br></div><div>What I am having tough time figuring out is how to tra= nsform the result into <a href=3D"https://gist.github.com/daya/0cb7f868= 2520a1dd4cdda8c0266f77f6">this</a> even after using multiple CTEs</div></div= ><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sun= , Dec 1, 2019 at 10:58 PM Rob Sargent <<a href=3D"mailto:robjsargent@gmai= l.com">[email protected]</a>> wrote:<br></div><blockquote class=3D"gm= ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2= 04,204);padding-left:1ex"><div dir=3D"auto"><div dir=3D"ltr"><br></div><div d= ir=3D"ltr"><br><blockquote type=3D"cite">On Dec 1, 2019, at 4:38 PM, Iaam On= kara <<a href=3D"mailto:[email protected]" target=3D"_blank">iamonkara@= gmail.com</a>> wrote:<br><br></blockquote></div><blockquote type=3D"cite"= ><div dir=3D"ltr">=EF=BB=BF<div dir=3D"ltr">@Rob: There is no time window. I= t is the latest values for given set of attributes regardless of timestamp. I= f some attributes have multiple values then multiple rows can be returned wi= th other attributes having blank values.<div><br></div><div>Creating on= e sub select for one column is an obvious approach but will not be performan= t specially when the dataset grows, I am looking for a solution which doesn'= t require one sub select per column.<br></div></div><br><div class=3D"gmail_= quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, Dec 1, 2019 at 5:28 PM R= ob Sargent <<a href=3D"mailto:[email protected]" target=3D"_blank">ro= [email protected]</a>> wrote:<br></div><blockquote class=3D"gmail_quote= " style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p= adding-left:1ex"><div><br><div><br><blockquote type=3D"cite"><div>On Dec 1, 2= 019, at 3:54 PM, Iaam Onkara <<a href=3D"mailto:[email protected]" targ= et=3D"_blank">[email protected]</a>> wrote:</div><br><div><div dir=3D"l= tr">Hi Friends,<div><br></div><div>I have a table with data like this gist <= a href=3D"https://gist.github.com/daya/d0794efcd4278fc5dce6e7339d03a8fd" tar= get=3D"_blank">https://gist.github.com/daya/d0794efcd4278fc5dce6e7339d03a8fd= </a> and I want to fetch the latest values for a given set of attributes so t= he result set looks like this gist <a href=3D"https://gist.github.com/d= aya/0cb7f8682520a1dd4cdda8c0266f77f6" target=3D"_blank">https://gist.github.= com/daya/0cb7f8682520a1dd4cdda8c0266f77f6</a></div><div><br></div><div>Pleas= e note in the desired result set </div><div><ol><li>There is an assumed= mapping of Code to display i.e. code 39156-5 is BMI.</li><li>"Oxygen S= aturation" has only one value and "Pulse" has no value</li></ol><div>In my a= ttempts and with some help I have this query </div></div><div><br= ></div><div><font face=3D"monospace">with v_max as <br>(SELECT <br> cod= e, uom, val, created_on, dense_rank() over ( partition by code order by crea= ted_on desc) as r <br>FROM vitals v<br>where (v.code =3D '8480-6' or v.code=3D= '8462-4' or v.code=3D'39156-5' or v.code=3D'8302-2')<br>) <br>SELECT c.displ= ay, uom, val, created_on <br>from v_max v inner join codes c on v.code=3Dc.c= ode<br>where r =3D 1; <br></font></div><div><font face=3D"monospace"><b= r></font></div>which gives <a href=3D"https://gist.github.com/daya/19ca22a83= 7ed9998c117f38ff3cce3f2" target=3D"_blank">this result</a> <div><br></d= iv><div>But the <a href=3D"https://gist.github.com/daya/0cb7f8682520a1dd4cdd= a8c0266f77f6" target=3D"_blank">result set that I want</a> I am unable t= o get. Or if is it even possible to get?</div><div><br></div><div>Thanks for= your help</div><div><br></div></div> </div></blockquote></div><br><div>I take it the last value by timestamp per c= ode per patient is the one to be reported? Or is there a time window?</div><= div>Turning rows into columns can be done with sub-selects per derived colum= n or (usually faster) temporary tables built up in separate selects with eac= h adding usually one column (but possibly more).</div><div><br></div></div><= /blockquote></div> </div></blockquote><br><div>I agree the sub select per column can easily bec= ome slow. Incremental tables does not, in my experience, suffer the same pro= blem. (One can also repeatedly update a single table with the predefin= ed structure.) There maybe a way to get what you want with multiple CTEs but= I suspect that approach would be more akin to multiple sub selects than to i= ncremental tables. </div><div>=46rom your further description of the pr= oblem it will be critical to have an index on the code AND time stamp column= s of the source table. </div></div></blockquote></div> </div></blockquote></body></html>= --Apple-Mail-82EA7EEA-102E-4180-ADAE-784AEDEB90B6--