Mit wiederverwendbaren Spalten-Aliassen arbeiten

GoogleSQL in BigQuery unterstützt wiederverwendbare Spalten-Aliasse. Mit einem wiederverwendbaren Spaltenalias können Sie einen Alias für einen Ausdruck in der SELECT-Klausel einer SQL-Abfrage definieren und diesen Alias sofort in derselben SELECT-Liste oder in der WHERE-Klausel desselben Abfrageblocks referenzieren.

Die folgende Abfrage zeigt beispielsweise, wie Sie Ergebnisse anhand einer Berechnung wie gross_price filtern. Sie können den Alias gross_price direkt in der WHERE-Klausel referenzieren, anstatt die Berechnung in der WHERE-Klausel zu wiederholen oder eine Unterabfrage zu verwenden:

SELECT
  net_price * 1.23 as gross_price
FROM
  product
WHERE
  gross_price < 10.0

Wiederverwendbare Spalten-Aliasse bieten Ihnen folgende Vorteile:

  • Vereinfachte SQL-Syntax: Sie können saubere, prägnante Berechnungen von links nach rechts schreiben, ohne komplexe Ausdrücke zu wiederholen oder Abfragen in Unterabfragen zu verpacken.
  • Verbesserte Lesbarkeit: Analysten und Entwickler können einen Ausdruck einmal deklarieren und dann sofort wiederverwenden. Dadurch sind Abfragen einfacher zu lesen, zu debuggen und zu verwalten.

Wiederverwendbare Spalten-Aliasse aktivieren oder deaktivieren

Wiederverwendbare Spaltenaliasse sind standardmäßig deaktiviert. Damit Sie Spalten-Aliasse verwenden können, muss Ihr BigQuery-Administrator die Funktion aktivieren. Dazu muss er die Option reusable_column_aliases_mode mit einer der folgenden DDL-Anweisungen (Data Definition Language) auf on setzen:

Zum Festlegen dieser Konfigurationsoption benötigen Sie die Rolle „BigQuery-Administrator“ (roles/bigquery.admin) oder die Berechtigung bigquery.projects.update für das Projekt oder die Berechtigung bigquery.organizations.update für die Organisation. Informationen zu den Berechtigungen, die zum Festlegen von Konfigurationsoptionen erforderlich sind, finden Sie auf der Seite „Konfigurationseinstellungen verwalten“ unter Erforderliche Berechtigungen.

Wenn Sie wiederverwendbare Spaltenaliasse deaktivieren möchten, setzen Sie reusable_column_aliases_mode auf off. Wenn Sie den Wert auf NULL setzen, wird die Standardeinstellung des Systems verwendet und die Funktion ist ebenfalls deaktiviert.

Beispiele

Geben Sie die folgende DDL-Anweisung ein, um wiederverwendbare Spalten-Aliasse auf Organisationsebene zu aktivieren:

ALTER ORGANIZATION
SET OPTIONS (
  `region-REGION.reusable_column_aliases_mode` = "on"
);

Ersetzen Sie REGION durch Ihre Region.

Geben Sie die folgende DDL-Anweisung ein, um wiederverwendbare Spalten-Aliasse auf Projektebene zu aktivieren:

ALTER PROJECT PROJECT_ID
SET OPTIONS (
  `region-REGION.reusable_column_aliases_mode` = "on"
);

Ersetzen Sie Folgendes:

  • REGION: Ihre Region.
  • PROJECT_ID: Ihre Projekt-ID.

Prüfen, ob Spalten-Aliasse aktiviert oder deaktiviert sind

Sie können prüfen, ob wiederverwendbare Spalten-Aliasse auf Projekt- oder Organisationsebene aktiviert oder deaktiviert sind, indem Sie die folgenden Informationsschema-Ansichten abfragen:

Weitere Informationen zum Arbeiten mit Informationsschema-Ansichten finden Sie unter Einführung in INFORMATION_SCHEMA.

Wiederverwendbare Spaltenaliasse verwenden

Sie können einen Alias (eine Kennung, die einem Ausdruck zugewiesen ist) in einer WHERE- oder SELECT-Klausel verwenden:

  • WHERE-Klausel: Filterung basierend auf einer berechneten Spalte, die in der SELECT-Liste definiert ist.
  • SELECT-Klausel: Mit einem berechneten Alias einen anderen Alias in derselben Projektionsliste definieren oder festlegen, welche Daten von der Abfrage zurückgegeben werden.

Beispiel

In diesem Beispiel wird gezeigt, wie Sie wiederverwendbare Spalten-Aliasse verwenden, um einen Wert basierend auf einer anderen Berechnung zu berechnen, die Sie zuvor in der SELECT-Liste definiert haben. Die Abfrage zeigt auch, wie Sie in der WHERE-Klausel auf den Alias verweisen:

SELECT
  net_price * 1.23 as gross_price,
  gross_price * 0.5 as discounted_gross_price
