データ分析でよく使うSQL構文まとめ【基礎編】

こんにちは。

今日はSQLによるデータ分析でよく使うクエリ構文をまとめてみようと思います。

ビッグデータから自分の扱いやすいデータを抽出・集計するのにSQLは必須ですので、忘れないように整理してみようと思います。

RDBMSにはPostgreSQLを使いますが、方言は極力避けて汎用性の高い構文を扱います。

【準備】PostgreSQLのインストール

PostgreSQLの準備はこちらをご参照ください。

【準備】サンプルデータ 東京都のCOVID-19感染者数

こちらの記事でダウンロードした東京都のCOVID-19(新型コロナウイルス)陽性患者の詳細データを使用します。

テーブル名はcovで、使用するカラムは以下の5列です。

CSVの列名	SQLのカラム名	データ型
No	id	INTEGER
公表_年月日	date	DATE
患者_年代	age_class	VARCHAR
患者_性別	gender	VARCHAR
退院済フラグ	ex_flag	BOOLEAN

SQL記述ルール

SQL文の書き方ルールです。

• 文の一番最後はセミコロン;が必要です。改行しても、;が来るまでは実行されません。
• 大文字小文字は区別されません。このブログではキーワード(予約語)は大文字で書きます

最初に基本的な操作おさらい

データベースの接続

$ psql -U penta test

userpentaでDBtestに接続します。

スキーマの選択

SET SEARCH_PATH TO private;

privateスキーマにパスを通します。

これをしないと、テーブルの選択にprivate.covといった操作が毎回必要になります。

テーブル一覧

\dt

テーブル名の変更

ALTER TABLE cov2 RENAME TO cov;

わかりやすく操作しやすい名前に変えておきます。(covid-19のcovです。)

テーブルの構造(スキーマ)確認

\d cov

Column、Type、Nullableなどが確認できます。

コメントアウト

/* 改行可能なコメント */
-- 1行コメント

SELECT : 列の選択・集計

全ての列を選択(*)

SELECT * FROM cov LIMIT 5;

*で全ての列を選択して表示します。FROMでテーブル名を、LIMITで上位件数を指定します。

 id |    date    | age_class | gender | ex_flag 
----+------------+-----------+--------+---------
  1 | 2020-01-24 | 40代      | 男性   | t
  2 | 2020-01-25 | 30代      | 女性   | t
  3 | 2020-01-30 | 30代      | 女性   | t
  4 | 2020-02-13 | 70代      | 男性   | t
  5 | 2020-02-14 | 50代      | 女性   | t

表示する列の選択と集計

SELECT
  COUNT(id) AS id_count,
  COUNT(ex_flag) AS ex_count 
FROM cov;

*ではなく列名を書くとその列が表示されます。 COUNTでその列のデータ数をカウントします。 AS句で別名を付けて表示させます。 ダブルクオーテーション"列名"で括ると日本語の名前をつけられます。

 id_count | ex_count 
----------+----------
   119974 |   111806

全体(idカウント)に比べて退院した人が少ないという結果なので合ってそうです。

集計はCOUNT(レコード数)の他にSUM(合計)、AVG(平均)、MAX(最大値)、MIN(最小値)などが使えます。これらを集約関数といいます。

重複を省く DISTINCT

重複がない、つまりユニーク値を調べられます。 age_class列にどんな値が入っているかを調べたいときは以下のように書きます。

SELECT DISTINCT age_class FROM cov;

ほとんどは「10代」「20代」など「代」がつく形式が多いですが、「歳以上」や「不明」など異なる形式の分類もあることがわかります。データを扱う上で注意ですね。

ちなみにNULLは空白で出てきます。結果に空白の行がなければ、NULLはありません。

DISTINCTは集約関数の中でも使えます。

SELECT COUNT(DISTINCT age_class) FROM cov;

結果は13でした。年代を表すユニーク値が13種類あることがわかります。値の種類が何種類あるか調べたいときに便利ですね。

行の選択　条件抽出　WHERE

特定の行を抜き出したいとき、WHERE句を使います。 先程見た中で「100歳以上」というデータがあったので、これを抜き出してみます。

SELECT COUNT(id) FROM cov WHERE age_class = '100歳以上';

82名いらっしゃいました。

このようにWHEREのあとに条件式を書きます。 ASのときと違って日本語でもシングルクォーテーション'なのが注意です。

ちなみにWHEREはFROMの直後に書かないとエラーになります。

算術演算

SELECTは電卓のようにも使えます。

SELECT (10 + 14) * 2 / 4 - 1 AS result;

 result 
