Eine Analysefunktion berechnet Werte über eine Gruppe von Zeilen und gibt für jede Zeile ein einzelnes Ergebnis zurück. Dies unterscheidet sich 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 analytische Funktionsergebnis unter Verwendung des ausgewählten Zeilenfensters als Eingabe berechnet, wobei möglicherweise eine Aggregation durchgeführt wird.

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

Die folgenden Funktionen können als Analysefunktionen verwendet werden:Navigationsfunktionen, Nummerierungsfunktionen und Aggregatanalysefunktionen

Syntax der Analysefunktion

analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }window_specification: ] ] window_frame_clause: { rows_range } { frame_start | frame_between }rows_range: { ROWS | RANGE }

Notationsregeln

• Eckige Klammern „“ kennzeichnen optionale Klauseln.
• Klammern „( )“ gibt wörtliche Klammern an.
• Der vertikale Balken „|“ zeigt ein logisches ODER an.
• Geschweifte Klammern „{ }“ schließen eine Reihe von Optionen ein.
• Ein Komma gefolgt von einem Auslassungspunkt in eckigen Klammern „“ zeigt an, dassdas vorhergehende Element kann in einer durch Kommas getrennten Liste wiederholt werden.

Beschreibung

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

• analytic_function_name: Die Funktion, die eine Analyseoperation 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: Schlüsselwort, das in der Syntax der Analysefunktion vor der OVER -Klausel benötigt wird.
• 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 Fensterrahmen für das Fenster.
• rows_range: Definiert die physikalischen Zeilen oder den logischen Bereich für einen Fensterrahmen.

Anmerkungen

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

• Die SELECT Liste. Wenn die Analysefunktion in der SELECT -Liste angezeigt wird, können ihre Argumentliste und die OVER -Klausel nicht auf Aliase verweisen, die in derselben SELECT-Liste eingeführt wurden.
• Die ORDER BY Klausel. Wenn die Analysefunktion in der ORDER BY-Klausel der Abfrage vorkommt, kann ihre Argumentliste auf SELECT-Listenaliase verweisen.

Eine Analysefunktion kann nicht auf eine andere Analysefunktion in ihrer Argumentliste oder ihrer OVER Klausel verweisen, auch nicht indirekt über einen Alias.

Eine analytische Funktion wird nach der Aggregation ausgewertet. Beispielsweise werden zuerst dieGROUP BY -Klausel und nicht analytische Aggregatfunktionen ausgewertet.Da Aggregatfunktionen vor analytischen Funktionen ausgewertet werden, können Aggregatfunktionen als Eingabeoperanden für analytische Funktionen verwendet werden.

Gibt

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

Definieren der OVER-Klausel

analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }

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 einenamed_window angeben, die 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 für jede Zeile in das Fenster aufgenommen.

Beispiele mit der OVER Klausel

Diese Abfragen verwenden Fensterspezifikationen:

• Berechnen Sie eine Gesamtsumme
• Berechnen Sie eine Zwischensumme
• Berechnen Sie eine kumulative Summe
• Berechnen Sie einen gleitenden Durchschnitt
• Berechnen Sie die Anzahl der Elemente innerhalb eines Bereichs
• Holen Sie sich das beliebteste Element in jeder Kategorie
• Ermittelt den letzten Wert in einem Bereich
• Compute rank

Diese Abfragen verwenden ein benanntes Fenster:

• Den letzten Wert in einem Bereich abrufen
• Benanntes Fenster in einer window frame-Klausel verwenden

Definieren der Fensterspezifikation

window_specification: ] ] 

Beschreibung

Definiert die Spezifikationen für das Fenster.

• named_window: Der Name eines vorhandenen Fensters, das mit einer WINDOW -Klausel definiert wurde.

• 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 darf keine Gleitkommatypen, nicht gruppierbaren Typen, Konstanten oder Analysefunktionen enthalten.
  • Wenn diese optionale Klausel nicht verwendet wird, bilden alle Zeilen in der Eingabetabelleeine einzelne Partition.
