Konzepte für Analysefunktionen in Standard-SQL

Eine Analysefunktion berechnet Werte für eine Gruppe von Zeilen und gibt für jede Zeile ein einzelnes Ergebnis zurück. Dies unterscheidet sie von einer Aggregatfunktion, die ein einzelnes Ergebnis für eine Gruppe von Zeilen zurückgibt.

Eine Analysefunktion enthält eine OVER-Klausel, die ein Fenster mit Zeilen um die auszuwertende Zeile definiert. Für jede Zeile wird das Ergebnis der Analysefunktion anhand des ausgewählten Zeilenfensters als Eingabe berechnet, wobei möglicherweise eine Aggregation stattfindet.

Mit Analysefunktionen können Sie gleitende Durchschnitte berechnen, Elemente sortieren, kumulative Summen berechnen und andere Analysen durchführen.

Folgende Funktionen können als Analysefunktionen verwendet werden: Navigationsfunktionen, Nummerierungsfunktionen und aggregierte Analysefunktionen.

Syntax von Analysefunktionen

analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

Notationsregeln

  • Eckige Klammern "[ ]" zeigen optionale Klauseln an.
  • Runde Klammern "( )" sind tatsächlich im Code vorhandene Klammern.
  • Der senkrechte Strich "|" zeigt ein logisches ODER an.
  • Geschweifte Klammern "{ }" umschließen einen Optionssatz.
  • Ein Komma gefolgt von Auslassungspunkten in eckigen Klammern "[, ... ]" zeigt an, dass das vorhergehende Element in einer durch Kommas getrennten Liste wiederholt werden kann.

Beschreibung

Eine Analysefunktion berechnet Ergebnisse für eine Gruppe von Zeilen. Sie können die folgende Syntax verwenden, um eine Analysefunktion zu erstellen:

  • analytic_function_name: Die Funktion, die einen Analysevorgang ausführt. Hier könnte beispielsweise die Nummerierungsfunktion RANK() verwendet werden.
  • argument_list: Argumente, die für die Analysefunktion spezifisch sind. Einige Funktionen haben sie, andere nicht.
  • OVER: Keyword, das in der Syntax der Analysefunktion vor der OVER-Klausel stehen muss.
  • over_clause: verweist auf ein Fenster, das eine Gruppe von Zeilen in einer Tabelle definiert, für die eine Analysefunktion verwendet werden soll
  • window_specification: definiert die Spezifikationen für das Fenster
  • window_frame_clause: definiert den Window Frame für das Fenster
  • rows_range: definiert die physischen Zeilen oder einen logischen Bereich für einen Window Frame

Hinweise

Eine Analysefunktion kann als skalarer Ausdrucksoperand an zwei Stellen in der Abfrage vorkommen:

  • In der SELECT-Liste. Wenn die Analysefunktion in der Liste SELECT angezeigt wird, können sich ihre Argumentliste und die OVER-Klausel nicht auf Aliasse beziehen, die in derselben SELECT-Liste eingeführt wurden.
  • In der ORDER BY-Klausel. Wenn die Analysefunktion in der ORDER BY-Klausel der Abfrage enthalten ist, kann ihre Argumentliste auf Aliasse aus der SELECT-Liste verweisen.

Eine Analysefunktion kann sich in ihrer Argumentliste oder ihrer OVER-Klausel nicht auf eine andere Analysefunktion beziehen, auch nicht indirekt durch einen Alias.

Eine Analysefunktion wird nach der Aggregation ausgewertet. Beispielsweise werden die GROUP BY-Klausel und nicht analytische Aggregatfunktionen zuerst ausgewertet. Da Aggregatfunktionen vor Analysefunktionen ausgewertet werden, können Aggregatfunktionen als Eingabeoperanden für Analysefunktionen verwendet werden.

Rückgabe

Ein einzelnes Ergebnis für jede Zeile in der Eingabe.

OVER-Klausel definieren

analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

Beschreibung