--------
     11

列に対して計算すると、全ての行が計算されます。ただし、NULLは何を演算してもNULLにしかなりません。

ちなみに、COUNT以外の集約関数SUM、AVGなどは、NULLを除外した結果が出てきます。

比較演算

「等しくない」を表すのが<>となるのがSQL独特な気がします。

また、文字列型の大小関係は辞書式なので、たとえば'3'と'21'だと'3'の方が大きいです。

数字が文字列で入っていると意図している大小関係と異なる場合があるので注意ですね。

NULLを判別　IS NULL , IS NOT NULL

NULLに対しては比較演算が使えない代わりに、専用の句があります。

SELECT COUNT(id) FROM cov WHERE ex_flag IS NULL;

退院フラグが立っていない行はNULLだったので、退院していない方の人数がわかります。

 count 
-------
  8168

論理演算 AND, OR, NOT

20代未満の感染者数を調べてみます。こういうときは、複数条件を指定したいので、ORを使います。

SELECT COUNT(id)
FROM cov
WHERE age_class = '10歳未満' OR age_class = '10代';

 count 
-------
  8904

ちなみに、ANDとORが両方あるときは、ANDが優先されます。 ORを優先したいときは算術演算同様、カッコ()で囲みましょう。

また、SQLの論理値にはUNKNOWNという不明値もあります。たとえばNULLとの論理演算結果などです。UNKNOWNをTRUEやFALSEと論理演算すると、演算がANDでもORでも結果はUNKNOWNとなります。

分類ごとに集計する GROUP BY

GROUP BYを使うと分類ごとに集計できます。 たとえば、感染者数を性別によって集計してみます。

SELECT gender, COUNT(id) FROM cov GROUP BY gender;

GROUP BYを使うときは、集約に使う列名はSELECTの中に含めることができます。このように書くと、どの分類がどの結果かがわかりやすくなります。

結果は以下のようになりました。

 gender | count 
--------+-------
 男性   | 66586
 ―      |     1
 －     |     2
 女性   | 53378
 -      |     7

GROUP BYの結果の表示順序はランダムなので、もし昇順か降順で並べたい場合は、追加で末尾にORDER BYでソートする必要があります。

というかハイフンの書式が統一されていなくて汚いデータですね...

結果に対して条件を指定して抽出する HAVING

上のようなグループ集計でエラー値を弾きたい場合に使えますね。

たとえば10件以下のノイズは省きたい、といったときは以下のように、GROUP BYの後にHAVINGを続けて条件式を書きます。

SELECT gender, COUNT(id)
FROM cov
GROUP BY gender
HAVING COUNT(id) > 10;

 gender | count 
--------+-------
 男性   | 66586
 女性   | 53378

逆に集約対象を元のテーブルから予め絞っておきたい場合は、WHEREを使ったほうが良いです。HAVINGはグループ集計したあとで抽出するので、先に絞れる場合はWHEREで絞っておいたほうが、大量データの場合無駄な処理が少なくなる分、実行速度が早くなります。(これくらいの少量データならどちらも一瞬で結果が出ますが...)

SELECT gender, COUNT(id)
FROM cov
WHERE gender = '男性' OR gender = '女性'
GROUP BY gender;

これも上記と同じ結果になりました。

結果をソートする　ORDER BY

年代別の感染者数を降順で見てみます。

SELECT
  age_class AS "年代", count(id) AS "人数"
FROM cov
GROUP BY age_class
ORDER BY "人数" DESC;

ORDER BYは末尾に書きます。ASCが昇順、DESCが降順です。また、ASで別名をつけた場合は、その名前を使って列を指定することができます。

   年代    | 人数  
-----------+-------
 20代      | 30805
 30代      | 22785
 40代      | 18158
 50代      | 15503
 60代      |  8735
 70代      |  7391
 10代      |  5897
 80代      |  5573
 10歳未満  |  3007
 90代      |  2027
 100歳以上 |    82
 不明      |     6
 -         |     5

並び替えしたことで、人数が多いのがどの世代かがわかりやすくなりますね。

まとめ

というわけで、基礎的なSQL構文をまとめてみました。

せっかく日時情報が入っているので、次回は時系列処理についてまとめたいと思います。

参考書籍

データベースやSQLの知識がない状態からでも読めるわかりやすい内容で、基本的なSQL操作が網羅されています。

PostgreSQLの導入はWindows向けに書かれていますので、Macの場合はこちらの記事を参考にインストールして頂けると良いと思います。

SQL 第2版 ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)