SQL Server でウィンドウ関数を使い，1行前の行を取得する

　ウィンドウ関数は比較的新しい技術である．筆者は正直，SQL が苦手だ．IPA の試験が終わったのでデータベース関連の勉強を再開している．

テーブル定義

　テーブルはすでにあるものを使う．今回は熱中症の搬送人員と最高気温との相関関係を可視化し閾値をχ二乗検定するで作成した EMERGENCYDB の Temperature テーブルを使う．念のためテーブル定義を記述しておく．

　該当テーブルを右クリックして「テーブルをスクリプト化」「新規作成」「新しいクエリエディターウィンドウ」と進んだ先に得られたものである．

　主キーが作成されていないが，ここでは問題にしていない．設定するなら「年月日」「都道府県コード」の複合キーがそれに当たる．

USE [EMERGENCYDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Temperature](
	[年月日] [date] NOT NULL,
	[都道府県] [nvarchar](50) NOT NULL,
	[日別最高気温] [float] NULL,
	[都道府県コード] [nvarchar](2) NOT NULL
) ON [PRIMARY]
GO

クエリ

　都道府県ごとに群別 (PARTITON BY) して年月日で昇順ソートし，各群内で 1 行前の行から最高気温を抽出している．BETWEEN句で 1 行前のフレームを選択している．フレーム内には 1 行しか存在しない (BETWEEN 1 PRECEDING AND 1 PRECEDING) ため，集約関数は AVG でも SUM でも MAX でも MIN でも構わないが，COUNT だけは使用してはいけない．

USE EMERGENCYDB;
GO
SELECT	年月日
, 	日別最高気温	AS 当日
,	MAX(日別最高気温)　OVER (PARTITION BY 都道府県コード	ORDER BY 年月日
			  ROWS  BETWEEN 1 PRECEDING AND 1 PRECEDING) AS 前日
,	都道府県
FROM	dbo.Temperature

結果

　全部で 111708 件の結果が得られるが，ここでは最初の数件のみとしておく．最初の行の「前日」が NULL なのはこれより前の行が存在しないからである．

年月日	当日	前日	都道府県
2008-04-01	4.3	NULL	北海道
2008-04-02	8.4	4.3	北海道
2008-04-03	9.8	8.4	北海道
2008-04-04	11.8	9.8	北海道
2008-04-05	9.8	11.8	北海道
2008-04-06	14.5	9.8	北海道