Cet article explique en profondeur comment utiliser les instructions de cas pour effectuer une agrégation conditionnelle dans MySQL pour atteindre la sommation conditionnelle et le comptage de champs spécifiques. Grace à un cas de système d'abonnement pratique, il montre comment calculer dynamiquement la durée totale et le nombre d'événements en fonction de l'état des enregistrements (tels que "end" et "annuler"), surmontant ainsi les limites des fonctions de somme traditionnelles qui ne peuvent pas répondre aux besoins d'une agrégation conditionnelle complexe. Le tutoriel analyse en détail l'application des instructions de cas en détail et souligne l'importance de la fusion lorsqu'il s'agit des valeurs nulles possibles de la jointure gauche.

Master l'agrégation conditionnelle dans MySQL: la combinaison de déclarations de somme et de cas

Dans les requêtes de base de données, nous devons souvent agréger les données basées sur des conditions spécifiques, telles que le calcul de la somme ou du nombre d'enregistrements qui répondent à une certaine condition. La fonction SUM () ou COUNT () standard ne peut agréger que tous les enregistrements qui remplissent les conditions de la clause WHERE, mais si nous devons effectuer plusieurs agrégations en fonction de différentes conditions dans la même requête, ou n'incluons que des valeurs numériques qui remplissent des conditions spécifiques pendant l'agrégation, ce qui nécessite une agrégation conditionnelle de compétence plus avancée. Dans MySQL, la combinaison des instructions de cas et des fonctions agrégées est un outil puissant pour atteindre cet objectif.

Exemple de scénario: Statistiques du temps de réservation des employés

Supposons que nous ayons un système de réservation qui contient deux tables: le personnel et la réservation.

Structure de la table du personnel:

Personnel	Prénom	Nom de famille
1	John	Biche
2	Marie	Biche

Structure de table de réservation:

Réservant	Personnel	Statut	durée
1	1	annulé	20
2	1	terminé	20
3	1	terminé	10
4	2	annulé	30
5	1	confirmé	40

Nos objectifs sont:

1. Calculez la durée totale de réservation pour le statut "terminé" de chaque employé.
2. Dans le même temps, comptez le nombre de réservations pour chaque employé avec le statut "annulé".

Limites des méthodes traditionnelles

Si nous n'utilisons qu'une somme simple (réservation.Duration), nous obtiendrons la durée totale dans tous les états, et nous ne pouvons pas distinguer les états spécifiques tels que "fin" ou "annuler". Par exemple, la requête suivante calcule la durée totale de tous les états:

 SéLECTIONNER
    S.StAFFID,
    s.first_name,
    S.Last_name,
    Sum (b.Duration) comme total duration
DEPUIS
    personnel
Rejoindre
    réservation B sur S.Staffid = B.Staffid
Se regrouper
    S.Staffid, S.First_name, S.Last_name;

Cela renverra la durée totale de John Doe à (20 20 10 40) = 90 au lieu de (20 10) = 30 uniquement à l'état "fin".

Instruction de cas d'utilisation pour implémenter l'agrégation conditionnelle

L'instruction de cas nous permet de définir la logique conditionnelle à l'intérieur de la fonction SUM (). Lorsque la condition est satisfaite, nous incluons la valeur correspondante; Sinon, nous fournissons une valeur qui n'affecte pas la somme (généralement 0).

Solution SQL Query:

 SéLECTIONNER
    S.StAFFID,
    s.first_name,
    S.Last_name,
    - Calculez la durée totale de l'abonnement de la somme d'état "End" (cas
        Quand b.status = 'terminé', alors b.Duration
        D'autre 0
    Fin) en tant que FinDbookingsDuration,
    - Statistiques Le nombre de réservations dans le statut "annulé" fusionnez (sum (b.status = 'annulé'), 0) comme casse
DEPUIS
    personnel
Rejoindre
    réservation B sur S.Staffid = B.Staffid
Se regrouper
    S.Staffid, S.First_name, S.Last_name
Commander
    S.StaFide;

Exemple de résultat de la requête:

Personnel	Prénom	Nom de famille	FinDBookingsDuration	Couvre à casse-casse
1	John	Biche	30	1
2	Marie	Biche	0	1

