派生テーブルとは、クエリ結果をデータベース内の物理テーブルのように使用できるクエリを指します。ネイティブ派生テーブルは、LookMLの用語を使用して定義するクエリに基づいています。これはSQL用語を使用して定義するクエリに基づくSQLベースの派生テーブルとは異なります。SQLベースの派生テーブルと比較すると、ネイティブ派生テーブルでは、データをモデル化するときの読みやすさと理解しやすさが向上します。詳細は、Lookerの派生テーブルのドキュメンテーションページのネイティブ派生テーブルとSQLベースの派生テーブルのセクションを参照してください。
ネイティブ派生テーブルとSQLベースの派生テーブルは、LookMLではビューレベルでderived_tableパラメーターを使用して定義されます。ただし、ネイティブ派生テーブルでは、SQLクエリを作成する必要がありません。代わりに、explore_sourceパラメーターを使用して、派生テーブルのベースとなるExplore、対象とする列やその他の特性を指定します。
SQL Runnerを使用して派生テーブルを作成するドキュメンテーションページに説明されている方法で、LookerでSQL Runnerから派生テーブルLookMLを作成することもできます。
Exploreを使用したネイティブ派生テーブルの定義の開始
Exploreで開始すると、Lookerは大半の派生テーブルのLookMLを生成できます。まずはExploreを作成し、派生テーブルに含めるすべてのフィールドを選択します。その後、次の方法でネイティブ派生テーブルLookMLを生成します。
Exploreの歯車メニューをクリックして[LookMLの取得]を選択します。
[派生テーブル]タブをクリックして、Exploreでネイティブ派生テーブルを作成するためのLookMLを確認します。
そのLookMLをコピーします。
生成されたLookMLをコピーしたら、ビューファイルにペーストします。
プロジェクトファイルへ移動します。
Looker IDEのプロジェクトファイルリストの上部にある[+]をクリックして、[Create View]を選択します。または、フォルダのメニューをクリックして、メニューから[Create View]を選択し、フォルダ内にファイルを作成できます。
内容がわかりやすいビュー名を設定します。
必要に応じて、列名の変更、派生列の指定、フィルタの追加を行います。
Exploreで
type: countのメジャーを使用する場合、ビジュアリゼーションで結果の値のラベルに使用されるのは「カウント」という言葉ではなくビュー名です。そのため、混乱しないようにビュー名は複数形で設定することを推奨します。ビジュアリゼーション設定の[Series]で[Show Full Field Name]を選択するか、view_labelに複数形のビュー名を使用して設定します。
LookMLでのネイティブ派生テーブルの定義
SQLとネイティブLookMLのいずれの方法で宣言された派生テーブルを使用する場合でも、derived_tableのクエリの出力は一連の列を含むテーブルとなります。派生テーブルがSQLで記述される場合、出力列名はSQLクエリによって暗黙的に示されます。例えば、次のSQLクエリには出力列user_id、lifetime_number_of_orders、lifetime_customer_valueが含まれます。
Lookerでは、クエリはExploreに基づき、メジャーフィールドとディメンションフィールドが含まれます。また、適用可能なフィルタも追加され、ソート順を指定することもできます。ネイティブ派生テーブルには、これらすべての要素に加え、その列の出力名が含まれます。
次の簡単な例では、user_id列、lifetime_customer_value列、lifetime_number_of_orders列の3列を含む派生テーブルを生成しています。クエリをSQLで手動記述する必要はありません。Lookerによって、指定されたExplore order_itemsとそのExploreのいくつかのフィールドの（order_items.user_id、order_items.total_revenue、order_items.order_count）を使用してクエリが作成されます。
includeステートメントを使用した参照フィールドの有効化
ネイティブ派生テーブルのビューファイルで、explore_sourceパラメーターを使用してExploreを指し示し、ネイティブ派生テーブルで必要な列やその他の特性を定義します。ネイティブ派生テーブルのビューファイル内からExploreを指し示しているため、Exploreの定義を含むファイルも含める必要があります。Exploreは通常、モデルファイル内で定義されますが、ネイティブ派生テーブルの場合は.explore.lkmlファイル拡張子を使用してExplore用に別のファイルを作成する方が明確になります。Exploreファイルの作成に関するドキュメンテーションを参照してください。この方法によって、ネイティブ派生テーブルのビューファイル内に、モデルファイル全体ではなく、単一のExploreファイルを含めることができます。この場合、次のようになります。
- ネイティブ派生テーブルのビューファイルには次のようにExploreのファイルを含める必要があります。例：
include: "/explores/order_items.explore.lkml" - Exploreのファイルには次のように必要なビューファイルを含める必要があります。例：
include: "/views/order_items.view.lkml"include: "/views/users.view.lkml" - モデルには次のようにExploreのファイルを含める必要があります。例：
include: "/explores/order_items.explore.lkml"
Exploreファイルは、自身を含んでいるモデルの接続をリッスンします。Exploreファイルの親モデルとは異なる接続を使って構成されているモデルにExploreファイルを含める場合は、この点を考慮してください。Exploreファイルを含むモデルの接続スキーマが親モデルの接続スキーマと異なる場合、クエリエラーが発生する可能性があります。
ネイティブ派生テーブルの列の定義
上記の例で示したとおり、派生テーブルの出力列の指定にはcolumnを使用します。
列名の指定
user_id列については、列名は元のExplore内の指定されたフィールド名に一致します。
元のExploreのフィールド名ではなく、出力テーブルの別の列名を使用したい場合もよくあることでしょう。上記の例では、order_items Exploreを使用して、ユーザーによる生涯価値計算を行っています。出力テーブルでは、total_revenueは実際には顧客のlifetime_customer_valueを示します。
column宣言は、入力フィールドと異なる出力名の宣言に対応しています。例えば、次のコードでは、”フィールドorder_items.total_revenueからの出力列の名をlifetime_valueとする”と記述しています。
暗黙的な列名
列の宣言でfieldパラメーターを省略した場合、<explore_name>.<field_name>であると見なされます。例えば、explore_source: order_itemsを指定したとします。
上記は次と等しくなります。
計算値の派生列の作成
derived_columnパラメーターを追加して、explore_sourceパラメーターのExploreに存在しない列を指定できます。それぞれのderived_columnパラメーターには、値の構成方法を指定するsqlパラメーターが伴います。
sqlの計算においては、columnパラメーターを使用して指定した任意の列を使用できます。派生列に集計関数を含めることはできませんが、テーブルの単一行に対して実行される計算を含めることができます。
次の例では、前述の例と同じ派生テーブルに、ネイティブ派生テーブルのlifetime_customer_value列とlifetime_number_of_orders列から計算されるaverage_customer_order計算列を追加したものを生成しています。
SQLウィンドウ関数の使用
データベースダイアレクトの一部は、ウィンドウ関数に対応しています。この関数は、特に順序番号、主キー、現在の値までの合計や累積合計、その他の有用な複数行の計算の作成などに使用されます。プライマリクエリが実行された後、derived_column宣言が個別に実行されます。
データベースダイアレクトがウィンドウ関数に対応している場合には、ネイティブ派生テーブルでこれを使用できます。希望のウィンドウ関数を含むsqlパラメーターを使用してderived_columnパラメーターを作成します。値を参照する際には、ネイティブ派生テーブルで定義された列名を使用する必要があります。
次の例では、user_id列、order_id列、およびcreated_time列を含むネイティブ派生テーブルを作成します。その後、SQL ROW_NUMBER()ウィンドウ関数を含む派生列を使用して、顧客の注文の順序番号を含む列を計算します。
ネイティブ派生テーブルへのフィルタの追加
過去90日間の顧客の値の派生テーブルを作成するとします。上記で実行したものと同じ計算を行おうとしていますが、過去90日間の購入のみを含める必要があります。
過去90日間の取引をフィルタリングするderived_tableにフィルタを追加するだけです。派生テーブルのfiltersパラメーターは、フィルタされたメジャーの作成に使用するものと同じ構文を使用します。
Lookerが派生テーブルのSQLを記述する際にフィルタがWHERE句に追加されます。
また、explore_sourceのdev_filtersサブパラメーターをネイティブの派生テーブルで使用できます。dev_filtersパラメーターを使用すると、Lookerで開発バージョンの派生テーブルにのみ適用されるフィルタを指定できます。こうすることで、フィルタによって絞り込まれた、より小さなテーブルを作成して繰り返しやテストを行えるため、変更のたびにテーブル全体が作成されるのを待たなくてすむようになります。
dev_filtersパラメーターはfiltersパラメーターと連動するので、開発バージョンのテーブルにすべてのフィルタが適用されます。dev_filtersとfiltersの両方が同じ列のフィルタを指定している場合、開発バージョンのテーブルにはdev_filtersが優先的に適用されます。
詳細については開発モードでの作業の効率化を参照してください。
テンプレートフィルタの使用
bind_filtersを使用してテンプレートフィルタを含めることができます。
これは、本質的にはsqlブロックで次のコードを使用する場合と同じです。
フィルタの適用先はフィールドto_fieldとなります。to_fieldは、基礎となるexplore_sourceのフィールドでなければなりません。
実行時にフィルタが存在する場合、from_fieldはフィルタの取得先のフィールドを指定します。
上記のbind_filtersの例では、Lookerはfiltered_lookml_dt.filter_dateフィールドに適用される任意のフィルタを取得し、そのフィルタをusers.created_dateフィールドに適用します。
また、explore_sourceのbind_all_filtersサブパラメーターを使用して、すべてのランタイムフィルタをExploreからネイティブ派生テーブルのサブクエリに渡すことができます。詳細については、explore_sourceパラメーターのドキュメンテーションページを参照してください。
ネイティブ派生テーブルのソートおよび制限
また、必要に応じて派生テーブルをソートおよび制限することもできます。
Exploreでは基礎となるソートと異なる順で行が表示される場合があるので注意してください。
ネイティブ派生テーブルの異なるタイムゾーンへの変換
timezoneサブパラメーターを使用して、ネイティブ派生テーブルのタイムゾーンを指定できます。
timezoneサブパラメーターを指定する場合、ネイティブ派生テーブルの時刻に基づくデータはすべて、指定されたタイムゾーンに変換されます。サポートされるタイムゾーンのリストは、timezone値のドキュメンテーションページをご覧ください。
ネイティブ派生テーブルの定義でタイムゾーンを指定しない場合、時刻に基づくデータのタイムゾーン変換は行われず、ネイティブ派生テーブルの時刻に基づくデータは、デフォルトのデータベースのタイムゾーンになります。
ネイティブ派生テーブルが永続的ではない場合、タイムゾーン値を"query_timezone"に設定することで、自動的に現在実行中のクエリのタイムゾーンが使用されます。