データ分析で用いるSQLクエリの設計方法

こんにちは。風音屋（@Kazaneya_PR）データエンジニアの妹尾です。 今回は、データ分析で用いるSQLクエリの設計方法についてご紹介します。

この記事は、datatech-jp Advent Calendar 2024 の 8 日目の記事です。

データアナリストやデータエンジニアの皆様は、以下のような悩みを抱えていませんでしょうか。

• 分析でSQLクエリを書けと言われたが、どういう手順でクエリを作成すれば良いかわからない
• 分析クエリを書いても、要件漏れや作業ミスなどで手戻りが発生する
• 分析クエリのレビューに時間がかかる
• すでに退社済みの人が作成したクエリの解読に手間がかかっている

分析クエリもソフトウェアと同じで、設計（考えること）と作業（手を動かす）を分解することで、以下のような恩恵を受けることができます。

• 作業の見通しが立てられるので、手戻りを減らせる
• 無駄な処理を設計段階で排除できるので、可読性とパフォーマンスの高い分析クエリが書ける
• 設計書を基にクエリを読むことができるので、クエリの仕様理解にかかるコストを削減できる

まず、今回作成するクエリの設計図を最初にお見せします。

インプットとなるデータソースが左側、アウトプットとなるグラフとサンプルテーブルが右側に位置しています。 それらを繋ぐ形でプロセスのブロックが存在します。 この1つ1つのブロックを、WITH句を用いてCTE（Common Table Expression：共通テーブル式）で再現していき、最終的にクエリを作成します。

今回は、動画サイトを運営する会社で、「10代のユーザーの月間視聴数（性別 / 動画カテゴリごと）の推移をグラフで見たい」という要望があったと仮定し、要件の整理とクエリを設計、作成するまでの手順をご紹介いたします。

手順

今回ご紹介するクエリ設計では、要件を大きく分けて3つの要素「アウトプット」「インプット」「プロセス」の順番で整理し、「概念設計」「論理設計」「物理設計」と段階を踏みながら、徐々に設計の具体性を上げていく進め方をします。

• STEP1. アウトプットイメージを具体化する
  • グラフやテーブルのイメージを書く
  • アウトプットを実現するためのテーブルイメージを書く
• STEP2. アウトプットを実現するために必要なインプット（データ）を書き出す
• STEP3. インプットからアウトプットを実現するデータの加工プロセスを書く
  • 概念設計をする
  • 論理設計をする
  • 物理設計をする
• STEP4. クエリを作成する

STEP1. アウトプットイメージを具体化する

グラフやテーブルのイメージを書く

まずは、アウトプットを整理しましょう。

今回は、「10代のユーザーの月間視聴数（性別 / 動画カテゴリごと）の推移をグラフで見たい」という要望なので、アウトプットは「グラフ」です。 「紙にラフ図を描く」「描画ツールなどで作成する」など、手間のかからない方法でグラフのイメージを描きましょう。今回はGoogle スプレッドシートで作成しました。

アウトプットイメージは、具体性が高ければ高いほど良いです。 アウトプットの整理ができたら依頼者と認識合わせをしましょう。依頼者はどういったグラフになるか想像しやすいので、イメージと違う場合はこの段階で修正できます。 作成するグラフを整理すると、以下のように必要なデータが見えてきます。

アウトプットを実現するためのテーブルイメージを書く

クエリで最終的に取得できるのはグラフではなくレコードです。アウトプットに必要となるレコードのサンプルを書き出し、解像度を上げましょう。 アウトプットの1つ手前のテーブルのサンプルレコードを作成するイメージです。10行程度書き出せると、どのようなクエリを書く必要があるか想像しやすいです。

分析要件をヒアリングする際は、どのBIツールを使用するのかなどの、アウトプットの提供形式を確認しておきましょう。 提供形式によって、このテーブルイメージも変わってくるからです。 例えば、グラフで内訳の出力をしたい場合、多くのBIツールでは縦持ちのデータにしておけば問題ないですが、Google スプレッドシートだと横持ちのデータにする必要があります。

今回は、縦持ちのテーブルイメージを作成しました。

視聴月（年月）	ユーザーの性別	動画カテゴリ	視聴数
2024-01	男	スポーツ	1000000
2024-01	女	スポーツ	400000
2024-01	男	バラエティ	150000
2024-01	女	バラエティ	30000
2024-01	男	ニュース	410000
2024-01	女	ニュース	10000
2024-02	男	スポーツ	1100000
2024-02	女	スポーツ	600000
2024-02	男	バラエティ	270000
2024-02	女	バラエティ	40000
2024-02	男	ニュース	315000
2024-02	女	ニュース	9000

STEP2. アウトプットを実現するために必要なデータソースを書き出す

アウトプットの整理ができたら、今度はインプットとなるデータソースの整理を行いましょう。

必要なデータソースは要件から読み解くことができます。 今回は「10代のユーザーの月間視聴数（性別 / 動画カテゴリごと）の推移をグラフで見たい」という要件です。 ここから、この分析に必要なエンティティ（実体）とその属性、集計値を抽出しましょう。