• ORDER BY: Definiert, wie Zeilen innerhalb einer Partition angeordnet werden.Diese Klausel ist in den meisten Situationen optional, aber in einigen Fällen für Navigationsfunktionen erforderlich.
• window_frame_clause: Definiert für aggregate analyticfunctions den Fensterrahmen innerhalb der aktuellen Partition.Der Fensterrahmen bestimmt, was in das Fenster aufgenommen werden soll.Wenn diese Klausel verwendet wird, ist ORDER BY erforderlich, außer für vollständig nicht begrenzte Fenster.

Anmerkungen

Wenn weder die ORDER BY-Klausel noch die window frame-Klausel vorhanden sind, enthält der window frame alle Zeilen in dieser Partition.

Wenn bei aggregierten 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 äquivalent:

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 ein benanntes Fenster in Ihren Fensterspezifikationen verwenden, gelten folgende Regeln:

• Die Spezifikationen im benannten Fenster können um neue Spezifikationen erweitert werden, die Sie in der window specification-Klausel definieren.
• Sie können keine redundanten Definitionen haben. Wenn Sie eine ORDER BY Klausel habenim benannten Fenster und in der window specification Klausel wird anerror ausgelöst.

• Die Reihenfolge der Klauseln ist wichtig. PARTITION BY muss zuerst kommen, 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_popularFROM ProduceWINDOW item_window AS (ORDER BY purchases)--this does not work:SELECT item, purchases, LAST_VALUE(item) OVER (item_window ORDER BY purchases) AS most_popularFROM ProduceWINDOW item_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

• Ein benanntes Fenster und PARTITION BY können nicht zusammen in derwindow-Spezifikation erscheinen. Wenn Sie PARTITION BY , fügen Sie es dem benannten Fenster hinzu.

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

Beispiele für die Verwendung der Fensterspezifikation

Diese Abfragen definieren Partitionen in einer Analysefunktion:

• Eine Zwischensumme berechnen
• Eine kumulative Summe berechnen
• Das beliebteste Element in jeder Kategorie abrufen
• Den letzten Wert in einem Bereich abrufen
• Rang berechnen
• Ein benanntes Fenster in einer window frame-Klausel verwenden

Diese Abfragen enthalten ein benanntes Fenster in einer Fensterspezifikation:

• Den letzten Wert in einem Bereich abrufen
• Benanntes Fenster in einer Fensterrahmenklausel verwenden

Diese Abfragen definieren, wie Zeilen in einer Partition angeordnet werden:

• Berechnen Sie eine Zwischensumme
• Berechnen Sie eine kumulative Summe
• Berechnen Sie einen gleitenden Durchschnitt
• Berechnen Sie die Anzahl der Elemente innerhalb eines Bereichs
• Holen Sie sich das beliebteste Element in jeder Kategorie
• Holen Sie sich den letzten Wert in einem Bereich
• Berechnen Sie den Rang
• Verwenden Sie ein benanntes Fenster in einer window frame-Klausel

h3>

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 | }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 PRECEDINGnumeric_preceding: numeric_expression PRECEDINGunbounded_following: UNBOUNDED FOLLOWINGnumeric_following: numeric_expression FOLLOWINGcurrent_row: CURRENT ROW

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

• rows_range: Eine Klausel, die einen Fensterrahmen mit physischen Zeilen oder einem logischen Bereich definiert.

  • ROWS: Berechnet den Fensterrahmen basierend auf physikalischen Offsets aus der aktuellen Zeile. Sie können beispielsweise zwei Zeilen vor und nach der aktuellen Zeile einfügen.
  • RANGE: Berechnet den Fensterrahmen basierend auf einem logischen Zeilenbereich um die aktuelle Zeile, basierend auf dem Schlüsselwert ORDER BY der aktuellen Zeile.Der angegebene Bereichswert wird zum Schlüsselwert der aktuellen Zeile addiert oder subtrahiert, um eine Start- oder Endbereichsgrenze für den Fensterrahmen zu definieren. In einem bereichsbasierten Fensterrahmen muss genau ein Ausdruck in der ORDER BY -Klausel vorhanden sein, und der Ausdruck muss einen anumerischen Typ haben.

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

