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

(夏休み企画 9/20)

先に

「SQL を使って新型コロナのオープンデータを処理」
https://qiita.com/nanbuwks/items/3f7a9ab616803c8211e1
において、足立区のオープンデータを使った10歳未満の感染状況の集計をした記事を書きました。

更に発展させて、こういうの作ってみましょう。

(「新型コロナウイルス感染症の国内発生動向（速報値）（令和３年８月11日18時時点）」
https://www.mhlw.go.jp/content/10906000/000818427.pdf
4ページより抜粋)

このためには、以下のようなデータを作ることが必要です。

こういったものを SQL で作るにはどうしたらいいでしょうか？

環境

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

SUM 構文を使って集計する

先の記事中で、簡単な集計として全体から10歳未満を数えるSQLが以下のようにありました

SELECT COUNT("") from コロナ WHERE 年代="10歳未満"

これはSUM構文を使って以下のように書くことができます。

SELECT SUM(年代="10歳未満") from コロナ

とすると、440が出ます。

ちょっと見栄えを良くしましょう。
```

SELECT SUM(年代="10歳未満") AS 10歳未満 from コロナ
```

見出しが見やすくなりました。

また、先に

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

として月ごとの集計を出しました。これを書き直して

SELECT DATE_FORMAT(診断日, "%Y年%m月") as 診断月,SUM(年代="10歳未満") as 10歳未満 from コロナ GROUP BY 診断月 ORDER BY 診断月

とすることもできます。

SQL を見やすく書こう

さて、SQLは見やすく以下のようにもできます。

SELECT 
  DATE_FORMAT(診断日, "%Y年%m月") as 診断月,
  SUM(年代="10歳未満") as 10歳未満
from コロナ
GROUP BY 診断月
ORDER BY 診断月

年齢別に横にデータを並べてクロス集計

更に、以下のようにしてみましょう。

SELECT 
  DATE_FORMAT(診断日, "%Y年%m月") as 診断月,
  SUM(年代="0歳") as 0歳,
  SUM(年代="10歳未満") as 10歳未満,
  SUM(年代="10代") as 10代,
  SUM(年代="20代") as 20代,
  SUM(年代="30代") as 30代,
  SUM(年代="40代") as 40代,
  SUM(年代="50代") as 50代,
  SUM(年代="60代") as 60代,
  SUM(年代="70代") as 70代,
  SUM(年代="80代") as 80代,
  SUM(年代="90代") as 90代,
  SUM(年代="100代") as 100代
from コロナ
GROUP BY 診断月
ORDER BY 診断月

積み上げグラフの作成

先のデータを、「クリップボードにコピー」してLibreOffice Calcに持っていきます。

LibreOffice Calc上で積み上げバーグラフを作ると、以下ができました。

これを見ると、足立区でも10歳未満が増えてきています。
また、20代以下を合計するとほぼ50%ぐらい。感染のリスクは若年層が大きいことがわかります。

やってみよう

• こっちのグラフを作るにはどうしたらいいかな？
• 無症状の人はどれだけいるか集計できるかな？
• 年齢別の死亡率を出すにはどうしたらいいかな？