この記事は、2022年7月15日に掲載した「エンジニアへの挑戦状 #4 新型ウイルス感染者数をSQLで抽出」の解説です。まだ問題を解いていない方は、こちらからご覧ください。
あなたのSQL基礎力を測る問題でした。所要時間は3～5分としましたが、SQLに親しんでいる方なら、1分足らずで解けたかもしれません。迷わずに正しいSQLを選ぶことができましたか？
設問1の解答・解説
設問1は、日本全国で感染者数が多かった日、その日の全国での感染者数、その日の東京での感染者数の値を取得する問題でした。ちなみに、3つの選択肢のうち、正解は2つあります。SQLでの集計は柔軟に対応できるので、複数の方法でも同じ結果を得ることができるからです。それぞれの選択肢を一つずつ見ていきましょう。
【選択肢1】
case_date,
all_case,
tokyo
FROM
covid19
ORDER BY all_case DESC
LIMIT 1;
SQLは上から順に読むことができます。SELECTを用いて、発症日(case_date)、全国での感染者数(all_case)、東京での感染者数(tokyo)のデータを取得しています。FROMに続くcovid19はデータが格納されているテーブル名です。ORDER BYはデータを並び替えるときに使う機能です。この選択肢では、all_caseで並び替えをしています。列名の後ろにDESCをつけると降順に並び替え、何もつけない場合は昇順に並び替えます。最後の行にあるLIMIT 1で、データを1行だけ取得するようになります。
つまり、【選択肢1】のSQLは、全国での感染者数を昇順に並び替えた上で、最初の1行のみを取得して、発症日、全国での感染者数、東京での感染者数を表示させています。
よって、【選択肢1】のSQLは目的どおりのデータを取得できます。
【選択肢2】
case_date,
all_case,
tokyo
FROM
covid19
WHERE
all_case = max(all_case)
;
【選択肢2】のSQLは、SELECTとFROMは【選択肢1】と同じです。WHERE以降が異なります。WHEREでは、どの行のデータを取得するかの条件を書くことができます。全国での感染者数が最大の日を取得したいので、all_caseをWHEREに使うのは正しいです。しかし、WHEREの中で最大値を計算するmax関数を使うことができません。
よって、【選択肢2】のSQLは誤りです。
【選択肢3】
case_date,
all_case,
tokyo
FROM
covid19
WHERE
all_case = (
SELECT
max(all_case)
FROM
covid19
)
;
【選択肢3】のSQLは、WHEREにあるall_caseまでは【選択肢2】と同じです。WHEREの中で集計ができないので【選択肢2】のSQLは実行できませんでしたが、例外があります。SELECTを重ねてデータ取得を複数回実行する方法で、サブクエリと呼ばれます。【選択肢3】のサブクエリは以下の箇所で、全国での感染者数の最大値をmax関数で取得しています。
max(all_case)
FROM
covid19
よって、【選択肢3】のSQLは、次のような意味になります。
case_date,
all_case,
tokyo
FROM
covid19
WHERE
all_case = 全国での感染者数の最大値
;
つまり、全国での感染者数が最大となる行を条件として、発症日、全国での感染者数、東京での感染者数を取得するSQLになっており、目的通りのデータを取得できます。
よって、正解は【選択肢1】と【選択肢3】です。
設問2の解答・解説
設問2は、感染者数が100番目に多かった日を調べる問題でした。ROW_NUMBERというウィンドウ関数を使うことで、並び替えた結果に対して連番を付与することができます。OVERの中身に並び替えを記述します。感染者数の多い順に並び替えるので、「ORDER BY all_case DESC」となります。「as number」でnumberという列名を付けています。
case_date,
all_case
FROM (
SELECT
case_date,
all_case,
ROW_NUMBER() OVER(ORDER BY all case DESC) as number
FROM
covid19
)
WHERE number = 100
;
いかがでしたか。どちらも正解していましたか？SQLを駆使すると大きなデータから目的のデータを高速に取り出すことができるようになります。ぜひSQLのスキルを身につけてみてください。
早川 敦士さん
株式会社DATAFLUCTで事業責任者（プロダクトマネージャー）として従事。データと機械学習をビジネスに活かすプロダクトを開発している。また、株式会社ホクソエムで執行役員を務めたり、プログラミング教育に携わる。新卒でリクルートコミュニケーションズに入社しWeb広告やマーケティングオートメーションなどのB2Cマーケティングを経験し、その後ユーザベースにてB2BマーケティングプラットフォームであるFORCASのクローラーやデータ分析基盤を開発するチームリーダーをする。大学在学中に『データサイエンティスト養成読本』（技術評論社刊）を共著にて執筆。その後も『機械学習のための特徴量エンジニアリング』（オライリー・ジャパン刊）や『Pythonによるはじめての機械学習プログラミング』（技術評論社刊）などで執筆活動を続けている。YouTubeチャンネル『データサイエンティストgepuro』で動画を投稿。
Twitterアカウント
▼今回解けた方も、解けなかった方も、他の挑戦状にチャレンジ！
・エンジニアへの挑戦状 #01 プログラム修正問題
・エンジニアへの挑戦状 #02 Pythonでお宝探し
・エンジニアへの挑戦状 #03 テストデータ作成問題
