このページは、LookML を使用して Looker で Explore をビルドするすべてのユーザーを対象としています。このページは、SQL に精通している方、特に内部結合と外部結合の違いを理解している方にとって理解しやすい内容になっています。内部結合と外部結合の違いについては、SQL 結合にある w3schools の記事をご覧ください。
Looker は、企業にとって強力な SQL エンジンとして機能します。LookML の抽象的なモデリングにより、データチームと IT チームは常に正しい一般的なルールを構築することができ、ビジネス アナリストは、データチームがその必要性を予測しなかった場合でも、常に正しいクエリを作成することができます。この機能の中核となるのは、対称集計アルゴリズムです。このアルゴリズムは、SQL 結合に関する業界全体の問題を解決します。ただし、このアルゴリズムを利用するには、2 つのことを正しく行う必要があります。メジャーを含むすべてのビュー（通常はすべてのビュー）で主キーが正確であること、すべての結合で relationship パラメータが正しいことです。
主キー
多くの場合、テーブルの主キーを理解することは、テーブルの性質とテーブルでできることを理解することと本質的に同じです。主キーとして選択する列（または連結された列のセット）に重複する値がないことのみが条件となります。
relationship パラメータ
主キーを確認したので、結合の relationship パラメータの正しい値を決定できます。relationship パラメータの目的は、結合が SQL クエリに書き込まれたときに対称集計を呼び出すかどうかを Looker に伝えることです。考えられるアプローチとしては、Looker に常に呼び出すように指示し、常に正確な結果が得られるようにする方法があります。ただし、これを行うとパフォーマンスに影響するため、対称集計は慎重に使用してください。
正しい値を決定するプロセスは、内部結合と外部結合で若干異なります。
内部結合
たとえば、order_id を主キーとする注文のテーブルがあるとします。
| order_id | 金額 | customer_id |
|---|---|---|
| 1 | $25.00 | 1 |
| 2 | $50.00 | 1 |
| 3 | $75.00 | 2 |
| 4 | $35.00 | 3 |
customer_id を主キーとする顧客のテーブルがあるとします。
| customer_id | first_name | last_name | visits |
|---|---|---|---|
| 1 | Amelia | Earhart | 2 |
| 2 | Bessie | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
これらのテーブルは、両方のテーブルに存在する customer_id フィールドで結合できます。この結合は、LookML で次のように表されます。
explore: orders {
join: customers {
type: inner
sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
relationship: many_to_one
}
}
この LookML 結合の結果は、次のように単一の結合テーブルとして表すことができます。
| order_id | 金額 | customer_id | customer_id | first_name | last_name | visits |
|---|---|---|---|---|---|---|
| 1 | $25.00 | 1 | 1 | Amelia | Earhart | 2 |
| 2 | $50.00 | 1 | 1 | Amelia | Earhart | 2 |
| 3 | $75.00 | 2 | 2 | Bessie | Coleman | 2 |
| 4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
many_to_one 関係は、結合フィールド（customer_id）の 1 つの値が各テーブルに表現される回数を指します。orders テーブル（左側のテーブル）では、1 つの顧客 ID が複数回表されています（この場合は、ID が 1 の顧客が複数の行に存在します）。
customers テーブル（右側のテーブル）では、customer_id がそのテーブルの主キーであるため、すべての顧客 ID は 1 回だけ表されます。したがって、orders テーブルのレコードには、customers テーブルの 1 つの値に多くの一致が存在する可能性があります。customer_id が customers テーブルのすべての行で一意でない場合、関係は many_to_many になります。
主キーを確認して、プログラムによって正しい関係値を決定するには、次の手順を行います。
- まず、関係として
many_to_manyを記述します。主キーが正しい限り、Looker は常に対称集計アルゴリズムをトリガーして精度を確保するため、常に正確な結果が得られます。ただし、このアルゴリズムを使用するとクエリが複雑になり、実行時間が長くなるため、片側または両側をmanyではなくoneに変更することをおすすめします。 - 左側のテーブルの
sql_on句にあるフィールドを確認します。左側のテーブルの主キーを形成するフィールドがある場合は、relationshipパラメータの左側をoneに変更できます。そうでない場合は、通常はmanyのままにする必要があります。（特殊なケースについては、このページの考慮事項をご覧ください）。 - 次に、
sql_on句で適切なテーブルを表すフィールドを確認します。右側のテーブルの主キーを形成するフィールドがある場合は、右側をoneに変更できます。
sql_on のフレーズは、等号の左側にある左側のテーブルと、右側にある右側のテーブルから記述することをおすすめします。順序がデータベースの SQL 言語に関連していない限り、sql_on パラメータの条件の順序は重要ではありません。sql_on パラメータでは、このようにフィールドを並べる必要はありませんが、等号の左右が relationship パラメータの左から右への読み方と一致するように sql_on 条件を並べると、関係を判断するのに役立ちます。このようにフィールドを並べ替えると、新しいテーブルを結合する Explore 内の既存のテーブルを一目で把握しやすくなります。
外部結合
外部結合では、結合中に null レコードが追加された場合にファンアウトが発生する可能性があることも考慮する必要があります。Looker では左側外部結合がデフォルトであるため、これは特に重要です。null レコードは合計や平均には影響しませんが、Looker が type: count のメジャーを実行する方法には影響します。これが正しく行われないと、null レコードがカウントされます（これは望ましくありません）。
完全外部結合では、結合キーに他方のテーブルに存在する値がない場合、どちらかのテーブルに null レコードが追加される可能性があります。これを次の例で示します。この例では、orders テーブルを使用しています。
| order_id | 金額 | customer_id |
|---|---|---|
| 1 | $25.00 | 1 |
| 2 | $50.00 | 1 |
| 3 | $75.00 | 2 |
| 4 | $35.00 | 3 |
たとえば、次の customers テーブルがあるとします。
| customer_id | first_name | last_name | visits |
|---|---|---|---|
| 1 | Amelia | Earhart | 2 |
| 2 | Bessie | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
| 4 | Charles | Yeager | 3 |
これらのテーブルが結合されると、結合されたテーブルは次のように表示されます。
| order_id | 金額 | customer_id | customer_id | first_name | last_name | visits |
|---|---|---|---|---|---|---|
| 1 | $25.00 | 1 | 1 | Amelia | Earhart | 2 |
| 2 | $50.00 | 1 | 1 | Amelia | Earhart | 2 |
| 3 | $75.00 | 2 | 2 | Bessie | Coleman | 2 |
| 4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
| null | null | null | 4 | Charles | Yeager | 3 |
内部結合と同様に、テーブルの主キー間の関係は many_to_one です。ただし、追加された null レコードにより、左側のテーブルでも対称集計が必要になります。この結合を実行すると、左側のテーブルのカウントが中断されるので、relationship パラメータを many_to_many に変更する必要があります。
この例が左外部結合の場合、null 行は追加されず、余分な顧客レコードは破棄されます。この場合、関係は引き続き many_to_one になります。これは、ベーステーブルが分析を定義すると想定されているため、Looker のデフォルトです。この場合、分析対象は顧客ではなく注文です。顧客テーブルが左側にある場合は、状況は異なります。
マルチレベル結合
一部の Explore では、ベーステーブルが 1 つ以上のビューに結合され、さらに 1 つ以上の他のビューに結合する必要があります。この例では、テーブルが顧客テーブルに結合されます。このような状況では、relationship パラメータを評価するときに、書き込まれる個々の結合のみを調べることをおすすめします。ダウンストリーム ファンアウトがクエリに影響するタイミングは、影響を受けるビューが実際にファンアウトを作成した結合に存在しない場合でも、Looker が把握します。
Looker のメリット
Looker には、関係値が正しいことを確認するためのメカニズムがあります。1 つは、主キーの一意性を確認するものです。メジャーを計算するためにファンアウトがあり、対称集計が必要な場合、Looker は使用された主キーの一意性をチェックします。一意でない場合、クエリの実行時にエラーが表示されます（ただし、この場合の LookML バリデータのエラーはありません）。
また、Looker がファンアウトを処理できない場合（通常は主キーが指定されていないため）、そのビューの Explore にメジャーは表示されません。この問題を解決するには、メジャーを Explore に取り込めるように、フィールドを主キーとして指定します。
注意点
対称集計の言語 サポート
Looker では、対称集計をサポートしていない一部の言語と接続できます。言語とその対称集計のサポート状況のリストについては、symmetric_aggregates のドキュメント ページをご覧ください。
特殊なケース
このページの前の内部結合の項では、正しい関係値を決定するために、左側のテーブルから sql_on 句にある 1 つまたは複数のフィールドを見る必要があると述べています。「1 つまたは複数のフィールドが左のテーブルの主キーを形成している場合、relationship パラメータの左側を one に変更することができます。そうでない場合は、通常は many のままにする必要があります。」これは、テーブルに重複するレコードがない複数の列が含まれていない限り、正です。この場合、primary_key: yes に指定された列でなくても、リレーションを定義するときに、そのような列を主キーとして扱うことができます。
指定した列に対して、前段落の記述が常に正となるように、なんらかのソフトウェア ルールを設定しておくと便利です。そうであれば、そのまま実行し、後でそれを参照できるように、ビューファイルにその特別なプロパティをメモします（SQL Runner リンクを使用して証明します）。ただし、Looker はフィールドが主キーとして指定されている場合に暗黙の一意性の真偽を確認しますが、他のフィールドに対しては確認しません。対称集計アルゴリズムは呼び出されません。