FROM
  product
WHERE
  discounted_gross_price < 10.0;

Verhaltensänderungen (semantische Abweichung) bei der Reihenfolge der Namensauflösung von Abfragen

Ein ID-Name kann als physische Spalte in Ihrer Tabelle und als von Ihnen erstellter Alias vorhanden sein. Sie sollten daher die Reihenfolge der Namensauflösung der Abfrage berücksichtigen. BigQuery löst Namen in der folgenden Reihenfolge auf:

  1. Spaltenname: Vorhandene Spalten in der Tabelle haben immer die höchste Priorität.
  2. Alias: der in der Abfrage definierte Alias.
  3. Spalte der äußeren Abfrage: Spalten aus einer übergeordneten Abfrage.
  4. Funktionsargument: wenn es innerhalb einer benutzerdefinierten Funktion (UDF) oder einer Tabellenwertfunktion (TVF) ausgeführt wird.
  5. Scriptvariablen: in einem Script definierte Variablen.

Wenn Ihre Tabelle eine Spalte mit dem Namen gross_price enthält und Sie einen Alias mit dem Namen gross_price erstellen, wird in der Abfrage der Spaltenwert und nicht der Alias verwendet.

Wenn Sie also eine äußere Abfragespalte, ein Funktionsargument oder eine Skriptvariable mit demselben Namen wie ein Alias haben, den Sie in Ihrer SELECT-Klausel definieren, verwendet BigQuery für Berechnungen den Aliaswert anstelle der Variable oder des Arguments.

Beispiele für die Funktionsweise der Reihenfolge der Namensauflösung

Die folgenden Beispiele zeigen, wie die Reihenfolge der Namensauflösung bei wiederverwendbaren Spalten-Aliassen funktioniert, wenn Namenskonflikte auftreten.

Konflikt mit einer Spalte der äußeren Abfrage

Wenn Sie wiederverwendbare Spaltenaliasse nicht aktivieren, wird ein in der SELECT-Klausel definierter Alias, der denselben Namen wie eine Spalte in der äußeren Abfrage hat, dazu führen, dass die Spalte der äußeren Abfrage verwendet wird. Wenn wiederverwendbare Spaltenaliasse aktiviert sind, „verdeckt“ der Alias die Spalte der äußeren Abfrage innerhalb des Abfragebereichs.

Im folgenden Beispiel verwendet eine verschachtelte Abfrage Aliasse und hat Zugriff auf die Spalten der äußeren Abfrage:

SELECT
  name,
  net_price,
  category_id, -- table product has category_id column
  (SELECT 100 AS category_id -- alias defined with the same name
     FROM category c
     WHERE
       id = category_id  -- Now the alias category_id is used here, not the outer query column
  ) as X
FROM
  product;

Wenn Sie wiederverwendbare Spaltenaliasse nicht aktivieren, verwendet die Unterabfrage die Spalte category_id aus der Tabelle product in der äußeren Abfrage. Daher variiert der Wert category_id je nach Zeile. Wenn Sie wiederverwendbare Spalten-Aliasse aktivieren, wird in der Abfrage der Aliaswert von 100 für alle Zeilen verwendet.

Konflikt mit Funktionsargumenten

Wenn eine Abfrage innerhalb einer benutzerdefinierten Funktion (User-Defined Function, UDF) ausgeführt wird, hat ein in dieser Abfrage definierter Alias Vorrang vor den Argumenten der Funktion.

Im folgenden Beispiel hat die Funktion calculate_metric ein Eingabeargument namens val:

CREATE TEMP FUNCTION calculate_metric(val INT64) AS ((
  SELECT AS STRUCT
    5 AS val,       -- Alias defined as constant 5
    val + 10        -- Reference to 'val'
));

-- Calling the function with argument 100
SELECT calculate_metric(100);

Wenn Sie wiederverwendbare Spaltenaliasse nicht aktivieren, verwendet die Abfrage den an die Funktion übergebenen Argumentwert (100) und das Ergebnis wird als 110 (100 + 10) berechnet. Wenn Sie wiederverwendbare Spaltenaliasse aktivieren, verwendet die Abfrage den in der SELECT-Liste (5) definierten Alias und das Ergebnis wird als 15 (5 + 10) berechnet.

Konflikt mit Skriptvariablen

Wenn Sie wiederverwendbare Spaltenaliasse nicht aktivieren und in der SELECT-Klausel einen Alias mit demselben Namen wie eine Skriptvariable definiert haben, wird die Skriptvariable verwendet. Wenn Sie wiederverwendbare Spaltenaliasse aktivieren, verdeckt der Alias die Variable innerhalb des Abfragebereichs.

Im folgenden Beispiel wird eine Variable namens gross_price mit dem Standardwert 10.0 deklariert. Die Abfrage berechnet auch eine Spalte und weist ihr den Alias gross_price zu:

