T-SQL - OVER句のROWS句のサンプル

OVER句のROWS句がすごいなと思ったので、少し整理していくつかサンプルを残しておきます。

OVER句を使うと行をグルーピングしたり、行に順位付けをしたり、行セットの範囲を指定したりといったことができます。

OVER句の括弧の中には次にようにPARTITION BY句、ORDER BY句、ROWS句を書きますが、今回はグルーピングするためのPARTITION BY句は使わず、集約関数とあわせて使うORDER BY句、ROWS句を見ていきたいと思います。

OVER(
    PARTITION BY カラム名
    ORDER BY カラム名
    ROWS(RANGE) なんとか
)

ROWS句

ROWS句は結果セットの範囲を指定する機能です。現在の行からみた前後の行の範囲を指定します。おそらくこの範囲のことを「ウィンドウ」と言うのかなと。

次の書き方で行セットをAからBの範囲内に絞ります。

rows between A and B

AとBの書き方としては5パターンあります。

-- 最初の行
unbounded preceding

-- 現在の行からみてn行前の行
n preceding

-- 現在の行
current row

-- 現在の行からみてn行後の行
n following

-- 最後の行
unbounded following

AとBを埋めていくつかROWS句を書いてみます。

-- 最初の行から現在の行まで
rows between unbounded preceding and current row

-- 1つ前の行から1つの後の行まで
rows between 1 preceding and 1 following

-- 1つ前の行から1つ前の行まで（つまり1つ前の行）
rows between 1 preceding and 1 preceding

ROWS句を使ったサンプル

ROWS句を使ったクエリを書いてみます。まずは月別の売上みたいなテストデータがあるとします。

drop table if exists #Sale;

select *
into #Sale
from (values
    (N'201610', 100),
    (N'201611', 200),
    (N'201612', 300),
    (N'201701', 200),
    (N'201702', 400),
    (N'201703', 200)) as Src([Date], Sale);

select *
from #Sale
order by [Date];

/*
Date   Sale
------ -----------
201610 100
201611 200
201612 300
201701 200
201702 400
201703 200
*/

移動平均を求める

例えば直近3ヶ月間の平均といったものを移動平均と言うようです。

ROWS句に2行前の行から現在の行を指定して、直近3ヶ月の移動平均を計算してみます。わかりやすいように直近3ヶ月の合計も計算しておきます。

-- 移動平均
select *,
    sum(Sale) over(
        order by [Date]
        rows between 2 preceding and current row
    ) as 直近3ヶ月の合計,
    avg(Sale) over(
        order by [Date]
        rows between 2 preceding and current row
    ) as 直近3ヶ月の移動平均
from #Sale
order by [Date];

/*
Date   Sale        直近3ヶ月の合計    直近3ヶ月の移動平均
------ ----------- ----------- -----------
201610 100         100         100
201611 200         300         150
201612 300         600         200
201701 200         700         233
201702 400         900         300
201703 200         800         266
*/

1行目（Dateが201610）は前月と前々月の行がないので、合計も平均も現在の行の値そのままになっています。2行目（Dateが201611）は前々月の行がないので2ヶ月分の合計と平均になっています。

累計を求める

最初の行からその行までの累計を計算してみます。

-- 累計
select *,
    sum(Sale) over(
        order by [Date]
        rows between unbounded preceding and current row
    ) as 累計
from #Sale
order by [Date];

/*
Date   Sale        累計
------ ----------- -----------
201610 100         100
201611 200         300
201612 300         600
201701 200         800
201702 400         1200
201703 200         1400
*/

実はこれ数年前に試しました。

ichiroku11.hatenablog.jp

このとき書いたクエリはこのROWS句を省略したようなものでした。（このときはROWS句をよく理解していなかったような気が・・・。）

別の行と比較する

ROWS句では1つ前の1行、1つ後の1行というようにピンポイントで行を指定できるので、ある行と現在の行を比較することもできます。1つの前の行と比較して、前月からの増減を計算してみます。わかりやすいように前月のSaleの値も取得しています。

--- 前月からの増減
select *,
    sum(Sale) over(
        order by [Date]
        rows between 1 preceding and 1 preceding
    ) as 前月のSale,
    Sale - sum(Sale) over(
        order by [Date]
        rows between 1 preceding and 1 preceding
    ) as 前月からの増減
from #Sale
order by [Date];
/*
Date   Sale        前月のSale     前月からの増減
------ ----------- ----------- -----------
201610 100         NULL        NULL
201611 200         100         100
201612 300         200         100
201701 200         300         -100
201702 400         200         200
201703 200         400         -200
*/

1行目（Dateが201610）は前月の行がないので前月の値がNULLになっています。

ROWS句すごいなと。