Lookerでview上のサブクエリを活用したデータ抽出について

## Lookerでサブクエリを活用したデータ抽出の実践ガイド

Lookerを使用して複雑なデータセットを扱う際、サブクエリを活用することで効率的なデータ抽出が可能になります。本記事では、サブクエリの利用方法、実行順序、GROUP BYの使用方法、そしてLookerにおけるmeasureとdimensionの違いについて詳細に解説します。

## サブクエリを使ったLooker Viewの定義

まず、サブクエリを使用して必要なデータを取得し、それを結合して最終的なデータセットを作成する方法を見ていきましょう。

## サブクエリの実行順序

以下のクエリでは、最初に`entity_info`サブクエリが実行され、その後`classification_info`サブクエリが実行されます。最後に、`transaction_data`サブクエリが実行され、これらの結果がメインクエリで結合されます。

### エンティティ情報のサブクエリ
エンティティ情報を取得するサブクエリを定義します。このサブクエリでは、エンティティIDと分類IDを取得します。

```sql
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、グループ名、および種類名を取得します。

```sql
, 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、金額、トランザクション日、年、および月を取得します。

```sql
, 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を使用してデータを集計します。ここでは、トランザクション日とグループ名で集計し、合計金額を計算します。

```sql
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の例です。

```sql
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に該当します。

```sql
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に該当します。

```sql
measure: total_amount {
  type: sum
  sql: ${TABLE}.amount ;;
  label: "Total Amount"
}
```

## まとめ

本記事では、Lookerでサブクエリを活用してデータを抽出する方法と、measureとdimensionの基本的な違いについて説明しました。また、GROUP BYの使用方法やDimension Groupについても解説しました。サブクエリを使用することで、複雑なデータセットでも効率的に必要な情報を取得することができます。是非、自身のプロジェクトに役立ててください。