政府のダッシュボードに含まれていない、医療従事者等のワクチン接種状況の日別の実績をデータベースに取り込んでみました。以下、その作業ログです。
表とアプリケーションを作成する
元となるデータは首相官邸のサイトにあるExcelファイルです。
それより過去のデータは厚生労働省のこちらのサイトに掲載されているデータです。
厚生労働省のサイトに掲載されている4月9日までのデータと、Excelに含まれる4月20日以前のデータは、実質的には手作業の入力になります。
最初にデータの入れ物になる表を作成します。SQLワークショップのクイックSQLで以下のモデルを定義します。
# prefix: covid19_iryo
# semantics: default
# ondelete: restrict
data
store_date
content file
results
count_date
first_pfizer num
first_moderna num
second_pfizer num
second_moderna num
source_data /reference data
results_old
count_date
first num
second num
表COVID19_IRYO_RESULTSに投入されたデータが、元々どのExcelファイルに含まれていたかを示すために列SOURCE_DATAを定義しています。/reference dataの列ディレクティブを付加することにより、表COVID19_IRYO_DATAへの外部キー参照を定義しています。設定として# ondelete: restrictを追加し、保存されているExcelファイルから入力されたデータが表COVID19_IRYO_RESULTSに存在している限り、Excelファイルの削除を禁止しています。NOT NULL制約は付けず、NULLの場合は手入力と見做します。
クイックSQLの画面で、SQLの生成、SQLスクリプトの保存、レビューおよび実行を行います。
レビューの画面で表COVID19_IRYO_RESULTS_OLDの主キー制約名をcovid19_iryo_resul_id_pkからcovid19_iryo_resold_id_pkに変更します。表名の最初から18文字を元に制約名が生成されているため、表COVID19_IRYO_RESULTSの制約名と競合しています。
実行をクリックし、確認画面で即時実行をクリックします。
３つの表、COVID19_IRYO_DATA、COVID19_IRYO_RESULTS_OLD、COVID19_IRYO_RESULTSが作成されます。アプリケーションの作成を実行します。確認画面が開いたら、アプリケーションの作成をクリックします。
アプリケーションの名前を医療従事者ワクチン接種状況とします。アプリケーションの作成を実行します。
アプリケーションが作成されるので、実行して確認します。
首相官邸のサイトに掲載されているデータの入力画面です。
厚生労働省のサイトに掲載されているデータの入力画面です。
大量のデータ入力には向いていないですが、基本的なデータの一覧、登録、編集、削除は行うことができます。
今回は自動で行う処理を対象にしているため、この画面の改良は行いません。また、初期のデータ入力のためにExcelファイルの作成および修正を行ない、SQLワークショップのデータ・ワークショップを使ってデータをロードするため、この入力画面は使用しません。
Excelファイルを取得する
Excelファイルを取得するボタンを、データファイルの一覧画面に追加します。画面は以下になります。
ページ・デザイナで表COVID19_IRYO_DATAを一覧する画面を開き、リージョン・ボタンの位置で、ボタンの作成を実行します。識別のボタン名をB_GET、ラベルをデータの取得とします。ボタン位置は対話モード・レポートの検索バーの右を選んでいます。動作のアクションはデフォルトで、ページの送信です。
ボタンを押した時に実行されるプロセスを作成します。左ペインで
プロセス・ビューを開き、
プロセスの作成を実行します。
識別の
名前を
データの取得とします。
タイプは
コードの実行、
ソースの
タイプは
PL/SQLで、
PL/SQLコードとして以下を記述します。
サーバー側の条件の
ボタン押下時に、ボタン名の
B_GETを指定します。
declare
l_blob blob;
begin
l_blob := apex_web_service.make_rest_request_b
(
p_url => :IRYO_RESULT_URL,
p_http_method => 'GET'
);
insert into covid19_iryo_data(store_date, content) values(sysdate, l_blob);
end;
コード内でExcelファイルを入手するURLとして、置換文字列IRYO_RESULT_URLを使用しています。アプリケーション定義の置換にIRYO_RESULT_URLを定義します。
https://www.kantei.go.jp/jp/content/IRYO-vaccination_data2.xlsx
以上でデータの取得については実装完了です。ページを実行しデータの取得をクリックしてみます。レポートに１行追記されることが確認できます。
Excelファイルからデータをロードする
取得したExcelファイルから接種数のデータを取り出し、表COVID19_IRYO_RESULTSにロードするプロセスを作成します。表COVID19_IRYO_RESULTSの一覧ページにデータをロードするボタンを作成します。
ボタンの作成自体は、先程のB_GETと同じです。識別のボタン名をB_LOAD、ラベルをデータのロードとしています。
ボタンを押した時に実行されるプロセスを作成します。左ペインで
プロセス・ビューを開き、
プロセスの作成を実行します。
識別の
名前を
データのロードとします。
タイプは
コードの実行、
ソースの
タイプは
PL/SQLで、
PL/SQLコードとして以下を記述します。
サーバー側の条件の
ボタン押下時に、ボタン名の
B_LOADを指定します。
declare
l_id number;
l_blob blob;
begin
-- 最後の取得したExcelファイルをロード対象とする
select id, content into l_id, l_blob from covid19_iryo_data
where id = (select max(id) from covid19_iryo_data);
--
merge into covid19_iryo_results r
using
(
select
to_date(col001,'YYYY-MM-DD') count_date,
to_number(col004) first_pfizer,
to_number(col005) first_moderna,
to_number(col006) second_pfizer,
to_number(col007) second_moderna
from
apex_data_parser.parse(
p_content => l_blob,
p_file_name => 'dummy.xlsx'
-- 読み込める範囲に限定する
, p_skip_rows => 5
, p_max_rows => 34
)
) l
on ( r.count_date = l.count_date )
when matched then
update set
r.first_pfizer = l.first_pfizer,
r.first_moderna = l.first_moderna,
r.second_pfizer = l.second_pfizer,
r.second_moderna = l.second_moderna,
r.source_data = l_id
when not matched then
insert
(
count_date,
first_pfizer, first_moderna,
second_pfizer, second_moderna,
source_data
)
values
(
l.count_date,
l.first_pfizer, l.first_moderna,
l.second_pfizer, l.second_moderna,
l_id
)
;
end;
プロシージャAPEX_DATA_PARSER.PARSEの引数として、
p_skip_rowsと
p_max_rows指定することにより、ロード対象の行の範囲を限定しています。
Excelファイルの先頭行は以下の様になっています。実際のデータが始まるのは6行目からなので、p_skip_rowsに5を指定しています。
p_skip_rowsの設定をしないと、ORA-1841が発生します。
次にp_max_rowsですが、Excel内のデータで4月19日以前の曜日に式が設定されています。Oracle APEXのデータ・パーサーはこれを読むことができませんでした。そのためp_max_rowsを指定し、2021年4月20日の行までをロード対象にしています。
p_max_rowsを指定しないと、0RA-6502が発生します。
Excelファイルに含まれているすべてのデータを置き換えるのではなく、先頭から34行(5行スキップしているので読み込まれるデータは29行)のみが表COVID19_IRYO_RESULTSに投入されます。
最近のファイルで過去のデータが変更されている場合（記録には土日が含まれていないので、大体１ヶ月前のデータ）、その変更はロード対象外なので表に反映されないことになります。政府CIOポータルからダウンロードできる高齢者などのワクチン接種回数のデータ(NDJSON形式)では、過去に遡ってデータが更新されているため、医療従事者のデータについても過去に遡って更新されることが無い、とはいえないと思います。変更があったときに確認できるように、表COVID19_IRYO_RESULTSには列SOURCE_DATAを追加し、ロード時に使用したファイルを特定できるようにしています。
データの更新を1ヶ月以上行わないと取り込まれない行が発生しますが、定期的な更新をOracle APEXの自動化の機能を使って実装することにより回避できます。
以上で、データをロードするプロセスが完成です。データのロードのボタンをクリックし、動作を確認します。
手作業でデータを入力する
厚生労働省のデータを取り込みます。Web上の実績データをExcelにコピペします。
Excelブック形式(.xlsx)で保存し、SQLワークショップのデータ・ワークショップよりロードします。
データを変換しないと表COVID19_IRYO_RESULTS_OLDには投入できないため、一旦、新規表を作成して内容をロードします。表名はOLD_DATAとしました。列見出しの最初の行にヘッダーが含まれるのチェックを外します。データのロードを実行します。
37行のデータがロードされます。
表OLD_DATAの内容を表COVID19_IRYO_RESULTS_OLDへ、データ変換を行ないつつ投入します。SQLコマンドを開き、以下のSQLを実行します。
insert into covid19_iryo_results_old(count_date, first, second)
select
to_date(to_single_byte(replace(col001, chr(49824))),'YYYY/MM/DD') count_date
, to_number(to_single_byte(replace(col003, chr(49824))), '999G999G999') first
, to_number(to_single_byte(replace(col004, chr(49824))), '999G999G999') second
from old_data;
使用するデータはCOUNT_DATEとしてCOL001、列FIRSTとしてCOL003、列SECONDとしてCOL004を投入しています。それぞれto_single_byteで全角/半角変換、replaceを使ってUnicodeのC2A0(ノーブレークスペース)を文字列から削除しています。
最近のデータを含むExcelファイルの方は、先頭と末尾のコメントの削除と、曜日列の削除を行います。
変更したファイルを保存します。
SQLワークショップのデータ・ワークショップを使って、表COVID19_IRYO_RESULTSヘロードします。
ロード先として既存の表を選択します。表はCOVID19_IRYO_RESULTSです。Updateメソッドには置換を選びます。これから投入するデータは、過去のデータをすべて含んでいるため、今までに投入したデータは不要です。列見出しの最初の行にヘッダーが含まれるのチェックは外します。データのロードを実行する前に構成をクリックします。
列のマッピングとして、
COL001に
COUNT_DATEをマップします。
COL002は合計なので
未マップのままとします。
COL003に
FIRST_PFIZER、
COL004に
FIRST_MODERNA、
COL005に
SECOND_PFIZER、
COL006に
SECOND_MODERNAをそれぞれマップします。
変更の保存をクリックしダイアログを閉じ、戻った画面で
データのロードを実行します。
データのロードが成功すれば、医療従事者に関するワクチン接種状況のデータのロードは完了です。
チャートを作成する
せっかくデータをデータベースに取り込んだので、いくつかチャートを作成してみます。ページ・デザイナでホーム・ページを開き、リージョンの作成を行います。
識別のタイトルを接種数日次推移とし、タイプにチャートを選びます。ソースの位置をローカル・データベースとし、タイプにSQL問合せを選択します。SQL問合せとして以下を記述します。
select
count_date,
(nvl(first_pfizer,0) + nvl(first_moderna,0)) as first,
(nvl(second_pfizer,0) + nvl(second_moderna,0)) as second
from covid19_iryo_results
union all
select
count_date,
first,
second
from covid19_iryo_results_old
厚生労働省から取得した過去のデータと首相官邸のサイトから取得したExcelのデータをUNION ALLで連結して、チャートに表示します。
Attributesを開き、チャートの属性を調整します。チャートのタイプは棒、外観の向きは縦、積上げはON、マルチシリーズ・チャート・データのチャート・データのギャップを埋めるはON、ソート順序はラベル - 昇順、ギャップをゼロとしてレンダリングはOFF、設定の時間軸タイプは有効、凡例の表示をON、位置は下、としました。時系列での表示で必須の設定もありますが、色々変えてみてチャートを調整してもよいでしょう。
シリーズの設定を行います。最初のシリーズの識別の名前は医療従事者2回目とします。ソースの位置をリージョン・ソースとし、列のマッピングのラベルにCOUNT_DATE、値にSECONDを選択します。
医療従事者2回目のシリーズを重複させ、識別の名前を医療従事者1回目に変更します。列のマッピングの値をSECONDからFIRSTへ変更します。
ページを実行し、作成したチャートを確認してみます。
以前の記事の作業を行なっていると、表COVID19_VACCINATION_RESULTSとして高齢者などの接種データがデータベースに取り込まれています。こちらのデータもシリーズとして追加してみます。
シリーズの作成を行い、識別の名前を2回目とします。ソースの位置をローカル・データベース、タイプをSQL問合せとし、SQL問合せとして以下を記述します。
select
count_date,
sum(count) count
from covid19_vaccination_results
where status = 2
group by count_date
年代、性別、都道府県による絞り込みはしないので、SQLは単純です。列のマッピングのラベルにCOUNT_DATE、値にCOUNTを選択します。
作成したシリーズを重複させ、1回目のシリーズを作成します。識別の名前を1回目に変更します。SQL問合せのwhere status = 2の部分をstatus = 1へ変更します。
select
count_date,
sum(count) count
from covid19_vaccination_results
where status = 1
group by count_date
ページを実行して結果を確認します。
もうひとつ接種数の累積を表示するチャートを作ってみます。接種数日次推移のチャートを重複させ、識別のタイトルを接種数累積に変更します。ソースのSQL問合せは、元のSQLにWINDOW関数を使用して累積を計算する様に変更します。
select
count_date,
sum(first) over (order by count_date asc) first,
sum(second) over (order by count_date asc) second
from
(
select
count_date,
(nvl(first_pfizer,0) + nvl(first_moderna,0)) as first,
(nvl(second_pfizer,0) + nvl(second_moderna,0)) as second
from covid19_iryo_results
union all
select
count_date,
first,
second
from covid19_iryo_results_old
)
チャートのAttributesを開き、チャートのタイプを面付き折れ線に変更します。
高齢者などの接収数のSQLも、累積を計算する様に変更します。
シリーズ2回目のSQLを以下に変更します。
select
count_date,
sum(count) over (order by count_date asc) count
from
(
select
count_date,
sum(count) count
from covid19_vaccination_results
where status = 2
group by count_date
)
1回目は以下のSQLに変更します。where status = 2の条件がstatus = 1になっています。
select
count_date,
sum(count) over (order by count_date asc) count
from
(
select
count_date,
sum(count) count
from covid19_vaccination_results
where status = 1
group by count_date
)
以上で一旦ページを実行し、作成したチャートを確認してみます。
累接種数の累積であるにもかかわらず、値が減少している部分があります。これは主に医療従事者の接種データに土日が含まれていないため、前日と同じ値で累積を計算すべきところを0と見做されているためです。チャートとして問題があります。
SQLを修正して対応します。
リージョン・ソースのSQLは以下の様に記述します。
select
a.count_date as count_date
,sum(nvl(d.first,0)) over (order by a.count_date asc) as first
,sum(nvl(d.second,0)) over (order by a.count_date asc) as second
from
(
select (select min(count_date) from covid19_iryo_results_old) + level - 1 as count_date
from dual
connect by
(select min(count_date) from covid19_iryo_results_old) + level -1
<=
(select max(count_date) from covid19_iryo_results)
) a
left outer join
(
select
count_date,
(nvl(first_pfizer,0) + nvl(first_moderna,0)) as first,
(nvl(second_pfizer,0) + nvl(second_moderna,0)) as second
from covid19_iryo_results
union all
select
count_date,
first,
second
from covid19_iryo_results_old
) d on a.count_date = d.count_date
以下のSQLにて、医療従事者のワクチン接種が開始した日から最近のデータが存在する日までのそれぞれの日付となる行を生成します。
select (select min(count_date) from covid19_iryo_results_old) + level - 1 as count_date
from dual
connect by
(select min(count_date) from covid19_iryo_results_old) + level -1
<=
(select max(count_date) from covid19_iryo_results)
----
追記: 医療従事者のデータの方が更新が遅い様です。高齢者等のデータと重ね合わせる場合は、生成する日付の最大値はcovid19_iryo_resultsの最大ではなくcovid19_vaccination_resultsの最大値にする方が適切です。つまり、以下です。
select (select min(count_date) from covid19_iryo_results_old) + level - 1 as count_date
from dual
connect by
(select min(count_date) from covid19_iryo_results_old) + level -1
<=
(select max(count_date) from covid19_vaccination_results)
---
元々の接種数のデータを、この日付だけのビューとCOUNT_DATEをキーにして外部結合するすることにより、COUNT_DATEの抜けを無くしています。対応する日付の行が接種数の表に無い場合はNVL関数を適用して、0に変換しています。
シリーズの2回目のSQLも同様に、以下に変更します。
select
a.count_date as count_date
,sum(nvl(d.count,0)) over (order by a.count_date asc) as count
from
(
select (select min(count_date) from covid19_vaccination_results) + level - 1 as count_date
from dual
connect by
(select min(count_date) from covid19_vaccination_results) + level -1
<=
(select max(count_date) from covid19_vaccination_results)
) a
left outer join
(
select
count_date,
sum(count) count
from covid19_vaccination_results
where status = 2
group by count_date
) d on a.count_date = d.count_date
シリーズの1回目はwhere status = 2の部分がstatus = 1に変えただけですが、以下に変更します。
select
a.count_date as count_date
,sum(nvl(d.count,0)) over (order by a.count_date asc) as count
from
(
select (select min(count_date) from covid19_vaccination_results) + level - 1 as count_date
from dual
connect by
(select min(count_date) from covid19_vaccination_results) + level -1
<=
(select max(count_date) from covid19_vaccination_results)
) a
left outer join
(
select
count_date,
sum(count) count
from covid19_vaccination_results
where status = 1
group by count_date
) d on a.count_date = d.count_date
以上で対策は完了です。ページを実行して結果を確認してみます。
きれいに累積値が表示されるようになりました。
データの更新を自動化する
Excelファイルの取得とデータのロードは、PL/SQLコードを実行するプロセスとして作成しています。コードをそのまま流用し、自動的にデータを取得し、ロードを行うようにします。
共有コンポーネントの自動化を開きます。
作成済みの自動化が一覧されます。作成をクリックします。
自動化の作成のダイアログで、処理が実行されるタイミングを設定します。名前はデータのロードとします。
タイプはスケジュール済を選択します。定期的に実行させる設定です。オンデマンドは定期的ではなく、プロシージャAPEX_AUTOMATION.EXECUTEを都度呼び出す使い方になります。
アクションの開始は常時です。指定した時刻になると無条件で処理が開始します。問合せを選ぶと、開始の条件を設定することができます。
実行スケジュールはカスタムを選びました。正時というのは毎時0分に開始の意味です。
頻度として日を選び、間隔を1にしているので毎日実行されます。実行時刻に12:00を指定していますが、これはサーバーの時刻です。サーバーがUTCで動いているときはUTCの12:00になります。サーバーのSYSDATEが返す時刻を確認しましょう。
作成をクリックします。
概ね設定が完了した状態で自動化が作成されます。アクションを設定します。
名前をデータの取得とし、コードはデータを取得するプロセスに記述したコードをそのまま記載します。変更の適用をクリックします。
データの取得に続いて、データのロードを行うアクションを追加します。アクションの追加をクリックします。
名前をデータのロードとし、コードはデータをロードするプロセスに記述したコードをそのまま記載します。作成をクリックします。
最低限必要な設定が完了したので、スケジュール・ステータスをアクティブに変更し、保存して実行をクリックします。スケジュールの設定にかかわらず、定義したアクションが即時で実行されます。
メッセージに自動化実行が開始されました。と表示されます。また、最終実行や次の実行期限も確認しましょう。
自動化の実行ログは、自動化のページの実行ログのタブから確認できます。
今回は画面で使用しているプロセスをそのままアクションにしましたが、自動化の場合はAPEX_AUTOMATION.LOG_INFOといったプロシージャをコードに組み込むことで、処理の進捗などを記録することができます。
表COVID19_IRYO_RESULTSを確認することでも、データの更新が行われたかどうか、確認することができます。今回作成したアプリケーションであれば、以下のページになります。
以上で今回の作業は終了です。
作成したアプリケーションのエクポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/iro-vaccination.sql
Oracle APEXのアプリケーション作成の参考になれば幸いです。
完
