日日是Oracle APEX: ワクチン接種状況ダッシュボードをOracle APEXで作ってみる

政府CIOポータルより公表されている、ワクチン接種状況ダッシュボードをOracle APEXを使って作ってみました。元のサイトはTabuleauで作っているようです。

ダッシュボードで使用しているデータは、すべてオープンデータとして公開されています。これらのデータの取り込み方法は、以前に記事を書いています。

改行で区切ったJSON(Newline Delimited JSON)のデータをロードする

ワクチン接種数のデータです。表COVID19_VACCINATION_RESULTSにデータをロードしています。公開されているデータの取り込みに表COVID19_DATA_FILESを作成していますが、ダッシュボードの作成には使用していません。

総務省が公表している【総計】令和2年1月1日住民基本台帳年齢階級別人口（市区町村別）のExcelファイルを読み込む

ワクチン接種対象となる人口のデータです。表EGOV_POPULATIONにデータをロードしています。

作成するページは１ページのみです。

以下より、アプリケーションの作り方を紹介します。

最初にワクチン接種の対象となる人数を、都道府県別(PREFECTURE)、男女別(GENDER)、年代(AGE)毎に集計したビューCOVID19_VACCINATION_TARGETSを作成します。以下のDDLを実行します。集計結果はCOUNTになります。

CREATE OR REPLACE FORCE VIEW "COVID19_VACCINATION_TARGETS"
("PREFECTURE", "PREFECTURE_NAME", "GENDER", "AGE", "COUNT") AS 
select 
    trunc(municipality_code/10000) prefecture, 
    prefecture_name,
    gender,
    '-64' age,
    (age00+age05+age10+age15+age20+age25+age30+age35+age40+age45+age50+age55+age60) count
from egov_population
where city_name is null and gender = 'M' and municipality_code is not null
union all
select 
    trunc(municipality_code/10000) prefecture, 
    prefecture_name,
    gender,
    '65-' age,
    (age65+age70+age75+age80+age85+age90+age95+age100) count
from egov_population
where city_name is null and gender = 'M' and municipality_code is not null
union all
select 
    trunc(municipality_code/10000) prefecture, 
    prefecture_name,
    gender,
    '-64' age,
    (age00+age05+age10+age15+age20+age25+age30+age35+age40+age45+age50+age55+age60) count
from egov_population
where city_name is null and gender = 'F' and municipality_code is not null
union all
select 
    trunc(municipality_code/10000) prefecture, 
    prefecture_name,
    gender,
    '65-' age,
    (age65+age70+age75+age80+age85+age90+age95+age100) count
from egov_population
where city_name is null and gender = 'F' and municipality_code is not null
/

PREFECTUREは1から47、GENDERはMとF、AGEは-64と65-の値をそれぞれ持ちます。これらの値はNDJSON形式で提供されているワクチン接種のデータに合わせています。

ホーム・ページのみの空のアプリケーションを作成します。アプリケーション作成ウィザードを起動し、名前をワクチン接種状況ダッシュボードとします。アプリケーションの作成を実行します。

アプリケーションが作成されます。編集するページは1 - ホームのみです。

ページ・デザイナでホーム・ページを開き、全国の概要を表示するレポートを作成します。リージョンの作成を行い、タイトルを全国 - 概要とします。タイプにクラシック・レポートを選択します。ソースのタイプをSQL問合せとし、以下のSELECT文を記述します。

with v_first as (
    select
        sum(count) count
    from covid19_vaccination_results
    where status = 1
),
v_second as (
    select
        sum(count) count
    from covid19_vaccination_results
    where status = 2
),
v_total as (
    select 
        sum(count) total
    from covid19_vaccination_targets
)
select
    t.total population,
    f.count + s.count count_total,
    f.count count_first,
    (f.count / t.total) * 100 rate_first,
    s.count count_second,
    (s.count / t.total) * 100 rate_second
from v_first f, v_second s, v_total t

1回目の全接種回数はビューv_firstで集計、2回目はv_second、対象者はv_totalで集計しています。人口、接種数と接種率をSELECT文の結果として返しています。

政府のダッシュボードでは、都道府県を選択すると、その選択が表示に反映されます。都道府県ごとの表示は別に実装するので、このレポートではつねに全国の情報を表示しています。

レポートに表示される列の設定を行います。列を選択し、それぞれ見栄えの調整を行います。

列POPULATIONには、以下を設定します。

識別

