日日是Oracle APEX: 医療従事者の日別実績を読み込む

政府のダッシュボードに含まれていない、医療従事者等のワクチン接種状況の日別の実績をデータベースに取り込んでみました。以下、その作業ログです。

表とアプリケーションを作成する

元となるデータは首相官邸のサイトにある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