SQLで顧客の問いに答える

目標

他人のスキーマの上で、顧客の質問をクエリに置き換え、エラーなしに間違う落とし穴を避けられるようになります。

なぜ重要なのか

文法が間違ったクエリはすぐに知らせてくれますが、危険なのは実行されるクエリです。顧客の前で読み上げた数字が間違っていたなら、そのプロジェクトでその後に話すすべての数字が疑われます。

このラボは、そのうちの2つを自分で体験させます。COUNT(*)とCOUNT(열)の違い(プレースホルダーは列名です)です。後者は、その列がNULLでない行だけを数えます。そして、ない行は結合されないということです。チケットが一度もない顧客はINNER JOINの結果にまったく現れないので、そうした顧客を数えるには、LEFT JOINのあとでNULLを探すか、NOT EXISTSを使わなければなりません。

もう1つ。NOT INサブクエリにNULLが1つでもあると、全体の結果が0件になりますが、エラーも警告もありません。ビジネス的にもっともらしい結論が、そのままレポートに載ります。習慣的にNOT EXISTSを使うほうが安全です。

スキーマ

customers(id, name, plan, signed_at): planの値はfree / pro / enterpriseです
orders(id, customer_id, amount, status, created_at): statusの値はpaid / pending / refundedです
tickets(id, customer_id, severity, opened_at, closed_at): 閉じていないチケットはclosed_atがNULLです

ステップ

/root/sqlを作成し、/opt/data/support.sqlを/root/sql/support.dbにロードしてください。
全顧客数を/root/sql/q1.txtに書いてください。
statusがpaidの注文の金額の合計を/root/sql/q2.txtに書いてください。
確定売上が最も大きい顧客の名前を/root/sql/q3.txtに書いてください。
まだ閉じていないチケットの数を/root/sql/q4.txtに書いてください。
SELECT COUNT(closed_at) FROM tickets;の値を/root/sql/q5.txtに書いてください。
チケットを一度も開いたことがない顧客の数を/root/sql/q6.txtに書いてください。
確定売上の合計が最も大きい料金プラン名を/root/sql/q7.txtに書いてください。

参考

ロード: sqlite3 /root/sql/support.db < /opt/data/support.sql
照会: sqlite3 /root/sql/support.db "SELECT COUNT(*) FROM customers;"
よくあるミス1: ステップ5でclosed_at = ''で比較することです。NULLはどんな比較とも真にならないので、IS NULLを使う必要があります。
よくあるミス2: ステップ8で、結果が直感と違うからといってクエリを疑うことです。料金プランの等級と売上の規模は別の話で、この事実を顧客に説明すること自体が価値のある発見です。

スナップショットをロードする

/root/sqlを作成し、/opt/data/support.sqlを/root/sql/support.dbにロードしてください。

sqlite3は標準入力でSQLを受け取ります。/opt/data/support.sqlを/root/sql/support.dbにロードしてください。

全顧客数を数える

全顧客数を/root/sql/q1.txtに書いてください。

最も単純な質問から始めます。この数字が、以降のすべての割合の分母になります。

確定売上の合計を求める

statusがpaidの注文の金額の合計を/root/sql/q2.txtに書いてください。

すべての注文が売上ではありません。まず、status列にどんな値があるかを見てください。

最大売上の顧客を探す

確定売上が最も大きい顧客の名前を/root/sql/q3.txtに書いてください。

顧客名が必要なので、結合が必要です。確定売上を基準にグループ化して並べ替えてください。

未解決のチケット数を数える

まだ閉じていないチケットの数を/root/sql/q4.txtに書いてください。

閉じていないチケットは、closed_atがNULLです。空文字列とNULLは違うので、IS NULLを使う必要があります。

COUNT(closed_at)の値を求める

SELECT COUNT(closed_at) FROM tickets;の値を/root/sql/q5.txtに書いてください。

COUNT(列)は、その列がNULLでない行だけを数えます。全体が80なのに、この値がなぜ違うのかを考えてみてください。

チケットがない顧客を数える

チケットを一度も開いたことがない顧客の数を/root/sql/q6.txtに書いてください。

ない行は結合されません。LEFT JOINのあとで右側がNULLのものを数えるか、NOT EXISTSを使ってください。

料金プラン別の売上1位を探す

確定売上の合計が最も大きい料金プラン名を/root/sql/q7.txtに書いてください。

料金プランでグループ化して、確定売上を足します。結果が予想と違うかもしれませんが、それがこのステップの要点です。