Un guide de SQL avancé pour la manipulation des données complexes

Les fonctions de fenêtre permettent des calculs entre les lignes associées sans les effondrer, permettant des classements, des totaux d'exécution et des comparaisons dans les partitions, en utilisant la syntaxe comme fonction () sur (partition par ordre de colonne par colonne). 2. Les expressions de table courantes (CTES) améliorent la lisibilité de la requête en créant des ensembles de résultats temporaires avec la clause avec la clause, et les CTES récursifs gèrent les données hiérarchiques via l'ancrage et les membres récursifs combinés avec Union All, garantissant la terminaison. 3. Pivoting transforme les lignes en colonnes (par exemple, la conversion des quarts en colonnes distinctes à l'aide de pivot), tandis que Univot inverse ce processus, bien que la syntaxe varie à l'autre de données comme SQL Server, Oracle et PostgreSQL. 4. Les jointures avancées incluent les jointures non équipées en utilisant des conditions comme entre, les auto-joins pour comparer les lignes dans le même tableau (par exemple, les employés contre les gestionnaires), et les opérations définies telles que l'Union, l'intersection et sauf pour combiner ou filtrer les ensembles de résultats, avec un syndicat, tous étant plus rapides lorsque les doublons sont acceptables. 5. La logique conditionnelle avec le cas permet une affectation de valeur dynamique (par exemple, la catégorisation des tailles d'ordre) et peut être combinée avec une agrégation pour la tabulation croisée (par exemple, les statuts de comptage). 6. Gestion des doublons implique d'utiliser ROW_NUMBER () dans un CTE pour les identifier et les supprimer en fonction des colonnes clés et des derniers horodatages, tandis que la qualité des données est améliorée en standardisant du texte avec des fonctions telles que TRIM et inférieur, et gérer Nulls avec Coalesce (). Advanced SQL permet aux utilisateurs d'effectuer des manipulations de données sophistiquées en tirant parti de ces techniques pour des informations plus profondes, des données plus propres et des transformations plus efficaces, permettant finalement les requêtes plus puissantes et maintenables.

Lorsque vous travaillez avec des ensembles de données importants ou complexes, les requêtes SQL de base ne sont souvent pas à court. Pour extraire des informations significatives, nettoyer les données en désordre ou transformer des informations sur plusieurs sources, vous avez besoin de techniques SQL avancées. Ce guide plonge dans de puissantes fonctionnalités SQL qui permettent une manipulation sophistiquée de données - parfaite pour les analystes, les ingénieurs de données et les développeurs traitant des défis de données du monde réel.

1. Fonctions de la fenêtre: analyser les données en contexte

Les fonctions de fenêtre vous permettent d'effectuer des calculs à travers un ensemble de lignes associées sans les s'effondrer en une seule ligne de sortie (contrairement à un groupe par). Ils sont essentiels pour le classement, les totaux de course, les moyennes mobiles et les comparaisons dans les partitions.

Cas d'utilisation courants:

• Classez les employés par salaire au sein de chaque département
• Calculez un total de ventes en cours d'exécution au fil du temps
• Comparez les valeurs individuelles aux moyennes de groupe

Syntaxe:

 Fonction () sur (
  [Partition par colonne]
  [Ordre par colonne]
  [Clause de trame]
)

Exemple: exécution du total des ventes par mois

 SÉLECTIONNER 
  order_date,
  ventes,
  Sum (ventes) over (ordonnance by order_date) en tant que running_total
De Sales_data;

Exemple: classez les produits par revenus dans chaque catégorie

 SÉLECTIONNER 
  catégorie,
  Product_name,
  revenu,
  Rank () over (partition by category ordonnance by revenue desc) as rank_in_category
Des produits;

Astuce: utilisez ROW_NUMBER() pour un classement unique, RANK() pour les lacunes sur les liens, et DENSE_RANK() pour aucune lacune.

2. Expressions de table communes (CTES) et requêtes récursives

Les CTES rendent les requêtes complexes plus lisibles et modulaires. Ce sont des ensembles de résultats temporaires définis WITH et peuvent être référencés plus tard dans la requête principale.

CTE de base:

 Avec high_value_orders comme (
  Sélectionnez * parmi les commandes où le montant> 1000
)
Sélectionnez Customer_ID, Count (*) 
De high_value_orders 
Groupe par client_id;

Les CTES récursifs sont puissants pour les données hiérarchiques comme les graphiques organisationnels ou les factures.

Exemple: hiérarchie des employés

 Avec un org_tree récursif comme (
  - Cas de base: gestionnaires de niveau supérieur (pas de gestionnaire)
  Sélectionnez Employee_ID, nom, manager_id, 1 en tant que niveau
  Des employés
  Où manager_id est nul

  Union All

  - Cas récursif: employés sous quelqu'un
  Sélectionnez e.employee_id, e.name, e.manager_id, ot.level 1
  Des employés e
  Inner Join org_tree ot sur e.manager_id = ot.employee_id
)
SELECT * FROM ORG_TREE;

Remarque: les CTES récursifs nécessitent un membre d'ancrage et un membre récursif, combiné avec UNION ALL . Assurez-vous toujours que la récursivité se termine.

3. Données pivotantes et non-diffamées

Parfois, les données sont stockées dans un long format mais doivent être présentées largement (ou vice versa). SQL fournit des moyens de pivoter (tourner les lignes vers des colonnes) et Univot.

Exemple pivotant: les ventes par trimestre supposent un tableau quarterly_sales avec colonnes: year , quarter , revenue .

 SÉLECTIONNER *
De Quarterly_Sales
Pivoter (
  Somme (revenus)
  Pour le quart dans ('Q1', 'Q2', 'Q3', 'Q4')
) Comme pvt;

Cela crée des colonnes: year , Q1 , Q2 , Q3 , Q4 .

UNPIVOTTING (RENDOCT): Convertir les colonnes en lignes.

 Année sélectionnée, trimestre, revenus
DEPUIS (
  SELECT année, Q1, Q2, Q3, Q4 de Sales_Summary
) Comme src
Univot (
  Revenus pour le trimestre en (Q1, Q2, Q3, Q4)
) Comme impvt;

Remarque: la syntaxe varie selon la base de données. PostgreSQL utilise une agrégation conditionnelle pour le pivot; SQL Server et Oracle Support PIVOT / UNPIVOT .

4. Jointes avancées et opérations

Au-delà des jointures de base intérieures et gauche, les modèles de jointure avancés aident à résoudre des problèmes de correspondance délicats.

un. Non-Equi se joint à des conditions autres que l'égalité.

 Sélectionnez E.Name, S.Salary_Range
Des employés e
Rejoignez Salary_bands S
Sur E.Salary entre S.Min_salary et S.max_salary;

né Les auto-joins comparent les lignes dans le même tableau.

 - Trouvez des employés gagnant plus que leurs managers
Sélectionnez E.Name comme employé, E.Salary, m.name en tant que directeur, M.Salary en tant que mgr_salary
Des employés e
Rejoignez les employés M sur e.manager_id = M.Employee_id
Où e.salary> M.Salary;

c. Opérations de set: Union, intersecte, sauf combiner ou comparer les ensembles de résultats.

 - les clients qui ont acheté au premier trimestre mais pas au T2
Sélectionnez Customer_ID dans Q1_Sales
SAUF
Sélectionnez Customer_ID dans Q2_Sales;

CONSEIL: Utilisez UNION ALL lorsque les doublons sont acceptables - c'est plus vite que UNION .

5. Logique conditionnelle avec cas et agrégation

Les expressions CASE apportent une logique de type programmation dans SQL, permettant des valeurs de colonne dynamique.

Exemple: catégoriser les tailles de commande

 SÉLECTIONNER 
  order_id,
  montant,
  CAS 
    Quand le montant> 1000 puis «grand»
    Lorsque le montant entre 500 et 1000, alors «moyen»
    Sinon «petit»
  Se terminer comme Order_Size
Des ordres;

Combinez avec agrégation pour la réduction de la tabulation:

 SÉLECTIONNER
  Compter (*) comme total,
  Compter (cas quand status = 'expédié' puis 1 extrémité) comme expédié,
  Compter (cas quand status = «en attente» puis 1 fin) comme en attente
Des ordres;

6. Gestion des doublons et qualité des données

Même avec des schémas propres, les données réelles ont des doublons et des incohérences.

Supprimer les doublons à l'aide de row_number ()

 Avec classé comme (
  SÉLECTIONNER *,
    Row_number () sur (
      Partition par COL1, Col2 Order by Updated_at DESC
    ) Comme RN
  De Raw_Table
)
Supprimer de classé où RN> 1;

Remarque: Certaines bases de données ne permettent pas DELETE directement des CTES: utilisez des sous-requêtes ou des tables temporaires.

Standardiser les données de texte

 Mettre à jour les clients
Définir Email = inférieur (TRIM (EMAIL)),
    name = initcap (trim (name));

Utilisez COALESCE() pour gérer Nulls:

 Sélectionnez Coalesce (Phone_Home, Phone_Mobile, «pas de numéro») comme contact_number
Des clients;

Réflexions finales

Advanced SQL ne concerne pas seulement la rédaction de requêtes plus longues - il s'agit de penser dans les ensembles, de comprendre les relations de données et d'appliquer le bon outil pour le travail. Que vous vous classions, pivotez, traversiez des hiérarchies ou des données de nettoyage, ces techniques vous donnent un contrôle fin sur votre flux de travail de manipulation de données.

Pratiquez-les avec de vrais ensembles de données, et bientôt vous rédigerez des requêtes non seulement puissantes mais aussi claires et maintenables.

Fondamentalement, une fois que vous passez au-delà de la sélection et où, la véritable puissance de SQL s'ouvre.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!