SQL を使って新型コロナのオープンデータを処理

(夏休み企画 9/20)

先に
「LibreOffice Calc を使って新型コロナのオープンデータを処理」
https://qiita.com/nanbuwks/items/d13fb40138660015ef8a
を書きましたが、もう少し高度な処理をしてみます。

先の記事では最近の1週間のキッズの感染者数を出しました。今回は去年3月からのキッズの感染者の変動をグラフにしてみます。
しかしながらこれより高度な集計を表計算ソフトでがんばるのはどうかなと思います。社会人向けにはその方法でもいいかも知れませんが、小学生にセル集計テクニックを覚えてもらうのはバッドノウハウかなあということで、データ処理のところは SQL で行ってみましょう。

# phpMyAdmin を使う

SQL 環境として、今回は phpMyAdmin を自分専用ツールとして使っていきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/f51bb80b-27f1-cbdd-ab08-1be36dfde98a.png)

手軽な SQL 環境として LibreOffice Base も検討しましたが残念ながら調子が悪い感じでした。
cf.,「LibreOffice Base を使うテスト」
https://qiita.com/nanbuwks/items/3742c7ab4805d187326d

# 環境

- Ubuntu 20.04 LTS Desktop
- MariaDB 10.3.31-MariaDB-0ubuntu0.20.04.1
- phpMyAdmin 4.9.5deb2
- LibreOffice 6.4.7.2 Calc
- 足立区感染状況のオープンデータ (2021/8/14朝取得)


# phpMyAdmin を用意

以下の記事を元に、phpMyAdmin の画面が出るところまで進んでおきます。

「phpMyAdmin + AMP を Ubuntu パッケージからインストール」
https://qiita.com/nanbuwks/items/6768bc73661bdba43af9


# オープンデータを用意

「LibreOffice Calc を使って新型コロナのオープンデータを処理」
https://qiita.com/nanbuwks/items/d13fb40138660015ef8a
の通り、足立区の
「区内での新型コロナウイルス感染者の発生状況」
https://www.city.adachi.tokyo.jp/juyo/covid19list.html

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/53662310-b618-38cd-1655-f7ae79e41979.png)

からのデータを持ってきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/c7f65303-856d-fddb-c3ee-db3670c28f88.png)


ここの CSV をクリックし、ファイルをダウンロードします


# CSV データをデータベースに登録するために整理する

本来、SQLで使うためにはデータを正規化するという処理をしないといけません。
しかしながら 「統計表における機械判読可能なデータの表記方法の統一ルール」
https://www.soumu.go.jp/main_content/000723626.pdf
に基づいたデータである場合は、ほぼ正規化されているのでそのまま使います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/ff096091-2527-d545-8144-d35e6c4daa23.png)

しかしながら、ファイルの中身を詳しく見てみると以下のように行の最後に「^M」というヘンテコなものがついています。これは改行コードと言われるもので、処理する機械によって改行コードが異なるのでLinuxで扱う場合はこれを除去しないといけません。



![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/05b883d7-152d-6e48-9591-f39260161f05.png)
簡単に除去するために、LibreOffice Calcで一旦開きます。



ダウンロードしたデータを開くと、インポートの画面が出てくるので「OK」
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/c4b049e2-f60e-e24d-9ff0-cfe20fc8bd88.png)


以下の画面のように読み込まれます


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/ecbac2a1-6796-faa7-cd57-8c3a9eebd3a5.png)
そのまま保管します。「テキストCSV形式を使用」を押します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/222f922d-15e4-e0c1-1ad3-4957f9d7cc96.png)

そうすると、「^M」はうまく無くなりました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/0ac535f2-1f84-da37-6a96-b588c86880ba.png)

# phpMyAdmin で CSVデータをインポート

ログインします。

必要に応じ、データベースを作成します。
例では「夏休みのテスト」という名前のデータベースで作業します。もしまだデータベースを作成していない場合は以下のようにして作成します。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/510cc9e6-d0fc-53c4-b038-fda1bbef7b9c.png)