Solution détaillée

1. Sélectionner la clause :

   • S.Staffid, S.First_name, S.Last_name: Informations de base sur la sélection des employés.
   • Sum (cas lorsque b.status = 'a terminé' puis b.duration else 0 fin) comme fin bookingsDuration: c'est la clé de la mise en ?uvre de la sommation conditionnelle.
     • Cas lorsque B.Status = 'terminé' alors B.Duration else 0 End: Pour chaque enregistrement de réservation, si son statut est ?terminé?, sa valeur de durée est prise; Sinon, 0 est pris.
     • Sum (...): résume toutes les valeurs renvoyées par l'énoncé de cas. De cette fa?on, seule la durée de l'état "fin" sera accumulée, et la durée dans d'autres états sera remplacée par 0, ce qui n'affectera pas la somme.
   • Coalesce (sum (B.Status = 'Annuled'), 0) en tant que Livre-BookingsCount: Il s'agit d'une compétence pour mettre en ?uvre le comptage conditionnel.
     • B.Status = 'Annuled': Dans MySQL, une expression booléenne est traitée comme 1 (true) ou 0 (false) dans un contexte numérique. Ainsi, lorsque l'état est ?annulé?, le résultat d'expression est 1; Sinon, c'est 0.
     • Sum (...): résume ces 1 et 0s, et le résultat est le nombre d'enregistrements dans l'état ?annulé?.
     • Coalesce (..., 0): L'opération de jointure de gauche peut faire en sorte que certains employés ne disposent pas de dossiers correspondants dans la table de réservation. Dans ce cas, la fonction sum () renvoie null. La fonction de coalesce est utilisée pour remplacer la valeur nul par 0 pour assurer la précision et la lisibilité du résultat.

2. Des clauses de joints et de gauche :

   • Le personnel de gauche à gauche de jointure B Werming B sur S.Staffid = B.StAFFID: Utilisez la gauche à gauche pour vous assurer que certains employés appara?tront toujours dans les résultats même s'ils n'ont pas de dossiers de réservation. Si vous utilisez Inner Join, seuls les employés avec des dossiers de réservation seront affichés.

3. Groupe par clause :

   • Groupe par S.Staffid, S.First_name, S.Last_name: Groupes par ID et nom de l'employé pour calculer les résultats agrégés indépendants pour chaque employé.

Notes et meilleures pratiques

• Flexibilité des instructions de cas : L'instruction de cas est très flexible et peut contenir plusieurs lorsque ... puis des branches et une branche Else facultative pour une logique conditionnelle plus complexe.
• Importance de la clause d'autre : En somme (cas ...), Else 0 est une pratique standard car elle n'affecte pas la somme. Si la clause ELSE est omise, les enregistrements qui ne remplissent pas les conditions renvoient NULL et la fonction SUM () ignore la valeur nul, ce qui peut entra?ner des résultats inattendus (par exemple, si tous les enregistrements ne remplissent pas les conditions, la somme peut être nul au lieu de 0).
• Traitement de fusion NULL : Lors de l'agrégation à l'aide de la jointure de gauche, la fonction d'agrégation (telle que SUM, COUNT) peut retourner null si les enregistrements dans la table de gauche n'ont aucune correspondance dans la table droite. Ces valeurs nulles peuvent être converties en 0 en utilisant Coalesce (agrégate_function_result, 0) pour rendre le résultat davantage comme prévu.
• Considérations de performance : les instructions de cas sont exécutées à l'intérieur de la fonction agrégée et sont généralement plus efficaces. Cependant, pour des ensembles de données très importants, il est crucial de s'assurer que la condition de jointure et où la clause (si elle est présente) peut utiliser efficacement l'index.

Résumer

En incorporant des instructions de cas dans des fonctions agrégées telles que SUM (), nous pouvons implémenter une fonction d'agrégation conditionnelle puissante pour calculer plusieurs statistiques qui remplissent différentes conditions dans une seule requête. Cette méthode améliore non seulement l'efficacité de la requête, mais rend également le code SQL plus concis et plus facile à entretenir. La ma?trise de cette technique améliorera considérablement votre capacité à gérer les taches complexes d'analyse des données dans MySQL.

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!