タイプ：プレーン・テキスト

ヘッダー

ヘッダー：人口
位置合わせ：中央

レイアウト

列の位置合せ：右

外観

書式マスク：999G999G999G999G990

ソート

ソート可能：OFF

列COUNT_TOTALは、ヘッダーを接種数とします。それ以外は列POPULATIONと同じです。

ヘッダー

ヘッダー：接種数

列COUNT_FIRSTは、ヘッダーを1回目接種数とします。それ以外は列POPULATIONと同じです。

ヘッダー

ヘッダー：1回目接種数

列RATE_FIRSTは、ヘッダーを1回目接種率とします。今までと設定は大体同じですが、書式マスクの指定に小数点２桁を含め、HTML式を使って数値の末尾に%を付けています。

ヘッダー

ヘッダー：1回目接種率

外観

書式マスク：999G999G999G999G990D00

列の書式

HTML式：#RATE_FIRST#%

列COUNT_SECONDは、ヘッダーを2回目接種数とします。それ以外は列COUNT_FIRSTと同じです。

ヘッダー

ヘッダー：2回目接種数

列RATE_SECONDは、ヘッダーを2回目接種率とします。それ以外は列RATE_FIRSTと同じです。

ヘッダー

ヘッダー：2回目接種率

列の書式

HTML式：#RATE_SECOND#%

クラシック・レポートの表示を調整します。Attributesの外観のテンプレート・オプションを開きます。

Stretch Reportにチェックを入れます。また、詳細のPagination DisplayはHide when all rows displayedに設定します。結果は1行なのでページネーションの表示は不要です。

ページの保存と実行を行い、今まで作成した内容を確認します。

ダッシュボードを表示する条件を作成します。リージョンの作成を行い、名前を条件、タイプを静的コンテンツとします。レイアウトの新規行の開始はOFFにします。

作成したリージョンで、ページ・アイテムの作成を行います。

性別を選択するページ・アイテムには、以下の設定を行います。

識別

名前：P1_GENDER
タイプ：ラジオ・グループ

ラベル

ラベル：性別

設定

列の数：３
選択時のページ・アクション：None

タイプ：静的値
静的値

男：M
女：F
すべて：M:F:U
実行時にソート：OFF

追加値の表示：OFF
NULL値の表示：OFF

デフォルト

タイプ：静的
静的値：M:F:U

年代を選択するページ・アイテムを作成し、以下の設定を行います。

識別

名前：P1_AGE
タイプ：ラジオ・グループ

ラベル

ラベル：年代

設定

列の数：２
選択時のページ・アクション：None

レイアウト

新規行の開始：OFF

タイプ：静的値

65歳以上：65-
すべて：-64:65-:UNK
実行時にソート：OFF

追加値の表示：OFF
NULL値の表示：OFF

デフォルト

タイプ：静的
静的値：-64:65-:UNK

都道府県を選択するページ・アイテムを作成し、以下の設定を行います。都道府県の選択は対話グリッドから行うため、このページ・アイテムは非表示とします。

識別

名前：P1_PREFECTURE
タイプ：非表示

デフォルト

タイプ：静的
静的値：1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:32:33:34:35:36:37:38:39:40:41:42:43:44:45:46:47

都道府県の選択をリセットし、すべての都道府県を対象にするボタンの作成を行います。

識別

ボタン名：B_ALL
ラベル：全国

レイアウト

ボタン位置：Region Body
新規行の開始：OFF (場所はP1_PREFECTUREの右隣に配置します)

動作

アクション：動的アクションで定義

ボタンB_ALLに動的アクションの作成を行います。

識別

名前：都道府県を全選択

タイミング

イベント：クリック
選択タイプ：ボタン
ボタン：B_ALL

Trueアクションの設定を行います。

識別

アクション：値の設定

設定

タイプの設定：Static Assignment
値：1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:32:33:34:35:36:37:38:39:40:41:42:43:44:45:46:47
変更イベントの禁止：OFF

影響を受ける要素

選択タイプ：アイテム
アイテム：P1_PREFECTURE

ページの保存と実行を行い、今まで作成した内容を確認します。

接種数日次推移のチャートの作成を行います。リージョンの作成を実行します。

識別

タイトル：接種数日次推移
タイプ：チャート

Attributesタブを開き、チャートの属性を設定します。

チャート

タイプ：棒

外観

向き：縦
積上げ：ON

設定

時間軸タイプ：有効

