異なるstampを持つデータの同期と前値保持について
以下のようなデータを結合し、データない部分は前値保持したいと考えています。
テーブル:t1
1 a 2 b 3 c 5 e 6 f
テーブル:t2
1 aa 3 cc 4 dd 7 gg
上記をfull outer join したのは以下のようになります。
1 a 1 aa 2 b 3 c 3 cc 4 dd 5 e 6 f 7 gg
以下のスクリプトで結合を実施しています。
drop table if exists t1; drop table if exists t2; create temp table t1(stamp integer, val text); create temp table t2(stamp integer, val text); insert into t1 values(1,'a'); insert into t1 values(2,'b'); insert into t1 values(3,'c'); insert into t1 values(5,'e'); insert into t1 values(6,'f'); insert into t2 values(1,'aa'); insert into t2 values(3,'cc'); insert into t2 values(4,'dd'); insert into t2 values(7,'gg'); with full_outer_joined as( SELECT coalesce(t1.stamp,t2.stamp)as stamp, t1.val as val1, t2.val as val2 FROM t1 FULL JOIN t2 ON t1.stamp = t2.stamp ) , carryovered as( select stamp,first_value(val1) over w1 as val1, first_value(val2) over w2 as val2 from ( select * , sum(case when val1 is null then 0 else 1 end) over (order by full_outer_joined.stamp asc ) as value_partition1 , sum(case when val2 is null then 0 else 1 end) over (order by full_outer_joined.stamp asc ) as value_partition2 from full_outer_joined )as tmp window w1 as (partition by value_partition1 order by value_partition1), w2 as (partition by value_partition2 order by value_partition2) ) select * from carryovered;
その結果以下の出力となります。
1 a aa 2 b aa 3 c cc 4 c dd 5 e dd 6 f dd 7 f gg
この結果は期待通りの結果なのですが、full outer joinが出力する結果の順番が保証されてないので、毎回期待通りになるのか、たまたま期待通りになるのかわからず困っています。
なお、postgrsqlのバージョンは10.7です。
追記です。
full outer join の結果が保証されず例えば以下となってしまったとき。
1 a 1 aa 2 b 3 c 3 cc 4 dd 5 e 7 gg 6 f
前値補完した結果は以下となります。
1 a aa 2 b aa 3 c cc 4 c dd 5 e dd 7 f gg 6 f gg
この結果をソートしたとしても、6の2つ目の値はggとなり、期待するddとは異なる値となってしまいます。