Postgresql

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

  • March 25, 2021

給定兩個表:

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值也僅方便與每一個非空值會增加,從而形成將共享相同的數值組。外SELECTmin()(或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

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