• エンティティと属性
  • ユーザー
    • 性別
    • 年代
  • 動画
    • カテゴリ
• 集計値
  • 視聴数

これらのデータを管理するテーブルを、調査やヒアリングを実施して探します。

今回は以下のテーブルを使用することとします。

• user：ユーザー登録に必須な入力項目を管理するテーブル
• user_profile：ユーザーが登録後に設定できる任意の入力項目を管理するテーブル
• video：ユーザーが投稿した動画を管理するテーブル
• video_category：動画のカテゴリを管理するテーブル
• view_log：動画の視聴履歴を管理するテーブル

対象のテーブルを洗い出せたら、ER図を作成してテーブルのリレーションについて理解しておくことも重要です。

STEP3. インプットからアウトプットを実現するデータの加工プロセスを書く

概念設計をする

アウトプットイメージとデータソースが出揃ったら、あとはそれを繋げるプロセスを設計するだけです。

まずは概念設計を行います。

ブロックは、なるべく意味のあるデータの単位で作成します。 エンティティごとに必要な属性を集めて、最後に他のエンティティと結合するイメージです。 今回は、ユーザー、動画ごとにエンティティの属性をまず紐づけてから視聴ログと結合しています。 こうすることで、処理の流れが頭の中で整理しやすくなり、全体として理解しやすいクエリになります。 ブロックにうまく名前を付けられない場合は、処理の流れを変えることを検討しても良いかもしれません。

エンティティの属性による絞り込み処理は、結合するテーブルとの関係、BIツールの機能、クエリのパフォーマンスに問題がなければ、なるべく後段（集計対象の一覧を作った後や集計処理の後）で実施することをおすすめします。 今後、同じ集計を違う切り口で見たいという要望が出た時に対応しやすい汎用性の高いクエリになるからです。 ただ、まずは依頼者の要望の実現を優先すべきなので、あくまでブラッシュアップ観点として意識できると良いかと思います。

1つ1つのブロックの記述が長くなりすぎないようにすることも重要です。 ダイアグラムとして見やすくするという意図もありますが、記述が長くなるということは、そのブロックで色々な処理を行いすぎている可能性があるからです。 1つのブロックに処理を詰め込みすぎると、可読性が低くメンテナンスのしにくいクエリになってしまいます。 今回は、まずユーザーの視聴リストを作成してから、集計処理を別のブロックで実行しています。 こうすることで、結合処理と集計処理を別のCTEで行うため、クエリが読みやすくなります。

また、この段階でレビューするのが重要です。 例えば、今回だと以下のブラッシュアップ観点が挙げられそうです。

• 「最初に10代のユーザーで絞り込んでいるが、最後に絞り込むようにすれば、他の年代の視聴数を確認したいという要望が出た時に、軽微な修正を加えるだけで見れるようになるのではないか。」
• 「ユーザーの年齢で絞り込んでから視聴ログと結合しているが、これだと冪等性が担保されない。動画視聴時点における年齢を判定するほうが良いのではないか。」

レビューによって設計に変更が必要になった場合は、設計図も修正しましょう。 手戻りを最小限に抑えるためにも、概念設計が完了した時点でのレビューは重要です。

論理設計をする

概念設計が完了したら論理設計を行います。

ここでは概念設計を詳細化し、具体的な処理内容、テーブル・カラムの論理名、CTEごとのテスト内容などについて記載します。

結合条件を別ブロックに切り出すと、何と何をどの条件で結合するか分かりやすいです。

論理設計が完了したらレビューを実施しましょう。 CTEやカラムの論理名が「名が体を表す」状態になっているか、処理内容、テスト内容のチェックを行います。 STEP4で後述しますが、CTEで定義しておけば、そのCTEに対して簡単にクエリのテストができます。 「この段階でテスト、データのチェックをしておきたいな。」という基準でブロックを分けるのもオススメです。

物理設計をする

最後は物理設計です。

CTE、カラムの論理名を物理名に変更し、処理内容と結合条件は具体的なSQLの記法に従って記載します。テストに漏れがあれば追加しておきましょう。 この内容をクエリで再現すればOKとなるところまで詳細に詰めることが重要です。

ここで設計段階の最後のレビューを実施しましょう。 CTEやカラムの名前、使用されている関数、結合条件、集計の方法をチェックしましょう。

STEP4. クエリを作成する

クエリの作成は、物理設計をCTEで再現するだけです。 今回設計したクエリは以下のようになります。BigQueryを想定して作成しました。（プロジェクト名やデータセット名の記載は割愛しています。）

WITH

/*
  プロフィール情報を紐づけたユーザー
*/
user_with_profile AS (
  SELECT
    u.user_id
    , up.gender
    , up.birthday
  FROM
    user AS u
  LEFT JOIN
    user_profile AS up
  ON
    u.user_id = up.user_id
)
,

