Snowflake Cross Apply

Einführung

Wenn Sie SQL für komplexe Abfragen verwenden, müssen Sie möglicherweise Daten aus verschiedenen Tabellen auf fortgeschrittenere Weise kombinieren. Dies geht über die Verwendung von inneren und äußeren Joins hinaus. Der CROSS APPLY Operator in SQL Server ermöglicht es Ihnen, eine Tabelle mit einer tabellenwertigen Funktion zu joinen und so vielseitige Abfrageoptionen zu schaffen. Wenn Sie jedoch die Snowflake-Cloud-Datenplattform verwenden, haben Sie möglicherweise festgestellt, dass es kein Snowflake-CROSS-APPLY-Analog gibt.

Dieser Artikel erklärt, wie CROSS APPLY funktioniert und wie Sie es in Snowflake mit LATERAL JOIN verwenden. Wir werden auch die Unterschiede zwischen diesen beiden Methoden vergleichen. Am Ende haben Sie ein solides Verständnis davon, wie Sie in Snowflake komplexe Joins ausführen können, die analog zu SQL Servers CROSS APPLY sind. Lassen Sie uns eintauchen!

Was ist CROSS APPLY?

In SQL Server ist CROSS APPLY ein Operator, der es ermöglicht, eine Tabelle mit einer tabellenwertigen Funktion zu joinen. Er wendet die Funktion auf jede Zeile der linken Tabelle an und erzeugt ein Ergebnisset, indem er die Zeilen der linken Tabelle mit den entsprechenden von der Funktion zurückgegebenen Zeilen kombiniert.

Hier ist ein einfaches Beispiel, um zu veranschaulichen, wie es funktioniert:

-- Verwendung von CROSS APPLY
SELECT *
FROM Person p
CROSS APPLY (
SELECT *
FROM Company c
WHERE p.companyid = c.companyId
) Czip;

In diesem Beispiel wird die Unterabfrage für jede Zeile in der Person-Tabelle mithilfe des Operators ausgeführt. Sie gibt die übereinstimmenden Zeilen aus der Company-Tabelle zurück, bei denen die companyId übereinstimmt. Das Ergebnis ist ein Join zwischen Person und Company basierend auf der ‘companyId’-Beziehung.

Die äquivalente Abfrage mit einer Standard-INNER-JOIN-Syntax wäre:

-- Äquivalente Abfrage mit INNER JOIN
SELECT *
FROM Person p
INNER JOIN Company c ON p.companyid = c.companyId;

Beide Abfragen liefern das gleiche Ergebnisset, aber die CROSS APPLY-Version ermöglicht komplexere Join-Bedingungen und kann besonders nützlich sein, wenn man mit tabellenwertigen Funktionen arbeitet.

Snowflake Cross Apply

Snowflake hat kein CROSS APPLY, bietet aber mit dem LATERAL-Schlüsselwort, wenn es mit einem Join verwendet wird, ähnliche Funktionalität. Im ANSI-SQL-Standard ermöglicht ein lateral join die Verwendung von Spalten aus vorherigen Tabellen in der Join-Bedingung. Dies führt zu demselben Ergebnis wie bei der Verwendung von CROSS APPLY.

Hier ist ein Beispiel, wie Sie einen lateral join in Snowflake verwenden können, um dasselbe Ergebnis wie im obigen Beispiel zu erzielen:

-- Verwendung von LATERAL JOIN in Snowflake
SELECT *
FROM Person p
LEFT JOIN LATERAL (
SELECT *
FROM Company c
WHERE p.companyid = c.companyId
) Czip ON TRUE;

In diesem Beispiel wird das Schlüsselwort LATERAL verwendet, um anzuzeigen, dass die folgende Unterabfrage auf Spalten der vorhergehenden Person-Tabelle verweisen kann. Die ON TRUE-Bedingung bindet die laterale Unterabfrage bedingungslos an die Person-Tabelle.

Der lateral join verhält sich ähnlich wie CROSS APPLY, indem die Unterabfrage für jede Zeile der linken Tabelle ausgeführt und die Ergebnisse kombiniert werden. Der Hauptunterschied besteht darin, dass CROSS APPLY einen inneren Join durchführt, während das oben genannte lateral join-Beispiel einen linken Join verwendet. Sie können ein inneres Joinverhalten erzielen, indem Sie einfach LEFT JOIN LATERAL in INNER JOIN LATERAL ändern.

Execution Plans

Werfen wir einen genaueren Blick auf die Ausführungspläne für die CROSS APPLY- und lateral join-Beispiele, um zu verstehen, wie sie sich unterscheiden.

Für das CROSS APPLY-Beispiel in SQL Server:

1. Das System scannt die Person-Tabelle, um alle Zeilen abzurufen.
2. Für jede Zeile in Person führt SQL Server die Unterabfrage nach dem CROSS APPLY aus. Es filtert die Company-Tabelle basierend auf der companyId-Bedingung.
3. Die Abfrage verbindet die resultierenden Zeilen der Unterabfrage mit der entsprechenden Zeile von Person.
4. Die Funktion gibt das endgültige Ergebnisset zurück.

Für das LATERAL JOIN-Beispiel in Snowflake:

