Snowflake Cross Apply
Introduzione
Quando si utilizza SQL per query complesse, potrebbe essere necessario combinare dati da tabelle diverse in modi più avanzati. Questo va oltre l’uso di inner e outer join. L’operatore CROSS APPLY in SQL Server consente di joinare una tabella con una funzione che restituisce una tabella, creando opzioni di query versatili. Tuttavia, se si utilizza la piattaforma di dati cloud di Snowflake, si potrebbe notare che non esiste un analogo CROSS APPLY in Snowflake.
Questo articolo spiegherà come funziona CROSS APPLY e come utilizzarlo in Snowflake con LATERAL JOIN. Confronteremo anche le differenze tra questi due metodi.
Alla fine di questo articolo, avrai una solida comprensione di come eseguire join complessi in Snowflake analoghi al CROSS APPLY di SQL Server. Iniziamo!
Che Cos’è il CROSS APPLY?
In SQL Server, CROSS APPLY è un operatore che consente di joinare una tabella con una funzione che restituisce una tabella. Applica la funzione a ogni riga della tabella di sinistra e produce un set di risultati combinando le righe della tabella di sinistra con le righe corrispondenti restituite dalla funzione.
Ecco un esempio semplice per illustrare come funziona:
-- Utilizzo di CROSS APPLY SELECT * FROM Person p CROSS APPLY ( SELECT * FROM Company c WHERE p.companyid = c.companyId ) Czip;
In questo esempio, la subquery viene eseguita per ogni riga nella tabella Person utilizzando l’operatore. Restituisce le righe corrispondenti dalla tabella Company dove companyId coincide. Il risultato è un join tra Person e Company basato sulla relazione ‘companyId’.
La query equivalente utilizzando la sintassi standard INNER JOIN sarebbe:
-- Query equivalente utilizzando INNER JOIN SELECT * FROM Person p INNER JOIN Company c ON p.companyid = c.companyId;
Entrambe le query restituiranno lo stesso set di risultati, ma la versione CROSS APPLY consente condizioni di join più complesse ed è particolarmente utile quando si lavora con funzioni che restituiscono tabelle.
Snowflake Cross Apply
Snowflake non ha CROSS APPLY, ma ha funzionalità simili con la keyword LATERAL quando usata con un join. Nel standard ANSI SQL, un lateral join permette di utilizzare colonne da tabelle precedenti nella condizione di join. Questo porta allo stesso risultato dell’utilizzo di CROSS APPLY.
Ecco un esempio di come utilizzare un lateral join in Snowflake per ottenere lo stesso risultato dell’esempio sopra:
-- Utilizzo di LATERAL JOIN in Snowflake SELECT * FROM Person p LEFT JOIN LATERAL ( SELECT * FROM Company c WHERE p.companyid = c.companyId ) Czip ON TRUE;
In questo esempio, la keyword LATERAL viene utilizzata per indicare che la subquery seguente può fare riferimento a colonne della tabella precedente Person. La condizione ON TRUE unisce incondizionatamente la subquery laterale con la tabella Person.
Il lateral join si comporta in modo simile a CROSS APPLY, eseguendo la subquery per ogni riga della tabella di sinistra e combinando i risultati. La principale differenza è che CROSS APPLY esegue un inner join, mentre nell’esempio di lateral join sopra viene utilizzato un left join. È possibile ottenere il comportamento dell’inner join semplicemente cambiando LEFT JOIN LATERAL con INNER JOIN LATERAL.
Piani di Esecuzione
Esaminiamo più da vicino i piani di esecuzione per gli esempi di CROSS APPLY e lateral join per capirne le differenze.
Per l’esempio CROSS APPLY in SQL Server:
- Il sistema esegue una scansione della tabella Person per recuperare tutte le righe.
- Per ogni riga in Person, SQL Server esegue la subquery seguente CROSS APPLY. Filtra la tabella Company in base alla condizione companyId.
- La query unisce le righe risultanti dalla subquery con la riga corrispondente di Person.
- La funzione restituisce il set di risultati finale.
Per l’esempio LATERAL JOIN in Snowflake:
- Il sistema esegue una scansione della tabella Person per recuperare tutte le righe.
- La subquery laterale viene eseguita per ogni riga di Person, filtrando la tabella Company in base alla condizione companyId.
- Le righe risultanti dalla subquery laterale sono left join-ate con la riga corrispondente di Person utilizzando la condizione ON TRUE.
- La funzione restituisce il set di risultati finale.
I piani di esecuzione per entrambi gli approcci sono simili, con la principale differenza nel tipo di join utilizzato (inner join per CROSS APPLY e left join per l’esempio di lateral join).
Esempio con Setup Preliminare
Esaminiamo un esempio più completo che include alcuni passaggi di setup preliminare. Supponiamo di avere due tabelle: Orders e OrderItems. Ogni ordine può avere più articoli e vogliamo recuperare l’importo totale per ogni ordine insieme ai dettagli dell’ordine.
Per prima cosa, creiamo le tabelle necessarie:
-- Creare tabella Orders CREATE TABLE Orders ( OrderID INT, CustomerID INT, OrderDate DATE ); -- Creare tabella OrderItems CREATE TABLE OrderItems ( OrderID INT, ItemID INT, Quantity INT, Price DECIMAL(10, 2) );
-- Inserire dati di esempio nella tabella Orders INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (1, 101, '2023-05-01'), (2, 102, '2023-05-02'), (3, 101, '2023-05-03'); -- Inserire dati di esempio nella tabella OrderItems INSERT INTO OrderItems (OrderID, ItemID, Quantity, Price) VALUES (1, 1, 2, 10.00), (1, 2, 1, 15.00), (2, 1, 3, 10.00), (2, 3, 2, 20.00), (3, 2, 1, 15.00);
Ora usiamo un lateral join per recuperare i dettagli dell’ordine insieme all’importo totale per ogni ordine:
SELECT
o.OrderID,
o.CustomerID,
o.OrderDate,
oi.TotalAmount
FROM Orders o
LEFT JOIN LATERAL (
SELECT
OrderID,
SUM(Quantity * Price) AS TotalAmount
FROM OrderItems
WHERE OrderID = o.OrderID
GROUP BY OrderID
) oi ON TRUE;In questo esempio, per ogni riga nella tabella Orders, la subquery laterale viene eseguita per calcolare l’importo totale dell’ordine sommando il prodotto di Quantity e Price per gli articoli dell’ordine corrispondenti. Il risultato poi si unisce con la tabella Orders usando la condizione ON TRUE.
Il risultato di questa query sarà:
OrderID | CustomerID | OrderDate | TotalAmount --------+------------+------------+------------ 1 | 101 | 2023-05-01 | 35.00 2 | 102 | 2023-05-02 | 70.00 3 | 101 | 2023-05-03 | 15.00
Considerazioni sulle Prestazioni
Le prestazioni di LATERAL JOIN variano in base al volume di dati. I piccoli dataset tipicamente processano rapidamente in Snowflake. Le tabelle grandi possono richiedere tecniche di ottimizzazione delle query. Un’adeguata indicizzazione migliora l’efficienza dei LATERAL JOIN. Considera visualizzazioni materializzate per subquery usate frequentemente. Il profiler di query di Snowflake aiuta a individuare i colli di bottiglia. I magazzini devono essere dimensionati appropriati per operazioni laterali complesse. Il clustering dei dati migliora le prestazioni con subquery laterali grandi. Evita colonne non necessarie nelle subquery laterali. Applica filtri precocemente per ridurre il sovraccarico di elaborazione. Monitora la cronologia delle query per tracciare i modelli di prestazioni. Testare con volumi di dati rappresentativi è essenziale. Cache i risultati quando possibile per operazioni ripetute.
Sommario e Conclusione
In questo articolo, abbiamo esaminato come funziona CROSS APPLY in SQL Server e il suo equivalente in Snowflake utilizzando lateral join. Abbiamo scoperto che Snowflake non ha CROSS APPLY, ma i lateral join funzionano in modo simile. Permettono alle subquery di utilizzare colonne da tabelle precedenti nella condizione di join.
Abbiamo esaminato esempi di come utilizzare CROSS APPLY in SQL Server e come ottenere lo stesso risultato utilizzando un lateral join in Snowflake. Abbiamo parlato dei piani per entrambi i metodi e fornito un esempio dettagliato con i passaggi di setup iniziale.
Conoscere come utilizzare i lateral join in Snowflake è cruciale per scrivere query efficienti e ottimizzate, specialmente quando si lavora con più tabelle e subquery. Padroneggiando i lateral join, si può garantire che le proprie query siano più flessibili e performanti, sfruttando la capacità di fare riferimento a tabelle precedenti all’interno delle subquery per il recupero complesso dei dati.
Informazioni su DataSunrise
DataSunrise fornisce strumenti user-friendly e flessibili per la sicurezza del database Snowflake, l’audit e la compliance. Le nostre soluzioni aiutano le organizzazioni a proteggere i dati sensibili, monitorare l’attività dei database e garantire la compliance con regolamenti come GDPR, HIPAA e PCI DSS.
Se sei interessato a saperne di più su ciò che DataSunrise ha da offrire, ti invitiamo a richiedere una demo online. Il nostro team di esperti sarà felice di mostrare i nostri prodotti e discutere come possono assisterti nel proteggere e gestire i tuoi database in modo efficace.
