こんにちは、HuluよりNetflix派の田中です（ロゴがお洒落）。

みなさん、SQL書いてますか？

私自身は未だに使う機会が多いことからもなかなか枯れない技術の1つだなと感じながら、日々愛着を持って書かせてもらってます。

ただ、触り始めた頃はわかってないことが多く、プログラミング言語とは勝手が大きく違うことからも、ある程度書けるようになるまでは時間を要した記憶があります。

正直それまでは、あまり好きにもなれませんでした。

今回はそんな経験から、今では好きと言えるレベルになっている「SQLとの付き合い方」をまとめてみました。

クラウドワークス開発におけるSQLとの関わり方

始めに、普段の開発でのSQLとの関わり方を簡単に紹介しておきます。

クラウドワークスの開発ではO/Rマッパー（Ruby on RailsのActiveRecord）に任せることが多く、素のSQLはさほど出てきません。

ただ、日常的なデータ分析や抽出作業、または分析基盤の裏側等ではゴリゴリのSQLが必要になることはままある状況です（エンジニア以外の分析チームやマーケティング関係のメンバーもSQLを書きます）。

RDBMSとしてはMySQL、DWHとしてはRedshiftを用いており、ベーシックなSQLを用いつつそれぞれの特性を活かした関数や拡張仕様を利用したりしなかったりしています。

書き方でだいぶ変わるSQL

触り始めた頃

私がSQLを触り始めた頃の書き方は、確かこんな感じでした。

ユーザー情報がはいっているusersテーブルから、nameに「tanaka」が含まれているユーザーをとってくる。

select * from users where name like '%tanaka%';

nameに「tanaka」が含まれているユーザーをとってこれます。

男性に絞りましょう（gender=1が男性とする）

select * from users where name like '%tanaka%' and gender = 1;

nameに「tanaka」が含まれており、genderが「1」のユーザーをとってこれます。

このユーザーの住所情報がuser_detailsテーブルにはいってるので、prefecureが「tokyo」か「hokkaido」の人だけに絞りnameとcityだけを出力してみましょう。

select users.name, user_details.prefecture from users
inner join user_details on users.id = user_details.user_id
where users.name like '%tanaka%' and users.gender = 1 and (user_details.prefecture = 'tokyo' or user_details.prefecture = 'hokkaido');

たぶん、nameに「tanaka」が含まれており、genderが「1」、prefectureが「tokyo」か「hokkaido」のユーザーのnameとcityをとってこれます。

ただ、明日の朝さらに条件を加えることになったらちょっと嫌な感じです。

なんか読みにくいし、どっか間違えそう。

今だったらどう書くか

恥ずかしさはさておき、今だったらどう書くかを考えてみます。

ユーザー情報がはいっているusersテーブルから、nameに「tanaka」が含まれているユーザーをとってくる。

SELECT
  *
FROM
  users
WHERE
  name LIKE '%tanaka%'
;

男性に絞りましょう。

SELECT
  *
FROM
  users
WHERE
  name LIKE '%tanaka%' AND
  gender = 1
;

このユーザーの住所情報がuser_detailsテーブルにはいってるので、prefectureが「tokyo」か「hokkaido」の人だけに絞りnameとcityだけを出力してみましょう。

SELECT
  users.name,
  user_details.city
FROM users
  INNER JOIN user_details ON users.id = user_details.user_id
WHERE
  users.name LIKE '%tanaka%' AND
  user_details.gender = 1 AND
  (
    user_details.prefecture = 'tokyo' OR
    user_details.prefecture = 'hokkaido'
  )
;

おそらく、nameに「tanaka」が含まれており、genderが「1」、prefectureが「tokyo」か「hokkaido」のユーザーのnameとcityをとってこれます。

少なくとも前述のSQLよりは条件見直しもしやすそうです。

細かな部分は個人の好みもあろうとは思いますが、

構文は大文字にする
適切な箇所で改行とインデントをとる

これだけでぐっと読みやすく、メンテしやすい書き方になります。

また、このような書き方にすると

SELECT
  users.name, -- ユーザ名
  user_details.city -- 市区町村
FROM users
  INNER JOIN user_details ON users.id = user_details.user_id
WHERE
  name LIKE '%tanaka%' AND
  gender = 1 AND -- 男性
  (
    user_details.prefecture = 'tokyo' OR
    user_details.prefecture = 'hokkaido'
  ) -- 東京 OR 北海道 在住
