以前会社のブログでSQLを学習するための記事を書いたのですが
・書籍を読んだり、演習問題が多いので時間が掛かる
・自走できる人じゃないと中々やり遂げられない
と感じていました。

そこで今回はそういった人でも1日で最低限のクエリが書けるようになる方法をご紹介します。
弊社のデータ分析職における5日間の選考インターンでは、初日にSQLを学習して残りの4日間でサービスの分析をして発表まで行うという内容になっているので、主にその初日に取り組んでもらっている部分になります。

SQLの概要の説明

まずSQLにおける最低限の概念や文法を理解してもらいます。社内では以前非エンジニア向けのSQL勉強会に使っていた資料を見てもらいつつ、疑問に思った点を調べるリファレンスとして 10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く を参照してもらっています。

データベースとは

SQLとは

テーブル定義

このセクションで使うテーブルは以下の2つで、データベースはRedshiftを想定しています。

基本構文（SELECT / FROM / WHERE / LIMIT）

SQLの基本の構文は以下のように、SELECTの後にカラムや集計項目を指定、FROMの後にテーブルを指定、WHEREの後に条件を指定します。
分析用のDBには膨大な数のレコードが入っていることが多いので、LIMITを付けておくと、思いがけずにテーブルのレコードを全件取得してしまったなどの事態を防げるので、慣れるまではLIMITは付けておくようにするのがベターです。

SELECT
    user_id,                               -- 抽出するレコードのカラムや集計項目を指定
    created_at
FROM
    logins                                 -- レコードを抽出するテーブルを指定
WHERE
    created_at >= '2019-08-01 00:00:00'    -- 抽出するレコードの条件を指定 
    AND created_at < '2019-08-02 00:00:00'
LIMIT
    100

ソートキー（or パーティション）

Redshiftにはカラムにソートキーというものが設定されています（BigQueryやAthenaでは似たような概念としてパーティションというものが存在します）。これを指定してSQLを書かないとクエリの速度が低下したり、DBに余分な負荷が掛かってしまうので、必ずソートキーの指定をWHERE句の条件に入れるようにしてください。

なんだか難しそうな気がしますが、初めの方はおまじないと思ってくれれば大丈夫です。上のクエリでは created_at がソートキーに相当します。（どのカラムがソートキーに設定されているかはエンジニアの方に聞けば教えてくれるでしょう。）

WHERE
    created_at >= '2019-08-01 00:00:00'    -- 抽出するレコードの条件を指定 
    AND created_at < '2019-08-02 00:00:00'

レコードの件数を求める（COUNT / DISTINCT）

SELECTの中でCOUNTを使用するとレコードの数をカウントすることができます。重複を排除してカウントしたい場合はDISTINCTを付けます。

SELECT
    COUNT(user_id) AS num,
    COUNT(DISTINCT user_id) AS uu
FROM
    logins
WHERE
    created_at >= '2019-08-01 00:00:00'
    AND created_at < '2019-08-02 00:00:00'
LIMIT
    100

複数のテーブルを結合する（JOIN）

テーブルは役割によって複数存在していて、カラムの内容もそれぞれのテーブルによって異なります。そこで複数のテーブルを結合してデータを集計する場合に使うのがJOINになります。JOINの後に結合するテーブル名を指定し、ONの後に結合条件を指定します。
JOINにはINNNER JOINやLEFT JOINなど種類がありますが、ここでは割愛します。

-- usersテーブル内のOS情報によって,iOSのユーザーだけカウントする

SELECT
    COUNT(logins.user_id) AS num,
    COUNT(DISTINCT logins.user_id) AS uu
FROM
    logins
JOIN
    users
ON
    users.user_id = logins.user_id
WHERE
    logins.created_at >= '2019-08-01 00:00:00'
    AND logins.created_at < '2019-08-02 00:00:00'
    AND users.os = 'ios'
LIMIT
    100

レコードを集計する（GROUP BY）

日付や週などのまとまった単位での集計をする場合にはGROUP BYを使用します。集計する項目は1つだけではなく、複数の項目を指定しても集計することができます。

SELECT
    DATE(created_at) AS date,
    COUNT(user_id) AS num,
    COUNT(DISTINCT user_id) AS uu
FROM
    logins
WHERE
    created_at >= '2019-08-01 00:00:00'
    AND created_at < '2019-09-01 00:00:00'
GROUP BY
    date
LIMIT
    100

実際に演習問題を解く

以上でSQLの簡単な概要は理解してもらったと思うので、次に演習問題をこなしてもらいます。SQLも言語なのでやはり使わないと覚えることはできません。いろいろなクエリを書きつつ、覚えていきましょう。
上記の説明はだいぶ簡略化しているので、疑問に思った点などは書籍やウェブサイトなどで調べて補完するとよいです。

問1 ~ 問4が基本問題、問5 ~ 問7が発展問題となっています。

テーブル定義

以下のRedshift上のサンプルテーブルを想定してください。

演習問題