DECLARE gross_price NUMERIC DEFAULT 10.0;

SELECT
  net_price * 1.23 AS gross_price,
  -- Usage of 'gross_price' here determines the behavior
  gross_price * 0.5 AS discounted_gross_price
FROM
  product;

Wenn Sie wiederverwendbare Spaltenaliasse nicht aktivieren, verwendet die Abfrage den Variablenwert von 10.0. Das Ergebnis ist ein discounted_gross_price-Wert von 5.0, der für alle Zeilen eine Konstante ist. Das Ergebnis wird als 10.0 * 0.5 = 5.0 berechnet.

Wenn Sie wiederverwendbare Spalten-Aliasse aktivieren, verwendet die Abfrage den Aliaswert von net_price * 1.23. Das Ergebnis ist ein discounted_gross_price-Wert, der sich von Zeile zu Zeile unterscheidet. Das Ergebnis wird als (net_price * 1.23) * 0.5 berechnet.

Best Practices

Beachten Sie bei der Verwendung wiederverwendbarer Spalten-Aliasse die folgenden Best Practices:

  • Bevor Sie wiederverwendbare Spaltenaliasse aktivieren, sollten Sie deren Auswirkungen auf Ihre vorhandenen Arbeitslasten bewerten.
  • Um unbeabsichtigtes Verhalten oder unbemerkte Verhaltensänderungen (semantische Abweichungen) zu vermeiden, sollten Sie den Namen Ihrer Skriptvariablen, Funktionsargumente oder Spalten in äußeren Abfragen nicht als Namen Ihres Alias verwenden.
  • Wenn Sie SQL aus einem Quelldialekt migrieren, der Aliasing-Semantik wie Teradata oder Snowflake unterstützt, folgen Sie der Anleitung zur SQL-Übersetzung, bevor Sie mit der Migration beginnen.

Fehlerbehebung bei Spaltenaliassen

In den folgenden Beispielen wird gezeigt, wie Sie häufige Probleme mit wiederverwendbaren Spalten-Aliassen beheben können.

Problem: Sie erhalten unerwartete Abfrageergebnisse oder der Alias wird scheinbar ignoriert.

Lösung: Prüfen Sie die Reihenfolge der Namensauflösung. Wenn Ihre Tabelle eine Spalte mit demselben Namen wie Ihr Alias enthält, hat der Spaltenname eine höhere Priorität als der Alias. Geben Sie Ihrem Alias einen eindeutigen Namen, um diesen Konflikt zu vermeiden.

Fehler: Column not found.

Lösung: Achten Sie darauf, dass Sie keine Vorwärtsreferenz verwenden. Sie können nur auf einen Alias verweisen, der vor seiner Verwendung definiert wurde. Die Auswertung erfolgt von links nach rechts.

Fehler: Column name is ambiguous.

Lösung: Achten Sie darauf, dass der Aliasname nicht mehr als einmal in derselben SELECT-Liste definiert ist, bevor darauf verwiesen wird.

Beschränkungen

Für wiederverwendbare Spaltenaliasse gelten die folgenden Einschränkungen:

  • Wenn eine Abfrage eine Gruppierung oder Aggregation enthält, sind Aliase für nicht aggregierte Ausdrücke (Gruppierungsspalten oder Projektionen von Gruppierungsspalten) in der WHERE-Klausel oder als Argumente für Aggregatfunktionen nicht zulässig. In der folgenden Abfrage verweist der Alias b fälschlicherweise auf einen Ausdruck, der a enthält, eine Gruppierungsspalte:

    SELECT a+1 as b, sum(b) -- sum(b) is invalid.
FROM t
WHERE b > 5         -- Referencing b under WHERE clause is not allowed.
GROUP BY a

  • Aliasse, die direkt oder transitiv auf eine Aggregat- oder Fensterfunktion verweisen, können nicht in der WHERE-Klausel oder als Argumente für andere Aggregatfunktionen verwendet werden. In der folgenden Abfrage bezieht sich der Alias x fälschlicherweise auf eine Aggregatfunktion:

    SELECT sum(a) AS x, sum(x) -- sum(x) is invalid.
FROM t
WHERE x > 0          -- Referencing x under WHERE clause is not allowed.

  • Es ist nicht zulässig, eine Fensterfunktion auf einen Alias anzuwenden, der direkt oder transitiv auf eine andere Fensterfunktion verweist. In der folgenden Abfrage bezieht sich der Alias x auf eine Fensterfunktion, die ungültig ist:

    SELECT count(*) over() AS x, sum(x) over() -- sum(x) over() is invalid.
FROM t

  • Wiederverwendbare Spaltenaliasse werden von der Pipe-Syntax nicht unterstützt.

  • Wiederverwendbare Spalten-Aliasse werden von der Graph Query Language (GQL) nicht unterstützt.