88005


SQLのJOINの方法で、効率的な方法を探しています。  
言葉で説明するのが難しく・・・テーブルで説明します  
（環境はBigQuery standardSQLです)

# table_A

ID | name | departure | arrival  
---|---|---|---  
1 | sato | 100 | 200  
2 | suzuki | 200 | 300  
3 | tanaka | 300 | 200  
  
# table_B

ID | spot  
---|---  
100 | tokyo  
200 | osaka  
300 | nagoya  
  
# 実現したい結果

name | departure | arrival  
---|---|---  
sato | tokyo | osaka  
suzuki | osaka | nagoya  
tanaka | nagoya | osaka  
  
これを実現するには、例えば

[code]

    WITH 
    dep_spot AS
    (
     SELECT
      departure,
      spot
     FROM
      table_A as a
     JOIN
      table_B as b
     ON
      a.departure = b.ID
    ),
    ari_spot AS
    (
     SELECT
      arrival,
      spot
     FROM
      table_A as a
     JOIN
      table_B as b
     ON
      a.arrival = b.ID
    )
    SELECT
    　name,
    　dep_spot.spot as departure,
    　ari_spot.spot as arrival
    FROM
    　table_A
    JOIN
    　dep_spot USING(departure)
    AND ari_spot USING(departure)
    
    
[/code]

といった形でそれぞれを参照するwith句をつくるしかないのかなあ、と思いつつ、大きなデータだと意図せぬ結果になるようなことがあり、そもそもやり方がおかしいのか？と思っている次第です。  
こうしたケースの良い知恵をお貸しください。


CC BY-SA 4.0
2022-03-24T02:54:50.767
88000
2022-03-24T10:53:53.457
41311
question
sql google-bigquery
SQL テーブル上の2つのそれぞれの値を、1つのテーブルから参照する効率的な方法


一般的なSQLで書くならこんな感じだと思います

[code]

    SELECT 
        name,
        dep.spot departure, 
        arr.spot arrival
    FROM 
        table_A
    JOIN   
        table_B dep
    ON  
        table_A.departure = dep.ID
    JOIN   
        table_B arr
    ON  
        table_A.arrivalint = arr.ID
    ;
    
[/code]

実行効率については、table_A.IDとtable_B.IDに（ユニーク）インデックスを付加することになると思いますが  
それらが主キーであれば、大概のDBMSで自動的にインデックス化されてると思います。

一般的なDBMSの実行については、オプティマイザが判断して最良と思われるものを選択してくれることになっています。  
理想的には同じ結果なら同じ最適な実行計画で実行してほしいのですが、複雑なSQLではなかなかそういかないのが実情です。  
もとのSQLよりこのSQLのほうが良い実行計画で実行される期待度が高いと思います。

これ以上の実行効率を求めるのであれば、実際のテーブル定義と行数で、使っているDBMSごとの特性を考慮したチューニングが必要になります。  
google-bigqueryについては私は詳しくないのでこれ以上の回答はできません


CC BY-SA 4.0
2022-03-24T10:53:53.457
88005
2022-03-24T10:53:53.457
9811
88000
answer