SecondaryUse開発日記

データベース参照ツールを開発しています。そのツールの開発、利用方法秘話などなどの紹介

SQLの学習に最適なSimpleUse

SQLを学ぶのにSQLを実行するDBが必要です
SQliteはファイル形式で実行可能なため簡単に環境が整えられます
SimpleUseではインストール後すぐに使えます
しかし実行するためのサンプルデータの作成が面倒です
でも今どきは新型コロナデータがたくさんありますので、これを使って学ぶこととします

 

www3.nhk.or.jp

今回はNHKの「都道府県ごとの感染者数の推移」データを使用します
都道府県ごとの感染者数の推移グラフのところにCSVデータへのリンクがありますのでここからダウンロードします

ダウンロードファイル名:nhk_news_covid19_prefectures_daily_data.csv

 

f:id:syskobo:20210801155323p:plain エクセルで開いたとき

 

f:id:syskobo:20210801155805p:plain SimpleUseで開いたとき

※エクセルでは日付変換、数値変換を自動で行いますが、 SimpleUseは文字列のままになります

 

ダウンロードしたら、SQLを実行するためのデータをインポートします

「ランチャー」から「ローカルSQL」を起動して「インポート」ボタンをクリック

f:id:syskobo:20210801160320p:plain f:id:syskobo:20210801160347p:plain

インポートファイル:ダウンロードした[nhk_news_covid19_prefectures_daily_data.csv]

出力先SQLファイル:サンプルの \Documents\SimpleUse\SQLite\LocalDB.sqlite3

f:id:syskobo:20210801160147p:plain

テーブル名が自動的にCSVファイル名になります
もっと短いテーブル名にしたいので、CSVファイル名を「COVID19.CSV」に変更して再度インポートします

インポートが終了したら、本当にインポートされているか確認します

「0.01.テーブル一覧.sql」で「LocalDB.sqlite3」に登録してあるテーブルを確認できます

f:id:syskobo:20210801161752p:plain

ショートカットメニューから、データ一覧、データ件数が確認できます

f:id:syskobo:20210801161015p:plain

 

データの確認が出来ましたら、SQLの実行ファイルを作成します

ここでは(SimpleUse)では \Documents\SimpleUse\LocalSQL フォルダに

ファイル名が「0.10.都道府県の集計.sql」のテキストファイルを作成します

f:id:syskobo:20210801162213p:plain

SQLの実行は、「DB再検索」で行います
実行時に「0.10.都道府県の集計.sql」を読込ますのでプログラムの再起動は不要です

但し、SQLの実行ファイルの追加をした場合は再起動が必要です

f:id:syskobo:20210801173120p:plain

 

都道府県の集計

select

都道府県コード
,都道府県名
,sum(各地の感染者数_1日ごとの発表数) 各地の感染者数_1日ごとの発表数

from COVID19

group by 都道府県コード , 都道府県名

order by 都道府県コード

↓↓↓↓↓ sum の使い方 ↓↓↓↓↓

https://www.google.com/search?q=SQLite+SUM

↓↓↓↓↓ group by の使い方 ↓↓↓↓↓

https://www.google.com/search?q=sql+group+by

 

感染者の多い順に並べ替えるには以下のorder by句を使います

select

都道府県コード
,都道府県名
,sum(各地の感染者数_1日ごとの発表数) 各地の感染者数_1日ごとの発表数

from COVID19

group by 都道府県コード , 都道府県名

order by sum(各地の感染者数_1日ごとの発表数) desc

↓↓↓↓↓ order by の使い方 ↓↓↓↓↓

https://www.google.com/search?q=sql+order+by

 

日付毎の患者数一覧をは以下の様にします

select

日付
,sum(各地の感染者数_1日ごとの発表数) 各地の感染者数_1日ごとの発表数

from COVID19

group by 日付

order by  日付 

 

order by  日付 を指定しましたが、正しく並ばない様です
これは、日付が文字列として登録したため、文字列の小さい方から並べて表示されています

f:id:syskobo:20210801164332p:plain

これを正しく並べるには、日付を文字列から日付型に変換します

select

日付
,substr(日付, 1, 4) YYYY
,replace(substr(日付, 6, 2),'/','') MM
,replace(substr(日付, 8, 3),'/','') DD

,date( substr(日付, 1, 4) || '-' ||
case length(replace(substr(日付, 6, 2),'/',''))
when 1 then '0'||replace(substr(日付, 6, 2),'/','')
else replace(substr(日付, 6, 2),'/','')
end
|| '-' ||
case length(replace(substr(日付, 8, 3),'/',''))
when 1 then '0'||replace(substr(日付, 8, 3),'/','')
else replace(substr(日付, 8, 3),'/','')
end
) 日付型

,sum(各地の感染者数_1日ごとの発表数) 各地の感染者数_1日ごとの発表数

from COVID19
group by 日付
order by 日付型 

YYYY、MM、DD は文字列の日付を解体したときの値です
SQLiteの日付型文字列は”YYYY-MM-DD”で"YYYY/MM/DD"や”YYYY-M-D”は使用できません    

substr、replace、case、date句については各々調べてください
自分で調べて、試さないと身になりませんよ