Kategorien:

Syntax und Verwendung von Fensterfunktionen (Rangliste)

FIRST_VALUE

Gibt den ersten Wert innerhalb einer geordneten Gruppe von Werten zurück.

Siehe auch:

LAST_VALUE, NTH_VALUE

Syntax

FIRST_VALUE( <expr> ) [ { IGNORE | RESPECT } NULLS ]
  OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2>  [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [ <window_frame> ] )

Die detaillierte Syntax von window_frame finden Sie unter Syntax und Verwendung von Fensterfunktionen.

Argumente

expr

Ausdruck, der den Rückgabewert bestimmt.

expr1

Ausdruck, nach dem die Zeilen partitioniert werden sollen. Es kann ein einzelner Ausdruck oder eine durch Komma getrennte Liste von Ausdrücken angegeben werden. Beispiel:

PARTITION BY column_1, column_2
expr2

Ausdruck, nach dem die Zeilen sortiert werden sollen. Es kann ein einzelner Ausdruck oder eine durch Komma getrennte Liste von Ausdrücken angegeben werden. Beispiel:

ORDER BY column_3, column_4
{ IGNORE | RESPECT } NULLS

Ob die NULL-Werte ignoriert oder beachtet werden sollen, wenn expr NULL-Werte enthält:

  • IGNORE NULLS gibt den ersten Wert zurück, der nicht NULL ist.

  • RESPECT NULLS gibt einen NULL-Wert zurück, wenn dies der erste Wert im Ausdruck ist.

Standard: RESPECT NULLS

Nutzungshinweise

  • Diese Funktion ist eine rangbezogene Analysefunktion, d. h. sie muss ein Fenster angeben. Eine Fensterklausel besteht aus den folgenden Unterklauseln:

    • Unterklausel PARTITION BY expr1 (optional)

    • Unterklausel ORDER BY expr2 (erforderlich) Weitere Informationen zu zusätzlich unterstützten Sortieroptionen (Sortierreihenfolge, Sortieren von NULL-Werten usw.) finden Sie in der Dokumentation zur ORDER BY-Klausel, die denselben Regeln folgt.

    • Unterklausel window_frame (optional)

  • Die Reihenfolge der Zeilen in einem Fenster (und damit das Ergebnis der Abfrage) ist nur dann vollständig deterministisch, wenn durch die Schlüssel in der ORDER BY-Klausel jede Zeile eindeutig identifizierbar ist. Betrachten Sie das folgende Beispiel:

    ... OVER (PARTITION BY p ORDER BY o COLLATE 'lower') ...

    Das Abfrageergebnis kann variieren, wenn eine Partition Werte der Spalte o enthält, die identisch sind oder bei einem Vergleich ohne Berücksichtigung der Groß- und Kleinschreibung identisch wären.

  • Die ORDER BY-Klausel innerhalb der OVER-Klausel steuert die Reihenfolge der Zeilen nur innerhalb des Fensters, nicht die Reihenfolge der Zeilen in der Ausgabe der gesamten Abfrage. Um die Reihenfolge der Ausgabe zu steuern, verwenden Sie eine separate ORDER BY-Klausel auf der äußersten Ebene der Abfrage.

  • Die optionale window_frame gibt die Teilmenge der Zeilen innerhalb des Fensters an, für die die Funktion berechnet wird. Wenn keine window_frame angegeben wird, ist der Standardwert das gesamte Fenster:

    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

    Beachten Sie, dass dies vom ANSI-Standard abweicht, der folgenden Standard für Fensterrahmen vorgibt:

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Die detaillierte Syntax von window_frame finden Sie unter Syntax und Verwendung von Fensterfunktionen.

Beispiele

In diesem Beispiel wird eine Abfrage gezeigt, die die FIRST_VALUE-Funktion verwendet, um den kostengünstigen Menüpunkt in jeder Kategorie zu finden. Die Abfrage enthält zwei ORDER BY-Klauseln: eine zur Steuerung der Reihenfolge der Zeilen in jeder Partition und eine zur Sortierung der Ausgabe der vollständigen Abfrage. Um die in diesem Beispiel verwendete Tabelle zu erstellen und zu laden, finden Sie entsprechende Informationen unter Erstellen und Laden der „menu_items“-Tabelle.

SELECT menu_category, menu_item_name, menu_price_usd,
       FIRST_VALUE(menu_item_name) OVER (PARTITION BY menu_category ORDER BY menu_price_usd) AS cheapest_item
  FROM menu_items
  WHERE menu_category IN ('Beverage', 'Dessert', 'Snack')
  ORDER BY menu_category, menu_price_usd
  LIMIT 12;

+---------------+--------------------+----------------+---------------+
| MENU_CATEGORY | MENU_ITEM_NAME     | MENU_PRICE_USD | CHEAPEST_ITEM |
|---------------+--------------------+----------------+---------------|
| Beverage      | Bottled Water      |           2.00 | Bottled Water |
| Beverage      | Iced Tea           |           3.00 | Bottled Water |
| Beverage      | Bottled Soda       |           3.00 | Bottled Water |
| Beverage      | Lemonade           |           3.50 | Bottled Water |
| Dessert       | Popsicle           |           3.00 | Popsicle      |
| Dessert       | Ice Cream Sandwich |           4.00 | Popsicle      |
| Dessert       | Mango Sticky Rice  |           5.00 | Popsicle      |
| Dessert       | Sugar Cone         |           6.00 | Popsicle      |
| Dessert       | Waffle Cone        |           6.00 | Popsicle      |
| Dessert       | Two Scoop Bowl     |           7.00 | Popsicle      |
| Snack         | Spring Mix Salad   |           6.00 | Fried Pickles |
| Snack         | Fried Pickles      |           6.00 | Fried Pickles |
+---------------+--------------------+----------------+---------------+