凡例

表示：ON
位置：下

シリーズとして、最初に2回目の接種数を作成します。（デフォルトでシリーズは１つ作成されているので、正しくは作成済みのシリーズの変更です）。

接種数を集計するSQLは以下になります。

select
    count_date,
    sum(count) count
from covid19_vaccination_results
where status = 2
  and gender in (select column_value from apex_string.split(:P1_GENDER, ':'))
  and age in (select column_value from apex_string.split(:P1_AGE, ':'))
  and prefecture in (select column_value from apex_string.split(:P1_PREFECTURE, ':'))
group by count_date

識別

名前：2回目

ソース

位置：ローカル・データベース
タイプ：SQL問合せ
SQL問合せ：上記
送信するアイテム：P1_GENDER,P1_AGE,P1_PREFECTURE

列のマッピング

ラベル：COUNT_DATE
値：COUNT

シリーズ2回目を重複させ、1回目を作成します。識別の名前を1回目に変更し、SQLのwhere status = 2の部分をwhere status = 1に書き換えます。

select
    count_date,
    sum(count) count
from covid19_vaccination_results
where status = 1
  and gender in (select column_value from apex_string.split(:P1_GENDER, ':'))
  and age in (select column_value from apex_string.split(:P1_AGE, ':'))
  and prefecture in (select column_value from apex_string.split(:P1_PREFECTURE, ':'))
group by count_date

y軸の値の書式を少数、小数点を0、書式スケールは自動にします。

ページの保存と実行を行い、今まで作成した内容を確認します。

接種率日次推移のチャートの作成を行います。作成済みの接種数日次推移のチャート・リージョンを重複させます。識別の名前を接種率日次推移に変更します。チャートを横並びにするため、レイアウトの新規行の開始をOFFにします。

チャートのタイプは折れ線に変更します。外観の積上げはOFFにします。

2回目のシリーズのSQL問合せを以下に変更します。

select
    count_date,
    (
        sum(count)
        over (order by count_date asc) 
        /
        (
            select sum(count) total
            from covid19_vaccination_targets
            where 1=1
                and gender in (select column_value from apex_string.split(:P1_GENDER, ':'))
                and age in (select column_value from apex_string.split(:P1_AGE, ':'))
                and prefecture in (select column_value from apex_string.split(:P1_PREFECTURE, ':'))
        )
    )
    rate
from 
(
    select
        count_date,
        sum(count) count
    from covid19_vaccination_results
    where status = 2
        and gender in (select column_value from apex_string.split(:P1_GENDER, ':'))
        and age in (select column_value from apex_string.split(:P1_AGE, ':'))
        and prefecture in (select column_value from apex_string.split(:P1_PREFECTURE, ':'))
    group by count_date
)

列のマッピングの値はRATEに変更します。

1回目のシリーズも変更します。SQL問合せはwhere status = 2の部分をstatus = 1に変更しています。列のマッピングの値はRATEに変更します。

select
    count_date,
    (
        sum(count)
        over (order by count_date asc) 
        /
        (
            select sum(count) total
            from covid19_vaccination_targets
            where 1=1
                and gender in (select column_value from apex_string.split(:P1_GENDER, ':'))
                and age in (select column_value from apex_string.split(:P1_AGE, ':'))
                and prefecture in (select column_value from apex_string.split(:P1_PREFECTURE, ':'))
        )
    )
    rate
from 
(
    select
        count_date,
        sum(count) count
    from covid19_vaccination_results
    where status = 1
        and gender in (select column_value from apex_string.split(:P1_GENDER, ':'))
        and age in (select column_value from apex_string.split(:P1_AGE, ':'))
        and prefecture in (select column_value from apex_string.split(:P1_PREFECTURE, ':'))
    group by count_date
)

y軸の値の書式を割合、小数点を2にします。

ページの保存と実行を行い、今まで作成した内容を確認します。

都道府県ごとの接種数、接種率を一覧する対話グリッドの作成を行います。リージョンの作成を行います。識別のタイトルは都道府県別接種数 - 接種率、タイプに対話グリッドを選択します。ソースのタイプはSQL問合せで、SQL問合せには以下を記述します。