Die OVER-Klausel verweist auf ein Fenster, das eine Gruppe von Zeilen in einer Tabelle definiert, für die eine Analysefunktion verwendet werden soll. Sie können ein named_window angeben, das in Ihrer Abfrage definiert ist, oder Sie können die Spezifikationen für ein neues Fenster definieren.

Hinweise

Wenn weder ein benanntes Fenster noch eine Fensterspezifikation angegeben ist, werden alle Eingabezeilen in das Fenster für jede Zeile aufgenommen.

Beispiele für die Verwendung der OVER-Klausel

In diesen Abfragen werden Fensterspezifikationen verwendet:

In diesen Abfragen wird ein benanntes Fenster verwendet:

Fensterspezifikation definieren

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
  [ window_frame_clause ]

Beschreibung

Definiert die Spezifikationen für das Fenster.

  • PARTITION BY: Zerlegt die Eingabezeilen in separate Partitionen, über die die Analysefunktion unabhängig ausgewertet wird.
    • In der PARTITION BY-Klausel sind mehrere Partitionsausdrücke zulässig.
    • Ein Ausdruck kann keine Gleitkommatypen, nicht gruppierbaren Typen, Konstanten oder Analysefunktionen enthalten.
    • Wenn diese optionale Klausel nicht verwendet wird, enthalten alle Zeilen der Eingabetabelle eine einzelne Partition.
  • ORDER BY: Definiert die Reihenfolge der Zeilen innerhalb einer Partition. Diese Klausel ist in den meisten Fällen optional, für manche Navigationsfunktionen jedoch erforderlich.
  • window_frame_clause: Definiert bei aggregierten Analysefunktionen den Window Frame innerhalb der aktuellen Partition. Der Window Frame bestimmt, was in das Fenster aufgenommen wird. Wenn diese Klausel verwendet wird, ist ORDER BY erforderlich, es sei denn, das Fenster ist vollständig unbegrenzt.

Hinweise

Wenn weder die ORDER BY- noch die Window-Frame-Klausel vorhanden sind, enthält der Window Frame alle Zeilen in der betreffenden Partition.

Wenn für aggregierte Analysefunktionen die ORDER BY-Klausel vorhanden ist, die Window-Frame-Klausel jedoch nicht, wird standardmäßig die folgende Window-Frame-Klausel verwendet:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Die folgenden Abfragen sind beispielsweise gleichwertig:

SELECT book, LAST_VALUE(item)
  OVER (ORDER BY year)
