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についても解説しました。サブクエリを使用することで、複雑なデータセットでも効率的に必要な情報を取得することができます。是非、自身のプロジェクトに役立ててください。