54252


以下のようなデータを結合し、データない部分は前値保持したいと考えています。

テーブル:t1

[code]

    1   a
    2   b
    3   c
    5   e
    6   f
    
[/code]

テーブル：t2

[code]

    1   aa
    3   cc
    4   dd
    7   gg
    
[/code]

上記をfull outer join したのは以下のようになります。

[code]

    1   a   1   aa
    2   b       
    3   c   3   cc
            4   dd
    5   e       
    6   f       
            7   gg
    
[/code]

以下のスクリプトで結合を実施しています。

[code]

    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;
    
[/code]

その結果以下の出力となります。

[code]

    1   a   aa
    2   b   aa
    3   c   cc
    4   c   dd
    5   e   dd
    6   f   dd
    7   f   gg
    
[/code]

この結果は期待通りの結果なのですが、full outer
joinが出力する結果の順番が保証されてないので、毎回期待通りになるのか、たまたま期待通りになるのかわからず困っています。  
なお、postgrsqlのバージョンは10.7です。

* * *

追記です。  
full outer join の結果が保証されず例えば以下となってしまったとき。

[code]

    1   a   1   aa
    2   b       
    3   c   3   cc
            4   dd
    5   e       
            7   gg
    6   f       
    
[/code]

前値補完した結果は以下となります。

[code]

    1   a   aa
    2   b   aa
    3   c   cc
    4   c   dd
    5   e   dd
    7   f   gg
    6   f   gg
    
[/code]

この結果をソートしたとしても、6の２つ目の値はggとなり、期待するddとは異なる値となってしまいます。


CC BY-SA 4.0
2019-04-17T01:54:47.287
54228
2019-04-17T08:40:18.253
2019-04-17T02:32:39.850
704
704
question
postgresql
異なるstampを持つデータの同期と前値保持について


`WINDOW`だの、`SUM
OVER`だの、私には使いこなせていないあれこれのSQLの機能を利用されまくっているので、(しかも手元にPostgreSQLの処理系がないので)解析に手間取ってしまいましたが、結論から言うと

**`full_outer_joined`の順序に依存するようなSQLにはなっていないから大丈夫**

と言えます。

まず、`carryovered`の内側のクエリーの結果を考えてみます。`SUM OVER(ORDER
BY)`なんてのを使ってますが、この結果は`OVER`内に示した`ORDER
BY`の順序で決まりますので、`full_outer_joined`の順序には依存しません。

`full_outer_joined`の結果があなたのご質問の最後にあるような:

[code]

    stamp val1 val2
    ----- ---- ----
    1     a    aa
    2     b       
    3     c    cc
    4          dd
    5     e       
    7          gg
    6     f       
    
[/code]

だったとしても、`value_partition1`, `value_partition2`の算出は`OVER(ORDER
BY)`で示した順序で行われるので、結果は次のようになります。

[code]

    stamp val1 val2 value_partition1 value_partition2
    ----- ---- ---- ---------------- ----------------
    1     a    aa   1                1
    2     b         2                1
    3     c    cc   3                2
    4          dd   3                3
    5     e         4                3
    7          gg   5                4
    6     f         5                3
    
[/code]

`OVER(ORDER BY stamp)`での`SUM`なので、最後の2行の結果に注意してください。

この表を`tmp`として外側の`SELECT stamp, ...`の処理が行われるわけです。

その部分で`WINDOW`の`w1`と`w2`はそれぞれ、

[code]

    w1 as (partition by value_partition1 order by value_partition1)
    w2 as (partition by value_partition2 order by value_partition2)
    
[/code]

と定義されているわけですから、  
(余計なことかもしれませんが、`order by value_partition1`や`order by
value_partition2`はいらないですよね。)

外側`SELECT`の1行目を処理している時の各`WINDOW`は、

`w1`がこの1行(`value_partition1`の値が1)

[code]

    stamp val1 val2 value_partition1 value_partition2
    ----- ---- ---- ---------------- ----------------
    1     a    aa   1                1
    
[/code]

よって、`first_value(val1) over w1`の値は`a`。(1行しかないんで当たり前。)

`w2`がこの2行(`value_partition2`の値が1)

[code]

    stamp val1 val2 value_partition1 value_partition2
    ----- ---- ---- ---------------- ----------------
    1     a    aa   1                1
    2     b         2                1
    
[/code]

こちら、`first_value(val2) over w2`の値は`aa`。(非nullの値は`aa`だけなんで順序非依存。)

ご心配の`stamp=6`のときの`val2`をチェックしてみると、  
`w2`がこの3行(`value_partition2`の値が3)

[code]

    stamp val1 val2 value_partition1 value_partition2
    ----- ---- ---- ---------------- ----------------
    4          dd   3                3
    5     e         4                3
    6     f         5                3
    
[/code]

従って、`first_value(val2) over w2`の値は`dd`。(非nullの値は`dd`だけ。)

* * *

つまり、あなたのSQLは、順序依存の部分は内側のクエリーで明示的に`ORDER BY
stamp`を指定した前処理を行い、残りの部分はその前処理の結果を利用して順序依存にならないよう構成されている。

と言うことになるかと思います。従って`full_outer_joined`の順序には依存しません。`full_outer_joined`用のSQLに変な`ORDER
BY`をくっつけてみればすぐ確かめられると思います。


CC BY-SA 4.0
2019-04-17T08:40:18.253
54252
2019-04-17T08:40:18.253
13972
54228
answer