Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iblyN-0006md-5x for pgsql-sql@arkaria.postgresql.org; Mon, 02 Dec 2019 13:42:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iblyK-0001Mr-Ae for pgsql-sql@arkaria.postgresql.org; Mon, 02 Dec 2019 13:42:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iblyK-0001Mk-0I for pgsql-sql@lists.postgresql.org; Mon, 02 Dec 2019 13:42:40 +0000 Received: from mail-io1-xd42.google.com ([2607:f8b0:4864:20::d42]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iblyC-0000p2-H4 for pgsql-sql@lists.postgresql.org; Mon, 02 Dec 2019 13:42:39 +0000 Received: by mail-io1-xd42.google.com with SMTP id i11so40458393iol.13 for ; Mon, 02 Dec 2019 05:42:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=content-transfer-encoding:from:mime-version:subject:date:message-id :references:cc:in-reply-to:to; bh=2WsMaP6jGIIVqFgDN5CfUTu/cS3+wpYtUAUaxLeZIko=; b=ZNLKwDWse90diWEzs0E9I522/SCa9FWWybugFH6Emp+4uji5e2VxSaSWiAk9Y9AhoW nl9Mrf7XF9FH6t5G8jqXtQfdDHyYLLz6ST5yf3MYagQKtSD1lX5Qc3ZW8MUiaSy9jKnL yA8rEFpHAwq7OQH6vTLIKXePHKZal7n75igSsutf4jwjW2lfVWOiK+LfobiI/9/sy5V7 i+9Dssqe/LbQyS9CKlWtx8aWKWo+MefKYtA/kqMiyAiIKI+xS4Llu44r0/3qaDsGGIbh CWjf9Qolk/2zcxmqdU1+K+j1IefCKEne0doBukm2J8fY/QGCwpfguOgINLMcRQu1jjuD IE3g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:content-transfer-encoding:from:mime-version :subject:date:message-id:references:cc:in-reply-to:to; bh=2WsMaP6jGIIVqFgDN5CfUTu/cS3+wpYtUAUaxLeZIko=; b=Y78S5Ba2NqnC5nsAN4hxaFjga9RMEF37kng9xM64o6vlqQTnlNd9ZD3ThY0HG8tjj9 tDEUEKrwgE+LjpHoXYpdcQbK2J7qMhqhAW8aaN6EWtzAGhIHehfJdcQNJlj9EuXlc2xI CgPtJPK0/hBN/vrs0xQjVETJNWTuFilUXb7TkYQmrShFOdpjaPw+sqLJDnee1zQvu44N Su/6Bf+FJZbrU7oXhdQ+icdCgDsfcAMGeWzb/1ztFxAYXb2Kp+YwOt+FVbyZZeDox7KT Nj+/N+4k/+coQ2jqyIwxj5+eXPHYt5/AH0rm+JrCVxqdi4Ivd1ETg/oG2UCKZKwDHwps 5Lug== X-Gm-Message-State: APjAAAUKrVd+ZEFifdeVk0YFp65pMf6TItp7YYdoMIvrXL8Z+/n6q7oq dPw9NEFV3Qb3VoqYU7Lmgw/yyk+i X-Google-Smtp-Source: APXvYqwLyNfj2OZRlpVAnv23UScfcjHoHVpgv5Z2Qq2Ixau+s6+flJBY1/xs4UjLtsacJCuV9vjWrA== X-Received: by 2002:a5d:8343:: with SMTP id q3mr26465693ior.7.1575294149852; Mon, 02 Dec 2019 05:42:29 -0800 (PST) Received: from ?IPv6:2601:681:5500:dde0:e8af:4d42:ecd:272? ([2601:681:5500:dde0:e8af:4d42:ecd:272]) by smtp.gmail.com with ESMTPSA id c20sm2901650iot.69.2019.12.02.05.42.28 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 02 Dec 2019 05:42:29 -0800 (PST) Content-Type: multipart/alternative; boundary=Apple-Mail-82EA7EEA-102E-4180-ADAE-784AEDEB90B6 Content-Transfer-Encoding: 7bit From: Rob Sargent Mime-Version: 1.0 (1.0) Subject: Re: Solving my query needs with Rank and may be CrossTab Date: Mon, 2 Dec 2019 06:42:28 -0700 Message-Id: <20558BC2-9439-4449-A9A4-542141811003@gmail.com> References: Cc: pgsql-sql@lists.postgresql.org In-Reply-To: To: Iaam Onkara X-Mailer: iPhone Mail (17A878) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --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 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 wrote= : >>=20 >>=20 >>>> On Dec 1, 2019, at 4:38 PM, Iaam Onkara 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 wrot= e: >>>>=20 >>>>=20 >>>>> On Dec 1, 2019, at 3:54 PM, Iaam Onkara 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


On Dec 1, 2019, at 11:09 PM, Iaam Onkara <= iamonkara@gmail.com> wrote:

=EF=BB=BF
Yes indexes on Code and Ti= mestamp column may also be needed even though Patient_ID column will be inde= xed.

I believe you will want a compou= nd index covering both columns
By incremental tables do you mean tables with Auto In= crement primary key for ID 
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.)

What I am having tough time figuring out is how to tra= nsform the result into this even after using multiple CTEs

On Sun= , Dec 1, 2019 at 10:58 PM Rob Sargent <robjsargent@gmail.com> wrote:


On Dec 1, 2019, at 4:38 PM, Iaam On= kara <iamonkara@= gmail.com> wrote:

=EF=BB=BF
@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.

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.

On Sun, Dec 1, 2019 at 5:28 PM R= ob Sargent <ro= bjsargent@gmail.com> wrote:


On Dec 1, 2= 019, at 3:54 PM, Iaam Onkara <iamonkara@gmail.com> wrote:

Hi Friends,

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= and I want to fetch the latest values for a given set of attributes so t= he result set looks like this gist https://gist.github.= com/daya/0cb7f8682520a1dd4cdda8c0266f77f6

Pleas= e 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 S= aturation" has only one value and "Pulse" has no value
In my a= ttempts and with some help I have  this query 
with v_max as
(SELECT
cod= e, uom, val, created_on, dense_rank() over ( partition by code order by crea= ted_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')
)
SELECT c.displ= ay, uom, val, created_on
from v_max v inner join codes c on v.code=3Dc.c= ode
where r =3D 1; 
which gives this result 

But the result set that I want I am unable t= o get. Or if is it even possible to get?

Thanks for= your help


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>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).

<= /blockquote>

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. 
=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. 
= --Apple-Mail-82EA7EEA-102E-4180-ADAE-784AEDEB90B6--