RANGE に関するウィンドウ関数

RAQL では、SELECT aggregationFunction(x) OVER(ORDER BY y) FROM s が SELECT aggregationFunction (x) OVER(ORDER BY y ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM s と意味論的に同等です。しかし、SQL 標準では、SELECT aggregationFunction (x) OVER(ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM s に相当する意味論を定義しています。これは、現在の行だけでなく、すべての「同等」行 (順序付けの観点から現在の行と同一の行) がウィンドウに含まれていることを意味します。これは、明示的なウィンドウ フレームの仕様がない移動集約は、現在のところ、RAQL と SQL 標準に準拠した DBMS で異なる結果を出すことを意味します。

SQL 標準との高い順守性を保証するため、ウィンドウ フレームも RANGE キーワードを使用して指定できます。

意味論

ROWS キーワードを使用して定義されるウィンドウ フレームは、理解が容易です。ROWS BETWEEN x PRECEDING AND y FOLLOWING ウィンドウ フレーム句は、入力パーティションの各行に対して、現在の行番号から相対的に対応するウィンドウの最初と最後の行番号を定義する、x と y の 2 つのオフセットを単純に定義します。PRECEDING と FOLLOWING は、オフセットが負 (その行番号が現在の行番号よりも前) なのか正なのかを個別に示します。

テーブル 2。例

rowNumber

ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING

ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

1

Window = [1,4]

Window = []

Window = [1,10]

2

Window = [1,5]

Window = [1,1]

Window = [2,10]

3

Window = [1,6]

Window = [1,2]

Window = [3,10]

4

Window = [1,7] (行番号 4-3 から 4+3)

Window = [1,3]

Window = [4,10]

5

Window = [2,8]

Window = [2,4]

Window = [5,10]

6

Window = [3,9]

Window = [3,5]

Window = [6,10]

7

Window = [4,10]

Window = [4,6]

Window = [7,10]

8

Window = [5,10]

Window = [5,7]

Window = [8,10]

9

Window = [6,10]

Window = [6,8]

Window = [9,10]

10

Window = [7,10]

Window = [7,9]

Window = [10,10]

一方、RAMGE キーワードを使用するウィンドウ フレーム仕様の意味論は、物理的な行番号ではなく、列の実際の値に依存します。それでもウィンドウ フレーム句は 2 つのオフセットの x と y を定義しますが、これらのオフセットは行番号ではなく、実際の列の値に加算されます。そのため、RANGE と、UNBOUNDED PRECEDING、UNBOUNDED FOLLOWING、CURRENT ROW 以外のオフセットが関わるウィンドウ フレーム仕様は、単一列で順序が付けられるウィンドウを必要とし、その列は数値データ型 (オフセットの計算を許可するため) である必要があります。

テーブル 3。例

rowNumber

salary

SELECT avg(salary) OVER (ORDER BY salary RANGE BETWEEN 300 PRECEDING AND 300 FOLLOWING) FROM ...

対象ウィンドウ

1

1750

1850

[1450,2050]

salary は 1750 - 300 と 1750 + 300 の間

2

1900

1850

[1600,2200]

3

1900

1850

[1600,2200]

4

2200

2100

[1900,2500]

5

2400

2300

[2100,2700]

6

2750

2866.67

[2450,3050]

7

2900

2980

[2600,3200]

8

2950

2980

[2650,3250]

9

3100

2980

[2800,3400]

10

3200

2980

[2900,3500]

テーブル 4。例

rowNumber

salary

SELECT avg(salary) OVER (ORDER BY salary RANGE BETWEEN 300 PRECEDING AND 300 FOLLOWING) FROM ...

対象ウィンドウ

1

1750

1

[1750,1750]

salary は 1750 - 0 と 1750 + 0 の間

2

1900

2

[1900,1900]

3

1900

2

[1900,1900]

4

2200

1

[2200,2100]

5

2400

1

[2400,2400]

6

2750

1

[2750,2750]

7

2900

1

[2900,2900]

8

2950

1

[2950,2950]

9

3100

1

[3100,3100]

10

3200

1

[3200,3200]

注意: RANGE フレーム句のオフセットは、句の順序で定義された順番で論理オフセットを定義します。句の順序が降順を指定すると (次の例を参照)、10 PRECEDING オフセットは、「値は現在の順番の現在の値より前で、最大で 10 (年/単位/...) 異なる」と言い換えられます。

テーブル 5。例

rowNumber

age

SELECT COUNT(*) OVER (ORDER BY age DESC RANGE 10 PRECEDING) FROM ...

対象ウィンドウ

1

75

1

[85, 75]

ages は 75 + 10 と 75 - 0 の間

2

68

2

[78, 68]

3

68

2

[72, 62]

4

56

2

[66, 56]

5

55

3

[65, 55]

6

51

3

[61, 51]

7

48

5

[58, 48]

8

48

5

[58, 48]

9

47

6

[57, 47]

10

43

5

[53, 43]

影響を受けるクエリ

前述のように、新しい RANGE 指定子は、SQL 標準の順守を保証するために導入されました。しかし、既存のクエリがこれらの変更に影響されるケースは非常にわずかです。仕様による明示的な順序によって

式による順序が一意であれば、各行に対して順序の観点から同等ではなく、暗黙的に追加されたウィンドウ フレーム句の RANGE UNBOUNDED PRECEDING は ROWS UNBOUNDED PRECEDING に相当するため、クエリの結果は以前と同じになります。ただし、仕様による順序が一意でない場合は、1 行または複数の同等行を持つ行がある可能性があるため、クエリ結果は以前の RAQL リリースとは異なり、ユーザーはウィンドウ フレーム句の ROWS UNBOUNDED PRECEDING を明示的に追加して、以前の結果を出すことを望む可能性があります。