Wiederverwendbare Spaltenaliasse verwenden

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

Die folgende Abfrage zeigt beispielsweise, wie Sie Ergebnisse anhand einer Berechnung wie gross_price filtern. Sie können direkt in der WHERE-Klausel auf den Alias gross_price verweisen, 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 Spaltenaliasse bieten Ihnen folgende Vorteile:

• Vereinfachte SQL-Syntax: Sie können übersichtliche, prägnante Berechnungen von links nach rechts schreiben, ohne komplexe Ausdrücke zu wiederholen oder Abfragen in Unterabfragen einzuschließen.
• 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 Spaltenaliasse aktivieren oder deaktivieren

Wichtig: Bevor Sie wiederverwendbare Spaltenaliasse aktivieren, sollten Sie die Auswirkungen auf Ihre vorhandenen Arbeitslasten prüfen. Spaltenaliasse ändern die Reihenfolge der Namensauflösung einer Abfrage.

Standardmäßig sind wiederverwendbare Spaltenaliasse deaktiviert. Wenn Sie Spaltenaliasse verwenden möchten, muss Ihr BigQuery-Administrator die Funktion aktivieren, indem er die Option reusable_column_aliases_mode mit einer der folgenden DDL-Anweisungen (Data Definition Language) auf on setzt:

• Verwenden Sie die Anweisung ALTER ORGANIZATION SET OPTIONS, um Spaltenaliasse auf Organisationsebene zu aktivieren.

• Verwenden Sie die Anweisung ALTER PROJECT SET OPTIONS, um Spaltenaliasse auf Projektebene zu aktivieren.

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 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 Spaltenaliasse auf Organisationsebene zu aktivieren:

ALTER ORGANIZATION
SET OPTIONS (
  `region-<code><var>REGION</var></code>.reusable_column_aliases_mode` = "on"
);

Ersetzen Sie REGION durch Ihre Region.

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

ALTER PROJECT <code><var>PROJECT_ID</var></code>
SET OPTIONS (
  `region-<code><var>REGION</var></code>.reusable_column_aliases_mode` = "on"
);

Ersetzen Sie Folgendes:

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

Prüfen, ob Spaltenaliasse aktiviert oder deaktiviert sind

Sie können prüfen, ob wiederverwendbare Spaltenaliasse auf Projekt- oder Organisationsebene aktiviert oder deaktiviert sind, indem Sie die folgenden Informationsschemaansichten abfragen:

• PROJECT_OPTIONS
• ORGANIZATION_OPTIONS
• EFFECTIVE_PROJECT_OPTIONS

Weitere Informationen zum Arbeiten mit Informationsschemaansichten 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-Klausel oder einer SELECT-Klausel verwenden:

• WHERE -Klausel: Filtern basierend auf einer berechneten Spalte, die in der SELECT-Liste definiert ist.
• SELECT -Klausel: Verwenden eines berechneten Alias, um einen anderen Alias in derselben Projektionsliste zu definieren oder um zu bestimmen, welche Daten von der Abfrage zurückgegeben werden.

Beispiel

In diesem Beispiel wird gezeigt, wie Sie wiederverwendbare Spaltenaliasse 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) in der Reihenfolge der Abfragenamensauflösung

Ein Kennungsname kann als physische Spalte in Ihrer Tabelle und als von Ihnen erstellter Alias vorhanden sein. Daher sollten Sie 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 die Abfrage in einer benutzerdefinierten Funktion (User-Defined Function, UDF) oder einer tabellarischen Funktion (Table-Valued Function, TVF) ausgeführt wird.
5. Skriptvariablen: Variablen, die in einem Skript definiert sind.

Wenn Ihre Tabelle eine Spalte mit dem Namen gross_price enthält und Sie einen Alias mit dem Namen gross_price erstellen, verwendet die Abfrage den Spaltenwert und nicht Ihren Alias.

