Re: Solving my query needs with Rank and may be CrossTab
Rob Sargent <[email protected]> Sun, 1 Dec 2019 21:58:27 -0700
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail-2DBC7B8E-350C-40E1-AE54-FFB9936948A1 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable > 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 at= tributes regardless of timestamp. If some attributes have multiple values th= en 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 not= be performant specially when the dataset grows, I am looking for a solution= which doesn't require one sub select per column. >=20 >> On Sun, Dec 1, 2019 at 5:28 PM Rob Sargent <[email protected]> wrote:= >>=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/d07= 94efcd4278fc5dce6e7339d03a8fd and I want to fetch the latest values for a gi= ven set of attributes so the result set looks like this gist https://gist.gi= thub.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 BMI.= >>> "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 order b= y created_on desc) as r >>> FROM vitals v >>> where (v.code =3D '8480-6' or v.code=3D'8462-4' or v.code=3D'39156-5' or= 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 poss= ible to get? >>>=20 >>> Thanks for your help >>>=20 >>=20 >> I take it the last value by timestamp per code per patient is the one to b= e reported? Or is there a time window? >> Turning rows into columns can be done with sub-selects per derived column= or (usually faster) temporary tables built up in separate selects with each= adding usually one column (but possibly more). >>=20 I agree the sub select per column can easily become slow. Incremental tables= does not, in my experience, suffer the same problem. (One can also repeate= dly update a single table with the predefined structure.) There maybe a way t= o get what you want with multiple CTEs but I suspect that approach would be m= ore akin to multiple sub selects than to incremental tables.=20 =46rom your further description of the problem it will be critical to have a= n index on the code AND time stamp columns of the source table.=20= --Apple-Mail-2DBC7B8E-350C-40E1-AE54-FFB9936948A1 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 4:38 PM, Iaam Onkara <i= [email protected]> 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 wind= ow. It is the latest values for given set of attributes regardless of timest= amp. If some attributes have multiple values then multiple rows can be retur= ned with other attributes having blank values.<div><br></div><div>Creating&n= bsp;one sub select for one column is an obvious approach but will not be per= formant specially when the dataset grows, I am looking for a solution which d= oesn't require one sub select per column.<br></div></div><br><div class=3D"g= mail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, Dec 1, 2019 at 5:2= 8 PM Rob Sargent <<a href=3D"mailto:[email protected]">robjsargent@gm= ail.com</a>> wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"m= argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:= 1ex"><div style=3D"overflow-wrap: break-word;"><br><div><br><blockquote type= =3D"cite"><div>On Dec 1, 2019, at 3:54 PM, Iaam Onkara <<a href=3D"mailto= :[email protected]" target=3D"_blank">[email protected]</a>> wrote:</= div><br><div><div dir=3D"ltr">Hi Friends,<div><br></div><div>I have a table w= ith data like this gist <a href=3D"https://gist.github.com/daya/d0794efcd427= 8fc5dce6e7339d03a8fd" target=3D"_blank">https://gist.github.com/daya/d0794ef= cd4278fc5dce6e7339d03a8fd</a> and I want to fetch the latest values for a gi= ven set of attributes so the result set looks like this gist <a href=3D= "https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6" target=3D"_b= lank">https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6</a></div= ><div><br></div><div>Please 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 Saturation" has only one value and "Pulse" has no v= alue</li></ol><div>In my attempts 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> code, uom, val, created_on, dense_rank() over ( par= tition by code order by created_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'83= 02-2')<br>) <br>SELECT c.display, uom, val, created_on <br>from v_max v inne= r join codes c on v.code=3Dc.code<br>where r =3D 1; <br></font></div><d= iv><font face=3D"monospace"><br></font></div>which gives <a href=3D"https://= gist.github.com/daya/19ca22a837ed9998c117f38ff3cce3f2" target=3D"_blank">thi= s result</a> <div><br></div><div>But the <a href=3D"https://gist.github= .com/daya/0cb7f8682520a1dd4cdda8c0266f77f6" target=3D"_blank">result set tha= t I want</a> I am unable to 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></body></html>= --Apple-Mail-2DBC7B8E-350C-40E1-AE54-FFB9936948A1--