Window Functions

CData Virtuality Server はANSI SQL 2003 Window 関数をサポートしています。Window 関数は、GROUP BY句を必要とせずに、結果セットのサブセットに集約関数を適用することを可能にします。Window 関数はAggregate関数と似ていますが、OVER句またはWindow指定を使用する必要があります。

Usage

aggregate|ranking OVER ([PARTITION BY ...] [ORDER BY ...] [<FRAME-CLAUSE>])

aggregateは任意の集約関数です。順位はROW_NUMBER() , RANK() , DENSE_RANK()のいずれかになります。

Syntax Rules

Window 関数は、クエリ式のSELECTおよびORDER BY句でのみ使用できます；
Window 関数を入れ子にすることはできません；
パーティショニングとorder by 式にサブクエリまたは外部参照を含めることはできません；
ランキング（ROW_NUMBER, RANK, DENSE_RANK）関数は、ウィンドウ指定ORDER BY句の使用を必要とします；
XMLAGG ORDER BY句は、ウィンドウの場合は使用できません；
ウィンドウ仕様ORDER BY句は、エイリアス名を参照したり、位置順序を使用したりすることはできません；
WINDOW 仕様が注文されている場合、WINDOW 集合体はDISTINCTを使用することはできません；
Frame句は、PARTITION句（PARTITION BY、ORDER BYのうち少なくとも1つが存在すること）と組み合わせてのみ使用できます。

Frame Clause

Frame句によって、ユーザはパーティション内の行の動的なグループ、もしくは範囲を指定することができます（ウィンドウのスライドフレームに似ています）。使い方は2通り：

```
[ RANGE | ROWS ] frame_start
```

[ RANGE | ROWS ] BETWEEN frame_start AND frame_end

frame_startおよび frame_endのいずれかを指定します：

```
UNBOUNDED PRECEDING
```
```
value PRECEDING (ROWS only)
```
```
CURRENT ROW
```
```
value FOLLOWING (ROWS only)
```
UNBOUNDED FOLLOWING

frame_startを UNBOUNDED FOLLOWINGにすることはできず、frame_end を UNBOUNDED PRECEDINGにすることはできず、frame_end をframe_start よりも前にすることはできません。例えば、RANGE BETWEEN CURRENT ROW AND value PRECEDING は許されません。

Examples of Usage

my_tableというテーブルがあるとします：

x	y
1	1
2	1
3	1
4	2
5	3

SELECT x, SUM(y) OVER (PARTITION BY y ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS window_column 
FROM data_source.my_table;
 
-- Result:
-- |-----------|---------------|
-- |     x     | window_column |
-- |-----------|---------------|
-- |     1     |       1       |
-- |     2     |       2       |
-- |     3     |       3       |
-- |     4     |       2       |
-- |     5     |       3       |
-- |-----------|---------------|
 
 
SELECT x, COUNT(y) OVER (PARTITION BY y RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS window_column 
FROM data_source.my_table;
 
-- Result:
-- |-----------|---------------|
-- |     x     | window_column |
-- |-----------|---------------|
-- |     1     |       3       |
-- |     2     |       3       |
-- |     3     |       3       |
-- |     4     |       1       |
-- |     5     |       1       |
-- |-----------|---------------|
 
 
SELECT x, y*100/SUM(y) OVER (PARTITION BY y RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS window_column 
FROM data_source.my_table;
 
-- Result:
-- |-----------|---------------|
-- |     x     | window_column |
-- |-----------|---------------|
-- |     1     |       33      |
-- |     2     |       33      |
-- |     3     |       33      |
-- |     4     |       100     |
-- |     5     |       100     |
-- |-----------|---------------|

Function Definitions

Function	Description
`ROW_NUMBER()`	Functionally same as `COUNT(*)` with the same window specification. Assigns a number to each row in a partition starting at 1
`RANK()`	Assigns a number to each unique ordering value within each partition starting at 1, such that the next rank is equal to the count of prior rows
`DENSE_RANK()`	Assigns a number to each unique ordering value within each partition starting at 1, such that the next rank is sequential
`LEAD(scalar_expression [, offset [, default]])`	Returns `scalar_expression` evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead returns default (which must be of the same type as `scalar_expression`). Both offset and default are evaluated for the current row. If omitted, offset defaults to `1` and default to `null`
`LAG(scalar_expression [, offset [, default]])`	Returns `scalar_expression` evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead returns default (which must be of the same type as `scalar_expression`). Both offset and default are evaluated for the current row. If omitted, offset defaults to `1` and default to `null`
`FIRST_VALUE(scalar_expression)`	Returns `scalar_expression` evaluated at the row that is the first row of the window frame
`LAST_VALUE(scalar_expression)`	Returns `scalar_expression` evaluated at the row that is the last row of the window frame

Processing

Window 関数は、SELECT句からの出力を作成する直前に論理的に処理されます。GROUP BY句がある場合、Window 関数は入れ子になった集約を使用することができます。Window関数の存在による出力順序への影響は保証されていません。SELECTステートメントには、ORDER BY句がないと、順序が予測できません。

CData Virtuality Server は同じWindow 指定を持つすべての Window 関数を一緒に処理します。一般的に、SELECT句に入力される行値に対する完全なパスは、各ユニークなウィンドウ指定に対して必要となります。各Window指定に対して、PARTITION BY句に従って値がグループ化されます。PARTITION BY句が指定されない場合、入力全体が1つのパーティションとして扱われます。出力値は、現在の行値、そのピア（つまり、順序に関して同じ行）、および、パーティション内の順序に基づくすべての前の行値に基づいて決定されます。ROW_NUMBER関数は、ピアの数に関係なく、すべての行に一意の値を割り当てます。

Example Windowed Results

SELECT name, salary, max(salary) over (partition by name) as max_sal,
          rank() over (order by salary) as rank, dense_rank() over (order by salary) as dense_rank,
          row_number() over (order by salary) as row_num 
FROM data_source.employees

name	salary	max_sal	rank	dense_rank	row_num
John	100000	100000	2	2	2
Henry	50000	50000	5	4	5
John	60000	100000	3	3	3
Suzie	60000	150000	3	3	4
Suzie	150000	150000	1	1	1