ウィンドウ (Windowing) 関数は、データに対してサーバーに繰り返し呼び出すことなく、一連のデータを異なる観点から見ることができる優れた方法です。 たとえば、ある列の合計を収集し、それを詳細レベルのデータと並べて表示することができます。たとえば、「SalesAmount」と「SUM（SalesAmount）」を同じ行に表示することができます。 PERCENT_RANK のような分析関数や、ROW_NUMBER のようなランキング関数も、結果セットの粒度を変更したり、同じソース データを何度も取得することなく実行することができます。 Quite bully!”
ウィンドウ関数はすべて OVER() 節を使用し、関数がどのように評価されるかを定義するために使用されます。 OVER()句は3つの異なる引数を受け付けます：
- PARTITION BY: 指定された列の値が変わるたびにカウンターをリセットします。
- ORDER BY。 関数が評価する行を並べ替えます。 これは結果セット全体を順番に並べるのではなく、関数が行を処理する方法のみを順番に並べます。
- ROWS BETWEEN: 関数によって評価される行をさらに制限する方法を指定します。
重量挙げ競技から簡略化したデータを見ていることにしましょう。 以下はいくつかのコード例です（ウィンドウ関数を追加/削除しても、戻ってくる行の数は変わらないので、すべて1つのSELECTステートメントで実行します）。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
SELECT
LiftID
, LiftDate
, LiftPersonID
, LiftWeight
/* ROW_NUMBERはLiftID順に行番号をリストアップします。
カウンターはLiftDateとLiftPersonIDの新しい組み合わせごとにリセットされます */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUMは持ち上げられた重量を合計します。
最初のSUMは、結果セット全体の総計を表示します。
2番目のSUMは、その行のリフト日のリフト重量の合計を表示します。
3番目のSUMは、その行のリフト日付と人のリフト重量を合計したものを表示します。 */
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* AVGは平均持ち上げ重量を表わします。
最初のAVGは、その行のリフト日付の平均リフト重量を表示します。
2番目のAVGは、その行のリフト日付と人の平均リフト重量を表示します。 */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG と LEAD により現在の行で後ろや前の行のデータについてレポートすることが可能です。
このLAG関数は、1つ後ろの行のLiftWeightを（LiftIDの順に）返し、値が見つからない場合はNULLの代わりに0を返します。
LEAD関数は、3つ前の行のLiftWeightを取得します。 オプションのデフォルト値（LAG関数に与えた「0」のような）を指定していないため、3行先に行がない場合はNULLを返します。 */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE AND LAST_VALUE は結果セット中の指定列の最初の値と最後の値を返します。
このFIRST_VALUE関数は、結果セット内の最初のLiftWeightを返します。
このLAST_VALUE関数は、結果セット内の最後のLiftWeightを返します。
***WARNING: LAST_VALUE に ROWS BETWEEN を指定しないと、予期しない結果を得ることがあります。***
*/
, FIRST_VALUE(LiftWeight) OVER (ORDER BY LiftDate) AS FirstLift
, LAST_VALUE(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastLift
/* ROWS BETWEENを使ってSUMするとウィンドウ関数で評価する範囲を狭くすることができます。
最初のSUMは、現在の行を含むまでの行のすべてのLiftWeightの値を追加します。
*/
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WeightRunningTotal
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS WeightSumLast4
FROM dbo.Lifts
|
ウィンドウ関数に関する考察
SQL Server 2012 以降を持っていない場合、ウィンドウ関数の棚はかなりむき出しです。SQL Server 2005 から 2008 R2 では集約関数の OVER 句で PARTITION BY しかできず、 RANK() と ROW_NUMBER() が使用できました。 それだけです。 もし、あなたがまだこれらの以前のバージョンを使用しているのであれば、2012年以降に移行することを強くお勧めします。 複数の CTE を記述することなく、どれだけの時間を節約でき、クエリがどれだけ速くなるかを考えてみてください。 インデックスをヒットしている場合、関連する読み取りを大幅に減らすことができます。 トレードオフはありますが、通常、非常に有利なものです。 ウィンドウ関数では、SQL Serverがウィンドウを構築し、関数を計算する必要があります（ウィンドウスプール、セグメント、シーケンスプロジェクト、スカラー計算などのタスクとして表示されます）。 その際、Worktableに読み込みが追加されます。 それでも、元データを何度も取りに行き、必要なら集計し、すべてを結合するよりは、一般にこの方がコストがかからない。 5081>
最後に、PARTITION BY、ORDER BY、ROWS BETWEENなどのウィンドウ関数に設定した制限は、ウィンドウ関数にコンテキストを適用するためにあり、結果セット全体には適用されないことを思い出してください。 言い換えれば、SELECT文はウィンドウ関数に何を指示しても影響を受けません。