Wenn Sie also eine Spalte der äußeren Abfrage, ein Funktionsargument oder eine Skriptvariable mit demselben Namen wie ein Alias haben, das Sie in der SELECT-Klausel definieren, verwendet BigQuery für Berechnungen den Wert des Alias anstelle der Variablen 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 Spaltenaliassen funktioniert, wenn es Namenskonflikte gibt.

Konflikt mit einer Spalte der äußeren Abfrage

Wenn Sie wiederverwendbare Spaltenaliasse nicht aktivieren, wird eine Spalte der äußeren Abfrage verwendet, wenn ein in der SELECT-Klausel definierter Alias denselben Namen wie eine Spalte in der äußeren Abfrage hat. Wenn wiederverwendbare Spaltenaliasse aktiviert sind, „überschattet“ der Alias die Spalte der äußeren Abfrage im Abfragebereich.

Im folgenden Beispiel verwendet eine verschachtelte Abfrage Aliase und hat Zugriff auf die Spalten aus 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 von category_id je nach Zeile. Wenn Sie wiederverwendbare Spaltenaliasse aktivieren, verwendet die Abfrage für alle Zeilen den Aliaswert 100.

Konflikt mit Funktionsargumenten

Wenn eine Abfrage in 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 mit dem Namen 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 ein Alias definiert ist, das denselben Namen wie eine Skriptvariable hat, wird die Skriptvariable verwendet. Wenn Sie wiederverwendbare Spaltenaliasse aktivieren, überschattet der Alias die Variable im Abfragebereich.

Im folgenden Beispiel wird eine Variable mit dem Namen gross_price deklariert, die den Standardwert 10.0 hat. Die Abfrage berechnet auch eine Spalte und gibt ihr den Alias gross_price:

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 10.0. Das Ergebnis ist ein discounted_gross_price-Wert von 5.0, der für alle Zeilen konstant ist. Das Ergebnis wird als 10.0 * 0.5 = 5.0 berechnet.

Wenn Sie wiederverwendbare Spaltenaliasse aktivieren, verwendet die Abfrage den Aliaswert net_price * 1.23. Das Ergebnis ist ein discounted_gross_price-Wert, der je nach Zeile variiert. Das Ergebnis wird als (net_price * 1.23) * 0.5 berechnet.

Best Practices

Beachten Sie bei der Verwendung von wiederverwendbaren Spaltenaliassen die folgenden Best Practices:

• Prüfen Sie vor dem Aktivieren von wiederverwendbaren Spaltenaliassen die Auswirkungen auf Ihre vorhandenen Arbeitslasten.
• Verwenden Sie nicht den Namen Ihrer Skriptvariablen, Funktionsargumente oder Spalten in äußeren Abfragen als Namen für Ihren Alias, um unbeabsichtigtes Verhalten oder unbemerkte Verhaltensänderungen (semantische Abweichung) zu vermeiden.
• Wenn Sie SQL aus einem Quelldialekt migrieren, der Alias semantik wie Teradata oder Snowflake unterstützt, folgen Sie der SQL Übersetzungsanleitung bevor Sie mit der Migration beginnen.

Fehlerbehebung bei Spaltenaliassen

Die folgenden Beispiele zeigen, wie Sie häufige Probleme mit wiederverwendbaren Spaltenaliassen beheben können.

Problem: Sie erhalten unerwartete Abfrageergebnisse oder der Alias wird 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. Dies wird von links nach rechts ausgewertet.

Fehler: Column name is ambiguous.

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

Beschränkungen

Wiederverwendbare Spaltenaliasse unterliegen den folgenden Beschränkungen:

• Wenn eine Abfrage Gruppierungen oder Aggregationen 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 verweist 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.
  

• Das Anwenden einer Fensterfunktion auf einen Alias, der direkt oder transitiv auf eine andere Fensterfunktion verweist, ist nicht zulässig. In der folgenden Abfrage verweist der Alias x auf eine ungültige Fensterfunktion:

  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 Spaltenaliasse werden von der Graph Query Language (GQL) nicht unterstützt.