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 BYORDER 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

See Also

Duplicate Removal with ROW_NUMBER() and PARTITION 重複を削除するためにWindow 関数がどのように使用できるかについては、こちらをご覧ください。