「夏休みのテスト」を押します。まだデータは空ですね。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/8bb4c6ee-0ca7-4b31-c197-d9be125844fd.png)


もし以前に「コロナ」テーブルを作っていた場合は以下「テーブルを削除する」で消しておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/dde95b47-bf36-145d-7d1b-795ecc4f4589.png)


「インポート」で「ファイルを選択」してCSVファイルを指定します。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/5f9b37dc-2557-e648-f957-c4fda0e92282.png)

「ファイルの最初の行にテーブルのカラム名が含まれている。」にチェックを付けて「実行」



![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/502fd50d-af5e-1d5f-01be-77dbe701568d.png)

うまく読み込まれました。



![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/5b892068-3c1e-7be2-acdf-66ecb714fc9b.png)
「TABLE 1」をクリックすると読み込まれたデータが表示されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/886c816f-896b-7629-48c1-515a5cf28f04.png)

「TABLE 1」の名前を扱いやすいように「コロナ」に変えました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/252a8890-39c4-ce21-59e3-54cb02916e1e.png)

# 10歳未満の最近の感染者を抽出

calc と同じようにデータ分析をしてみます。

10歳未満で検索してみます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/31e0de0e-aac7-0681-c507-473668d97e32.png)

440人と出てきました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/95465e87-6e07-000e-9eb8-126648c0b353.png)
日付を 2021-08-10 のみで検索すると、

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/5fa67921-2ef3-3e89-908b-03a5f245c11c.png)
7人になりました。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/fcc72cb8-4781-e889-3194-de6d2dd97809.png)


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/f1bcd143-3029-5260-99dc-bc6638f691af.png)

診断日をINにすると、複数日が検索できます。 2021-08-03,2021-08-04,2021-08-05,2021-08-06,2021-08-07,2021-08-08,2021-08-09,2021-08-10 と入れて検索します。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/41c75d0d-440f-ef44-dbb3-1a30b48714e1.png)


79と出てきました。

# SQL を使う

先程の最後の検索は行0-24の表示のところにSQLが表示されています。

```

SELECT * FROM `コロナ` WHERE `診断日` IN ('2021-08-03','2021-08-04','2021-08-05','2021-08-06','2021-08-07','2021-08-08','2021-08-09','2021-08-10') AND `年代` LIKE '10歳未満'
```
こういったものを自分で書いてみましょう。「SQL」を押して、

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/d876a793-ab10-6a87-01e3-7f5bb6abc3a7.png)


```

SELECT * FROM `コロナ` WHERE 年代="10歳未満"
```

とします。元々表示しているものを書き換えます。
「`コロナ`」 は 「コロナ」 でもいいです。
「"10歳未満"」は文字列として扱っているので 「"」 で囲む必要があります。「"」 は 「'」 でもいいです。
実行すると440の一覧が出てきました。

更に「クエリボックスを表示」して、以下のように書き換えると

```

SELECT * FROM `コロナ` WHERE 年代="10歳未満" AND 診断日="2021-08-10"
```

で7名の一覧が出てきます。

# SQL で集計する

これを一覧ではなく、集計してみるとどうなるでしょうか？


```

SELECT COUNT("No.") AS 合計 FROM `コロナ` WHERE 年代="10歳未満" AND 診断日="2021-08-10" 


```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/74d43217-9b46-48da-ed4a-40e728a25544.png)


```

SELECT COUNT("No.") AS 合計 FROM `コロナ` WHERE 診断日="2021-08-10"

```

だと
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/8733885b-5487-049e-f39d-bf65c8a2ca7c.png)

SELECT 年代,COUNT("No.") AS 人数 FROM `コロナ` GROUP BY 年代

だと

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/e51a5d2d-4eca-bfa5-879c-60aa1db14254.png)

並びがバラバラなので、直します。

```

SELECT 年代,COUNT("") AS 人数 FROM `コロナ` GROUP BY 年代 ORDER BY 年代
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/36b374c0-0dd1-b9ee-80a1-d39b9b02295f.png)

並び順がちょっと惜しいけど、さっきよりはずっといいですね！

8月10日だけの集計です

```