1. Das System scannt die Person-Tabelle, um alle Zeilen abzurufen.
2. Die laterale Unterabfrage wird für jede Zeile in Person ausgeführt, wobei die Company-Tabelle basierend auf der companyId-Bedingung gefiltert wird.
3. Die resultierenden Zeilen der lateralen Unterabfrage werden mit der entsprechenden Zeile von Person mithilfe der ON TRUE-Bedingung links verbunden.
4. Die Funktion gibt das endgültige Ergebnisset zurück.

Die Ausführungspläne beider Ansätze sind ähnlich, wobei der Hauptunterschied in der Art des verwendeten Joins liegt (innerer Join für CROSS APPLY und linker Join für das lateral join-Beispiel).

Beispiel mit vorläufiger Einrichtung

Schauen wir uns ein umfassenderes Beispiel an, das einige vorläufige Einrichtungsschritte umfasst. Angenommen, wir haben zwei Tabellen: Orders und OrderItems. Jede Bestellung kann mehrere Bestellpositionen haben, und wir möchten den Gesamtbetrag für jede Bestellung zusammen mit den Bestelldetails abrufen.

Erstellen Sie zuerst die erforderlichen Tabellen:

-- Create Orders table
CREATE TABLE Orders (
OrderID INT,
CustomerID INT,
OrderDate DATE
);
-- Create OrderItems table
CREATE TABLE OrderItems (
OrderID INT,
ItemID INT,
Quantity INT,
Price DECIMAL(10, 2)
);

-- Insert sample data into Orders table
INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES
(1, 101, '2023-05-01'),
(2, 102, '2023-05-02'),
(3, 101, '2023-05-03');
-- Insert sample data into OrderItems table
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);

Nun verwenden wir einen lateral join, um die Bestelldaten zusammen mit dem Gesamtbetrag für jede Bestellung abzurufen:

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 diesem Beispiel wird für jede Zeile in der Orders-Tabelle die laterale Unterabfrage ausgeführt, um den Gesamtbetrag der Bestellung zu berechnen, indem das Produkt von Quantity und Price für die entsprechenden Bestellpositionen addiert wird. Das Ergebnis wird dann mit der Orders-Tabelle unter Verwendung der ON TRUE-Bedingung verbunden.

Die Ausgabe dieser Abfrage wird sein:

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

Leistungsüberlegungen

Die Leistung von LATERAL JOIN variiert je nach Datenvolumen. Kleine Datensätze werden in Snowflake normalerweise schnell verarbeitet. Große Tabellen können Optimierungstechniken erfordern. Eine ordnungsgemäße Indizierung verbessert die Effizienz von LATERAL JOIN. Erwägen Sie materialisierte Ansichten für häufig verwendete Unterabfragen. Snowflakes Abfrageprofiler hilft, Engpässe zu identifizieren. Lagerhäuser sollten für komplexe LATERAL-Operationen entsprechend dimensioniert sein. Datenclustering verbessert die Leistung bei großen lateralen Unterabfragen. Vermeiden Sie unnötige Spalten in lateralen Unterabfragen. Wenden Sie Filter früh an, um die Verarbeitungskosten zu reduzieren. Überwachen Sie die Abfragehistorie, um Leistungsmuster zu erkennen. Tests mit repräsentativen Datenvolumen sind unerlässlich. Zwischenspeichern Sie Ergebnisse, wenn möglich, für wiederholte Vorgänge.

Zusammenfassung und Fazit

In diesem Artikel haben wir untersucht, wie CROSS APPLY in SQL Server funktioniert und wie das Äquivalent in Snowflake mittels lateral joins verwendet werden kann. Wir haben festgestellt, dass Snowflake kein CROSS APPLY hat, aber lateral joins ähnlich funktionieren. Sie ermöglichen es Unterabfragen, Spalten aus vorherigen Tabellen in der Join-Bedingung zu verwenden.

Wir haben Beispiele dafür angesehen, wie CROSS APPLY in SQL Server verwendet wird und wie man dasselbe Ergebnis mithilfe eines lateral joins in Snowflake erzielt. Wir haben die Pläne für beide Methoden besprochen und ein detailliertes Beispiel mit anfänglichen Einrichtungsschritten gegeben.

Das Wissen, wie man lateral joins in Snowflake verwendet, ist entscheidend für das Schreiben effizienter und optimierter Abfragen, insbesondere wenn man mit mehreren Tabellen und Unterabfragen arbeitet. Indem Sie sich lateral joins zu eigen machen, können Sie sicherstellen, dass Ihre Abfragen flexibler und leistungsfähiger sind, da Sie innerhalb von Unterabfragen auf vorhergehende Tabellen verweisen können, um komplexe Datenabfragen durchzuführen.

Über DataSunrise

DataSunrise bietet benutzerfreundliche und flexible Tools für Snowflake-Datenbanksicherheit, Audit und Compliance an. Unsere Lösungen helfen Organisationen, sensible Daten zu schützen, die Datenbankaktivität zu überwachen und die Einhaltung von Vorschriften wie GDPR, HIPAA und PCI DSS sicherzustellen.

Wenn Sie mehr darüber erfahren möchten, was DataSunrise zu bieten hat, laden wir Sie ein, eine Online-Demo anzufordern. Unser Expertenteam zeigt Ihnen gerne unsere Produkte und bespricht, wie sie Ihnen bei der effektiven Sicherung und Verwaltung Ihrer Datenbanken helfen können.