;

のように行ごとにコメントを入れやすくなり、レビュー依頼や今後の条件追加時など、心穏やかでいられることでしょう。

知ってるとうれしいSQL

さきほどのSQLですが、最後の条件式は実はIN句を使うとORを使うことなく、かつ対象が増えても辛くならない感じにできます。

SELECT
  users.name, -- ユーザ名
  user_details.city -- 市区町村
FROM users
  INNER JOIN user_details ON users.id = user_details.user_id
WHERE
  name LIKE '%tanaka%' AND
  gender = 1 AND -- 男性
  prefecture IN ('tokyo', 'hokkaido') -- 東京 OR 北海道 在住
;

あとは、もしユーザー名のみを出力すれば良いのであれば、以下のようにJOINを使わずに、EXISTSでサブクエリによる存在チェックをおこなう形に書き換えることもできます。

SELECT
  users.name -- ユーザ名
FROM
  users
WHERE
  name LIKE '%tanaka%' AND
  gender = 1 AND -- 男性
  EXISTS (
    SELECT 1 FROM
      user_details
    WHERE
      user_id = users.id AND prefecture IN ('tokyo', 'hokkaido')
  ) -- 東京 OR 北海道 在住
;

構文や関数の存在を知っているだけで、よりメンテしやすいSQLにできたり、求めるパフォーマンスに合わせたチューニングをできたりします。

今回は詳細省きますが、条件分岐が使える「CASE」、グループ集計時に取得条件を指定できる「GROUP BYでのHAVING」、複数のカラムから値があれば1つだけ取得したいときなどに使える「COALESCE」あたりは普段からよく利用しています。

重宝する系でいくと、MySQLはダメですがRedshiftやその他主要DBで使える系で、結果を順位付けして取得条件にも使える「ROW_NUMBER」があります。

例えばあるユーザーが前回購入した商品だけを取りたいときなどに使えます。

SELECT
  *
FROM
  (
    SELECT
      users.*,
      user_items.*,
      ROW_NUMBER () OVER (
        PARTITION BY users.id -- ユーザー単位
        ORDER BY user_items.bought_at DESC -- 購入日が新しい順
      ) AS row_number
    FROM users
      LEFT OUTER JOIN user_items ON users.id = user_items.user_id
  )
WHERE
  row_number = 1 -- 前回購入した商品（最新の1件）
;

ユーザー情報と前回購入した商品情報がユーザーごとに1行ずつ返ってくるはずです。

そのほか、JOIN時の複数レコードの特定の値をまとめて1つのカラムとして返してくれる「LISTAGG」は、例えばあるユーザーが興味のあるカテゴリを一覧して見たいときなどに使えます。

SELECT
  users.id,
  LISTAGG(user_categories.name, ',') -- カテゴリ名をカンマ区切りでまとめる
FROM users
  INNER JOIN user_categories ON users.id = user_categories.user_id
GROUP BY 1
;

ユーザーIDとカテゴリ名（カンマ区切り）がユーザーごとに1行ずつ返ってくるはずです。

とりあえず知っておければ、複雑な分析をおこなう際など活躍してくれる場面があるんじゃないかと思います。

最後に

ある程度SQLを使えるようになってくると、「こんなことやあんなことSQLでできないのか…」という感じでいろいろと夢が膨らんできます。

そこまでいけばSQLを好きになったも同然で、良い付き合いができていくと思います。

個人的には生年月日から年齢を算出するSQLとか好きです。

MySQLだと以下のように書けます。

SELECT
  CAST(
    (CURRENT_DATE - birthdate) / 10000
  AS UNSIGNED) -- 年単位で引き算して小数切り捨て
FROM
  users
;

なお、やりたいことによっては、SQLだけに頼らずJupyter + Pandasのようなツールに頼った方がぐっと楽に実現できるケースもあると思います。

効率的な分析、データ抽出を極めていきたい人はこちらもオススメです。

engineer.crowdworks.jp

We're Hiring!

クラウドソーシングのクラウドワークスでは、がんばらずに済むように効率化や良いやり方の模索に情熱を燃やせるエンジニアを募集中です。

少しでも気になったらお気軽に「話を聞きに行きたい」をポチッとしてみてください。

クラウドワークスエンジニアブログ

日本最大級のクラウドソーシング「クラウドワークス」の開発の裏側をお届けするエンジニアブログ

SQLとの付き合い方