/*
  カテゴリ名を紐づけた動画
*/
decode_video_category AS (
  SELECT
    v.video_id
    , vc.name AS video_category_name
  FROM
    video AS v
  LEFT JOIN
    video_category AS vc
  ON
    v.video_id = vc.video_id
)
,

/*
  ユーザーの視聴履歴
*/
user_views_video AS (
  SELECT
    uwp.user_id
    , uwp.gender AS gender
    , FLOOR(DATE_DIFF(DATE(vl.created_at), uwp.birthday, DAY)/ 365.25) AS age -- 視聴日と誕生日の差分を日単位で取得し、365日で割る（0.25は閏年を考慮した概算）
    , CASE -- TODO: UDF化する
        WHEN uwp.birthday IS NULL
          THEN '不明'
        WHEN (FLOOR(DATE_DIFF(DATE(vl.created_at), uwp.birthday, DAY)/ 365.25)) < 10
          THEN '10歳未満'
        WHEN (FLOOR(DATE_DIFF(DATE(vl.created_at), uwp.birthday, DAY)/ 365.25)) < 20
          THEN '10代'
        WHEN (FLOOR(DATE_DIFF(DATE(vl.created_at), uwp.birthday, DAY)/ 365.25)) < 30
          THEN '20代'
        WHEN (FLOOR(DATE_DIFF(DATE(vl.created_at), uwp.birthday, DAY)/ 365.25)) < 40
          THEN '30代'
        WHEN (FLOOR(DATE_DIFF(DATE(vl.created_at), uwp.birthday, DAY)/ 365.25)) < 50
          THEN '40代'
        WHEN (FLOOR(DATE_DIFF(DATE(vl.created_at), uwp.birthday, DAY)/ 365.25)) < 60
          THEN '50代'
        WHEN (FLOOR(DATE_DIFF(DATE(vl.created_at), uwp.birthday, DAY)/ 365.25)) < 70
          THEN '60代'
        WHEN (FLOOR(DATE_DIFF(DATE(vl.created_at), uwp.birthday, DAY)/ 365.25)) < 80
          THEN '70代'
        WHEN (FLOOR(DATE_DIFF(DATE(vl.created_at), uwp.birthday, DAY)/ 365.25)) < 90
          THEN '80代'
        WHEN (FLOOR(DATE_DIFF(DATE(vl.created_at), uwp.birthday, DAY)/ 365.25)) < 100
          THEN '90代'
        ELSE '100歳以上'
      END AS age_group
    , dvc.video_id
    , dvc.video_category_name
    , vl.created_at AS view_dt
    , DATE_TRUNC(DATE(vl.created_at), MONTH) AS view_month_date
  FROM
    view_log AS vl
  LEFT JOIN
    user_with_profile AS uwp
  ON
    vl.user_id = uwp.user_id
  LEFT JOIN
    decode_video_category AS dvc
  ON
    vl.video_id = dvc.video_id
)
,

/*
  10代ユーザーの年月、性別、動画カテゴリごとの視聴数の集計
*/
agg_user_10s_views_video AS (
  SELECT
    view_month_date
    , gender
    , video_category_name
    , COUNT(*) AS view_cnt
  FROM
    user_views_video
  WHERE age_group = '10代'
  GROUP BY ALL
)
,

test__user_with_profile AS (
  SELECT
    COUNT(*) AS cnt
    , COUNT(CASE WHEN birthday IS NULL THEN 1 ELSE NULL END) AS gender_null_cnt
    , COUNT(CASE WHEN gender IS NULL THEN 1 ELSE NULL END) AS gender_null_cnt
    , COUNT(CASE WHEN birthday IS NULL THEN 1 ELSE NULL END) / COUNT(*) * 100 AS birthday_null_percentage
    , COUNT(CASE WHEN gender IS NULL THEN 1 ELSE NULL END) / COUNT(*) * 100 AS gender_null_percentage
  FROM
    user_with_profile
)

SELECT * FROM agg_user_10s_views_video

設計段階で記載しておいたテストも「test__user_with_profile」というCTEで再現しています。 分析クエリの最終結果は「agg_user_10s_views_video」というCTEに記載しておき、テストケースごとにCTEを用意します。 テストしたい場合は、最後のSELECTのFROMをテストのCTEに変更します。 クエリの結果を見たい時は、「agg_user_10s_views_video」に変更します。 こうすることで、開発を進めながら必要なタイミングで気軽にテストができますし、テスト用のクエリを毎回書き直して実行するといった手間も省くことができます。

テストをCTEの名前で管理することで、以下のようにテスト結果をシートで管理しやすくなるという副次的な効果もあります。

#	テスト内容	テストCTE名	結果	証跡
1	性別や誕生日情報がないユーザーがどれぐらいいるか	test__user_with_profile	全体の約3%	スクリーンショットを貼る
2	…	…	…	…

おわりに

データ分析で用いるSQLクエリの設計方法についてご紹介しました。

ぜひ、ご参考にしていただければ幸いです。