💭

分析SQL最初の一歩.シンプルなクエリを書くためにテーブル定義書から読み取るべきこと

ありた

2023/10/13に公開

内容：
SQLの無駄を省くこと。SQLを書き始める前に、テーブル定義書から読み取るべきこと。
対象者：
分析用SQLを初めて書く人、あるいはいつも書いているSQLを見直したい人。
RDS等より高度なチューニングが必要なケースには合わない場合があります。

始めに

初めまして、istyleのありたです。
今期Tech部門から事業部門のデータ戦略推進室というところに移動しまして、日々Queryの山に追われています。

Queryを書く上で大切なことは色々ありますが、

想像力( = データ構造や処理工程を正確に思い描けること)
シンプルさ( = 冗長にならないこと)

この2つは特に重要な要素です。

データ戦略推進室では今期からモデリングという工程を取り入れ、
クエリを書き始める前に先に大まかなイメージ設計を書き起こすということを始めています。

Queryの設計においては、テーブル定義書からどれだけの情報を読み取れるかが最初の勝負です。

では、具体的に定義書から何を読み取ればよいのか？
そのヒントの1つとして、簡単な間違い探しクイズを始めていきたいと思います。

実際にやってみよう

Q.以下のQueryからおかしなところを探してください。

■お題：ユーザの購買毎の合計金額がわかるデータを作成してください。

WITH pre AS(
SELECT DISTINCT
  order_id,
  customer_id,
  COALESCE(amount, 0) AS amount
FROM
  order
WHERE
  order_id IS NOT NULL AND customer_id IS NOT NULL
)

SELECT
  order_id,
  customer_id,
  SUM(amount) AS total_amount
FROM
  pre
GROUP BY
  order_id,
  customer_id
;

妙に長いですね。
次にこのテーブルのDDLを載せます。

CREATE TABLE order
(
  order_id INTEGER PRIMARY KEY, -- 注文ID
  customer_id INTEGER NOT NULL, -- 顧客ID
  amount INTEGER DEFAULT 0 -- 購買金額
);

注：ソーステーブルのDDLを取得できない場合^[1]

答え合わせ

さて、間違いはいくつ見つかりましたか？
では、答え合わせです。

WITH pre AS(
SELECT DISTINCT -- [1]:order_idがPK = Distinctしても結果は変わらない
  order_id,
  customer_id,
  COALESCE(amount, 0) AS amount -- [2]:default値で0埋めが既にされている = 不要
FROM
  order
WHERE
  order_id IS NOT NULL AND customer_id IS NOT NULL -- [3]:order_idはPK&customer_idはIS NOT NULL = NULLはどちらも元から含まれない
)

SELECT
  order_id,
  customer_id,
  SUM(amount) AS total_amount -- [4]:amountは購買毎の合計額 = 足し合わせは不要
FROM
  pre
GROUP BY
  order_id,
  customer_id
;

テーブルの要素：

PK
そのテーブルにおけるレコードのユニーク性を担保するKEYであり、重複及び欠損(NULL)は起こらない。
NOT NULL
予めそのカラムにNULLが入らないことを保証する(NULLをInsertしようとするとはじかれる)。
default値
NULLをInsertしようとしたときに代入される値。結果default値で埋められることでそのカラムにNULLは存在しない。

テーブルの構造とそこから読み取る意味：

本テーブルはorder_id：1購買 = 1レコード
amountは必然的に1購買当たりの合計額となる。よって集計は不要。

ここまで理解できていれば、クエリの構造は全く変わりますね。

SELECT order_id, customer_id, amount FROM order;

必要なのはこれだけです。
いかがでしたでしょうか。

あたりまえだよ、と思われた方も多いかもしれませんが、案外普段のクエリを見返してみるとこうした無駄が見つかることもあります。
自戒も込めて気を付けていきたいですね。

以上です。

脚注

※外部のRDBからBigQuery, Redshift等分析DBにデータ連携される場合、実際に参照するテーブルからはPrimary key(以下PK)やNULL NOT NULLが正確に読み取れない場合があります。
その場合元のDBの定義を参照することがベストですが、何らかの理由でできない場合は以下のようなCOUNT文を用いて凡そのPKやNULL有無を確認することができます。
[1]と[2]が一致する場合、そのカラムは(高い確率で)PKです。
[1]と[3]が一致する場合、そのカラムはNOT NULLです。
SELECT
COUNT(1), -- 総レコード数:[1]
COUNT(DISTINCT order_id) -- NULLや重複が含まれるかどうか:[2]
COUNT(customer_id) -- NULLが含まれるかどうか:[3]
FROM
order
; ↩︎

株式会社アイスタイルPublication

コスメ・美容の総合サイト@cosme（アットコスメ）や関連サービスの企画・運営をしています。

始めに

実際にやってみよう

答え合わせ

Discussion