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 1ibdqL-0001HE-7A for pgsql-sql@arkaria.postgresql.org; Mon, 02 Dec 2019 05:01:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ibdqK-0005Na-1W for pgsql-sql@arkaria.postgresql.org; Mon, 02 Dec 2019 05:01:52 +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 1ibdnB-0008H8-Qv for pgsql-sql@lists.postgresql.org; Mon, 02 Dec 2019 04:58:37 +0000 Received: from mail-il1-x142.google.com ([2607:f8b0:4864:20::142]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ibdn5-0004rg-E1 for pgsql-sql@lists.postgresql.org; Mon, 02 Dec 2019 04:58:37 +0000 Received: by mail-il1-x142.google.com with SMTP id a7so32145591ild.6 for ; Sun, 01 Dec 2019 20:58:30 -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=GK1EIJyB/DfGajhJP/DHY0lEV076GYsYIhCpWp3P/q0=; b=pSC12IpaRcCKh5/IZCRoB0ya3XiYHCc8zd9efwFkn4tCgLBrA8K4fgYdXeXCbeGKs9 u2j4ii0O/l5InDzviV6P7XEMdelF32XQILySCP9h3bp6EJPAyRUwYMjcpvsMJv4Xtoau 2NS35cWpFdIig1zBvjGbB1qbWuiudGWZBxKFRCrZPTz5TBAZvY0AP+IkMjlHj/mGj9Qj MWkyVIqLNWdOxtSn+CRuk69JnkmaurgIwADIZ22VtEfgJwNzz+KFK1eZDJKlEfukwcU2 jnT+12Qxh7j9AgWqIcG/hbgDpo2MlnPq3KbBJYuet3FR9A4GOnTgW5THdcx7YE1ocWJk 36EA== 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=GK1EIJyB/DfGajhJP/DHY0lEV076GYsYIhCpWp3P/q0=; b=OIxb5nT823Z99Q8z5N0TxAnIwpg7epb2jb/NnzAJ+4txTE9HJm9KbuFiAP4dJgXcu5 VSb8bcbCd9mMV+NX/77d5SygBInAfYXGgOM6fOPNt4QVQ5pNkV4WbDOhtMTNJU534pxH WMzC6CYmnm6/GZv6TqqNdeljRK/wKFefidh253FQIpaGgoqlVxN3J8GYV3unNnDsM5DH t9POejYh0RO2WodHXYHCW+uekq0T2ZnNqV7qAkDaakkjiYK5ssptqbpAW/pliMxxk3BT ZizReYPXJAyIicI0N2dIHKPiVlWAFDQnNGVolfUCOUC695pxIqnpsC0oDCQAjsT/YwD0 Dvew== X-Gm-Message-State: APjAAAVSpptT8VbiPiJp9lJfnSaxo4jTMFeK32OmJgxlJeEb3VbMX4Ji ED2hxUcz9jibvclnwigBiXzDIALN X-Google-Smtp-Source: APXvYqyBFm9ZpNRWbMtM80m7ILoxs77RaS7oKjLXd/BgAKs6BixHGMZ4OT/v625UeHux2NVuDHxung== X-Received: by 2002:a92:8851:: with SMTP id h78mr10110437ild.308.1575262708969; Sun, 01 Dec 2019 20:58:28 -0800 (PST) Received: from ?IPv6:2601:681:5500:dde0:64fc:68af:c6a1:8333? ([2601:681:5500:dde0:64fc:68af:c6a1:8333]) by smtp.gmail.com with ESMTPSA id t3sm6575096ilf.53.2019.12.01.20.58.27 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 01 Dec 2019 20:58:28 -0800 (PST) Content-Type: multipart/alternative; boundary=Apple-Mail-2DBC7B8E-350C-40E1-AE54-FFB9936948A1 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: Sun, 1 Dec 2019 21:58:27 -0700 Message-Id: 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-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 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 wrote:= >>=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/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


On Dec 1, 2019, at 4:38 PM, Iaam Onkara <i= amonkara@gmail.com> wrote:

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

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.

On Sun, Dec 1, 2019 at 5:2= 8 PM Rob Sargent <robjsargent@gm= ail.com> wrote:


On Dec 1, 2019, at 3:54 PM, Iaam Onkara <iamonkara@gmail.com> wrote:
Hi Friends,

I have a table w= ith data like this gist https://gist.github.com/daya/d0794ef= cd4278fc5dce6e7339d03a8fd 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.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6

Please note in the desired result set 
<= ol>
  • 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 v= alue
    • In my attempts and with some help I have  this query=  

    with v_max as=
    (SELECT
    code, uom, val, created_on, dense_rank() over ( par= tition 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' or v.code=3D'83= 02-2')
    )
    SELECT c.display, uom, val, created_on
    from v_max v inne= r join codes c on v.code=3Dc.code
    where r =3D 1; 

    which gives thi= s result 

    But the result set tha= t I want 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 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-2DBC7B8E-350C-40E1-AE54-FFB9936948A1--