

PostgreSQLで不完全リスト情報に基づく完全リスト作成をしたいのですが、良い方法はないでしょうか。  
次のテーブルがあります。

[code]

    CREATE TABLE abscissa (
      a_id TEXT
      o_id_list TEXT[]
    );
    
    CREATE TABLE ordinate (
      o_id TEXT
      a_id_list TEXT[]
    );
    
[/code]

a_id_list, o_id_list は それぞれ a_id, o_id の不完全リストとなっています。  
これをもとに、完全リストを作りたいのです。  
具体例でいうと、次のようなデータがあるとします。

[code]

    a_1  | {o_1, o_2, o_4}
    a_2  | {o_1, o_3, o_5}
    a_3  | {o_3, o_5}
    
    o_1  | {a_1, a_2}
    o_2  | {a_1}
    o_3  | {a_2, a_3}
    o_4  | {a_1}
    o_5  | {a_2, a_3}
    
[/code]

これを

[code]

    {a_1, a_2, a_3} | {o_1, o_2, o_3, o_4, o_5}
    
[/code]

という形にまとめたいのです。  
よろしくお願いいたします。

追記：

重なりがない場合というのは、

[code]

    a_4 | {o_6,o_7}
    a_5 | {o_8,o_9}
    
[/code]

というような場合です。

使い方に慣れておらず、要領が悪くてすみません。


CC BY-SA 3.0
2015-11-20T09:13:11.067
19080
2018-06-22T07:35:13.057
2017-02-15T06:25:26.180
13345
13345
question
postgresql
配列を使った少し複雑なグルーピング方法について

[code]

    with w1 as (select array_agg(a_id order by a_id) from (select distinct unnest(a_id_list) as a_id from ordinate) as t2)
    , w2 as (select array_agg(o_id order by o_id) from (select distinct unnest(o_id_list) as o_id from abscissa) as t1)
    select * from w1,w2
    
[/code]


CC BY-SA 3.0
2015-11-20T10:10:45.820
19084
2015-11-20T10:10:45.820
13348
19080
answer


「グラフの各連結成分の要素を列挙する」問題のように思えます

グラフの問題はSQLを使わず、手続き的に探索した方が簡単で分かりやすいと思います  
効率の良いアルゴリズム(深さ優先探索など)も多く知られています

SQLを書いてみましたが、再帰SQLを使った複雑なものになってしまっています  
共通部分を持つ配列を変化がなくなるまで追加することで、極大なグラフを得ています

[code]

    CREATE TABLE foo(foo_id int4, bar_id_list int4[]);
    INSERT INTO foo(foo_id, bar_id_list)
    VALUES
    (1, ARRAY[1, 2, 4]),
    (2, ARRAY[1, 3, 5]),
    (3, ARRAY[3, 5]),
    (4, ARRAY[6]),
    (5, ARRAY[7, 8]),
    (6, ARRAY[8, 9]),
    (7, ARRAY[9, 10]),
    (8, ARRAY[10, 11]);
    
    WITH RECURSIVE r AS (
      SELECT
        T1.foo_id,
        T1.bar_id_list
      FROM
        foo T1
    
      UNION ALL
    
      SELECT
        T1.foo_id,
        array_agg(DISTINCT T1.bar_id ORDER BY T1.bar_id) AS bar_id_list
      FROM (
        SELECT DISTINCT
          T1.foo_id,
          unnest(T1.bar_id_list || T2.bar_id_list) AS bar_id,
          T2.bar_id_list AS prev_bar_id_list
        FROM
          foo T1
        CROSS JOIN
          r T2
        WHERE
          T1.bar_id_list && T2.bar_id_list
      ) T1
      GROUP BY
        foo_id
      HAVING
        max(T1.prev_bar_id_list) <> array_agg(T1.bar_id ORDER BY T1.bar_id)
    )
    SELECT
      T1.foo_id,
      T1.bar_id_list
    FROM (
      SELECT
        T1.foo_id,
        T1.bar_id_list,
        row_number() OVER (PARTITION BY T1.foo_id ORDER BY array_length(T1.bar_id_list, 1) DESC) AS row_number
      FROM
        r T1
    ) T1
    WHERE T1.row_number = 1;
    
[/code]

* * *
[code]

     foo_id |  bar_id_list  
    --------+---------------
          1 | {1,2,3,4,5}
          2 | {1,2,3,4,5}
          3 | {1,2,3,4,5}
          4 | {6}
          5 | {7,8,9,10,11}
          6 | {7,8,9,10,11}
          7 | {7,8,9,10,11}
          8 | {7,8,9,10,11}
    (8 rows)
    
[/code]


CC BY-SA 3.0
2017-05-26T13:00:01.197
35043
2017-05-26T13:00:01.197
9796
19080
answer