• frame_between: Erzeugt einen Fensterrahmen mit einer unteren und oberen Begrenzung.Die erste Grenze stellt die untere Grenze dar. Die zweite Grenze stellt die obere Grenze dar. Es können nur bestimmte Grenzkombinationen verwendet werden, wie in der vorhergehenden Syntax gezeigt.

  • Definieren Sie den Anfang des Fensterrahmens mit unbounded_precedingnumeric_precedingnumeric_following oder current_row.
    • unbounded_preceding: Der Fensterrahmen beginnt am Anfang der Partition.
    • numeric_preceding oder numeric_following: Der Anfang des Fensterrahmens ist relativ zur aktuellen Zeile.
    • current_row: Der Fensterrahmen beginnt bei der aktuellen Zeile.
  • Definieren Sie das Ende des Fensterrahmens mit numeric_precedingnumeric_followingcurrent_row oder unbounded_following.
    • numeric_preceding oder numeric_following: Das Ende des Fensterrahmens ist relativ zur aktuellen Zeile.
    • current_row: Der Fensterrahmen endet an der aktuellen Zeile.
    • unbounded_following: Der Fensterrahmen endet am Ende der Partition.

• frame_start: Erzeugt einen Fensterrahmen mit einer unteren Begrenzung.Der Fensterrahmen endet an der aktuellen Zeile.

  • unbounded_preceding: Der Fensterrahmen beginnt am Anfang der Partition.
  • numeric_preceding: Der Anfang des Fensterrahmens ist relativ zur aktuellen Zeile.
  • current_row: Der Fensterrahmen beginnt bei der aktuellen Zeile.

• numeric_expression: Ein Ausdruck, der einen numerischen Typ darstellt.Der numerische Ausdruck muss ein konstanter, nicht negativer Ganzzahloder Parameter sein.

Hinweise

Wenn eine Begrenzung über den Anfang oder das Ende einer Partition hinausgeht, enthält der Fensterrahmen nur Zeilen innerhalb dieser Partition.

Sie können keine Fensterrahmenklausel mit Navigationsfunktionen und Nummerierungsfunktionen verwenden, z. B. RANK().

Beispiele mit der window frame Klausel

Diese Abfragen berechnen Werte mit ROWS:

• Berechnen Sie eine kumulative Summe
• Berechnen Sie einen gleitenden Durchschnitt
• Holen Sie sich das beliebteste Element in jeder Kategorie
• Holen Sie sich den letzten Wert in einem Bereich
• Verwenden Sie ein benanntes Fenster in einer Fensterrahmenklausel

Diese Abfragen berechnen Werte mit RANGE:

• Berechnen

Diese Abfragen berechnen Werte mit einem teilweise oder vollständig ungebundenen Fenster:

• Berechnen Sie eine Gesamtsumme
• Berechnen Sie eine Zwischensumme
• Berechnen Sie eine kumulative Summe
• Holen Sie sich das beliebteste Element in jeder Kategorie
• Compute rank

Diese Abfragen berechnen Werte mit numerischen Grenzen:

• Berechnen Sie eine kumulative Summe
• Berechnen Sie einen gleitenden Durchschnitt
• Berechnen Sie die >Den letzten Wert in einem Bereich abrufen
• Benanntes Fenster in einer window Frame-Klausel verwenden

Diese Abfragen berechnen Werte mit der aktuellen Zeile als Grenze:

• Berechnen Sie eine Gesamtsumme
• Berechnen Sie eine Zwischensumme
• Berechnen Sie eine kumulative Summe

Referenzieren eines benannten Fensters

SELECT query_expr, analytic_function_name ( ) OVER over_clauseFROM from_itemWINDOW named_window_expression over_clause: { named_window | ( ) }window_specification: ] ] named_window_expression: named_window AS { named_window | ( ) }

Ein benanntes Fenster stellt eine Gruppe von Zeilen in einer Tabelle dar, für die eine analytische Funktion verwendet werden soll. Ein benanntes Fenster wird in derWINDOW -Klausel definiert und in der OVER einer Analysefunktion referenziert clause.In in einer OVER -Klausel kann ein benanntes Fenster entweder selbst oder eingebettet in eine Fensterspezifikation angezeigt werden.

Beispiele

• Den letzten Wert in einem Bereich abrufen
• Ein benanntes Fenster in einer Fensterrahmenklausel verwenden

Navigationsfunktionskonzepte

