近頃やっと暑くなくなったように感じます。今年の東京の日毎の最高気温を昨年の同日の最高気温と比較してみます。本記事の目的は、SQLの分析関数（ウィンドウ関数とも呼ばれます）の使い方を紹介することです。
折れ線グラフを５つほど作成してみます。
以下のクイックSQLのモデルより、最高気温のデータを保存する表OPM_TEMPERATURESを作成します。
# prefix: opm
temperatures
city vc40 /default Tokyo
sample_date date
max_temperature numSQLの生成、SQLスクリプトを保存、レビューおよび実行を順次実行します。表の作成までを実施し、アプリケーションの作成は行いません。
表OPM_TEMPERATURESが作成されます。
東京の最高気温のデータはOpen MeteoのHistrical Weather APIを呼び出して取得します。
Historical Weatherを開き、Search Locations...をクリックしてTokyoを選択します。LatitudeとLongitudeに東京の経度と緯度が設定されます。Specify Time IntervalのStart dateに2022-01-01、End dateに2023-10-01を設定します。
Daily Weather VariablesのMaximum Temperature (2m)にチェックを入れます。Hourly Weather Variablesや、その他のチェックはすべて外し、日毎の最高気温のデータだけを取得対象にします。続いて、TimezoneとしてAsia/Tokyoを設定します。
ダウンロードしたCSVファイルをエディタで開き、ダウンロード時に与えたパラメータなどが記載された、先頭の３行を削除します。
CSVファイルはtime,temperature_2m_max (°C)の行から始まるようにします。
SQLワークショップのユーティリティにあるデータ・ワークショップを使って、CSVファイルの内容を表OPM_TEMPERATURESにロードします。
データのロードを実行します。
ロード先に既存の表を選び、表としてOPM_TEMPERATURESを選択します。
構成をクリックします。
列のマッピングのタブを開き、ソース列がTIMEのマップ先としてSAMPLE_DATE(Date)、TEMPERATURE_2M_MAX__Â_C_のマップ先としてMAX_TEMPERATURE(Number)を選択します。
変更の保存をクリックします。
データのロードを実行します。
表OPM_TEMPERATURESにデータがロードされたことを確認し、取消をクリックしてダイアログを閉じます。
アプリケーション作成ウィザードを起動します。アプリケーションの名前は東京の最高気温とします。
デフォルトで追加されているホーム・ページを削除し、チャートのページを追加します。
チャートとして折れ線を選択し、ページ名は東京の最高気温とします。表またはビューとしてOPM_TEMPERATURES、ラベル列としてSAMPLE_DATE、列値を選択し、値列としてMAX_TEMPERATUREを指定します。
ページの追加をクリックします。
アプリケーションが作成されたら、一旦実行し、作成されたページを確認します。
チャートの設定を調整する必要がありそうです。
ページ・デザイナでページ東京の最高気温を開きます。
リージョン東京の最高気温を選択し、プロパティ・エディタの属性タブを開きます。
設定の時間軸タイプを有効にします。また、凡例の表示をオンにします。
識別の名前は2023年に変更します。ソースのSQL問合せを以下のSELECT文に変更します。表示期間を６月から９月末までに制限しています。
select
sample_date
,max_temperature
from opm_temperatures
where sample_date between date'2023-06-01' and date'2023-10-01'
order by sample_date asc
パフォーマンスの処理する最大行数を10000にし、実用上問題ない制限に変更します。
以上で2023年６月1日から9月30日までの、東京の最高気温のチャートが出来上がりました。
ページを実行して確認します。
このチャートに2022年の最高気温を重ねて表示します。
チャート東京の最高気温の重複を実行します。新たに作成したチャート・リージョンのタイトルは、東京の最高気温(2022&2023)に変更します。
リージョン東京の最高気温(2022&2023)を選択し、ソースを変更します。
位置をローカル・データベース、タイプをSQL問合せに変更し、SQL問合せとして以下を記述します。
select
sample_date
,max_temp_2023
,max_temp_2022
from
(
select
city
,sample_date
,max_temperature max_temp_2023
/* LAG関数を使って365日前（閏年であれば366)、つまり昨年の最高気温を参照する。 */
,lag(max_temperature, 365, null)
over (partition by city order by sample_date) max_temp_2022
from opm_temperatures
)
where sample_date between date'2023-06-01' and date'2023-10-01'
order by sample_date asc2022年のデータは分析関数のLAGを使って取得しています。partition by cityを指定していますが、列CITYのデータはTOKYOだけなので効果はありません。SQLのサンプルとして扱いやすくなるよう、partition by句を加えています。
シリーズ2023年を選択し、ソースの位置をリージョン・ソースに変更し、列のマッピングの値をMAX_TEMP_2023に変更します。
シリーズ2023年を重複させます。
複製したシリーズの名前を2022年に変更し、列のマッピングの値をMAX_TEMP_2022に変更します。
日毎の最高気温を差を表示させてみます。チャートを重複させます。
複製したチャートのタイトルを東京の最高気温の差分(2023 - 2023)に変更し、ソースのSQL問合せを以下に変更します。列MAX_TEMP_DIFFを追加しています。
select
sample_date
,max_temp_2023
,max_temp_2022
,(max_temp_2023 - max_temp_2022) max_temp_diff
from
(
select
city
,sample_date
,max_temperature max_temp_2023
/* LAG関数を使って365日前（閏年であれば366)、つまり昨年の最高気温を参照する。 */
,lag(max_temperature, 365, null)
over (partition by city order by sample_date) max_temp_2022
from opm_temperatures
)
where sample_date between date'2023-06-01' and date'2023-10-01'
order by sample_date asc
シリーズをひとつ削除し、残ったシリーズの名前を2023 - 2022に変更し、列のマッピングの値にMAX_TEMP_DIFFを設定します。
ページを実行し、作成したチャートを確認します。
今ひとつ、ピンときません。
チャート東京の最高気温(2022&2023)を重複させ、それぞれの年の最高気温を7日間の移動平均で表示します。移動平均の計算には、分析関数のAVGを使用します。
ソースのSQL問合せを以下に書き換えます。
select
sample_date
/* 2023年の最高気温の7日間移動平均 */
,avg(max_temp_2023) over (order by sample_date asc range between interval '6' day preceding and current row) max_temp_2023
/* 2022年の最高気温の７日間移動平均 */
,avg(max_temp_2022) over (order by sample_date asc range between interval '6' day preceding and current row) max_temp_2022
from
(
select
city
,sample_date
,max_temperature max_temp_2023
/* LAG関数を使って365日前（閏年であれば366)、つまり昨年の最高気温を参照する。 */
,lag(max_temperature, 365, null)
over (partition by city order by sample_date) max_temp_2022
from opm_temperatures
)
where sample_date between date'2023-06-01' and date'2023-10-01'
order by sample_date asc
ページを実行して、チャートを確認します。
2022年の６月下旬から７月上旬って、そんなに暑かったか思い出せません。
リージョン東京の最高気温の差分(2023 - 2022)を重複させ、移動平均の差分を表示するように変更します。
ソースのSQL問合せを以下に書き換えます。
select
sample_date
,max_temp_2023
,max_temp_2022
,(max_temp_2023 - max_temp_2022) max_temp_diff
from
(
select
sample_date
/* 2023年の最高気温の7日間移動平均 */
,avg(max_temp_2023) over (order by sample_date asc range between interval '6' day preceding and current row) max_temp_2023
/* 2022年の最高気温の７日間移動平均 */
,avg(max_temp_2022) over (order by sample_date asc range between interval '6' day preceding and current row) max_temp_2022
from
(
select
city
,sample_date
,max_temperature max_temp_2023
/* LAG関数を使って365日前（閏年であれば366)、つまり昨年の最高気温を参照する。 */
,lag(max_temperature, 365, null)
over (partition by city order by sample_date) max_temp_2022
from opm_temperatures
)
where sample_date between date'2023-06-01' and date'2023-10-01'
)
order by sample_date asc
ページを実行して、チャートを確認します。
夏は過ぎましたが、今年の夏は去年に比べて相当に暑かったことが分かります。
今回作成したAPEXアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/max-temperature-tokyo-2023.zip
Oracle APEXのアプリケーション作成の参考になれば幸いです。
完
