Date: Wed, 25 Aug 2021 16:42:56 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Roger Mason <rmason@mun.ca>,
	"David G. Johnston" <david.g.johnston@gmail.com>
Cc: pgsql-novice <pgsql-novice@postgresql.org>
Subject: Re: select from grouped data
Message-ID: <20210825204256.GX17906@tamriel.snowman.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="UQXM1TyQrj1TTJf+"
Content-Disposition: inline
In-Reply-To: 
 <CAKFQuwavG_JG63EHyWxY_bM3s08Av1vu=XTzBTywLafUO0-htQ@mail.gmail.com>
 <y65sfyxdbq4.fsf@mun.ca>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20210825204256.GX17906%40tamriel.snowman.net>
Precedence: bulk


--UQXM1TyQrj1TTJf+
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Roger Mason (rmason@mun.ca) wrote:
> I have written this function to process some multiline text data into a
> table with both text & numeric columns:
>=20
> CREATE OR REPLACE FUNCTION get_final_energy ()
>   RETURNS TABLE (
>     jid text,
>     "timestamp" text,
>     scf integer,
>     energy double precision
>   )
>   AS $$
>   WITH a AS (
>     SELECT
>       jid,
>       regexp_split_to_table(totenergy_out, '\n') AS teo
>     FROM
>       results
> ),
> b AS (
>   SELECT
>     results.jid,
>     results. "timestamp",
>     cast(
>       CASE WHEN split_part(a.teo, ' ', 2) =3D '' THEN
>         '0'
>       ELSE
>         split_part(a.teo, ' ', 2)
>       END AS integer) AS scf,
>     cast(
>       CASE WHEN split_part(a.teo, ' ', 3) =3D '' THEN
>         '0.0'
>       ELSE
>         split_part(a.teo, ' ', 3)
>       END AS double precision) AS energy
>   FROM
>     results,
>     a
>   WHERE
>     results.jid =3D a.jid
>   GROUP BY
>     results.jid,
>     results. "timestamp",
>     a.teo
>   --HAVING
>     -- scf =3D max(scf)
>     ORDER BY
>          timestamp ASC,
>          scf DESC
> )
> SELECT
>   *
> FROM
>   b;
>=20
> $$
> LANGUAGE sql;
>=20
> The output looks like:
>=20
>    jid     |    timestamp    | scf |     energy    =20
> ------------+-----------------+-----+----------------
>  1250_1     | 20210805-114634 |  18 | -1316.43700819
>  1250_1     | 20210805-114634 |  17 | -1316.43700825
>  1250_1     | 20210805-114634 |  16 |  -1316.4370097
>  1250_1     | 20210805-114634 |  15 | -1316.43700991
>  1250_1     | 20210805-114634 |  14 | -1316.43699775
>  1250_1     | 20210805-114634 |  13 | -1316.43699117
>  1250_1     | 20210805-114634 |  12 | -1316.43750771
>  1250_1     | 20210805-114634 |  11 | -1316.43805358
>  1250_1     | 20210805-114634 |  10 | -1316.43857192
>  1250_1     | 20210805-114634 |   9 | -1316.43070942
>  1251_1     | 20210806-062539 |  18 | -1316.43700819
>  1251_1     | 20210806-062539 |  17 | -1316.43700826
> ....
>=20
> What I want is to get (for each group) the energy corresponding to the
> maximum value of scf.

* David G. Johnston (david.g.johnston@gmail.com) wrote:
> >
> >
> > The output looks like:
> >
> >    jid     |    timestamp    | scf |     energy
> > ------------+-----------------+-----+----------------
> >  1250_1     | 20210805-114634 |  18 | -1316.43700819
> >  1250_1     | 20210805-114634 |  17 | -1316.43700825
> >  1250_1     | 20210805-114634 |  16 |  -1316.4370097
> >  1250_1     | 20210805-114634 |  15 | -1316.43700991
> >  1250_1     | 20210805-114634 |  14 | -1316.43699775
> >  1250_1     | 20210805-114634 |  13 | -1316.43699117
> >  1250_1     | 20210805-114634 |  12 | -1316.43750771
> >  1250_1     | 20210805-114634 |  11 | -1316.43805358
> >  1250_1     | 20210805-114634 |  10 | -1316.43857192
> >  1250_1     | 20210805-114634 |   9 | -1316.43070942
> >  1251_1     | 20210806-062539 |  18 | -1316.43700819
> >  1251_1     | 20210806-062539 |  17 | -1316.43700826
> > ....
> >
> > What I want is to get (for each group) the energy corresponding to the
> > maximum value of scf.
> >
> >
> SELECT DISTINCT ON (jid) jid, timestamp, scf, energy [...] ORDER BY jid,
> scf DESC

While this works, it's generally better to use a LATERAL join as that's
part of the SQL standard while DISTINCT ON isn't.  Using a LATERAL join
also would allow you to have multiple rows (top-N) if you wanted.  You'd
do that using:

WITH jids AS (SELECT jid FROM results GROUP BY jid)
SELECT jids.jid, t.ts, t.scf, t.energy
FROM jids CROSS JOIN LATERAL
 (SELECT ts, scf, energy FROM results
  WHERE results.jid =3D jids.jid
  ORDER BY scf DESC
  LIMIT 1) AS t
;

A couple of notes on this: don't name a column "timestamp" and when it's
a timestamp, use the 'timestamptz' data type, not text.  Your jids sure
look like they should just be numbers instead of text too.  If you
already have a distinct set of jids somewhere (like in another table),
you could use that as the source table instead of the CTE that I'm using
above.

Thanks,

Stephen

--UQXM1TyQrj1TTJf+
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJhJqtPAAoJEO1sijiDR2RVbNsP/iPfG1gOpS8j4lxzJ+qQF162
aUTD/4oRYBYjkok2ZMzFx/IB/grWiro0vS8AF1RmAfjaCdWDSlXkJ9zLsCbgX6DE
Rgfi1xFCzzBykOw6lei3iWCUDfUdE58h4cxg1T5DBTmbXRZv8lWtk9JM+647Rf7L
c3HQ1PEq4lrxbqR+iMq8SpLvJCXH2VraYdAVjA5Z/lZGsUnUxLFay1CPgwz3TtEM
WNS+FYwHTc5baamhp7wzAGltaS6r1QNELnI1Nv84dEpuqDWRBGqRvH8lCEKne6k0
+Fm3HojaZFWgwQ1Lc4i1H8Drkmipsi9y5QIrP12pe1Za7ESrqy/WSGElbLUTvywf
Gie43jcdh8avvBmQF3D0DSfWzmywPWeF2ADAzBPR7nSMbFWoOKRkopupmJ1yC+Jc
iqyjBqt7wkXKhsN07Q0PLdeeLUuEllm3Z4zFovHU71D3mexG1YSOfntFo+eyujkO
sDTJf3x1AXUhkORHOhWwrhwTE+RMqP0zuwNh2VF7xNec7byBLb8VZD7BKJ0Hlb9i
95r1axMVgxpurSVr4NEspaP61M+0ot0Fd3JvXWDiaBNqHcibQQb9sU3fI2wCYPTx
C0zZtGf55NTAwB9oKdiy9CAypBGMpCAk3Dc4sKkv/RekkEdp5tHUXm4jv8JZNJSO
x3OMPNe+CFvAEQogywT1
=xkTo
-----END PGP SIGNATURE-----

--UQXM1TyQrj1TTJf+--