12

Given two tables:

CREATE TABLE foo (ts timestamp, foo text);
CREATE TABLE bar (ts timestamp, bar text);

I wish to write a query that returns values for ts, foo, and bar that represents a unified view of the most recent values. In other words, if foo contained:

ts | foo
--------
1  | A
7  | B

and bar contained:

ts | bar
--------
3  | C
5  | D
9  | E

I want a query that returns:

ts | foo | bar
--------------
1  | A   | null
3  | A   | C
5  | A   | D
7  | B   | D
9  | B   | E

If both tables have an event at the same time, the order does not matter.

I have been able to create the structure needed using union all and dummy values:

SELECT ts, foo, null as bar FROM foo
UNION ALL SELECT ts, null as foo, bar FROM bar

which will give me a linear timeline of new values, but I'm not quite able to work out how to populate the null values based on the previous rows. I've tried the lag window function, but AFAICT it will only look at the previous row, not recursively backward. I've looked at recursive CTEs, but I'm not quite sure how to set up the start and termination conditions.

  • Are values in foo and bar strictly ascending over time or is the test case misleading in this respect? – Erwin Brandstetter Jul 2 '15 at 22:30
  • 2
    To save anyone else the hassle, sqlfiddle.com/#!15/511414 – Craig Ringer Jul 2 '15 at 22:35
  • 1
    Rather than changing the nature of the question after an answer has been given, please ask a new question. You can always link to this one for reference. (You can even provide your own answer if you have one.) The original version should be interesting to the general public. Let's not pack to much in a single question. – Erwin Brandstetter Jul 3 '15 at 18:20
  • Sorry for the overload. I've removed the follow up and added it as a new question. – Christopher Currie Jul 9 '15 at 21:57
7

Use a FULL [OUTER] JOIN, combined with two rounds of window functions:

SELECT ts
     , min(foo) OVER (PARTITION BY foo_grp) AS foo
     , min(bar) OVER (PARTITION BY bar_grp) AS bar
FROM (
   SELECT ts, f.foo, b.bar
        , count(f.foo) OVER (ORDER BY ts) AS foo_grp
        , count(b.bar) OVER (ORDER BY ts) AS bar_grp
   FROM   foo f
   FULL   JOIN bar b USING (ts)
   ) sub;

Since count() does not count NULL values it conveniently only increases with every non-null value, thereby forming groups that will share the same value. In the outer SELECT, min() (or max()) likewise ignores NULL values, thereby picking the one non-null value per group. Voilá.

Related FULL JOIN case:

It's one of those cases where a procedural solution might just be faster, since it can get the job done in a single scan. Like this plpgsql function:

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text) AS
$func$
#variable_conflict use_column
DECLARE
   last_foo text;
   last_bar text;
BEGIN
   FOR ts, foo, bar IN
      SELECT ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
      ORDER  BY 1
   LOOP
      IF foo IS NULL THEN foo := last_foo;
      ELSE                last_foo := foo;
      END IF;

      IF bar IS NULL THEN bar := last_bar;
      ELSE                last_bar := bar;
      END IF;

      RETURN NEXT;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM f_merge_foobar();

db<>fiddle here, demonstrating both.

Related answer explaining the #variable_conflict use_column:

  • Interesting problem isn't it. I think an efficient solution probably requires creation of a coalesce-like window function. – Craig Ringer Jul 2 '15 at 22:39
  • @CraigRinger: Indeed. I find myself wishing, wondering, thinking .. that this somehow should be possible without subquery, but I failed to find a way. It's one of those cases where a plpgsql function will be faster since it can scan each table once. – Erwin Brandstetter Jul 2 '15 at 22:41
  • @Christopher: I would be interested in the performance of each variant in your setup. EXPLAIN ANALYZE, best of 5 ...? – Erwin Brandstetter Jul 2 '15 at 23:19
  • 2
    Pity that Postgres has not implemented yet IGNORE NULLS (as Oracle has: sqlfiddle.com/#!4/fab35/1 ). – ypercubeᵀᴹ Jul 3 '15 at 20:03
  • 1
    @ypercube: Yes, Oracle simple does not store NULL values at all and consequently can't tell the difference between '' and NULL. – Erwin Brandstetter Jul 3 '15 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.