Utilizza alias di colonna riutilizzabili

GoogleSQL in BigQuery supporta gli alias di colonna riutilizzabili. Un alias di colonna riutilizzabile consente di definire un alias per un'espressione nella clausola SELECT di una query SQL e di fare riferimento immediatamente a quell'alias all'interno dello stesso elenco SELECT o nella clausola WHERE dello stesso blocco di query.

Ad esempio, la seguente query mostra come filtrare i risultati in base a un calcolo, come gross_price. Puoi fare riferimento all'alias gross_price direttamente nella clausola WHERE anziché ripetere il calcolo nella clausola WHERE o utilizzare una subquery:

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

Gli alias di colonna riutilizzabili ti offrono:

• Sintassi SQL semplificata: puoi scrivere calcoli chiari, concisi e da sinistra a destra senza ripetere espressioni complesse o aggregare query in sottoquery.
• Migliore leggibilità: gli analisti e gli sviluppatori possono dichiarare un'espressione una volta e riutilizzarla immediatamente. In questo modo le query sono più facili da leggere, eseguire il debug e gestire.

Abilita o disabilita gli alias di colonna riutilizzabili

Importante: prima di attivare gli alias di colonna riutilizzabili, valuta il loro impatto sui workload esistenti. Gli alias delle colonne modificano l'ordine di risoluzione dei nomi di una query.

Per impostazione predefinita, gli alias di colonna riutilizzabili sono disabilitati. Per utilizzare gli alias di colonna, l'amministratore di BigQuery deve abilitare la funzionalità impostando l'opzione reusable_column_aliases_mode su on utilizzando una delle seguenti istruzioni del linguaggio di definizione dei dati (DDL):

• Per attivare gli alias di colonna a livello di organizzazione, utilizza l'istruzione ALTER ORGANIZATION SET OPTIONS.

• Per abilitare gli alias di colonna a livello di progetto, utilizza l'istruzione ALTER PROJECT SET OPTIONS.

Per impostare questa opzione di configurazione, devi disporre del ruolo Amministratore BigQuery (roles/bigquery.admin) o dell'autorizzazione bigquery.projects.update sul progetto o dell'autorizzazione bigquery.organizations.update sull'organizzazione. Per informazioni sulle autorizzazioni necessarie per impostare le opzioni di configurazione, consulta Autorizzazioni richieste nella pagina Gestisci le impostazioni di configurazione.

Per disattivare gli alias di colonna riutilizzabili, imposta reusable_column_aliases_mode su off. Se imposti il valore su NULL, viene utilizzato il valore predefinito di sistema e la funzionalità viene disattivata.

Esempi

Per abilitare gli alias di colonna riutilizzabili a livello di organizzazione, inserisci la seguente istruzione DDL:

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

Sostituisci REGION con la tua regione.

Per abilitare gli alias di colonna riutilizzabili a livello di progetto, inserisci la seguente istruzione DDL:

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

Sostituisci quanto segue:

• REGION: la tua regione.
• PROJECT_ID: il tuo ID progetto.

Verifica se gli alias di colonna sono abilitati o disabilitati

Puoi verificare se gli alias di colonna riutilizzabili sono abilitati o disabilitati a livello di progetto o organizzazione eseguendo query sulle seguenti viste dello schema informativo:

• PROJECT_OPTIONS
• ORGANIZATION_OPTIONS
• EFFECTIVE_PROJECT_OPTIONS

Per saperne di più sull'utilizzo delle viste dello schema di informazioni, consulta Introduzione a INFORMATION_SCHEMA.

Come utilizzare gli alias di colonna riutilizzabili

Puoi utilizzare un alias, ovvero un identificatore assegnato a un'espressione, in una clausola WHERE o SELECT:

• Clausola WHERE: filtro basato su una colonna calcolata definita nell'elenco SELECT.
• Clausola SELECT: utilizzo di un alias calcolato per definire un altro alias all'interno dello stesso elenco di proiezione o per determinare quali dati vengono restituiti dalla query.

Esempio

Questo esempio mostra come utilizzare alias di colonna riutilizzabili per calcolare un valore in base a un altro calcolo definito in precedenza nell'elenco SELECT. La query mostra anche come fare riferimento all'alias nella clausola WHERE:

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

Modifiche del comportamento (deriva semantica) nell'ordine di risoluzione dei nomi delle query

Un nome identificatore può esistere come colonna fisica nella tabella e come alias creato da te, quindi devi considerare l'ordine di risoluzione dei nomi della query. BigQuery risolve i nomi nel seguente ordine:

1. Nome colonna: le colonne esistenti nella tabella hanno sempre la priorità più alta.
2. Alias: l'alias definito nella query.
3. Colonna della query esterna: colonne di una query principale.
4. Argomento della funzione: se viene eseguito all'interno di una funzione definita dall'utente dall'utente (UDF) o di una funzione con valori di tabella (TVF).
5. Variabili di script: variabili definite in uno script.

In base a questo ordine, se la tabella ha una colonna denominata gross_price e crei un alias denominato gross_price, la query utilizza il valore della colonna, non l'alias.

Ciò significa che se hai una colonna di query esterna, un argomento di funzione o una variabile di script con lo stesso nome di un alias definito nella clausola SELECT, BigQuery utilizza il valore dell'alias per i calcoli anziché la variabile o l'argomento.

