Snowflake Cross Apply

Introduction

Lors de l’utilisation de SQL pour des requêtes complexes, vous pouvez avoir besoin de combiner des données de différentes tables de manière plus avancée. Cela va au-delà de l’utilisation de simples jointures internes et externes. L’opérateur CROSS APPLY dans SQL Server vous permet de joindre une table avec une fonction de valeur de table, créant des options de requêtes polyvalentes. Cependant, si vous utilisez la plate-forme de données cloud Snowflake, vous avez peut-être remarqué qu’il n’y a pas d’analogue à CROSS APPLY dans Snowflake.

Cet article expliquera comment fonctionne CROSS APPLY et comment l’utiliser dans Snowflake avec LATERAL JOIN. Nous comparerons également les différences entre ces deux méthodes. Nous comparerons également les différences entre ces deux approches.

À la fin, vous aurez une compréhension solide de la façon d’effectuer des jointures complexes dans Snowflake qui sont analogues à CROSS APPLY de SQL Server. Plongeons dans le sujet !

Qu’est-ce que CROSS APPLY?

Dans SQL Server, CROSS APPLY est un opérateur qui vous permet de joindre une table avec une fonction de valeur de table. Il applique la fonction à chaque ligne de la table de gauche et produit un ensemble de résultats en combinant les lignes de la table de gauche avec les lignes correspondantes renvoyées par la fonction.

Voici un exemple simple pour illustrer son fonctionnement :

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

Dans cet exemple, la sous-requête est exécutée pour chaque ligne de la table Person en utilisant l’opérateur. Elle renvoie les lignes correspondantes de la table Company où companyId correspond. Le résultat est une jointure entre Person et Company basée sur la relation ‘companyId’.

La requête équivalente utilisant une syntaxe standard INNER JOIN serait :

-- Requête équivalente utilisant INNER JOIN
SELECT *
FROM Person p
INNER JOIN Company c ON p.companyid = c.companyId;

Les deux requêtes renverront le même ensemble de résultats, mais la version CROSS APPLY permet des conditions de jointure plus complexes et peut être particulièrement utile lorsqu’on travaille avec des fonctions de valeur de table.

Snowflake Cross Apply

Snowflake n’a pas CROSS APPLY, mais possède une fonctionnalité similaire avec le mot clé LATERAL lorsqu’il est utilisé avec une jointure. Dans la norme ANSI SQL, une jointure latérale vous permet d’utiliser des colonnes des tables précédentes dans la condition de jointure. Cela donne le même résultat que l’utilisation de CROSS APPLY.

Voici un exemple de la façon dont vous pouvez utiliser une jointure latérale dans Snowflake pour obtenir le même résultat que l’exemple ci-dessus :

-- Utilisation de LATERAL JOIN dans Snowflake
SELECT *
FROM Person p
LEFT JOIN LATERAL (
SELECT *
FROM Company c
WHERE p.companyid = c.companyId
) Czip ON TRUE;

Dans cet exemple, le mot clé LATERAL est utilisé pour indiquer que la sous-requête qui le suit peut référencer des colonnes de la table précédente Person. La condition ON TRUE joint inconditionnellement la sous-requête latérale avec la table Person.

La jointure latérale se comporte de manière similaire à CROSS APPLY, en exécutant la sous-requête pour chaque ligne de la table de gauche et en combinant les résultats. La principale différence est que CROSS APPLY effectue une jointure interne, tandis que l’exemple de jointure latérale ci-dessus utilise une jointure externe gauche. Vous pouvez obtenir un comportement de jointure interne en changeant simplement LEFT JOIN LATERAL en INNER JOIN LATERAL.

Plans d’Exécution

Examinons de plus près les plans d’exécution pour les exemples de CROSS APPLY et de jointure latérale afin de comprendre comment ils diffèrent.

Pour l’exemple de CROSS APPLY dans SQL Server :

Le système parcourt la table Person pour récupérer toutes les lignes.
Pour chaque ligne de Person, SQL Server exécute la sous-requête suivant le CROSS APPLY. Il filtre la table Company en fonction de la condition companyId.
La requête joint les lignes résultantes de la sous-requête avec la ligne correspondante de Person.
La fonction renvoie le résultat final.

Pour l’exemple de LATERAL JOIN dans Snowflake :

Le système parcourt la table Person pour récupérer toutes les lignes.
La sous-requête latérale s’exécute pour chaque ligne de Person, filtrant la table Company en fonction de la condition companyId.
Les lignes résultantes de la sous-requête latérale sont jointes à gauche avec la ligne correspondante de Person en utilisant la condition ON TRUE.
La fonction renvoie le résultat final.