SELECT 年代,COUNT("") AS 人数 FROM `コロナ` WHERE 診断日="2021-08-10" GROUP BY 年代 ORDER BY 年代
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/d843443e-5233-1a4d-1a75-a7c00f7b3d98.png)

# 日付ごとに集計する

```

SELECT 診断日,COUNT("No.") AS 人数 FROM `コロナ` WHERE 年代="10歳未満" GROUP BY 診断日 ORDER BY 診断日

```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/affc5bb3-242d-4904-6f2d-b70253c1776d.png)

166のデータが出ますが、24しか表示されていません。
「全て表示」として、一番下の「クリックボードにコピー」を押します。

# グラフを作る

LibreOffice Calc を起動して、「編集」-「貼り付け」で貼り付けます。「区切りのオプション」を「タブ」にするといい感じになります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/92f2961c-1069-bb90-f362-778ef10347e4.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/223772e2-a883-5cb8-6314-5694faaddc98.png)
余計な行を削除しましょう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/cf2adc21-00ec-ae5c-d47e-346cb9b3f173.png)
Ctrl+Aを押して、全体を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/e04600f6-dcaf-fe1d-e277-064760fd00e8.png)

「挿入」-「グラフ」で、縦棒「」を選択して「完了」
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/abadc1cc-f247-cbd5-bea9-b63507c2e89e.png)

1日ごとの感染者数が出ましたが、はじまりが2021-03-05となっていてちょっとわかりずらいですね。一度グラフを削除し、もう一度作り直しましょう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/3f09abae-3194-f669-13ad-64320ff62af8.png)
2020-03-01 0というデータを挿入し、グラフを作り直すと
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/c7e23076-cc16-ed29-46a2-a898d62d6a71.png)

見やすい感じになりました。

# データをいい感じに変換する

まずは、ユニークキーというものを作ってテーブルを編集できるようにします。

「構造」で No. の名前の . があると使いづらいので、 No に変更します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/74123afe-104f-b69d-8e5e-80784cf12d9e.png)

「その他」の「ユニーク」を押します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/db1be94e-f66e-1b2e-8367-b66db9174e42.png)


さて、日付のデータが、「2021-08-01」のようになってますが文字の形式なので、処理しやすい形式に変更します

「表示」で「診断日」を押すと、変なデータが1つあるのがわかりますね。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/1854516f-0567-3ad3-14cb-93620e248e47.png)

これを削除します。

次に「構造」で、「診断日」を「変更」して、データ型を「DATE」にします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/609ab617-62be-2bf0-11ca-3f195a019a50.png)

# 週の集計を簡単に

```


SELECT 年代,COUNT("") AS 人数 FROM `コロナ` WHERE 診断日 <= "2021-08-03" AND 診断日 <= "2021-08-10" GROUP BY 年代 ORDER BY 年代
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/bc5df545-3dcf-3e80-eb90-b75affea7dd5.png)


# 月、週ごとの集計をする

```

SELECT DATE_FORMAT(診断日, "%Y %U週") as 診断週,COUNT("") AS 人数 FROM `コロナ` WHERE 年代="10歳未満" GROUP BY 診断週 ORDER BY 診断週
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/c9e112a6-9f7a-0954-4468-62bfa662b7d7.png)


```

SELECT DATE_FORMAT(診断日, "%Y年%m月") as 診断月,COUNT("") AS 人数 FROM `コロナ` WHERE 年代="10歳未満" GROUP BY 診断月 ORDER BY 診断月
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/92d771c9-470e-2842-035f-d0426f8fa35f.png)

これを元に、以下のように足立区の10歳未満の感染者数グラフを作りました。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/1de5b0bb-3a40-89ae-a649-9550a76a38d4.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/139524/2c814ad0-5e56-0d0c-e28e-8f5e920803e1.png)

# 考えよう

- 最後のグラフの、2021年8月が下がっているのはどういうわけかな？
- この感染者数と、発症していないけれど感染力を持つ人の関係はどうなるかな？
- 小学校の1校ごとに何人感染力を持つ生徒がいるか計算できるかな？