Lookerでサブクエリを活用したデータ抽出の実践ガイド
Lookerを使用して複雑なデータセットを扱う際、サブクエリを活用することで効率的なデータ抽出が可能になります。本記事では、サブクエリの利用方法、実行順序、GROUP BYの使用方法、そしてLookerにおけるmeasureとdimensionの違いについて詳細に解説します。
サブクエリを使ったLooker Viewの定義
まず、サブクエリを使用して必要なデータを取得し、それを結合して最終的なデータセットを作成する方法を見ていきましょう。
サブクエリの実行順序
以下のクエリでは、最初にentity_infoサブクエリが実行され、その後classification_infoサブクエリが実行されます。最後に、transaction_dataサブクエリが実行され、これらの結果がメインクエリで結合されます。
エンティティ情報のサブクエリ
エンティティ情報を取得するサブクエリを定義します。このサブクエリでは、エンティティIDと分類IDを取得します。
WITH entity_info AS (
SELECT
e."entity_id" AS entity_id, -- エンティティID
e."classification_id" AS classification_id -- 分類ID
FROM
"database"."schema"."entity" AS e
)
分類情報のサブクエリ
次に、分類情報を取得するサブクエリを定義します。このサブクエリでは、分類ID、グループ名、および種類名を取得します。
, classification_info AS (
SELECT
c."classification_id" AS classification_id, -- 分類ID
c."group_name" AS group_name, -- グループ名
c."type_name" AS type_name -- 種類名
FROM
"database"."schema"."classification" AS c
)
サブクエリの繋げ方の説明
上記のように、サブクエリを繋げる際には最初のサブクエリにのみWITH句を使用し、それ以降のサブクエリはカンマで区切って定義します。これにより、クエリの読みやすさと効率が向上します。
トランザクションデータのサブクエリ
最後に、トランザクションデータを取得するサブクエリを定義します。このサブクエリでは、トランザクションID、エンティティID、金額、トランザクション日、年、および月を取得します。
, transaction_data AS (
SELECT
t."transaction_id" AS transaction_id, -- トランザクションID
t."entity_id" AS entity_id, -- エンティティID
t."amount" AS amount, -- 金額
t."transaction_date" AS transaction_date, -- トランザクション日
LEFT(t."transaction_year_month", 4) AS year, -- 年
RIGHT(t."transaction_year_month", 2) AS month -- 月
FROM
"database"."schema"."transaction" AS t
)
メインクエリでのデータ結合と集計
これらのサブクエリをメインクエリで結合し、GROUP BYを使用してデータを集計します。ここでは、トランザクション日とグループ名で集計し、合計金額を計算します。
SELECT
transaction_date, -- トランザクション日
group_name, -- グループ名
SUM(amount) AS total_amount -- 合計金額
FROM
transaction_data
JOIN
classification_info ON classification_info.classification_id = transaction_data.entity_id
JOIN
entity_info ON entity_info.entity_id = transaction_data.entity_id
GROUP BY
1, 2 -- カラム名ではなく、SELECT句の順序で指定
GROUP BYの詳細説明
GROUP BY句は、SELECT句で指定した列の順序でグループ化を行います。例えば、上記のクエリでは、transaction_dateが1番目、group_nameが2番目に指定されています。このように、カラム名ではなく、SELECT句の順序で指定することができます。
Dimension Groupの使用
Dimension Groupは、日付や時間に関するデータを扱う際に便利です。例えば、日付フィールドを使って、日、週、月、四半期、年ごとにデータをグループ化することができます。
Dimension Groupの定義
以下は、transaction_dateを使用して日付ごとにデータをグループ化するDimension Groupの例です。
dimension_group: transaction_date {
timeframes: [raw, date, week, month, quarter, year]
convert_tz: no
datatype: date
sql: ${TABLE}.transaction_date ;;
label: "Transaction Date"
}
Dimension Groupの詳細説明
-
timeframes: データをどの単位でグループ化するかを指定します。例として、日（raw）、日付（date）、週（week）、月（month）、四半期（quarter）、年（year）があります。 -
convert_tz: タイムゾーンの変換を行うかどうかを指定します。noに設定すると変換しません。 -
datatype: データの型を指定します。ここでは日付型（date）です。 -
sql: データベースから取得するSQLクエリを指定します。 -
label: ユーザーインターフェースに表示されるラベルを指定します。
MeasureとDimensionの違い
Lookerでは、measureとdimensionの違いを理解することが重要です。
Dimension
Dimensionは、データセット内の属性やカテゴリ情報を定義します。例えば、トランザクション日や分類情報などがdimensionに該当します。
dimension: transaction_date {
type: date
sql: ${TABLE}.transaction_date ;;
label: "Transaction Date"
}
dimension: group_name {
type: string
sql: ${TABLE}.group_name ;;
label: "Group Name"
}
Measure
Measureは、データセット内の数値データを集計するために使用されます。例えば、トランザクション金額の合計などがmeasureに該当します。
measure: total_amount {
type: sum
sql: ${TABLE}.amount ;;
label: "Total Amount"
}
まとめ
本記事では、Lookerでサブクエリを活用してデータを抽出する方法と、measureとdimensionの基本的な違いについて説明しました。また、GROUP BYの使用方法やDimension Groupについても解説しました。サブクエリを使用することで、複雑なデータセットでも効率的に必要な情報を取得することができます。是非、自身のプロジェクトに役立ててください。