Les plans d’exécution pour les deux approches sont similaires, avec la principale différence étant le type de jointure utilisé (jointure interne pour CROSS APPLY et jointure externe gauche pour l’exemple de jointure latérale).

Exemple avec Préparation Préalable

Regardons un exemple plus complet qui inclut quelques étapes de préparation préalable. Supposons que nous ayons deux tables : Orders et OrderItems. Chaque commande peut avoir plusieurs articles de commande, et nous voulons récupérer le montant total pour chaque commande ainsi que les détails de la commande.

Tout d’abord, créons les tables nécessaires :

-- Création de la table Orders
CREATE TABLE Orders (
OrderID INT,
CustomerID INT,
OrderDate DATE
);
-- Création de la table OrderItems
CREATE TABLE OrderItems (
OrderID INT,
ItemID INT,
Quantity INT,
Prix DECIMAL(10, 2)
);

-- Insertion de données d'exemple dans la table Orders
INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES
(1, 101, '2023-05-01'),
(2, 102, '2023-05-02'),
(3, 101, '2023-05-03');
-- Insertion de données d'exemple dans la table OrderItems
INSERT INTO OrderItems (OrderID, ItemID, Quantity, Prix)
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);

Maintenant, utilisons une jointure latérale pour récupérer les détails de la commande ainsi que le montant total pour chaque commande :

SELECT
o.OrderID,
o.CustomerID,
o.OrderDate,
oi.TotalAmount
FROM Orders o
LEFT JOIN LATERAL (
SELECT
OrderID,
SUM(Quantity * Prix) AS TotalAmount
FROM OrderItems
WHERE OrderID = o.OrderID
GROUP BY OrderID
) oi ON TRUE;

Dans cet exemple, pour chaque ligne de la table Orders, la sous-requête latérale est exécutée pour calculer le montant total de la commande en additionnant le produit de Quantité et Prix pour les articles de commande correspondants. Le résultat est ensuite joint à la table Orders en utilisant la condition ON TRUE.

La sortie de cette requête sera :

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

Considérations de Performance

La performance de LATERAL JOIN varie en fonction du volume de données. Les petits ensembles de données se traitent rapidement dans Snowflake. Les grandes tables peuvent nécessiter des techniques d’optimisation des requêtes. Un indexage adéquat améliore l’efficacité des LATERAL JOIN. Envisagez l’utilisation de vues matérialisées pour les sous-requêtes fréquemment utilisées. Le profileur de requête de Snowflake aide à identifier les goulots d’étranglement. Les entrepôts doivent être dimensionnés de manière appropriée pour les opérations complexes de type LATERAL. Le clustering des données améliore les performances avec des sous-requêtes latérales étendues. Évitez les colonnes inutiles dans les sous-requêtes latérales. Appliquez des filtres en amont pour réduire les coûts de traitement. Surveillez l’historique des requêtes pour suivre les schémas de performance. Tester avec des volumes de données représentatifs est essentiel. Mettez en cache les résultats lorsque cela est possible pour les opérations répétées.

Résumé et Conclusion

Dans cet article, nous avons examiné le fonctionnement de CROSS APPLY dans SQL Server et son équivalent dans Snowflake en utilisant des jointures latérales. Nous avons constaté que Snowflake n’a pas de CROSS APPLY, mais que les jointures latérales fonctionnent de manière similaire. Elles permettent aux sous-requêtes d’utiliser des colonnes des tables précédentes dans la condition de jointure.

Nous avons examiné des exemples sur la façon d’utiliser CROSS APPLY dans SQL Server et comment obtenir le même résultat en utilisant une jointure latérale dans Snowflake. Nous avons parlé des plans pour les deux méthodes et donné un exemple détaillé avec des étapes de préparation initiales.

Savoir utiliser les jointures latérales dans Snowflake est crucial pour écrire des requêtes efficaces et optimisées, notamment lorsque vous traitez plusieurs tables et sous-requêtes. En maîtrisant les jointures latérales, vous pouvez vous assurer que vos requêtes sont plus flexibles et performantes, en tirant parti de la capacité de référencer les tables précédentes dans les sous-requêtes pour une récupération de données complexe.

À propos de DataSunrise

DataSunrise fournit des outils conviviaux et flexibles pour la sécurité, l’audit et la conformité des bases de données Snowflake. Nos solutions aident les organisations à protéger les données sensibles, surveiller l’activité des bases de données et assurer la conformité aux réglementations telles que GDPR, HIPAA, et PCI DSS.

Si vous souhaitez en savoir plus sur ce que DataSunrise a à offrir, nous vous invitons à demander une démo en ligne. Notre équipe d’experts sera ravie de présenter nos produits et discuter de la manière dont ils peuvent vous aider à sécuriser et gérer efficacement vos bases de données.