Re: Solving my query needs with Rank and may be CrossTab
Iaam Onkara <[email protected]> Mon, 2 Dec 2019 10:55:27 -0600
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <CAMz9UCZvcmYE-5qZH8F=KWOM6-sxVA6F6A3zQUk3uY05p2XwGg@mail.gmail.com> |
--000000000000efc4130598bb7238 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable @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 <[email protected]> wrote: > > > On Dec 1, 2019, at 11:09 PM, Iaam Onkara <[email protected]> wrote: > > =EF=BB=BF > Yes indexes on Code and Timestamp column may also be needed even though > Patient_ID column will be indexed. > > I believe you will want a compound index covering both columns > > By incremental tables do you mean tables with Auto Increment primary key > for ID > > 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 repeatedly updating a single column.) > > > What I am having tough time figuring out is how to transform the result > into this <https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6> > even after using multiple CTEs > > On Sun, Dec 1, 2019 at 10:58 PM Rob Sargent <[email protected]> wrote= : > >> >> >> On Dec 1, 2019, at 4:38 PM, Iaam Onkara <[email protected]> wrote: >> >> =EF=BB=BF >> @Rob: There is no time window. It is the latest values for given set of >> attributes regardless of timestamp. If some attributes have multiple val= ues >> then multiple rows can be returned with other attributes having blank >> values. >> >> 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. >> >> On Sun, Dec 1, 2019 at 5:28 PM Rob Sargent <[email protected]> wrote= : >> >>> >>> >>> On Dec 1, 2019, at 3:54 PM, Iaam Onkara <[email protected]> wrote: >>> >>> Hi Friends, >>> >>> I have a table with data like this gist >>> https://gist.github.com/daya/d0794efcd4278fc5dce6e7339d03a8fd and I >>> want to fetch the latest values for a given set of attributes so the re= sult >>> set looks like this gist >>> https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6 >>> >>> Please note in the desired result set >>> >>> 1. There is an assumed mapping of Code to display i.e. code 39156-5 >>> is BMI. >>> 2. "Oxygen Saturation" has only one value and "Pulse" has no value >>> >>> In my attempts and with some help I have this query >>> >>> with v_max as >>> (SELECT >>> code, uom, val, created_on, dense_rank() over ( partition by code order >>> by 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' o= r >>> v.code=3D'8302-2') >>> ) >>> SELECT c.display, uom, val, created_on >>> from v_max v inner join codes c on v.code=3Dc.code >>> where r =3D 1; >>> >>> which gives this result >>> <https://gist.github.com/daya/19ca22a837ed9998c117f38ff3cce3f2> >>> >>> But the result set that I want >>> <https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6> I am >>> unable to get. Or if is it even possible to get? >>> >>> Thanks for your help >>> >>> >>> 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 >>> column or (usually faster) temporary tables built up in separate select= s >>> with each adding usually one column (but possibly more). >>> >>> >> I agree the sub select per column can easily become slow. Incremental >> tables does not, in my experience, suffer the same problem. (One can al= so >> repeatedly update a single table with the predefined 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 incremental >> tables. >> From your further description of the problem it will be critical to have >> an index on the code AND time stamp columns of the source table. >> > --000000000000efc4130598bb7238 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">@Rob. What your referring to sounds like Materialized view= s, isn't it? An example query would be helpful in understand your recom= mendation/approach better.</div><br><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]">[email protected]</a>> wrote:= <br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8= ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"aut= o"><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 <<a href=3D"mailto:iamonkara@= gmail.com" target=3D"_blank">[email protected]</a>> wrote:<br><br></bl= ockquote></div><blockquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF<div dir= =3D"ltr">Yes indexes on Code and Timestamp column may also be needed even t= hough Patient_ID column will be indexed.<div><br></div></div></div></blockq= uote>I believe you will want a compound index covering both columns<br><blo= ckquote type=3D"cite"><div dir=3D"ltr"><div dir=3D"ltr"><div>By incremental= tables do you mean tables with Auto Increment primary key for ID=C2=A0</di= v></div></div></blockquote>No. I mean a series of intermediate tables each = with one more report column. These can be temporary and unlogged but the wi= ll need an index on patient. (Again, you have the option of predefining the= full report table and repeatedly updating a single 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 transform the result into=C2= =A0<a href=3D"https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6= " target=3D"_blank">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, De= c 1, 2019 at 10:58 PM Rob Sargent <<a href=3D"mailto:[email protected]= om" target=3D"_blank">[email protected]</a>> wrote:<br></div><blockq= uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p= x solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto"><div dir=3D"lt= r"><br></div><div dir=3D"ltr"><br><blockquote type=3D"cite">On Dec 1, 2019,= at 4:38 PM, Iaam Onkara <<a href=3D"mailto:[email protected]" target= =3D"_blank">[email protected]</a>> wrote:<br><br></blockquote></div><b= lockquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF<div dir=3D"ltr">@Rob: Th= ere is no time window. It is the latest values for given set of attributes = regardless of timestamp. If some attributes have multiple values then multi= ple rows can be returned with other attributes having blank values.<div><br= ></div><div>Creating=C2=A0one sub select for one column is an obvious appro= ach but will not be performant specially when the dataset grows, I am looki= ng 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_a= ttr">On Sun, Dec 1, 2019 at 5:28 PM Rob Sargent <<a href=3D"mailto:robjs= [email protected]" target=3D"_blank">[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);padding-left:1ex"><div><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]= m</a>> wrote:</div><br><div><div dir=3D"ltr">Hi Friends,<div><br></div><= div>I have a table with data like this gist <a href=3D"https://gist.github.= com/daya/d0794efcd4278fc5dce6e7339d03a8fd" target=3D"_blank">https://gist.g= ithub.com/daya/d0794efcd4278fc5dce6e7339d03a8fd</a> and I want to fetch the= latest values for a given set of attributes so the result set looks like t= his gist=C2=A0<a href=3D"https://gist.github.com/daya/0cb7f8682520a1dd4cdda= 8c0266f77f6" target=3D"_blank">https://gist.github.com/daya/0cb7f8682520a1d= d4cdda8c0266f77f6</a></div><div><br></div><div>Please note in the desired r= esult set=C2=A0</div><div><ol><li>There is an assumed mapping of Code to di= splay i.e. code=C2=A039156-5 is BMI.</li><li>"Oxygen Saturation" = has only one value and "Pulse" has no value</li></ol><div>In my a= ttempts and with some help I have=C2=A0 this query=C2=A0</div></div><div><b= r></div><div><font face=3D"monospace">with v_max as <br>(SELECT <br> code, = uom, val, created_on, dense_rank() over ( partition by code order by create= d_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= inner join codes c on v.code=3Dc.code<br>where r =3D 1;=C2=A0<br></font></= div><div><font face=3D"monospace"><br></font></div>which gives <a href=3D"h= ttps://gist.github.com/daya/19ca22a837ed9998c117f38ff3cce3f2" target=3D"_bl= ank">this result</a>=C2=A0<div><br></div><div>But the <a href=3D"https://gi= st.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6" target=3D"_blank">resu= lt set that I want</a>=C2=A0I am unable to get. Or if is it even possible t= o 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= code per patient is the one to be reported? Or is there a time window?</di= v><div>Turning rows into columns can be done with sub-selects per derived c= olumn or (usually faster) temporary tables built up in separate selects wit= h each 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 be= come slow. Incremental tables does not, in my experience, suffer the same p= roblem. =C2=A0(One can also repeatedly update a single table with the prede= fined 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 tha= n to incremental tables.=C2=A0</div><div>From your further description of t= he problem it will be critical to have an index on the code AND time stamp = columns of the source table.=C2=A0</div></div></blockquote></div> </div></blockquote></div></blockquote></div> --000000000000efc4130598bb7238--