with v_first as (
    select
        prefecture,
        sum(count) count
    from covid19_vaccination_results
    where status = 1
        and gender in (select column_value from apex_string.split(:P1_GENDER, ':'))
        and age in (select column_value from apex_string.split(:P1_AGE, ':'))
        and prefecture in (select column_value from apex_string.split(:P1_PREFECTURE, ':'))
    group by prefecture
),
v_second as (
    select
        prefecture,
        sum(count) count
    from covid19_vaccination_results
    where status = 2
        and gender in (select column_value from apex_string.split(:P1_GENDER, ':'))
        and age in (select column_value from apex_string.split(:P1_AGE, ':'))
        and prefecture in (select column_value from apex_string.split(:P1_PREFECTURE, ':'))
    group by prefecture
),
v_total as (
    select 
        prefecture,
        prefecture_name,
        sum(count) total
    from covid19_vaccination_targets
    where 1=1
        and gender in (select column_value from apex_string.split(:P1_GENDER, ':'))
        and age in (select column_value from apex_string.split(:P1_AGE, ':'))
        and prefecture in (select column_value from apex_string.split(:P1_PREFECTURE, ':'))
    group by prefecture, prefecture_name
)
select
    f.prefecture prefecture, 
    t.prefecture_name prefecture_name,
    t.total population,
    (f.count + s.count) count_total,
    f.count count_first,
    (f.count / t.total) * 100 rate_first,
    s.count count_second,
    (s.count / t.total) * 100 rate_second
from v_first f 
    join v_second s on f.prefecture = s.prefecture
    join v_total t  on f.prefecture = t.prefecture

送信するページ・アイテムとして、P1_GENDER,P1_AGE,P1_PREFECTUREを指定します。

列をそれぞれ選択し、設定を行います。列PREFECTUREのヘッダーは#、列PREFECTURE_NAMEのヘッダーは都道府県とします。それ以外は全国 - 概要のレポートと同じヘッダー、位置合せ、列の位置合せ、書式マスクの設定を適用します。タイプはすべて、数値フィールドのまま変更しません。

対話グリッドのHTML式はクラシック・レポートとは異なるため、接種率に%をつけるためには使用できません。代わりにCSSを使います。

ページに、インラインで以下のCSSクラスappendPercentSignを定義します。ページのプロパティのCSS、インラインに記載します。

.appendPercentSign::after {
    content: "%";
}

列RATE_FIRSTとRATE_SECONDの外観のCSSクラスにappendPercentSignを指定します。

ページの保存と実行を行い、今まで作成した内容を確認します。

チャートとレポートは、すべて作成されました。

これから条件の変更がチャートやレポートに反映されるよう、動的アクションを追加します。

最初に対話グリッド上で都道府県を選択可能にします。対話グリッドで動的アクションの作成を行います。

識別の名前を都道府県選択とします。タイミングのイベントは選択の変更[対話グリッド]、選択タイプはリージョン、リージョンは都道府県別接種数 - 接種率です。

Trueアクションを選択し、アクションとしてJavaScriptコードの実行を選びます。設定のコードには以下を記述します。対話グリッドの行をクリックして選択された都道府県のコードが、ページ・アイテムP1_PREFECTUREに設定されます。

let model = this.data.model;
let record = this.data.selectedRecords[0];
let prefecture = model.getValue(record, "PREFCTURE");
$s("P1_PREFECTURE", prefecture);

選択された行が存在するときのみ実行されるよう、クライアント側の条件としてタイプにJavaScript式を選び、以下のJavaScript式を設定します。

this.data.selectedRecords.length > 0

アクションのクライアント側の条件は、Oracle APEX 21.1の新機能です。

ページ・アイテムP1_GENDER、P1_AGE、P1_PREFECTUREが変更されたときに、２つのチャートと１つの対話グリッドがリフレッシュされるよう、動的アクションの作成をします。

タイミングのイベントは変更、選択タイプはアイテムです。アイテムには、ぞれぞれのページ・アイテムを指定します。

Trueアクションの識別のアクションはリフレッシュです。影響を受ける要素はリージョンで、リージョンには接種数日次推移、接種率日次推移、都道府県別接種数 - 接種率の３つリージョンをそれぞれ別のTrueアクションとして作成します。３つのページ・アイテムすべてにTrueアクションを３つずつ作成します。

以上でアプリケーションの完成です。作成したアプリケーションは以下の様な動作になります。

見栄えなどはまだまだ調整の余地はありますが、基本的な機能は実装できていると思います。

作成したアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/vaccine-dashboard.sql

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完

2021年6月2日水曜日

ワクチン接種状況ダッシュボードをOracle APEXで作ってみる