SQLで東京都のCOVID-19感染陽性者のデータセットを集計してみる
目次
こんにちは。
今日はSQLで東京都のCOVID-19感染陽性者のデータを集計してみようと思います。
【準備】PostgreSQLとDBeaverのインストール
PostgreSQLとDB接続クライアント(DBeaver)の準備はこちらをご参照ください。
【準備】データセットのダウンロード
こちら(東京都_新型コロナウイルス陽性患者発表詳細)からCSV形式のファイルをダウンロードできます。
DBeaverでCSVをインポートしてテーブル作成
DBにテーブルを作るため、DBeaverでCSVファイルをインポートします。
手順は以下の通りです。
- 「新しい接続」(コンセントのアイコン)で「Embedded」から「CSV」を選択する。
- データセットをダウンロードしたフォルダパスを選択する。
これで、データベースナビゲータにCSVが追加され、CSVデータの中身が見れるようになりました。
PostgreSQLテーブルの作成
DBeaverでCSVの列が17個あること、すべてString型であることが確認できたので、同じようなテーブルを作ります。
まずDBに入ります。入り方はこちら
次に、テーブルを作成します。
ターミナルでPostgreSQLでDBに接続し、COPY先のテーブルを作ります。
CREATE TABLE cov1(
id INTEGER NOT NULL,
col1 VARCHAR ,
col2 VARCHAR ,
col3 VARCHAR ,
col4 VARCHAR ,
col5 VARCHAR ,
col6 VARCHAR ,
col7 VARCHAR ,
col8 VARCHAR ,
col9 VARCHAR ,
col10 VARCHAR ,
col11 VARCHAR ,
col12 VARCHAR ,
col13 VARCHAR ,
col14 VARCHAR ,
col15 VARCHAR ,
col16 VARCHAR
);
「No」を入れて17列あるので、id以外をcol1~col16と名付けておきます。
列選択のCOPYもできるようですが、日本語の列名がついていて書くのが面倒＆エラーを引き起こしそうなので安全策で進めます。
PostgreSQLにCSVテーブルをコピーする
CSVテーブルの情報を、上で作成したテーブルにコピーします。
カレントディレクトリを移動する必要があるときは以下のコマンドで移動します。
\cd directory_path
コピーコマンドは以下の通り。
\COPY cov1
FROM 'covid19_patients_tokyo.csv'
WITH CSV HEADER;
無事コピーされました。
HEADERをつけるとヘッダがあると認識して2行目から読み込みます。つまり1行目はコピーしなくなります。
必要な列を型変換して抽出する
集計に使用する列は以下の5列とします。
| CSVの列名 | SQLで使用するカラム名 |
|---|---|
| No | id |
| 公表_年月日 | date |
| 患者_年代 | age_class |
| 患者_性別 | gender |
| 退院済フラグ | ex_flag |
とりあえず5行見てみます。
SELECT
id,
CAST(col4 AS DATE) AS date,
col8 AS age_class,
col9 AS gender,
CAST(col16 AS BOOLEAN) AS ex_flag
FROM cov1
LIMIT 5;
集計用のテーブルを作成する
上記の型変換で集計用のテーブルcov2を作ります。
CREATE TABLE cov2 AS
SELECT
id,
CAST(col4 AS DATE) AS date,
col8 AS age_class,
col9 AS gender,
CAST(col16 AS BOOLEAN) AS ex_flag
FROM cov1;
色々集計してみる。
今までの感染者数
SELECT COUNT(id) FROM cov2;
要は行数ですね。
男性の感染者数
SELECT COUNT(id) FROM cov2 WHERE gender = '男性';
月ごとの感染者数
SELECT
TO_CHAR(date, 'YYYY-MM') AS month,
COUNT(id)
FROM cov2
GROUP BY month
ORDER BY month ASC
;
ASCは昇順なので、上から古い順に並びます。
まとめ
COVID-19の感染者データを基本的なSQL文で集計してみました。
このデータセットを使って、SQLの構文を別記事にまとめてみようと思います。
