Windowing って何だったんだろうとふと先週のとある会議のことを思い出してしまい気になって眠れなくなったので、ちょっと就寝前に調べてみた - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

　『 Windowing 』ってなんだろう？とふと思ったので、ちょっと調べてみました。Oracle などでは既にサポートされているものですね。SQL Server では、2012 から正式サポートされたんですね。私は長らく、ある DBMS しか触ってこなかったため、SQL にはかなり疎いところがあります。今からでも勉強しないといけませんね。

　ここのページが非常にわかりやすいと思ったので、ちょっと自分の Azure 環境で実験してみましょう。まずテスト環境として、下記のクエリを実行します。

CREATE DATABASE [TestDB]
--Create testable to hold some data
CREATE TABLE [dbo].[Test_table](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [Department] [nchar](10) NOT NULL,
 [Code] [int] NOT NULL,
 CONSTRAINT [PK_Test_table] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--Insert some test data
insert into Test_table values('A',111)
insert into Test_table values('B',29)
insert into Test_table values('C',258)
insert into Test_table values('D',333)
insert into Test_table values('E',15)
insert into Test_table values('F',449)
insert into Test_table values('G',419)
insert into Test_table values('H',555)
insert into Test_table values('I',524)
insert into Test_table values('J',698)
insert into Test_table values('K',715)
insert into Test_table values('L',799)
insert into Test_table values('M',139)
insert into Test_table values('N',219)
insert into Test_table values('O',869)

　下図のようなデータが格納されます。

f:id:koogucc11:20140302213315p:plain

　次に下記のクエリを実行します。

SELECT id,department,code,
FIRST_VALUE(code) OVER (ORDER BY code) FstValue,
LAST_VALUE(code) OVER (ORDER BY code) LstValue
FROM test_table

　赤枠で囲まれた部分が、結果セットの最小値および最大値となります。

f:id:koogucc11:20140302213614p:plain

　また、結果セットの最大値をすべて同じ値にしたい場合は、ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING を使用することで実現することができます。下記のクエリを実行します。

SELECT id,department,code,
FIRST_VALUE(code) OVER (ORDER BY code) FstValue,
LAST_VALUE(code) OVER (ORDER BY code
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LstValue
FROM test_table

f:id:koogucc11:20140302214225p:plain

　ここから、Windowing 関数メインの内容です。次に、下記のクエリを実行します。

insert into Test_table values( 'A',51)
insert into Test_table values( 'A',111)
insert into Test_table values( 'A',169)
insert into Test_table values( 'A',514)
insert into Test_table values( 'B',5331)
insert into Test_table values( 'B',12211)
insert into Test_table values( 'B',101)
insert into Test_table values( 'B',135)

　Patition By 句を使った下記のクエリを実行します。

SELECT id,department,code,
FIRST_VALUE(code)  OVER (PARTITION BY department ORDER BY code) FstValue,
LAST_VALUE(code) OVER (PARTITION BY department ORDER BY code) LstValue
FROM test_table

　下記のように、department 列の値で分割され、それぞれの区画で最大・最少値が求められています。これは便利ですね。

f:id:koogucc11:20140302214841p:plain