Navigationsfunktionen berechnen im Allgemeinen einigevalue_expression über eine andere Zeile im Fensterrahmen als die aktuelle Zeile. Die OVER -Klausel Syntax variiert über Navigationsfunktionen.

Anforderungen für die OVER Klausel:

• PARTITION BY: Optional.
• ORDER BY:
  1. Disallowed for PERCENTILE_CONT and PERCENTILE_DISC.
  2. Required for FIRST_VALUELAST_VALUENTH_VALUELEADand LAG.
• window_frame_clause:
  1. Disallowed for PERCENTILE_CONTPERCENTILE_DISCLEAD and LAG.
  2. Optional for FIRST_VALUELAST_VALUE, and NTH_VALUE.

Für alle Navigationsfunktionen ist der Ergebnisdatentyp derselbe wievalue_expression.

Konzepte der Nummerierungsfunktion

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

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_numFROM 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(): Für x=5 ist rank 4, da RANK() um die Anzahl der Peers in der vorherigen Fensterordnungsgruppe erhöht wird.
• DENSE_RANK(): Für x=5 ist dense_rank 3, da DENSE_RANK() immererhöht sich um 1 und überspringt niemals einen Wert.
• ROW_NUMBER(): Für x=5 ist row_num 4.

Aggregate analytic function concepts

Eine Aggregatfunktion ist eine Funktion, die eine Berechnung für eine Reihe von Werten durchführt. Die meisten Aggregatfunktionen können in einer analytischen Funktion verwendet werden. Diese Aggregatfunktionen werden genanntaggregate analytische Funktionen.

Bei aggregierten Analysefunktionen wird die OVER -Klausel an den Funktionsaufruf aggregate angehängt; die Funktionsaufrufsyntax bleibt ansonsten unverändert.Wie ihre Gegenstücke zur Aggregatfunktion führen diese Analysefunktionen Aggregationen durch, jedoch speziell über den relevanten Fensterrahmen für jede Zeile.Die Ergebnisdatentypen dieser Analysefunktionen sind dieselben wie ihre Gegenstücke zur Aggregatfunktion.

Beispiele für Analysefunktionen

In diesen Beispielen ist das hervorgehobene Element die aktuelle Zeile. Die boldeditems sind die Zeilen, die in der Analyse enthalten sind.

Allgemeine Tabellen, die in Beispielen verwendet werden

Die folgenden Tabellen werden in den nachfolgenden Aggregate analyticquery-Beispielen verwendet: ProduceEmployeesund Farm.

Tabelle erzeugen

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

Employees table

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

Farm table

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

Berechnen Sie eine Gesamtsumme

Dies berechnet eine Gesamtsumme für alle Elemente in derProduce Tabelle.

• (orange, Apfel, Lauch, kohl, Salat, Grünkohl) = 54 Gesamteinkäufe
• (orange, Apfel, Lauch, kohl, salat, Grünkohl) = 54 Gesamteinkäufe
• (orange, Apfel, Lauch, Kohl, Salat, Grünkohl) = 54 Gesamteinkäufe
• (orange, Apfel, Lauch, Kohl, Salat, Grünkohl) = 54 Gesamteinkäufe
• (orange, Apfel, Lauch, kohl, Salat, Grünkohl) = 54 Gesamtkäufe
• (Orange, Apfel, Lauch, Kohl, Salat, Grünkohl) = 54 Gesamtkäufe

SELECT item, purchases, category, SUM(purchases) OVER () AS total_purchasesFROM 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 |+-------------------------------------------------------+

Berechnen Sie eine Zwischensumme

Dies berechnet eine Zwischensumme für jede Kategorie im

Tabelle.

• Obst
  • (orange, Apfel) = 10 Gesamteinkäufe
  • (orange, Apfel) = 10 Gesamteinkäufe
• Gemüse
  • (Lauch, Kohl, Salat, Grünkohl) = 44 Gesamteinkäufe
  • (Lauch, Kohl, Salat, Grünkohl) = 44 Gesamteinkäufe
  • (Lauch, Kohl, Salat, Grünkohl) = 44 Gesamteinkäufe
  • (Lauch, Kohl, Salat, Grünkohl) = 44 gesamteinkäufe
  • (Lauch, Kohl, Salat, Grünkohl) = 44 Gesamteinkäufe

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS total_purchasesFROM 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 |+-------------------------------------------------------+