FROM Library
SELECT book, LAST_VALUE(item)
  OVER (
    ORDER BY year
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Library

Regeln für die Verwendung eines benannten Fensters in der Fensterspezifikation

Wenn Sie in Ihren Fensterspezifikationen ein benanntes Fenster verwenden, gelten die folgenden Regeln:

  • Die Spezifikationen im benannten Fenster können mithilfe neuer Spezifikationen, die Sie in der Fensterspezifikationsklausel definieren, erweitert werden.
  • Es ist nicht möglich, redundante Definitionen zu verwenden. Wenn das benannte Fenster und die Fensterspezifikationsklausel eine ORDER BY-Klausel enthalten, wird ein Fehler ausgegeben.
  • Die Reihenfolge der Klauseln ist wichtig. PARTITION BY muss zuerst stehen, gefolgt von ORDER BY und window_frame_clause. Wenn Sie ein benanntes Fenster hinzufügen, werden dessen Fensterspezifikationen zuerst verarbeitet.

    --this works:
    SELECT item, purchases, LAST_VALUE(item)
      OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
    FROM Produce
    WINDOW item_window AS (ORDER BY purchases)
    
    --this does not work:
    SELECT item, purchases, LAST_VALUE(item)
      OVER (item_window ORDER BY purchases) AS most_popular
    FROM Produce
    WINDOW item_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
    
  • Ein benanntes Fenster und PARTITION BY können nicht zusammen in der Fensterspezifikation vorkommen. Wenn Sie PARTITION BY benötigen, fügen Sie es dem benannten Fenster hinzu.

  • Sie können in einer ORDER BY-Klausel, einer äußeren Abfrage oder einer Unterabfrage nicht auf ein benanntes Fenster verweisen.

Beispiele für die Verwendung der Fensterspezifikation

Mit diesen Abfragen werden in einer Analysefunktion Partitionen definiert:

Diese Abfragen enthalten ein benanntes Fenster in einer Fensterspezifikation:

Mit diesen Abfragen wird die Reihenfolge der Zeilen in einer Partition definiert:

Window-Frame-Klausel definieren

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

frame_between:
  {
    BETWEEN  unbounded_preceding AND frame_end_a
    | BETWEEN numeric_preceding AND frame_end_a
    | BETWEEN current_row AND frame_end_b
    | BETWEEN numeric_following AND frame_end_c
  }

frame_start:
  { unbounded_preceding | numeric_preceding | [ current_row ] }

frame_end_a:
  { numeric_preceding | current_row | numeric_following | unbounded_following }

frame_end_b:
  { current_row | numeric_following | unbounded_following }

frame_end_c:
  { numeric_following | unbounded_following }

unbounded_preceding:
  UNBOUNDED PRECEDING

numeric_preceding:
  numeric_expression PRECEDING

unbounded_following:
  UNBOUNDED FOLLOWING

numeric_following:
  numeric_expression FOLLOWING

current_row:
  CURRENT ROW

Die Window-Frame-Klausel definiert den Window Frame um die aktuelle Zeile innerhalb der Partition, über die die Analysefunktion ausgewertet wird. Nur aggregierte Analysefunktionen können eine Window-Frame-Klausel verwenden.

  • rows_range: eine Klausel, die einen Window Frame mit physischen Zeilen oder einem logischen Bereich definiert

    • ROWS: berechnet den Window Frame anhand des physischen Versatzes aus der aktuellen Zeile. Sie können beispielsweise zwei Zeilen vor und nach der aktuellen Zeile einbeziehen.
    • RANGE: berechnet den Window Frame anhand eines logischen Zeilenbereichs um die aktuelle Zeile auf Grundlage des ORDER BY-Schlüsselwerts der aktuellen Zeile. Der angegebene Bereichswert wird zum Schlüsselwert der aktuellen Zeile hinzugefügt oder von diesem subtrahiert, um eine Start- oder Endbereichsbegrenzung für den Window Frame zu definieren. In einem bereichsbasierten Window Frame muss genau ein Ausdruck in der ORDER BY-Klausel vorhanden sein und der Ausdruck muss einen numerischen Typ haben.

    Tipp: Wenn Sie einen Bereich mit einem Datum verwenden möchten, nutzen Sie ORDER BY mit der Funktion UNIX_DATE(). Wenn Sie einen Bereich mit einem Zeitstempel verwenden möchten, nutzen Sie die Funktion UNIX_SECONDS(), UNIX_MILLIS() oder UNIX_MICROS().

  • frame_between: erstellt einen Window Frame mit einer unteren und einer oberen Begrenzung. Die erste Begrenzung stellt die untere Begrenzung dar. Die zweite Begrenzung stellt die obere Begrenzung dar. Wie in der obigen Syntax gezeigt wurde, können nur bestimmte Begrenzungskombinationen verwendet werden.

    • Definieren Sie den Beginn des Window Frames mit unbounded_preceding, numeric_preceding, numeric_following oder current_row.
      • unbounded_preceding: Der Window Frame beginnt am Anfang der Partition.
      • numeric_preceding oder numeric_following: Der Beginn des Window Frames ist relativ zur aktuellen Zeile.
      • current_row: Der Window Frame beginnt bei der aktuellen Zeile.
    • Definieren Sie das Ende des Window Frames mit numeric_preceding, numeric_following, current_row oder unbounded_following.
      • numeric_preceding oder numeric_following: Das Ende des Window Frames ist relativ zur aktuellen Zeile.
      • current_row: Der Window Frame endet bei der aktuellen Zeile.
      • unbounded_following: Der Window Frame endet am Ende der Partition.
  • frame_start: Erstellt einen Window Frame mit einer unteren Begrenzung. Der Window Frame endet bei der aktuellen Zeile.

    • unbounded_preceding: Der Window Frame beginnt am Anfang der Partition.
    • numeric_preceding: Der Beginn des Window Frame ist relativ zur aktuellen Zeile.
    • current_row: Der Window Frame beginnt bei der aktuellen Zeile.
  • numeric_expression: Ein Ausdruck, der einen numerischen Typ darstellt. Der numerische Ausdruck muss eine konstante, nicht negative Ganzzahl oder ein Parameter sein.

Hinweise

Wenn sich eine Begrenzung über den Anfang oder das Ende einer Partition hinaus erstreckt, enthält der Window Frame nur Zeilen aus dieser Partition.

Sie können eine Window-Frame-Klausel nicht mit Navigationsfunktionen und Nummerierungsfunktionen wie RANK() verwenden.

Beispiele für die Verwendung der Window-Frame-Klausel

Mit diesen Abfragen werden Werte mit ROWS berechnet:

Mit diesen Abfragen werden Werte mit RANGE berechnet:

Mit diesen Abfragen werden Werte mit einem teilweise oder vollständig unbegrenzten Fenster berechnet:

Mit diesen Abfragen werden Werte mit numerischen Begrenzungen berechnet:

Mit diesen Abfragen werden Werte mit der aktuellen Zeile als Begrenzung berechnet:

Auf ein benanntes Fenster verweisen

SELECT query_expr,
  analytic_function_name ( [ argument_list ] ) OVER over_clause
FROM from_item
WINDOW named_window_expression [, ...]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ ASC | DESC [, ...] ]
  [ window_frame_clause ]

