From: | Iaam Onkara <iamonkara(at)gmail(dot)com> |
---|---|
To: | Rob Sargent <robjsargent(at)gmail(dot)com> |
Cc: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Re: Solving my query needs with Rank and may be CrossTab |
Date: | 2019-12-02 06:09:03 |
Message-ID: | CAMz9UCatx408iKUGt5RThWxu34gJU_Y8ge4pUBuFoGXZxWxLBg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Yes indexes on Code and Timestamp column may also be needed even though
Patient_ID column will be indexed.
By incremental tables do you mean tables with Auto Increment primary key
for ID ?
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 <robjsargent(at)gmail(dot)com> wrote:
>
>
> On Dec 1, 2019, at 4:38 PM, Iaam Onkara <iamonkara(at)gmail(dot)com> wrote:
>
>
> @Rob: There is no time window. It is the latest values for given set of
> attributes regardless of timestamp. If some attributes have multiple values
> 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 <robjsargent(at)gmail(dot)com> wrote:
>
>>
>>
>> On Dec 1, 2019, at 3:54 PM, Iaam Onkara <iamonkara(at)gmail(dot)com> 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 result 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 = '8480-6' or v.code='8462-4' or v.code='39156-5' or
>> v.code='8302-2')
>> )
>> SELECT c.display, uom, val, created_on
>> from v_max v inner join codes c on v.code=c.code
>> where r = 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 to
>> 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 selects 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 also
> 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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2019-12-02 13:42:28 | Re: Solving my query needs with Rank and may be CrossTab |
Previous Message | Rob Sargent | 2019-12-02 04:58:27 | Re: Solving my query needs with Rank and may be CrossTab |