Fensterfunktionen über RANGEs

In RAQL stimmt SELECT aggregationFunction(x) OVER(ORDER BY y) FROM s mit SELECT aggregationFunction (x) OVER(ORDER BY y ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM s semantisch überein. Der SQL-Standard legt jedoch die Semantik fest, damit sie mit SELECT aggregationFunction (x) OVER(ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM s übereinstimmt, d. h., nicht nur die aktuelle Zeile sondern alle 'peer'-Zeilen (Zeilen, die mit der aktuellen Zeile bis auf die Sortierung übereinstimmen) sind im Fenster enthalten. Dies bedeutet, dass ein rollendes Aggregat ohne explizite Fensterrahmenspezifikation aktuell unterschiedliche Ergebnisse in RAQL und jeder mit dem SQL-Standard übereinstimmenden DBMS liefert.

Um eine hohe Übereinstimmung mit dem SQL-Standard sicherzustellen, können Fensterrahmen auch mithilfe des Schlüsselworts RANGE angegeben werden.

Semantik

Fensterrahmen, die mit dem Schlüsselwort ROWS definiert werden, sind leicht zu verstehen. Die Fensterrahmenklausel ROWS BETWEEN x PRECEDING AND y FOLLOWING definiert lediglich zwei Offsets x und y, die für jede Zeile in der Eingabepartition die erste und letzte Zeilennummer des entsprechenden Fensters in Bezug auf die aktuelle Zeilennummer definieren. PRECEDING und FOLLOWING geben lediglich an, ob das Offset negativ (die Zeilennummer steht vor der aktuellen Zeilennummer) oder positiv ist.

Tabelle 2: Beispiel

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] (ab Zeilennummer 4-3 bis 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]

Andererseits hängt die Semantik der Fensterrahmenspezifikationen anhand des Schlüsselworts RANGE nicht von den physischen Zeilennummern ab, sondern von den aktuellen Werten einer Spalte. Dennoch definiert die Fensterrahmen-Klausel zwei Offsets x und y. Diese Offsets werden nicht der Zeilennummer angefügt, sondern einem aktuellen Spaltenwert. Deshalb erfordert jede Fensterrahmenspezifikation mit RANGE und einem Offset außer UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING oder CURRENT ROW, dass das Fenster an einer einzelnen Spalte angeordnet ist und diese Spalte muss einen numerischen Datentyp haben (damit Offset-Arithmetiken möglich sind).

Tabelle 3: Beispiel

rowNumber

salary

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

considered window

1

1750

1850

[1450,2050]

salaries between 1750 - 300 and 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]

Tabelle 4: Beispiel

rowNumber

salary

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

considered window

1

1750

1

[1750,1750]

salaries between 1750 - 0 and 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]

Hinweis: Die Offsets in einer RANGE-Rahmenklausel definieren logische Offsets in der erwarteten Reihenfolge, die mithilfe der 'order by'-Klausel definiert ist. Wenn der Befehl mithilfe der 'order by'-Klausel eine absteigende Reihenfolge (siehe nächstes Beispiel) angibt, übersetzt das Offset 10 PRECEDING in "ein Wert, der dem aktuellen Wert in der aktuellen Abfolge vorausgeht und sich mindestens um 10 (Jahre/Einheiten/...) unterscheidet"

Tabelle 5: Beispiel

rowNumber

age

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

considered window

1

75

1

[85, 75]

ages between 75 + 10 and 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]

Betroffene Abfragen

Wie oben bereits erwähnt, wurde der neue RANGE-Spezifizierer eingeführt, um die Übereinstimmung mit dem SQL-Standard zu gewährleisten. Es gibt jedoch nur wenige Fälle, bei denen bestehende Abfragen durch diese Änderungen betroffen sein sollten. Es könnten nur Abfragen betroffen sein, die eine Fensterfunktion mit

Sind die 'order by'-Ausdrücke eindeutig, gibt es für jede Zeile keine Peers in Bezug auf die Reihenfolge und die implizit hinzugefügte Fensterrahmen-Klausel RANGE UNBOUNDED PRECEDING stimmt mit ROWS UNBOUNDED PRECEDING überein; somit bleiben die Abfrageergebnisse dieselben wie zuvor. Wenn allerdings die 'order by'-Spezifikation nicht eindeutig ist, können Zeilen vorhanden sein, die eine oder mehrere Peer-Zeilen haben, so dass sich das Abfrageergebnis vom Ergebnis aus früheren RAQL-Releases unterscheidet und der Benutzer die Fensterrahmen-Klausel ROWS UNBOUNDED PRECEDING explizit einfügen möchte, um die früheren Ergebnisse zu erhalten.