named_window_expression:
  named_window AS { named_window | ( [ window_specification ] ) }

Ein benanntes Fenster stellt eine Gruppe von Zeilen in einer Tabelle dar, für die eine Analysefunktion verwendet werden soll. Ein benanntes Fenster wird in der WINDOW-Klausel definiert und in der OVER-Klausel einer Analysefunktion referenziert. In einer OVER-Klausel kann ein benanntes Fenster entweder allein vorkommen oder in eine Fensterspezifikation eingebettet sein.

Beispiele

Navigationsfunktionen berechnen im Allgemeinen eine value_expression über eine andere Zeile im Window Frame aus der aktuellen Zeile. Die Syntax der OVER-Klausel hängt von der Navigationsfunktion ab.

Anforderungen für die OVER-Klausel:

  • PARTITION BY: Optional
  • ORDER BY:
    1. Nicht zulässig für PERCENTILE_CONT und PERCENTILE_DISC
    2. Erforderlich für FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD und LAG
  • window_frame_clause:
    1. Nicht zulässig für PERCENTILE_CONT, PERCENTILE_DISC, LEAD und LAG
    2. Optional für FIRST_VALUE, LAST_VALUE und NTH_VALUE

Bei allen Navigationsfunktionen ist der Datentyp des Ergebnisses derselbe Typ wie value_expression.

Konzepte für Nummerierungsfunktionen

Nummerierungsfunktionen weisen jeder Zeile auf Grundlage ihrer Position innerhalb des angegebenen Fensters ganzzahlige Werte zu.

Beispiel für RANK(), DENSE_RANK() und ROW_NUMBER():

WITH Numbers AS
 (SELECT 1 as x
  UNION ALL SELECT 2
  UNION ALL SELECT 2
  UNION ALL SELECT 5
  UNION ALL SELECT 8
  UNION ALL SELECT 10
  UNION ALL SELECT 10
)
SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (ORDER BY x) AS row_num
FROM Numbers

+---------------------------------------------------+
| x          | rank       | dense_rank | row_num    |
+---------------------------------------------------+
| 1          | 1          | 1          | 1          |
| 2          | 2          | 2          | 2          |
| 2          | 2          | 2          | 3          |
| 5          | 4          | 3          | 4          |
| 8          | 5          | 4          | 5          |
| 10         | 6          | 5          | 6          |
| 10         | 6          | 5          | 7          |
+---------------------------------------------------+
  • RANK(): Bei x = 5 ist rank 4, da sich RANK() durch die Anzahl der Peers in der vorherigen Fensteranordnungsgruppe erhöht.
  • DENSE_RANK(): Bei x = 5 ist dense_rank 3, da sich DENSE_RANK() immer um den Wert 1 erhöht und dabei nie ein Wert ausgelassen wird.
  • ROW_NUMBER(): Bei x = 5 ist row_num 4.

