結合を使用すると、異なるビューに接続できるため、複数のビューから同時にデータを探索し、データのさまざまな部分が互いにどのように関連しているかを確認できます。
たとえば、データベースにテーブル order_items
、orders
、users
が含まれている場合があります。すべてのテーブルから同時にデータを探索するために、結合を使用できます。 このページでは、特定の結合パラメーターや結合のパターンなど、LookMLでの結合について紹介します。
結合はExploreから開始
結合はモデルファイルで定義され、Explore とビューの関係を確立します。結合は1つ以上のビューを単一のExploreに、直接、または他の結合されたビューを介して連結します。
order_items
と orders
という 2 つのデータベース テーブルについて考えてみましょう。両方のテーブルのビューを生成してから、それらの 1 つ以上を、モデルファイルの explore
パラメータで次のように宣言します。
explore: order_items { ... }
order_items
Explore からクエリを実行すると、生成された SQL の FROM
句に order_items
が表示されます。
SELECT ...
FROM order_items
order_items
Explore に追加情報を結合できます。例えば、order_item
の所属先の order
についてのデータを追加するには、次のように実行できます。
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} ;;
}
}
上記のLookMLは2つのことを達成します。 まず、UI に orders
と order_items
の両方のフィールドを表示できます。
次に、LookML で orders
と order_items
を結合する方法について説明します。このLookMLが、次のようなSQLに変換されます。
SELECT ...
FROM order_items
LEFT JOIN orders
ON order_items.order_id = orders.id
これらのLookMLパラメーターについては、以下のセクションで詳しく説明します。 この LookML の SQL への変換方法については、join
パラメータのリファレンス ページをご覧ください。
チャットチームのヒント: 最もお問い合わせの多い検証エラーは「不明なフィールドまたはアクセスできないフィールドです」で、これは結合がされていない場合に起こる可能性があります。詳しくは、このエラーに関するヘルプセンター記事をご覧ください。
結合パラメータ
結合には次の 4 つの主要なパラメータが使用されます。joins
、join
、type
、relationship
、sql_on
。
ステップ1:Exploreの開始
まず、order_items
Explore を作成します。
explore: order_items { ... }
ステップ 2: join
テーブルを結合する場合は、まずビューで宣言する必要があります。 この例では、orders
はモデル内の既存のビューです。
次に、join
パラメータを使用して、orders
ビューを order_items
に結合することを宣言します。
explore: order_items {
join: orders { ... }
}
ステップ 3: type
どの結合の type
を実行するかを検討します。Looker は、LEFT JOIN
、INNER JOIN
、FULL OUTER JOIN
、CROSS JOIN
をサポートしています。これらは、left_outer
、inner
、full_outer
、cross
の type
パラメータ値に対応しています。
explore: order_items {
join: orders {
type: left_outer
}
}
type
のデフォルト値は left_outer
です。通常は一般的な結合タイプです。
ステップ 4: relationship
order_items
と orders
の間の結合 relationship
を定義します。Looker が正確なメジャー計算するには、結合の relationship
を適切に宣言することが重要です。関係は、order_items
Explore から orders
ビューへ定義されます。使用できるオプションは、one_to_one
、many_to_one
、one_to_many
、many_to_many
です。
この例では、1 つの order
に対して多くの order_items
がある可能性があります。order_items
から orders
への関係は many_to_one
です。
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
}
}
結合に relationship
を含めない場合、Looker はデフォルトで many_to_one
に設定されます。
結合の relationship
パラメータを正しく定義するその他のヒントについては、ヘルプセンター記事の relationship
パラメータの正しい使い方をご覧ください。
ステップ 5: sql_on
sql_on
パラメータまたは foreign_key
パラメータを使用して、この 2 つのテーブルを結合する方法を宣言します。通常は sql_on
をお勧めしています。foreign_key
でできるすべてのことができ、通常はより理解しやすいからです。
sql_on
は、クエリで生成される SQL の ON
句と同じです。このパラメーターを使用して、どのフィールドを組み合わせて結合を行うかを宣言できます。
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} ;;
}
}
もっと複雑な結合を記述することもできます。 例えば、id
が1,000より大きいオーダーだけを結合する必要があるかもしれません。
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} AND ${orders.id} > 1000 ;;
}
}
この例の ${ ... }
構文について詳しくは、置換演算子をご覧ください。
ステップ6:テスト
[Order Items] に移動して、この結合が期待通り機能するかテストします。order_items
と orders
の両方のフィールドが表示されます。
LookML の変更のテストについては、モデル開発をご覧ください。
別のビューを介した結合
別のビューを介してビューをExploreに結合できます。 上記の例では、order_id
フィールドを使用して orders
を order_items
に結合しました。また、共通のフィールドがなくても、users
というビューのデータを order_items
Explore に結合することもできます。これを行うには、orders
ビューから結合します。
order_items
ではなく sql_on
または foreign_key
を使用して users
を orders
に結合します。これは、orders
のフィールドを orders.user_id
と正しくスコーピングすることによって行います。
sql_on
を使用した例を次に示します。
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} ;;
}
join: users {
type: left_outer
relationship: many_to_one
sql_on: ${orders.user_id} = ${users.id} ;;
}
}
ビューの複数回結合
users
ビューには、購入者と販売者の両方のデータが含まれます。このビューのデータを order_items
に結合する(購入者と販売者は別々に結合)には、from
パラメータを使用して異なる名前で users
を 2 回結合します。
from
パラメータを使用すると、結合に固有の名前を付けて、その結合でどのビューを使用するかを指定できます。例:
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} ;;
}
join: buyers {
from: users
type: left_outer
relationship: many_to_one
sql_on: ${orders.buyer_id} = ${buyers.id} ;;
}
join: sellers {
from: users
type: left_outer
relationship: many_to_one
sql_on: ${orders.seller_id} = ${sellers.id} ;;
}
}
この場合、buyers
として結合されるのは購入者データのみであり、sellers
として結合されるのは販売者データのみです。
注: users
ビューは、結合内ではエイリアス名 buyers
と sellers
で参照される必要があります。
結合からのフィールドの制限
fields
パラメータを使用すると、結合から Explore に取り出すフィールドを指定できます。デフォルトでは、ビューのすべてのフィールドが結合時に取り出されます。 しかし、一部のフィールドだけを取り出したい場合もあります。
たとえば、orders
が order_items
に結合するとき、shipping
フィールドと tax
フィールドだけを結合できます。
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} ;;
fields: [shipping, tax]
}
}
[set_a*]
など、一連のフィールドを参照することもできます。各セットは、set
パラメータを使用してビュー内で定義されます。orders
ビューに次のセットが定義されているとします。
set: orders_set {
fields: [created_date, shipping, tax]
}
orders
を order_items
に結合するときには、以下の 3 つのフィールドのみを使用できます。
explore: order_items {
join: orders {
type: left_outer
relationship: many_to_one
sql_on: ${order_items.order_id} = ${orders.id} ;;
fields: [orders_set*]
}
}
対称集計
Looker は、結合によってファンアウトが起こっても、集計(合計や平均など)を正しく計算するために「対称集計」と呼ばれる機能を使用しています。対称集計については、ヘルプセンターの対称集計の簡単な説明をご覧ください。また、解決されるファンアウトの問題については、SQL ファンアウトの問題をご覧ください。
必要なプライマリキー
結合からメジャー(集計)を得るには、その結合に関係しているすべてのビューでプライマリキーを定義する必要があります。
これを行うには、各ビューの主キー フィールドの定義に primary_key
パラメータを追加します。
dimension: id {
type: number
primary_key: yes
}
Lookerが結合されたメジャーを正しく処理できるように、Null値ではない、完全に一意の値を使ってプライマリキーを指定することが必要です。 データに主キーが含まれていない場合は、複数のフィールドの連結によって、完全に一意で非 Null 値の主キーとなるかどうか検討してください。主キーが一意ではない場合、または主キーに NULL 値が含まれ、それらの問題を示すデータがクエリに含まれている場合、Looker はこちらのヘルプセンター記事で説明されているようにエラーを返します。
サポートされているSQLダイアレクト
LookerがあなたのLookerプロジェクトの対称集計をサポートできるようにするには、あなたのデータベースダイアレクトもそれをサポートしていなければなりません。 次の表は、Looker の最新リリースで対称集計をサポートしている言語を示しています。
お使いのダイアレクトが対称集計をサポートしていない場合は、Lookerで結合を実行するときに注意してください。結合のタイプによっては集計が不正確になることがあります(合計や平均など)。 この問題と回避策については、SQL ファンアウトの問題のヘルプセンター記事で詳しく説明されています。
結合についての詳細
LookML の結合パラメータの詳細については、結合リファレンスのドキュメントをご覧ください。