Berechnen Sie eine kumulative Summe

Dies berechnet eine kumulative Summe für jede Kategorie in der TabelleProduce. Die Summe wird in Bezug auf den mit der ORDER BY -Klausel definierten Theorem berechnet.

• Obst
  • (Orange, Apfel) = 2 Gesamteinkäufe
  • (Orange, Apfel) = 10 Gesamteinkäufe
• Gemüse
  • (Lauch, Kohl, Salat, Grünkohl) = 2 Gesamteinkäufe
  • (Lauch, Kohl, Salat, Grünkohl) = 11 Gesamteinkäufe
  • (Lauch, Kohl, Salat, Grünkohl) = 21 Gesamteinkäufe
  • (Lauch, Kohl, Salat, Grünkohl) = 44 Gesamtkäufe

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS total_purchasesFROM 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 macht dasselbe wie das vorhergehende Beispiel. Sie müssenCURRENT ROW nicht als Grenze hinzufügen, es sei denn, Sie möchten dies aus Gründen der Lesbarkeit.

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

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

• (orange, Lauch, apfel, kohl, salat, Grünkohl) = NULL
• (orange, lauch, apfel, kohl, salat, grünkohl) = NULL
• (orange, Lauch, apfel, kohl, Salat, Grünkohl) = 2
• (orange, Lauch, Apfel, kohl, Salat, Grünkohl) = 4
• (orange, Lauch, Apfel, kohl, Salat, Grünkohl) = 12
• (Orange, Lauch, Apfel, Kohl, Salat, Grünkohl) = 21

SELECT item, purchases, category, SUM(purchases) OVER ( ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING ) AS total_purchasesFROM 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 |+-------------------------------------------------------+

Berechnen Sie einen gleitenden Durchschnitt

Dies berechnet einen gleitenden Durchschnitt in der Produce Tabelle.Die untere Grenze ist 1 Zeile vor der aktuellen Zeile. Die obere Grenze ist 1 Zeile nach der aktuellen Zeile.

• (orange, Lauch, Apfel, Kohl, Salat, Grünkohl) = 2 durchschnittliche Einkäufe
• (orange, Lauch, apfel, kohl, Salat, Grünkohl) = 4 durchschnittliche Einkäufe
• (orange, Lauch, Apfel, Kohl, Salat, Grünkohl) = 6.3333 durchschnittliche Einkäufe
• (orange, Lauch, Apfel, Kohl, Salat, Grünkohl) = 9 durchschnittliche Einkäufe
• (orange, Lauch, Apfel, Kohl, Salat, Grünkohl) = , kohl, Salat, Grünkohl) = 14 durchschnittliche Einkäufe
• (Orange, Lauch, Apfel, Kohl, Salat, Grünkohl) = 16.5 durchschnittliche Einkäufe

