日平均気温の過去30日間の移動平均をSQL Serverのウィンドウ関数を用いて計算する

気象庁の日平均気温をダウンロードする

 気象庁の過去のデータをダウンロードするにはここから入る.

 「地点を選ぶ」では都道府県の県庁所在地を選ぶ.

「地点を選ぶ」では都道府県の県庁所在地を選ぶ
「地点を選ぶ」では都道府県の県庁所在地を選ぶ

 「項目を選ぶ」では日別値の日平均気温を選ぶ.

「項目を選ぶ」では日別値の日平均気温を選ぶ
「項目を選ぶ」では日別値の日平均気温を選ぶ

 「表示オプションを選ぶ」では「都道府県名を格納」をチェックする.

「表示オプションを選ぶ」では「都道府県名を格納」をチェックする
「表示オプションを選ぶ」では「都道府県名を格納」をチェックする

 「期間を選ぶ」では2009年から2022年まで1年ずつ3月1日から9月30日までを選ぶ.

「期間を選ぶ」では2009年から2022年まで1年ずつ3月1日から9月30日までを選ぶ
「期間を選ぶ」では2009年から2022年まで1年ずつ3月1日から9月30日までを選ぶ

 しかる後,csvファイルのダウンロードを行う.

Power Queryでデータクレンジングする

メモ帳で整形

 メモ帳で開き,上位2行を削除する.

メモ帳で開き,上位2行を削除
メモ帳で開き,上位2行を削除

Power Queryでの処理

 データの取得,ファイルから,フォルダーからと進む.

データの取得,ファイルから,フォルダーからと進む
データの取得,ファイルから,フォルダーからと進む

 データの変換をクリックする.

データの変換をクリック
データの変換をクリック

 Nameのテキストフィルターから「指定の値で始まる」を選ぶ.

Nameのテキストフィルターから「指定の値で始まる」を選ぶ
Nameのテキストフィルターから「指定の値で始まる」を選ぶ

 「行のフィルター」でdataとタイプする.

「行のフィルター」でdataとタイプ
「行のフィルター」でdataとタイプ

 カスタム列を追加し式にcsvとタイプする.

カスタム列を追加し式にcsvとタイプする
カスタム列を追加し式にcsvとタイプする

 Csv.Documentを選び,([とタイプすると引数がポップアップするので[Content]を選ぶ.

Csv.Documentを選び,([とタイプすると引数がポップアップするので[Content]を選ぶ
Csv.Documentを選び,([とタイプすると引数がポップアップするので[Content]を選ぶ

 更に引数に[Encoding=932]とタイプする.

更に引数に[Encoding=932]とタイプする
更に引数に[Encoding=932]とタイプする

 カスタム列以外を削除する.

カスタム列以外を削除する
カスタム列以外を削除する

 カスタム列を展開する.

カスタム列を展開する
カスタム列を展開する

 不要な列を削除した状態である.

不要な列を削除した状態
不要な列を削除した状態

 「年月日」列のフィルターで空白と「年月日」のチェックを外す.

「年月日」列のフィルターで空白と「年月日」のチェックを外す
「年月日」列のフィルターで空白と「年月日」のチェックを外す

 「年月日」列を選択した状態でその他の列のピボット解除を選ぶ.

「年月日」列を選択した状態でその他の列のピボット解除を選ぶ
「年月日」列を選択した状態でその他の列のピボット解除を選ぶ

 「都道府県」列を選択し右クリックして「値の置換」を選ぶ.

「都道府県」列を選択し右クリックして「値の置換」を選ぶ
「都道府県」列を選択し右クリックして「値の置換」を選ぶ

 石狩を北海道に置換する.

石狩を北海道に置換する
石狩を北海道に置換する

 最後に,閉じて読み込む.

テキストファイルに保存する

 タブ区切りのテキストファイルに保存する.これがSQL Serverにインポートする際のデフォルト形式だからである.

SQL Serverにインポートする

 インポートしたいデータベースを右クリックしタスク,データのインポートへと進む.

インポートしたいデータベースを右クリックしタスク,データのインポートへと進む
インポートしたいデータベースを右クリックしタスク,データのインポートへと進む

 データソースをFlat File Sourceとする.

データソースをflat file sourceとする
データソースをFlat File Sourceとする

 変換先をSQL Server Native Client 11.0とする.

変換先をSQL Server Native Client 11.0とする
変換先をSQL Server Native Client 11.0とする

 列マッピングを図のようにする.

列マッピングを図のようにする
列マッピングを図のようにする

クエリ

USE HeatStrokeDB;
GO
SELECT
    D.年月日
,	D.都道府県
,	AVG(D.日平均気温) 
		OVER (PARTITION BY D.都道府県
			  ORDER BY D.年月日 ASC
			  ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS '移動平均30日間'
FROM	dbo.T_DailyAvgTemp	AS D
ORDER	BY 都道府県, 年月日;

結果

 結果は150870行にもなるため,一部を掲載するに留める.

年月日	都道府県	移動平均30日間
2008-03-01	愛知	6.5
2008-03-02	愛知	6.8
2008-03-03	愛知	7.06666666666667
2008-03-04	愛知	6.95
2008-03-05	愛知	6.52
2008-03-06	愛知	6.36666666666667
2008-03-07	愛知	6.48571428571429
2008-03-08	愛知	6.6
2008-03-09	愛知	6.83333333333333
2008-03-10	愛知	7.15

まとめ

 気象庁の日平均気温をダウンロードし,過去30日間の移動平均気温を算出するSQL Serverのクエリを記述した.

“日平均気温の過去30日間の移動平均をSQL Serverのウィンドウ関数を用いて計算する” への1件の返信

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください