ワクチン接種状況ダッシュボードに組み込んだ（もしくは組み込もうとした）チャートについて、２つほど紹介します。
チャートにy2軸を追加する
以下のように接種数と接種率の２つのチャートを作成しています。これを左のy軸に接種量、右のy軸(y2軸)に接種率を割り当て、１つのチャートにまとめてみます。
時系列のチャートで複数のシリーズがある場合、それぞれのシリーズの時刻を合わせる必要があります。この場合、通常は設定の時間軸タイプを混合頻度にします。
例えば以下のチャートがあるとします。
折れ線チャートには、6月1,2,3,5,8日にデータがあります。
棒グラフには、6月2,3,6,7日にデータがあります。
これを１つのチャートに含まれる、２つのシリーズにまとめます。時間軸タイプが有効の場合の表示は以下になり、シリーズの間で時刻が一致しません。
接種数は積上げチャートなので、今回は時間軸タイプを有効にした上で、接種率をシリーズとしてチャートに加えます。
以前の記事で作成したアプリケーションを元にします。
接種数日次推移のチャートに接種率のシリーズを追加します。
シリーズの名前を接種率1回目とします。タイプは折れ線です。ソースのタイプはSQL問合せ、SQL問合せとして、接種率日次推移のチャートの1回目に記述したSQLをそのまま貼り付けます。送信するページ・アイテムとして、P1_GENDER,P1_AGE,P1_PREFECTUREを指定します。列のマッピングのラベルはCOUNT_DATE、値はRATEです。
ここまでは接種率日次推移のチャートの、1回目のシリーズと同じ設定になります。
外観のY2軸に割当てをONにします。他のシリーズとの積み上げにならないよう、積上げカテゴリに接種率1回目を設定します。他のシリーズとの違いが分かりやすくなるよう、折れ線のスタイルを点線にします。
作成したシリーズを重複させ、識別の名称を接種率2回目に変更します。SQL問合せのwhere status = 1の部分をstatus = 2に変更し、積上げカテゴリも接種率2回目に変更します。
y2軸の値の書式を割合にし、小数点を2とします。
チャートの識別のタイトルを、接種数日次推移から日次推移に変更します。
ツールチップの表示はつねにy軸の書式が適用されます。そのため、例えば5月31日の接種率1回目ですが、値が0.0467になっています。割合では表示されていません。
select
count_date,
rate,
'系列 接種率1回目' || CHR(10)
|| '日付 ' || to_char(count_date,'DL') || CHR(10)
|| '値 ' || round(rate * 100, 2) || '%' custom_tooltip
from
(
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
)
)
同様の変更を、接種率2回目のシリーズにも実施します。
最後にy軸とy2軸にタイトルを設定します。
y軸のタイトルとして、接種数を設定します。
y2軸のタイトルとして、接種率(%)を設定します。
以上で作業は完了です。ページを実行し、チャートを表示してみます。
左のチャートの凡例として表示されているシリーズをクリックし、今回追加した接種率だけを表示しています。表示されているチャートが同一であることを確認し、接種率日次推移のチャートを削除します。
こちらの作業を実施したアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/chart-merge.sql
都道府県をシリーズとして比較する
シリーズは、それぞれ定義する以外に、SELECT文の検索結果に含まれる列から生成することもできます。
接種数の移動平均のチャートを作成し、選択した都道府県をシリーズとして表示させます。また同様に、接種率のチャートを作成し、選択した都道府県をシリーズとして表示させます。
事前に都道府県のコードと都道府県名のペアをリストするためのビューPREFECTURES_Vを作成します。DDLは以下になります。
create or replace view prefectures_v
as
select prefecture_name, prefecture
from covid19_vaccination_targets
group by prefecture_name, prefecture;
SQLワークショップのSQLコマンドなどから実行します。
1回目の接種数の7日間移動平均について、以下のチャートを作成します。北海道、山口県、秋田県が選択されています。
1回目の接種率のチャートは以下になります。選択している都道府県は同様に、北海道、山口県、秋田県です。
チャートの作成手順を紹介します。最初は接種数1回目 - 7日間移動平均のチャートを作成します。
リージョンの作成を行い、識別のタイトルを接種数1回目 - 7日間移動平均、タイプをチャートとします。ソースの位置をローカル・データベース、タイプにSQL問合せを選択します。
SQL問合せとして、以下を記載します。
select
prefecture_name,
count_date,
avg(count) over (partition by prefecture_name order by count_date asc
range between interval '6' day preceding and current row
) count
from (
select
p.prefecture_name,
r.count_date,
sum(r.count) count
from covid19_vaccination_results r join prefectures_v p on r.prefecture = p.prefecture
where r.status = 1
and r.gender in (select column_value from apex_string.split(:P1_GENDER, ':'))
and r.age in (select column_value from apex_string.split(:P1_AGE, ':'))
and r.prefecture in (select column_value from apex_string.split(:P1_PREFECTURE, ':'))
group by p.prefecture_name, r.count_date
)
列PREFECTURE_NAMEが検索結果に含まれるようにしています。接種数は接種日(COUNT_DATE)だけでなく都道府県名(PREFECTURE_NAME)ごとに集計し、また、移動平均はpartition by prefecture_nameの指定を追加することで、都道府県ごとに計算しています。
送信するページ・アイテムにP1_GENDER,P1_AGE,P1_PREFECTUREを指定しています。
チャートのAttributesとして、以下の設定を行いました。
- タイプ：折れ線
- マルチシリーズ・チャート・データ
- チャート・データのギャップを埋める：ON
- ソート順序：ラベル - 昇順
- ギャップをゼロとしてレンダリング：OFF
- 設定
- 時間軸タイプ：混合頻度
- ズームとスクロール：いいえ
- 凡例
- 表示：ON
- 位置：下
シリーズの識別の名前は、接種数とします。ソースの位置をリージョン・ソースとし、列のマッピングのシリーズ名としてPREFECTURE_NAMEを選択します。この指定により、列PREFECTURE_NAMEとして検索された都道府県名がそれぞれシリーズになります。
ラベルにはCOUNT_DATE、値にはCOUNTを指定します。マーカーの表示をはいにします。
以上で最初に掲載した1回目の接種数の7日間移動平均のチャートが表示されます。
続いて、1回目の接種率のチャートを作成します。今、作成したチャートを重複させ、タイトルを接種率1回目とします。以下のSQL問合せに変更します。
select
prefecture_name,
count_date,
(
sum(count)
over (partition by prefecture_name order by count_date asc)
/
total
)
rate
from
(
select
p.prefecture_name,
p.prefecture,
r.count_date,
sum(r.count) count
from covid19_vaccination_results r
join prefectures_v p on r.prefecture = p.prefecture
where r.status = 1
and r.gender in (select column_value from apex_string.split(:P1_GENDER, ':'))
and r.age in (select column_value from apex_string.split(:P1_AGE, ':'))
and r.prefecture in (select column_value from apex_string.split(:P1_PREFECTURE, ':'))
group by p.prefecture_name, p.prefecture, r.count_date
) s
join
(
select
prefecture,
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
) t on s.prefecture = t.prefecture
接種数(表COVID19_VACCINATION_RESULTSの集計)および対象人口(表COVID19_VACCINATION_TARGETSの集計)の集計を行います。接種数の累積値を求める際にはpartition by prefecture_nameを含めることにより、都道府県ごとに累積値を計算しています。累積値を都道府県ごとの対象人口で割ることにより、接種率を計算しています。
シリーズの識別の名前は接種率に変更し、列のマッピングの値はCOUNTからRATEへ変更します。
y軸を%で表示するため値の書式を割合、小数点を2と設定します。
以上で1回目の接種率のチャートも完成です。アプリケーションを保存して実行すると、最初に示したチャートが表示されます。
この他に２回目の移動平均、合計の移動平均、2回目の接種率のチャートを加えたアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/select-prefectures.sql
このアプリケーションでは、値の適用は動的アクションを使わず、ページの送信で行なっています。そのため、本来はリージョンのソースの設定に、送信するページ・アイテムの指定は不要です。今までに作成したダッシュボードの設定と整合させるために、送信するページ・アイテムの設定を行なっています。
以上になります。
Oracle APEXのアプリケーション作成の参考になれば幸いです。
完
