將兩個事件表合併到一個時間線中

給定兩個表：

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

我希望編寫一個返回, 和的值的查詢ts，它表示最新值的統一視圖。換句話說，如果包含：foo``bar``foo

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

並bar包含：

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

我想要一個返回的查詢：

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

如果兩個表同時有一個事件，則順序無關緊要。

我已經能夠使用 union all 和虛擬值創建所需的結構：

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

這將為我提供一個新值的線性時間線，但我不太能夠弄清楚如何根據前幾行填充空值。我已經嘗試過lag視窗函式，但是 AFAICT 它只會查看前一行，而不是遞歸向後。我看過遞歸 CTE，但我不太確定如何設置開始和終止條件。

使用A FULL [OUTER] JOIN，結合兩輪視窗功能：

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;

由於count()不計算NULL值也僅方便與每一個非空值會增加，從而形成將共享相同的數值組。外SELECT，min()（或max()）同樣忽略空值，從而拾取每組一個非空值。瞧。

相關FULL JOIN案例：

• 添加了對同一個表的多個列的條件計數

這是一個程序解決方案可能更快的情況之一，因為它可以在單次掃描中完成作業。像這個plpgsql函式一樣：

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$;

稱呼：

SELECT * FROM f_merge_foobar();

DB <>小提琴在這裡，展示兩者。

舊的sqlfiddle。

相關答案解釋#variable_conflict use_column：

• 函式參數與使用 USING 子句的 JOIN 結果之間的命名衝突

引用自：https://dba.stackexchange.com/questions/105736