

複数システムのそれぞれ独自のユーザーIDに対して、同一ユーザーのIDを紐付けるためのユニークIDを振りたいと考えています。  
SQLでどのようなロジックにすれば良いか、頭を悩ませておりまして、助言いただけませんでしょうか。

同一ユーザーを紐付けたテーブルはあります。これを元に「同一ユーザーを区別するための」ユニークIDを振りたいです。  
複数システムの同一ユーザーを紐付けるため、3システム以上にまたがるユーザーに、同一IDを振る点が難しいです。

  * 処理はSQLで実行します。（BigQueryのため、ある程度重い処理になっても問題はありません。なお、標準のSQLとほぼ同じなので、標準のSQLで考えていただいて大丈夫です）
  * SQLの実行自体は別のプログラムで行うため、複数SQLを順序立てて実行することが可能です。ただしデータ件数が多いため、プログラム側にSELECTでデータを取ってきて処理を行うことは難しいです。(システムID一覧であれば件数が小さいので取得可能です)
  * 対象のシステムは百近くあるため、「まず２つのシステム分のデータだけを抜き出して紐付けをして・・・」の繰り返しだと5千ループくらい必要なため厳しいです。  
（ループがどうしても必要なら、せめてシステムの数＝百回ですむようにしたい）

  * 2つのシステムで、ユーザーは1対1で紐付きます。(紐付かないユーザーもありますが、1対多はありません)
  * 列[システムID1]と[システムID2]の値は、文字列で見た時に[システムID1]＜[システムID2]となっています(つまり、2つのシステムがあった時、どちらが[システムID1][システムID2]になるかは決まっている)
  * BigQueryの特性上、UPDATE文、INSERT文、DELETE文は制限されており、使用は避けたいです
  * 中間テーブルの作成は可能です
  * window関数も使用できます

元テーブルの列

[code]

    [システムID１，ユーザーID１，システムID２，ユーザーID２]
    
[/code]

元テーブルの値の例 ※行は何千万とあります  
↓たとえば、A005＝B021＝C101なので、この3つには同じユニークIDを振りたい

[code]

    sys01, A001, sys02, B002
    sys01, A003, sys02, B011
    sys01, A005, sys02, B021
    sys02, B001, sys03, C050
    sys02, B021, sys03, C101
    sys01, A001, sys03, C102
    sys01, A003, sys03, C111
    
[/code]

作りたいテーブル

[code]

    [システムID，ユーザーID, ユニークID]
    sys01, A001, 0001
    sys01, A003, 0002
    sys01, A005, 0003
    sys02, B002, 0001
    sys02, B001, 0004 
    sys02, B011, 0002
    sys02, B021, 0005
    sys03, C101, 0003
    ・・・
    
[/code]

どうぞよろしくお願いいたします。


CC BY-SA 4.0
2021-05-08T06:57:28.237
75738
2021-05-09T08:15:29.210
2021-05-08T23:21:09.780
19110
24433
question
sql google-bigquery
SQLを用いて、複数システム同一ユーザーのIDを紐付けしてIDを振るロジック


システム間でのユーザー ID 対応に多対一対応が無い、かつ、システムの数自体が動的に増減するといったことが無いのであれば、同一行にそれぞれのシステムでの
ID が載ったテーブルを作れば良いのではないでしょうか。

つまり、以下のようなテーブルではなく、

[code]

    src,   ID1,  dst,   ID2
    ------------------------
    sys01, A001, sys02, B002
    sys01, A003, sys02, B011
    sys01, A005, sys02, B021
    sys02, B001, sys03, C050
    sys02, B021, sys03, C101
    sys01, A001, sys03, C102
    sys01, A003, sys03, C111
    
[/code]

以下のようにデータを持てば解決しませんでしょうか。一番左の列は primary key で、それ以外の列は nullable です。

[code]

    PK, sys01, sys02, sys03
    -----------------------
    1,  A001,  B002,  C102
    2,  A003,  B011,  C111
    3,  A005,  B021,  C101
    4,  null,  B001,  C050
    
[/code]

現状の元テーブルは ID
対応のグラフの辺を列挙したような形になっているせいで、本当に知りたい情報である所の、グラフの連結成分を求める計算に計算コストがかかるようになっていそうです。データの持ち方を上記のように変えることで連結成分を行として持っておけば、今回の問題は解決しませんでしょうか。

高々数千万行程度しか元テーブルの行が無いのであれば、テーブルのデータをすべてローカル環境に持ってきてローカルにてお好きな言語で計算し新しいテーブルに
insert する、という計算はそこまで時間がかからず終わるのではないかなと思います。上のような表記の文字列としてデータを受け渡ししたとしても元テーブルは
1 行 20 byte そこらで、ということは 1000 万行あっても 2 億 byte ≒ 200 MB 程度です。


CC BY-SA 4.0
2021-05-08T23:42:59.637
75740
2021-05-09T08:15:29.210
2021-05-09T08:15:29.210
19110
19110
75738
answer