public inbox for [email protected]  

help / color / mirror / Atom feed

From: Rob Foehl <[email protected]>
To: [email protected]
Subject: Finding "most recent" using daterange
Date: Wed, 22 May 2024 03:58:32 -0400
Message-ID: <[email protected]> (raw)

Coming back to PostgreSQL after a (decades-)long absence...  If I have
something like:

CREATE TABLE example (
	id integer NOT NULL,
	value text NOT NULL,
	dates daterange NOT NULL
);

INSERT INTO example VALUES
	(1, 'a', '[2010-01-01,2020-01-01)'),
	(1, 'b', '[2010-01-01,)'),
	(1, 'c', '[,2021-01-01)'),
	(2, 'd', '[2010-01-01,2021-01-01)'),
	(2, 'e', '[2015-01-01,2020-01-01)'),
	(3, 'f', '[2014-01-01,2016-01-01)'),
	(3, 'g', '[2013-01-01,)'),
	(3, 'h', '[2012-01-01,)'),
	(3, 'i', '[2013-01-01,2017-01-01)'),
	(4, 'j', '[2010-01-01,2015-01-01)');

and I want to find the "most recent" value out of each group, meaning
that having the greatest upper bound followed by the greatest lower
bound, what I've managed to come up with thus far is:

WITH intermediate AS (
	SELECT e.id, e.value, e.dates
	FROM example AS e
	JOIN (
		SELECT id, max(coalesce(upper(dates), 'infinity')) AS max_date
		FROM example GROUP BY id
	) AS max_upper ON e.id = max_upper.id
		AND coalesce(upper(dates), 'infinity') = max_upper.max_date
)
SELECT i.id, i.value, i.dates
FROM intermediate AS i
JOIN (
	SELECT id, max(coalesce(lower(dates), '-infinity')) AS max_date
	FROM intermediate GROUP BY id
) AS max_lower ON i.id = max_lower.id
	AND coalesce(lower(dates), '-infinity') = max_lower.max_date;

which produces the desired result for this minimal example:

 id | value |          dates
----+-------+-------------------------
  1 | b     | [2010-01-01,)
  2 | d     | [2010-01-01,2021-01-01)
  3 | g     | [2013-01-01,)
  4 | j     | [2010-01-01,2015-01-01)

I pretty quickly discovered that there's no max(daterange) -- although
it isn't obvious what that would do, anyway -- and the "intermediate"
CTE is what followed.  Is there a better way?

(Note that this doesn't try to handle duplicate ranges -- I haven't
decided whether that'll be necessary in the real case.  Assume it'll
have something beyond daterange NOT NULL and/or some _agg() magic,
eventually.)

-Rob

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Finding "most recent" using daterange
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox