Introduzione al supporto per alias di colonna laterali

Siamo entusiasti di presentare il supporto di una nuova funzionalità SQL in Apache Spark e Databricks: Lateral Column Alias (LCA). Questa funzionalità semplifica query SQL complesse consentendo agli utenti di riutilizzare un'espressione specificata in precedenza nello stesso elenco SELECT, eliminando in molti casi la necessità di utilizzare sottoquery annidate e Common Table Expressions (CTE). Questo post del blog discute i casi d'uso della funzionalità e i vantaggi che apporta agli utenti Spark e Databricks.

Cos'è il supporto per Lateral Column Alias?

Lateral Column Alias (LCA) offre agli utenti la possibilità di riutilizzare un'espressione specificata in precedenza all'interno dello stesso elenco SELECT.
Questa funzionalità può essere compresa meglio attraverso l'esempio fornito di seguito. Ecco una semplice query:

In assenza del supporto LCA, gli utenti riceveranno un errore su questa query, poiché la seconda occorrenza di a nell'elenco SELECT non può essere risolta:

Fortunatamente, con la funzionalità LCA, questa seconda occorrenza di a nella query viene ora identificata con successo come alias definito in precedenza nello stesso elenco SELECT: 1 AS a. Gli utenti non riceveranno più un errore, ma invece i seguenti risultati:

Elimina sottoquery e CTE complesse con LCA Chaining

Mentre gli esempi precedenti illustrano il concetto base di LCA, il vero potere di questa funzionalità risiede nella sua capacità di eliminare sottoquery e CTE complesse.

Prima dell'introduzione di LCA, gli utenti dovevano gestire più sottoquery e CTE quando cercavano di fare riferimento a qualsiasi attributo definito da un alias precedente. Ciò aumentava la complessità e la verbosità delle query SQL, rendendole difficili da leggere, scrivere e mantenere. Al contrario, il supporto LCA semplifica fondamentalmente queste query, rendendole più user-friendly e gestibili.

Prendiamo un esempio. Supponiamo che esista una tabella products che memorizza informazioni sui prodotti come nome, categoria, prezzo e valutazione dei clienti. Il nostro obiettivo è calcolare un prezzo aggiustato in base a diversi fattori influenti. Lo scenario delineerà chiaramente come LCA può trasformare una query contorta in una versione significativamente semplificata.

Ecco la struttura della tabella:

Vorremmo calcolare il prezzo aggiustato per ogni prodotto in base al valore maggiore di due fattori: la percentuale di aumento del prezzo basata sulla valutazione degli utenti del prodotto e basata sul rango del prodotto all'interno della sua categoria. Senza il supporto LCA, la query appare così:

La logica contiene molte operazioni di concatenamento in cui un calcolo successivo dipende dai risultati precedentemente calcolati. Pertanto, richiede più CTE per memorizzare ogni calcolo intermedio in modo adatto per riferimenti successivi nelle fasi successive della query.

Tuttavia, con LCA, è possibile esprimere la query come un'unica istruzione SELECT invece:

Gli LCA possono anche essere concatenati! Ciò significa che l'espressione alias corrente, a cui possono fare riferimento espressioni successive, può fare riferimento a un alias laterale definito in precedenza. Ad esempio, la definizione di final_increase_percentage dipende da due alias di colonna laterali: increase_percentage_based_on_rating e increase_percentage_based_on_rank. Il calcolo successivo di adjusted_price fa quindi riferimento a final_increase_percentage. Questa potenza di concatenamento di LCA consente agli utenti di creare una serie di calcoli dipendenti, in cui i risultati di un calcolo vengono utilizzati come input per il successivo.

Come possiamo vedere nell'esempio precedente, LCA semplifica notevolmente la query, eliminando calcoli ripetuti o la necessità di più CTE, rendendola più facile da capire, mantenere e debuggare. Migliora anche la leggibilità poiché la definizione del calcolo e l'utilizzo sono vicini nella query.

LCA per tutto

Espressioni semplici, di aggregazione o di finestra

Quasi ogni espressione può risiedere all'interno di un alias di colonna laterale. Gli esempi nella sezione precedente mostrano che espressioni CASE-WHEN complesse, così come espressioni GREATEST o persino funzioni di finestra, possono risiedere all'interno di un alias di colonna laterale per un uso futuro in espressioni successive.

Allo stesso modo, possiamo anche annidare espressioni di aggregazione in questo modo. Ecco un esempio sulla stessa tabella products:

Tipi di dati complessi

LCA funziona bene anche con tipi di dati complessi come struct, array e map. Ad esempio,

Espressioni non deterministiche

LCA garantisce che le espressioni non deterministiche vengano valutate una sola volta, rispecchiando la semantica "esegui una volta" offerta dalle CTE. Ciò garantisce risultati coerenti quando si utilizzano espressioni non deterministiche nella query.

Ad esempio, consideriamo uno scenario in cui esiste un member_price per ogni prodotto nella tabella products sopra. Vorremmo applicare una percentuale di sconto casuale tra il 0% e il 5% a ciascun prodotto e quindi calcolare il prezzo scontato sia del price che del member_price. Questo esercizio dovrebbe garantire che la percentuale di sconto applicata a entrambi i prezzi rimanga la stessa.

Con LCA, possiamo scrivere:

In questo esempio, Databricks calcola il discounted_rate una sola volta, e questo valore rimane lo stesso in tutti i riferimenti successivi, inclusi il calcolo di adjusted_price e adjusted_member_price.

D'altra parte, se stiamo semplicemente copiando espressioni non deterministiche, questo comportamento non si applica perché ogni espressione verrebbe valutata separatamente, causando tassi di sconto incoerenti per i due prezzi:

Prova LCA!

In sintesi, Lateral Column Alias (LCA) è una funzionalità potente che semplifica notevolmente le query SQL consentendo agli utenti di definire un alias nominato su un albero di espressioni e quindi fare riferimento a questo alias in seguito nella stessa clausola SELECT.

• Ciò evita di ripetere le stesse espressioni più volte o la necessità di subquery o CTE, generando invece query SELECT concise e leggibili.
• È compatibile con tutti i tipi di espressioni e tipi di dati complessi. La sintassi SQL supporta la concatenazione di questi alias per una maggiore flessibilità.
• Garantisce che ogni espressione non deterministica venga valutata una sola volta, consentendo così risultati coerenti tra più riferimenti.

LCA è completamente disponibile e abilitato per impostazione predefinita in Databricks Runtime 12.2 LTS e versioni successive, in Databricks SQL 2023.20 e versioni successive, e Apache Spark 3.4.

Leggi di più

• Ordine di risoluzione
  I lettori curiosi potrebbero essere interessati all'ordine di risoluzione dei nomi nelle query SQL con l'introduzione di LCA. Questo documento Databricks sulla risoluzione dei nomi definisce un chiaro set di regole ordinate ed esempi concreti per risolvere i riferimenti, incluso il ruolo di LCA in questo processo.

(Questo post sul blog è stato tradotto utilizzando strumenti basati sull'intelligenza artificiale) Post originale