問1.
logins テーブルにどのようなデータが保存されているか確認してください。LIMITをつけるのを忘れずに実行しましょう。

問2.
2019年8月1日のDAU（Daily Active User）を算出してください。
DAU = 2019-08-01 00:00:00 ~ 2019-08-01 23:59:59までにログインしたユニークユーザー数

問3.
2019年8月1日にアプリをインストールしたユーザーの2019年8月2日のDAUを算出してください。

問4.
2019年8月1日から7日までの、OSごとのDAUを算出してください。

問5.
2019年8月1日の記事ごとにCTRを計算してCTRが高い順に抽出してください。ただしインプ・クリックはともに1記事に対して、1ユーザー1回までとして計算してください（ログは1記事に対して1ユーザーで複数存在する場合があります）。

問6.
2019年8月1日に登録したユーザーの7日後継続率を算出してください。
7日後継続率 = 登録日から7日後にログインしたユニークユーザー数 / 登録ユニークユーザー数

問7. 
2019年8月1日にログインしたユーザーの平均記事クリック数を計算してください。1つの記事に対してユーザーが複数回クリックしても1回とカウントしてください。

解答

値の算出方法は一通りではないですが、参考までに載せておきます。

問1.

SELECT
    *
FROM
    logins
WHERE
    created_at >= '2019-08-01 00:00:00'
    AND created_at < '2019-08-02 00:00:00'
LIMIT 100

問2.

SELECT
    COUNT(DISTINCT user_id) AS uu
FROM
    logins
WHERE
    created_at >= '2019-08-01 00:00:00'
    AND created_at < '2019-08-02 00:00:00'

問3.

SELECT
    COUNT(DISTINCT logins.user_id) AS uu
FROM
    logins
JOIN
    users
ON
    logins.user_id = users.user_id
WHERE
    logins.created_at >= '2019-08-02 00:00:00'
    AND logins.created_at < '2019-08-03 00:00:00'
    AND users.created_at >= '2019-08-01 00:00:00'
    AND users.created_at < '2019-08-02 00:00:00'

問4.

SELECT
    DATE(logins.created_at) AS date,
    users.os,
    COUNT(DISTINCT logins.user_id) AS uu
FROM
    logins
JOIN
    users
ON
    logins.user_id = users.user_id
WHERE
    logins.created_at >= '2019-08-01 00:00:00'
    AND logins.created_at < '2019-08-08 00:00:00'
GROUP BY
    date,
    users.os

問5.

SELECT
   clicks.article_id,
   clicks.click_uu,
   imps.imp_uu,
   1.0 * clicks.click_uu / imps.imp_uu as ctr
FROM (
 SELECT
     article_id,
     COUNT(DISTINCT user_id) as click_uu
 FROM
     clicks
 WHERE
     created_at >= '2019-08-01 00:00:00'
     AND created_at < '2019-08-02 00:00:00'
 GROUP BY
     article_id
) AS clicks
JOIN (
   SELECT
       article_id,
       COUNT(DISTINCT user_id) as imp_uu
   FROM
       impressions
   WHERE
     created_at >= '2019-08-01 00:00:00'
     AND created_at < '2019-08-02 00:00:00'
   GROUP BY
       article_id
) AS imps
ON clicks.article_id = imps.article_id
ORDER BY ctr DESC

問6.

SELECT
   COUNT(DISTINCT u.user_id) AS register_number,
   COUNT(DISTINCT l.user_id) AS active_number,
   1.0 * COUNT(DISTINCT l.user_id) / COUNT(DISTINCT u.user_id) AS retention_rate
FROM
   users AS u
LEFT JOIN (
   SELECT
       DISTINCT user_id
   FROM
       logins
   WHERE
       created_at >= '2019-08-08 00:00:00'
       AND created_at < '2019-08-09 00:00:00'
) AS l
ON
   u.user_id = l.user_id
WHERE
   u.created_at >= '2019-08-01 00:00:00'
   AND u.created_at < '2019-08-02 00:00:00'

問7.

SELECT
   daily_logins.user_number,
   daily_clicks.click_number,
   1.0 * daily_clicks.click_number / daily_logins.user_number AS average_click_number
FROM (
   SELECT
       COUNT(DISTINCT user_id) AS user_number
   FROM
       logins
   WHERE
       created_at >= '2019-08-01 00:00:00'
       AND created_at < '2019-08-02 00:00:00'
) AS daily_logins
JOIN (
   SELECT
       SUM(click_number) AS click_number
   FROM (
       SELECT
           user_id,
           COUNT(DISTINCT article_id) AS click_number
       FROM
           clicks
       WHERE
           created_at >= '2019-08-01 00:00:00'
           AND created_at < '2019-08-02 00:00:00'
       GROUP BY
           user_id
   )
) AS daily_clicks
ON 1 = 1

おわりに

演習問題は以上になります。繰り返しになりますが、いかに手を動かして書くかというアウトプットの部分をしっかりやることがSQLを書けるようになる近道です。

エンジニア職ではない人がSQLに入門する上での参考になれば幸いです。