Konzepte für aggregierte Analysefunktionen

Eine Aggregatfunktion ist eine Funktion, die eine Berechnung für eine Gruppe von Werten ausführt. Die meisten Aggregatfunktionen können in einer Analysefunktion verwendet werden. Diese Aggregatfunktionen werden als aggregierte Analysefunktionen bezeichnet.

Mit aggregierten Analysefunktionen wird die OVER-Klausel an den Aggregatfunktionsaufruf angehängt. Ansonsten bleibt die Funktionsaufrufsyntax unverändert. Wie ihre Gegenstücke, die Aggregatfunktionen, führen diese Analysefunktionen Aggregationen durch, aber speziell über den relevanten Window Frame für jede Zeile. Die Ergebnisdatentypen dieser Analysefunktionen sind die gleichen wie die von Aggregatfunktionen.

Beispiele für Analysefunktionen

In diesen Beispielen ist das markierte Element die aktuelle Zeile. Die fett formatierten Elemente sind die Zeilen, die in der Analyse enthalten sind.

Gängige, in den Beispielen verwendete Tabellen

Die folgenden Tabellen werden in den nachfolgenden Beispielen für aggregierte Analyseabfragen verwendet: Produce, Employees und Farm.

Tabelle "Produce" (Erzeugnisse)

Einige Beispiele verweisen auf eine Tabelle mit dem Namen Produce:

WITH Produce AS
 (SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
  UNION ALL SELECT 'orange', 2, 'fruit'
  UNION ALL SELECT 'cabbage', 9, 'vegetable'
  UNION ALL SELECT 'apple', 8, 'fruit'
  UNION ALL SELECT 'leek', 2, 'vegetable'
  UNION ALL SELECT 'lettuce', 10, 'vegetable')
SELECT * FROM Produce

+-------------------------------------+
| item      | category   | purchases  |
+-------------------------------------+
| kale      | vegetable  | 23         |
| orange    | fruit      | 2          |
| cabbage   | vegetable  | 9          |
| apple     | fruit      | 8          |
| leek      | vegetable  | 2          |
| lettuce   | vegetable  | 10         |
+-------------------------------------+

Tabelle "Employees" (Mitarbeiter)

Einige Beispiele verweisen auf eine Tabelle mit dem Namen Employees:

WITH Employees AS
 (SELECT 'Isabella' as name, 2 as department, DATE(1997, 09, 28) as start_date
  UNION ALL SELECT 'Anthony', 1, DATE(1995, 11, 29)
  UNION ALL SELECT 'Daniel', 2, DATE(2004, 06, 24)
  UNION ALL SELECT 'Andrew', 1, DATE(1999, 01, 23)
  UNION ALL SELECT 'Jacob', 1, DATE(1990, 07, 11)
  UNION ALL SELECT 'Jose', 2, DATE(2013, 03, 17))
SELECT * FROM Employees

+-------------------------------------+
| name      | department | start_date |
+-------------------------------------+
| Isabella  | 2          | 1997-09-28 |
| Anthony   | 1          | 1995-11-29 |
| Daniel    | 2          | 2004-06-24 |
| Andrew    | 1          | 1999-01-23 |
| Jacob     | 1          | 1990-07-11 |
| Jose      | 2          | 2013-03-17 |
+-------------------------------------+

Tabelle "Farm" (Bauernhof)

Einige Beispiele verweisen auf eine Tabelle mit dem Namen Farm:

WITH Farm AS
 (SELECT 'cat' as animal, 23 as population, 'mammal' as category
  UNION ALL SELECT 'duck', 3, 'bird'
  UNION ALL SELECT 'dog', 2, 'mammal'
  UNION ALL SELECT 'goose', 1, 'bird'
  UNION ALL SELECT 'ox', 2, 'mammal'
  UNION ALL SELECT 'goat', 2, 'mammal')