Im folgenden Beispiel wird ebenfalls die menu_items-Tabelle verwendet, um drei verwandte Funktionen zu vergleichen: FIRST_VALUE, NTH_VALUE und LAST_VALUE:

  • Die Abfrage erstellt einen gleitenden Fensterrahmen, der drei Zeilen breit ist und Folgendes enthält:

    • Die Zeile, die der aktuellen Zeile vorausgeht.

    • Die aktuelle Zeile.

    • Die Zeile, die auf die aktuelle Zeile folgt.

  • Die 2 im Aufruf NTH_VALUE(menu_price_usd, 2) gibt die zweite Zeile im Fensterrahmen an (die in diesem Fall auch die aktuelle Zeile ist).

  • Wenn die aktuelle Zeile die allererste Zeile im Fensterrahmen ist, gibt es keine vorherige Zeile, auf die verwiesen werden kann, sodass FIRST_VALUE einen NULL-Wert für diese Zeile zurückgibt.

  • Rahmengrenzen reichen manchmal über die Zeilen einer Partition hinaus, aber nicht vorhandene Zeilen werden bei den Berechnungen der Fensterfunktion nicht berücksichtigt. Wenn zum Beispiel die aktuelle Zeile die allererste Zeile in der Partition ist und der Fensterrahmen ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING lautet, gibt es keine vorangehende Zeile, auf die Sie verweisen können. Daher gibt die Funktion FIRST_VALUE den Wert der ersten Zeile in der Partition zurück.

  • Die Ergebnisse stimmen nie für alle drei Funktionen überein, wenn man die Daten in der Tabelle betrachtet. Diese Funktionen wählen den ersten, letzten oder n-ten Wert für jede Zeile im Rahmen aus, und die Auswahl der Werte gilt für jede Partition separat.

SELECT menu_category, menu_item_name, menu_price_usd,
       FIRST_VALUE(menu_price_usd) OVER (PARTITION BY menu_category ORDER BY menu_price_usd
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS first_val,
       NTH_VALUE(menu_price_usd, 2) OVER (PARTITION BY menu_category ORDER BY menu_price_usd
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS nth_val,
       LAST_VALUE(menu_price_usd) OVER (PARTITION BY menu_category ORDER BY menu_price_usd
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS last_val
  FROM menu_items
  WHERE menu_category = 'Dessert'
  ORDER BY menu_price_usd;

+---------------+--------------------+----------------+-----------+---------+----------+
| MENU_CATEGORY | MENU_ITEM_NAME     | MENU_PRICE_USD | FIRST_VAL | NTH_VAL | LAST_VAL |
|---------------+--------------------+----------------+-----------+---------+----------|
| Dessert       | Popsicle           |           3.00 |      3.00 |    4.00 |     4.00 |
| Dessert       | Ice Cream Sandwich |           4.00 |      3.00 |    4.00 |     5.00 |
| Dessert       | Mango Sticky Rice  |           5.00 |      4.00 |    5.00 |     6.00 |
| Dessert       | Sugar Cone         |           6.00 |      6.00 |    6.00 |     7.00 |
| Dessert       | Waffle Cone        |           6.00 |      5.00 |    6.00 |     6.00 |
| Dessert       | Two Scoop Bowl     |           7.00 |      6.00 |    7.00 |     7.00 |
+---------------+--------------------+----------------+-----------+---------+----------+

Dieses Beispiel zeigt den Unterschied zwischen IGNORE NULLS und RESPECT NULLS. Die Beispieldaten enthalten Zeilen, in denen der Kostenwert NULL ist. Beim Standardverhalten RESPECT NULLS gibt FIRST_VALUE den Wert NULL zurück, wenn die erste Zeile in der geordneten Partition einen NULL-Wert enthält. Bei IGNORE NULLS überspringt FIRST_VALUE NULL-Werte und gibt den ersten Wert zurück, der nicht NULL ist.

SELECT item_name, item_cost, item_price,
       FIRST_VALUE(item_cost) RESPECT NULLS
         OVER (ORDER BY item_price) AS first_cost_respect,
       FIRST_VALUE(item_cost) IGNORE NULLS
         OVER (ORDER BY item_price) AS first_cost_ignore
  FROM VALUES
    ('Pretzel', NULL, 3.00),
    ('Corn Dog', NULL, 4.00),
    ('Hot Dog', 1.50, 5.00),
    ('Sandwich', 2.50, 6.00)
  AS menu(item_name, item_cost, item_price)
  ORDER BY item_price;

+-----------+-----------+------------+--------------------+-------------------+
| ITEM_NAME | ITEM_COST | ITEM_PRICE | FIRST_COST_RESPECT | FIRST_COST_IGNORE |
|-----------+-----------+------------+--------------------+-------------------|
| Pretzel   |      NULL |       3.00 |               NULL |              1.50 |
| Corn Dog  |      NULL |       4.00 |               NULL |              1.50 |
| Hot Dog   |      1.50 |       5.00 |               NULL |              1.50 |
| Sandwich  |      2.50 |       6.00 |               NULL |              1.50 |
+-----------+-----------+------------+--------------------+-------------------+