Postgresql
將兩個事件表合併到一個時間線中
給定兩個表:
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 <>小提琴在這裡,展示兩者。
相關答案解釋
#variable_conflict use_column
: