都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

Windowing って何だったんだろうとふと先週のとある会議のことを思い出してしまい気になって眠れなくなったので、ちょっと就寝前に調べてみた

 『 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

 おお!、サブクエリとか使うよりCOUNT, SUM などの集計とか非常に楽になるし、サブクエリより、SQL がすっきりし、断然読みやすいです。

※ああ、今やってるパッケージにも適用したら、かなり簡単になる個所がいっぱいありそう... ひとりごとです....

※あ、自習書の Kindle 版とかあったんだ。ちょっとほしいかも。