Esempi di funzionamento dell'ordine di risoluzione dei nomi

Gli esempi seguenti mostrano come funziona l'ordine di risoluzione dei nomi con gli alias di colonna riutilizzabili in caso di conflitti di denominazione.

Conflitto con una colonna della query esterna

Se non attivi gli alias di colonna riutilizzabili, un alias definito nella clausola SELECT con lo stesso nome di una colonna nella query esterna comporta l'utilizzo della colonna della query esterna. Se gli alias di colonna riutilizzabili sono abilitati, l'alias "nasconde" la colonna della query esterna nell'ambito della query.

Nell'esempio seguente, una query nidificata utilizza alias e ha accesso alle colonne della query esterna:

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;

Se non attivi gli alias di colonna riutilizzabili, la subquery utilizza la colonna category_id della tabella product nella query esterna. Di conseguenza, il valore category_id varia in base alla riga. Se attivi gli alias di colonna riutilizzabili, la query utilizza il valore alias di 100 per tutte le righe.

Conflitto con gli argomenti della funzione

Se una query viene eseguita all'interno di una funzione definita dall'utente (UDF), un alias definito all'interno della query ha la precedenza sugli argomenti della funzione.

Nell'esempio seguente, la funzione calculate_metric ha un argomento di input denominato 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);

Se non attivi gli alias di colonna riutilizzabili, la query utilizza il valore dell'argomento passato alla funzione (100) e il risultato viene calcolato come 110 (100 + 10). Se attivi gli alias di colonna riutilizzabili, la query utilizza l'alias definito nell'elenco SELECT (5) e il risultato viene calcolato come 15 (5 + 10).

Conflitto con le variabili dello script

Se non attivi gli alias di colonna riutilizzabili e hai un alias definito nella clausola SELECT con lo stesso nome di una variabile di script, viene utilizzata la variabile di script. Se attivi gli alias di colonna riutilizzabili, l'alias nasconde la variabile nell'ambito della query.

L'esempio seguente dichiara una variabile denominata gross_price con un valore predefinito di 10.0. La query calcola anche una colonna e le assegna l'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;

Se non attivi gli alias di colonna riutilizzabili, la query utilizza il valore della variabile 10.0. Il risultato è un valore discounted_gross_price di 5.0 che è una costante per tutte le righe. Il risultato viene calcolato come 10.0 * 0.5 = 5.0.

Se attivi gli alias di colonna riutilizzabili, la query utilizza il valore dell'alias di net_price * 1.23. Il risultato è un valore discounted_gross_price che varia per riga. Il risultato viene calcolato come (net_price * 1.23) * 0.5.

Best practice

Quando utilizzi alias di colonna riutilizzabili, tieni presente le seguenti best practice:

• Prima di abilitare gli alias di colonna riutilizzabili, valuta il loro impatto sui workload esistenti.
• Per evitare comportamenti indesiderati o modifiche silenziose del comportamento (deriva semantica), non utilizzare il nome delle variabili dello script, degli argomenti della funzione o delle colonne nelle query esterne come nome dell'alias.
• Se esegui la migrazione di SQL da un dialetto di origine che supporta la semantica di aliasing, come Teradata o Snowflake, segui le istruzioni di traduzione SQL prima di iniziare la migrazione.

Risoluzione dei problemi relativi agli alias delle colonne

I seguenti esempi mostrano come risolvere i problemi comuni relativi agli alias di colonna riutilizzabili.

Problema: ricevi risultati delle query imprevisti o l'alias sembra essere ignorato.

Risoluzione: verifica l'ordine di risoluzione dei nomi. Se la tabella contiene una colonna con lo stesso nome dell'alias, il nome della colonna ha una priorità maggiore rispetto all'alias. Assegna un nome univoco all'alias per evitare questo conflitto.

Errore: Column not found.

Soluzione: assicurati di non utilizzare un riferimento in avanti. Puoi fare riferimento solo a un alias definito prima del suo utilizzo. La valutazione viene eseguita da sinistra a destra.

Errore: Column name is ambiguous.

Soluzione: assicurati che il nome dell'alias non sia definito più di una volta nello stesso elenco SELECT prima che venga fatto riferimento.

Limitazioni

Gli alias di colonna riutilizzabili sono soggetti alle seguenti limitazioni:

• Quando una query include il raggruppamento o l'aggregazione, gli alias per le espressioni non aggregate (colonne di raggruppamento o proiezioni di colonne di raggruppamento) non sono consentiti nella clausola WHERE o come argomenti per le funzioni di aggregazione. Nella query seguente, l'alias b fa riferimento in modo errato a un'espressione che include a, che è una colonna di raggruppamento:

  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
  

• Gli alias che fanno riferimento a una funzione di aggregazione o finestra direttamente o in modo transitivo non possono essere utilizzati nella clausola WHERE o come argomenti di altre funzioni di aggregazione. Nella query seguente, l'alias x fa riferimento in modo errato a una funzione di aggregazione:

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

• Non è consentito applicare una funzione finestra su un alias che fa riferimento a un'altra funzione finestra direttamente o in modo transitivo. Nella query seguente, l'alias x fa riferimento a una funzione finestra non valida:

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

• Gli alias di colonna riutilizzabili non sono supportati dalla sintassi con pipe.

• Gli alias di colonna riutilizzabili non sono supportati da Graph Query Language (GQL).