OVER 句の紹介

SQL Server

OVER 句の紹介です。SQL Server 2005 から使えるようになりました。
SQL Server 2012 でも強化されていますが、2005 時点の内容で説明させてもらいます。
OVER 句 (Transact-SQL)


順位付け関数と集計関数に使用出来ます。
順位付け関数は、RANK, ROW_NUMBER 等があります。
順位付け関数 (Transact-SQL)
ROW_NUMBER はよくサンプルが転がってると思うので、ここでは集計関数にスポットを当ててみます。


さっそくクエリと結果を見ながら説明していきます。
ここでの例は、同じコードのデータでも開始日違いで属性が変わるような履歴を持つ部門マスタがあります。

use [テスト]

create table [部門マスタ] ( 
  [部門コード] char(4) not null, 
  [開始日] datetime not null, 
  [部門名] nvarchar(20) not null, 
  [削除フラグ] bit not null 
)
alter table [部門マスタ]
  add constraint [部門マスタ_PK] primary key ([部門コード], [開始日])
insert into [部門マスタ] values ('0001', '1753/01/01', N'営業', 0)
insert into [部門マスタ] values ('0002', '1753/01/01', N'人事', 0)
insert into [部門マスタ] values ('0003', '1753/01/01', N'経理', 0)
insert into [部門マスタ] values ('0001', '2010/04/01', N'営業_大阪', 0)
insert into [部門マスタ] values ('0004', '2010/04/01', N'営業_東京', 0)
insert into [部門マスタ] values ('0001', '2011/04/01', N'営業_大阪本社', 0)
insert into [部門マスタ] values ('0002', '2011/04/01', N'人事_大阪本社', 0)
insert into [部門マスタ] values ('0003', '2011/04/01', N'経理_大阪本社', 0)
insert into [部門マスタ] values ('0002', '2012/04/01', N'人事_大阪本社', 1)
select * from [部門マスタ]


この中から最新の部門データで削除されていない物が欲しい！としましょう。
OVER 句が追加される以前なら、このようなクエリになると思います。

select 
  [部門マスタ].* 
from 
  [部門マスタ] inner join ( 
    select [部門コード], max([開始日]) as [最新開始日] 
    from [部門マスタ] 
    group by [部門コード]
  ) [最新] on ( 
    [部門マスタ].[部門コード] = [最新].[部門コード] 
    and [部門マスタ].[開始日] = [最新].[最新開始日]
  ) 
where [部門マスタ].[削除フラグ] = 0 
order by [部門マスタ].[部門コード]


部門コード毎に最新の開始日のデータ取得するサブクエリを用意し、それを自己結合してデータを取得するイメージです。

しかし、OVER 句があると自己結合が不要になります！

select 
  [部門コード], [開始日], [部門名], [削除フラグ]
from 
  ( 
    select 
      [部門コード], [開始日], [部門名], [削除フラグ]
      , max([開始日]) over (partition by [部門コード]) as [最新開始日] 
    from 
      [部門マスタ]  
  ) [データ] 
where 
  [開始日] = [最新開始日] 
  and [削除フラグ] = 0
order by [部門コード]

こちらのクエリもまず、サブクエリを用意しますが、over partition by で部門コード毎の最大の開始日を取得しています。

select 
  [部門コード], [開始日], [部門名], [削除フラグ]
  , max([開始日]) over (partition by [部門コード]) as [最新開始日] 
from 
  [部門マスタ]

これだけを実行したイメージはこちら

この中で、[開始日]と[最新開始日] が一致するデータが対象のデータとなります。
自己結合をしていないので、部門マスタを見るのは1回で済みます。
全体の実行結果は、自己結合しているクエリと同じなので、省きます。


ここでの肝は、partition by を使うことによって、集計関数を適用する範囲を決めていることです。
MAX だけでは無く SUM 等でも使えますので、知っていると便利かもしれませんね。


以下、クエリ全文

use [テスト]

create table [部門マスタ] ( 
  [部門コード] char(4) not null, 
  [開始日] datetime not null, 
  [部門名] nvarchar(20) not null, 
  [削除フラグ] bit not null 
)
alter table [部門マスタ]
  add constraint [部門マスタ_PK] primary key ([部門コード], [開始日])
insert into [部門マスタ] values ('0001', '1753/01/01', N'営業', 0)
insert into [部門マスタ] values ('0002', '1753/01/01', N'人事', 0)
insert into [部門マスタ] values ('0003', '1753/01/01', N'経理', 0)
insert into [部門マスタ] values ('0001', '2010/04/01', N'営業_大阪', 0)
insert into [部門マスタ] values ('0004', '2010/04/01', N'営業_東京', 0)
insert into [部門マスタ] values ('0001', '2011/04/01', N'営業_大阪本社', 0)
insert into [部門マスタ] values ('0002', '2011/04/01', N'人事_大阪本社', 0)
insert into [部門マスタ] values ('0003', '2011/04/01', N'経理_大阪本社', 0)
insert into [部門マスタ] values ('0002', '2012/04/01', N'人事_大阪本社', 1)
select * from [部門マスタ]
-- 最新の部門情報が欲しい！(有効なものだけ)
select 
  [部門マスタ].* 
from 
  [部門マスタ] inner join ( 
    select [部門コード], max([開始日]) as [最新開始日] 
    from [部門マスタ] 
    group by [部門コード]
  ) [最新] on ( 
    [部門マスタ].[部門コード] = [最新].[部門コード] 
    and [部門マスタ].[開始日] = [最新].[最新開始日]
  ) 
where [部門マスタ].[削除フラグ] = 0 
order by [部門マスタ].[部門コード] 
-- OVER 句を使った場合
select 
  [部門コード], [開始日], [部門名], [削除フラグ]
from 
  ( 
    select 
      [部門コード], [開始日], [部門名], [削除フラグ]
      , max([開始日]) over (partition by [部門コード]) as [最新開始日] 
    from 
      [部門マスタ]  
  ) [データ] 
where 
  [開始日] = [最新開始日] 
  and [削除フラグ] = 0
order by [部門コード] 
go
drop table [部門マスタ]
go