SELECT * FROM Farm

+-------------------------------------+
| animal    | category   | population |
+-------------------------------------+
| cat       | mammal     | 23         |
| duck      | bird       | 3          |
| dog       | mammal     | 2          |
| goose     | bird       | 1          |
| ox        | mammal     | 2          |
| goat      | mammal     | 2          |
+-------------------------------------+

Gesamtsumme berechnen

Damit wird eine Gesamtsumme für alle Elemente in der Tabelle Produce berechnet.

  • (orange (Orange), apple (Apfel), leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 54 Käufe insgesamt
  • (orange (Orange), apple (Apfel), leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 54 Käufe insgesamt
  • (orange (Orange), apple (Apfel), leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 54 Käufe insgesamt
  • (orange (Orange), apple (Apfel), leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 54 Käufe insgesamt
  • (orange (Orange), apple (Apfel), leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 54 Käufe insgesamt
  • (orange (Orange), apple (Apfel), leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 54 Käufe insgesamt
SELECT item, purchases, category, SUM(purchases)
  OVER () AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 54              |
| leek      | 2          | vegetable  | 54              |
| apple     | 8          | fruit      | 54              |
| cabbage   | 9          | vegetable  | 54              |
| lettuce   | 10         | vegetable  | 54              |
| kale      | 23         | vegetable  | 54              |
+-------------------------------------------------------+

Zwischensumme berechnen

Hiermit wird für jede Kategorie in der Tabelle Produce eine Zwischensumme berechnet.

  • fruit (Obst)
    • (orange (Orange), apple (Apfel)) = 10 Käufe insgesamt
    • (orange (Orange), apple (Apfel)) = 10 Käufe insgesamt
  • vegetable (Gemüse)
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 44 Käufe insgesamt
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 44 Käufe insgesamt
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 44 Käufe insgesamt
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 44 Käufe insgesamt
SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 10              |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 44              |
| cabbage   | 9          | vegetable  | 44              |
| lettuce   | 10         | vegetable  | 44              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

Kumulative Summe berechnen

Hiermit wird für jede Kategorie in der Tabelle Produce eine kumulative Summe berechnet. Die Summe wird in Bezug auf die Reihenfolge berechnet, die mit der ORDER BY-Klausel definiert wurde.

  • fruit (Obst)
    • (orange (Orange), apple (Apfel)) = 2 Käufe insgesamt
    • (orange (Orange), apple (Apfel)) = 10 Käufe insgesamt
  • vegetable (Gemüse)
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 2 Käufe insgesamt
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 11 Käufe insgesamt
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 21 Käufe insgesamt
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 44 Käufe insgesamt
SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 2               |
| cabbage   | 9          | vegetable  | 11              |
| lettuce   | 10         | vegetable  | 21              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

Dies entspricht dem obigen Beispiel. Sie müssen CURRENT ROW nicht als Begrenzung hinzufügen, es sei denn, Sie bevorzugen es im Sinne der Lesbarkeit.

SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS UNBOUNDED PRECEDING
  ) AS total_purchases
FROM Produce

In diesem Beispiel sind alle Elemente der Tabelle Produce in der Partition enthalten. Nur vorherige Zeilen werden analysiert. Die Analyse beginnt zwei Zeilen vor der aktuellen Zeile in der Partition.

  • (orange (Orange), leek (Lauch), apple (Apfel), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = NULL
  • (orange (Orange), leek (Lauch), apple (Apfel), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = NULL
  • (orange (Orange), leek (Lauch), apple (Apfel), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 2
  • (orange (Orange), leek (Lauch), apple (Apfel), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 4
  • (orange (Orange), leek (Lauch), apple (Apfel), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 12
  • (orange (Orange), leek (Lauch), apple (Apfel), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = 21
SELECT item, purchases, category, SUM(purchases)
  OVER (
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
  ) AS total_purchases
FROM Produce;

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | NULL            |
| leek      | 2          | vegetable  | NULL            |
| apple     | 8          | fruit      | 2               |
| cabbage   | 9          | vegetable  | 4               |
| lettuce   | 10         | vegetable  | 12              |
| kale      | 23         | vegetable  | 21              |
+-------------------------------------------------------+

Gleitenden Durchschnitt berechnen

Hierdurch wird ein gleitender Durchschnitt in der Tabelle Produce berechnet. Die untere Begrenzung liegt eine Zeile vor der aktuellen Zeile. Die obere Begrenzung liegt eine Zeile nach der aktuellen Zeile.

  • (orange (Orange), leek (Lauch), apple (Apfel), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = durchschnittlich 2 Käufe
  • (orange (Orange), leek (Lauch), apple (Apfel), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = durchschnittlich 4 Käufe
  • (orange (Orange), leek (Lauch), apple (Apfel), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = durchschnittlich 6,3333 Käufe
  • (orange (Orange), leek (Lauch), apple (Apfel), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = durchschnittlich 9 Käufe
  • (orange (Orange), leek (Lauch), apple (Apfel), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = durchschnittlich 14 Käufe
  • (orange (Orange), leek (Lauch), apple (Apfel), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = durchschnittlich 16.5 Käufe
SELECT item, purchases, category, AVG(purchases)
  OVER (
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS avg_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | avg_purchases   |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| leek      | 2          | vegetable  | 4               |
| apple     | 8          | fruit      | 6.33333         |
| cabbage   | 9          | vegetable  | 9               |
| lettuce   | 10         | vegetable  | 14              |
| kale      | 23         | vegetable  | 16.5            |
+-------------------------------------------------------+

Anzahl der Elemente in einem Bereich berechnen

In diesem Beispiel wird die Anzahl der Tiere mit einer ähnlichen Populationszahl in der Tabelle Farm aufgeführt.

  • (goose (Gans), dog (Hund), ox (Rind), goat (Ziege), duck (Ente), cat (Katze)) = 4 Tiere im Populationsbereich 0–2.
  • (goose (Gans), dog (Hund), ox (Rind), goat (Ziege), duck (Ente), cat (Katze)) = 5 Tiere im Populationsbereich 1–3.
  • (goose (Gans), dog (Hund), ox (Rind), goat (Ziege), duck (Ente), cat (Katze)) = 5 Tiere im Populationsbereich 1–3.
  • (goose (Gans), dog (Hund), ox (Rind), goat (Ziege), duck (Ente), cat (Katze)) = 5 Tiere im Populationsbereich 1–3.
  • (goose (Gans), dog (Hund), ox (Rind), goat (Ziege), duck (Ente), cat (Katze)) = 4 Tiere im Populationsbereich 2–4.
  • (goose (Gans), dog (Hund), ox (Rind), goat (Ziege), duck (Ente), cat (Katze)) = 1 Tier im Populationsbereich 22–24.
SELECT animal, population, category, COUNT(*)
  OVER (
    ORDER BY population
    RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS similar_population
FROM Farm;

+----------------------------------------------------------+
| animal    | population | category   | similar_population |
+----------------------------------------------------------+
| goose     | 1          | bird       | 4                  |
| dog       | 2          | mammal     | 5                  |
| ox        | 2          | mammal     | 5                  |
| goat      | 2          | mammal     | 5                  |
| duck      | 3          | bird       | 4                  |
| cat       | 23         | mammal     | 1                  |
+----------------------------------------------------------+

In diesem Beispiel wird das beliebteste Element aus jeder Kategorie abgerufen. Dafür wird definiert, wie Zeilen in einem Fenster in jeder Partition partitioniert und sortiert werden. Es wird auf die Tabelle Produce verwiesen.

  • fruit (Obst)
    • (orange (Orange), apple (Apfel)) = Apfel ist am beliebtesten.
    • (orange (Orange), apple (Apfel)) = Apfel ist am beliebtesten.
  • vegetable (Gemüse)
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = Grünkohl ist am beliebtesten.
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = Grünkohl ist am beliebtesten.
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = Grünkohl ist am beliebtesten.
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = Grünkohl ist am beliebtesten.
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| orange    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | kale         |
| cabbage   | 9          | vegetable  | kale         |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+

Letzten Wert in einem Bereich ermitteln

In diesem Beispiel wird das beliebteste Element in einem bestimmten Window Frame mithilfe der Tabelle Produce ermittelt. Der Window Frame analysiert bis zu drei Zeilen gleichzeitig. Sehen Sie sich die Spalte most_popular für Gemüsesorten genauer an. Anstelle des beliebtesten Elements in einer bestimmten Kategorie wird das beliebteste Element in einem bestimmten Bereich dieser Kategorie ermittelt.

  • fruit (Obst)
    • (orange (Orange), apple (Apfel)) = Apfel ist am beliebtesten.
    • (orange (Orange), apple (Apfel)) = Apfel ist am beliebtesten.
  • vegetable (Gemüse)
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = Weißkohl ist am beliebtesten
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = Salat ist am beliebtesten.
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = Grünkohl ist am beliebtesten.
    • (leek (Lauch), cabbage (Weißkohl), lettuce (Salat), kale (Grünkohl)) = Grünkohl ist am beliebtesten.
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| orange    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | cabbage      |
| cabbage   | 9          | vegetable  | lettuce      |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+

In diesem Beispiel werden dieselben Ergebnisse wie im obigen Beispiel zurückgegeben, es enthält aber ein benanntes Fenster mit dem Namen item_window. Einige Fensterspezifikationen werden direkt in der OVER-Klausel und andere im benannten Fenster definiert.

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    item_window
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases)

Rang berechnen

In diesem Beispiel wird der Rang jedes Mitarbeiters innerhalb seiner Abteilung anhand seines Einstiegsdatums berechnet. Die Fensterspezifikation wird direkt in der OVER-Klausel definiert. Es wird auf die Tabelle Employees verwiesen.

  • department (Abteilung) 1
    • (Jacob, Anthony, Andrew) = Jacob Rang 1 geben
    • (Jacob, Anthony, Andrew) = Anthony Rang 2 geben
    • (Jacob, Anthony, Andrew) = Andrew Rang 3 geben
  • department (Abteilung) 2
    • (Isabella, Daniel, Jose) = Isabella Rang 1 geben
    • (Isabella, Daniel, Jose) = Daniel Rang 2 geben
    • (Isabella, Daniel, Jose) = Jose Rang 3 geben
SELECT name, department, start_date,
  RANK() OVER (PARTITION BY department ORDER BY start_date) AS rank
FROM Employees;

+--------------------------------------------+
| name      | department | start_date | rank |
+--------------------------------------------+
| Jacob     | 1          | 1990-07-11 | 1    |
| Anthony   | 1          | 1995-11-29 | 2    |
| Andrew    | 1          | 1999-01-23 | 3    |
| Isabella  | 2          | 1997-09-28 | 1    |
| Daniel    | 2          | 2004-06-24 | 2    |
| Jose      | 2          | 2013-03-17 | 3    |
+--------------------------------------------+

Benanntes Fenster in einer Window-Frame-Klausel verwenden

Sie können einen Teil Ihrer Logik in einem benannten Fenster und einen Teil in einer Window-Frame-Klausel definieren. Diese Logik wird kombiniert. Hier sehen Sie ein Beispiel mit der Tabelle Produce.

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

+-------------------------------------------------------+
| item      | purchases  | category   | most_popular    |
+-------------------------------------------------------+
| orange    | 2          | fruit      | apple           |
| apple     | 8          | fruit      | apple           |
| leek      | 2          | vegetable  | lettuce         |
| cabbage   | 9          | vegetable  | kale            |
| lettuce   | 10         | vegetable  | kale            |
| kale      | 23         | vegetable  | kale            |
+-------------------------------------------------------+

Sie können die vorherigen Ergebnisse auch mit diesen Beispielen erhalten:

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
  item_window AS (c)
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  item_window AS (b)

Das folgende Beispiel führt zu einem Fehler, da eine Window-Frame-Klausel zweimal definiert wurde:

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    item_window
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS most_popular
FROM Produce
WINDOW item_window AS (
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)