SELECT item, purchases, category, AVG(purchases) OVER ( ORDER BY purchases ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS avg_purchasesFROM 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 |+-------------------------------------------------------+

Berechnen Sie die Anzahl der Artikel innerhalb eines Bereichs

In diesem Beispiel wird die Anzahl der Tiere mit einer ähnlichen Populationcount in der Tabelle Farm abgerufen.

• (Gans, Hund, Ochse, Ziege, Ente, Katze) = 4 Tiere zwischen Populationsbereich 0-2.
• (Gans, Hund, Ochse, Ziege, Ente, Katze) = 5 Tiere zwischen Populationsbereich 1-3.
• (Gans, Hund, Ochse, Ziege, Ente, Katze) = 5 Tiere zwischen Populationsbereich 1-3.
• (Gans, Hund, Ochse, Ziege, Ente, Katze) = 5 Tiere zwischen Populationsbereich 1-3.
• (Gans, Hund, Ochse, Ziege, Ente, Katze) = 4 Tiere zwischen Populationsbereich 2-4.
• (Gans, Hund, Ochse, Ziege, Ente, Katze) = 1 Tier zwischen Populationsbereich 22-24.

SELECT animal, population, category, COUNT(*) OVER ( ORDER BY population RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS similar_populationFROM 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 |+----------------------------------------------------------+

Holen Sie sich das beliebteste Element in jeder Kategorie

Dieses Beispiel ruft das beliebteste Element in jeder Kategorie ab. Es definiert, wie Zeilen in einem Fenster in jeder Partition partitioniert und geordnet werden. DieProduce -Tabelle wird referenziert.

• Obst
  • (orange, Apfel) = Apfel ist am beliebtesten
  • (orange, Apfel) = Apfel ist am beliebtesten
• Gemüse
  • (Lauch, Kohl, Salat, Grünkohl) = Grünkohl ist am beliebtesten
  • (Lauch, Kohl, Salat, Grünkohl) = Grünkohl ist am beliebtesten
  • (Lauch, Kohl, Salat, Grünkohl) = grünkohl ist am beliebtesten
  • (Lauch, Kohl, Salat, 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_popularFROM 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 |+----------------------------------------------------+

Holen Sie sich den letzten Wert in einem Bereich

In diesem Beispiel wird das beliebteste Element in einem bestimmten Fensterrahmen mithilfe des Produce Tabelle. Der Fensterrahmen analysiert bis zu dreibögen gleichzeitig. Schauen Sie sich die most_popular Spalte für Gemüse genau an.Anstatt den beliebtesten Artikel in einer bestimmten Kategorie zu erhalten, wird der beliebteste Artikel in einem bestimmten Bereich in dieser Kategorie angezeigt.

• Obst
  • (orange, Apfel) = Apfel ist am beliebtesten
  • (orange, Apfel) = Apfel ist am beliebtesten
• Gemüse
  • (Lauch, Kohl, Salat, Grünkohl) = Kohl ist am beliebtesten
  • (Lauch, Kohl, Salat, Grünkohl) = Salat ist am beliebtesten
  • (Lauch, Kohl, Salat, Grünkohl) = Grünkohl ist am beliebtesten
  • (Lauch, Kohl, Salat, 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_popularFROM 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 |+----------------------------------------------------+

Dieses Beispiel liefert die gleichen Ergebnisse wie das vorhergehende Beispiel, enthält jedoch ein benanntes Fenster mit dem Namen item_window. Einige der Fensterspezifikationen sind direkt in der OVER -Klausel definiert und einige sind im benannten Fenster definiert.

SELECT item, purchases, category, LAST_VALUE(item) OVER ( item_window ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM ProduceWINDOW item_window AS ( PARTITION BY category ORDER BY purchases)

Compute rank

In diesem Beispiel wird der Rang jedes Mitarbeiters innerhalb seiner Abteilung basierend auf seinem Startdatum berechnet. Die Fensterspezifikation wird direkt in der OVER -Klausel definiert. Die Employees -Tabelle wird referenziert.

• Abteilung 1
  • (Jacob, Anthony, Andrew) = Jacob Rang 1 zuweisen
  • (Jacob, Anthony, Andrew) = Anthony Rang 2 zuweisen
  • (Jacob, Anthony, Andrew) = Andrew Rang 3 zuweisen
• Abteilung 2
  • (Isabella, Daniel, Jose) = Isabella Rang 1 zuweisen
  • (Isabella, Daniel, Jose) = Isabella Rang 2 zuweisen Daniel
  • (Isabella, Daniel, Jose) = Ordnen Sie Jose Rang 3 zu

SELECT name, department, start_date, RANK() OVER (PARTITION BY department ORDER BY start_date) AS rankFROM 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 |+--------------------------------------------+

Verwenden Sie ein benanntes Fenster in einer Fensterrahmenklausel

Sie können einen Teil Ihrer Logik in einem benannten Fenster und einen Teil awindow frame-Klausel. Diese Logik wird kombiniert. Hier ist ein Beispiel mit derProduce -Tabelle.

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window) AS most_popularFROM ProduceWINDOW 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_popularFROM ProduceWINDOW 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_popularFROM ProduceWINDOW a AS (PARTITION BY category), b AS (a ORDER BY purchases), item_window AS (b)

Das folgende Beispiel erzeugt einen Fehler, weil 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_popularFROM ProduceWINDOW item_window AS ( ORDER BY purchases ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)