clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591684 fiddles created (45708 in the last week).

CREATE TABLE foo (ts int, foo text); INSERT INTO foo (ts, foo) VALUES (1, 'A'), (7, 'B'); CREATE TABLE bar (ts int, bar text); INSERT INTO bar (ts, bar) VALUES (3, 'C'), (5, 'D'), (9, 'E');
2 rows affected
3 rows affected
 hidden batch(es)


CREATE OR REPLACE FUNCTION f_merge_foobar() RETURNS TABLE(ts int, foo text, bar text) LANGUAGE plpgsql 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$;
 hidden batch(es)


-- Function may be faster: SELECT * FROM f_merge_foobar();
ts foo bar
1 A
3 A C
5 A D
7 B D
9 B E
 hidden batch(es)


-- Plain query 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 ORDER BY 1;
ts foo bar
1 A
3 A C
5 A D
7 B D
9 B E
 hidden batch(es)