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

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

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

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

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

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

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

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

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

メモ帳で整形

　メモ帳で開き，上位2行を削除する．

Power Queryでの処理

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

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

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

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

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

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

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

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

　カスタム列を展開する．

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

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

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

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

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

　最後に，閉じて読み込む．

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

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

SQL Serverにインポートする

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

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

　変換先を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のクエリを記述した．