Patrons SQL utiles : Compression conditionnelle avec CASE

Lorsque vous commencez à coder en SQL, vous allez utiliser certaines déclarations et techniques de manière répétée. Nous les appelons les "modèles SQL". Dans cette série, nous examinerons les motifs SQL les plus courants et nous verrons comment les utiliser.

Précédemment, nous avons examiné Le modèle SQL des NULL correspondants. Ceci est important lorsque vous comparez des colonnes contenant des valeurs NULL. Aujourd'hui, nous allons examiner une autre pratique SQL : la compression conditionnelle avec l'opérateur CASE.

Qu'est-ce que la synthèse conditionnelle ?

Lorsque vous utilisez des fonctions d'agrégation pour créer des requêtes de rapport, vous serez souvent amené à utiliser la compression conditionnelle avec l'opérateur CASE. Rappelez-vous que CASE renvoie une valeur basée sur des critères définis. (Pour en savoir plus sur l'expression CASE, voir cet article et celui-ci.) Lorsque vous effectuez une compression avec CASE, vous additionnez simplement (en les comprimant) les valeurs qui répondent à l'expression CASE.

De toute évidence, vous allez utiliser la partie SUM de la requête pour agréger ces valeurs. Je sais que cela semble compliqué, mais ce n'est pas le cas. Prenons un exemple simple pour l'expliquer.

Nous allons commencer par considérer une table de base transactions qui contient des données transactionnelles d'une petite entreprise.

La table transactions comporte les colonnes suivantes :

• id - Un identifiant unique pour chaque transaction
• datetime - L'horodatage de la transaction
• customer - L'ID du client
• creditcard - L'identifiant du type de carte de crédit utilisé
• amount - Le montant de la transaction, en dollars
• account - Le numéro de compte du client
• type - le type de transaction.

Voici les données que nous pourrions trouver dans un tableau comme celui-ci :

Nous voulons trouver la somme des montants des transactions et le nombre de transactions effectuées avant le 1er avril. De plus, nous voulons les répertorier par client individuel. Nous pouvons y parvenir en utilisant la requête suivante :

	SELECT
 customer,
 SUM(
 CASE WHEN datetime >= TIMESTAMP '2017-04-01'
 THEN amount
 ELSE 0
 END
 )        AS sum_amount_after,
 SUM(CASE WHEN datetime >= TIMESTAMP '2017-04-01'
 THEN 1
 ELSE 0
 END) AS transaction_count_after,
 SUM(CASE WHEN datetime < TIMESTAMP '2017-04-01'
 THEN amount
 ELSE 0
 END) AS sum_amount_prior,
 SUM(CASE WHEN datetime < TIMESTAMP '2017-04-01'
 THEN 1
 ELSE 0
 END) AS transaction_count_prior
 FROM transactions
 WHERE datetime BETWEEN TIMESTAMP '2017-01-01'  AND '2017-05-01'
 GROUP BY customer

Le résultat de cette requête est :

Attendez ! Comment ça a marché ?

Cette longue requête peut prêter à confusion, aussi allons-nous la décomposer un peu. Nous nous concentrerons d'abord sur la partie traitant de la date limite du 1er avril (2017-04-01).

👾 [NOUVEAU] Que se passe-t-il lorsque vous combinez #CASE avec les instructions de modification des données de #SQL? Découvrez-le ! !! ➽ https://t.co/7IuBoDT85z... pic.twitter.com/wNiDiv5hSa

- Vertabelo (@Vertabelo) 6 juin 2017

Ci-dessous, nous examinons le montant de la transaction pour le client " 1 ". Tous les montants de transaction qui ont été postés avant le 01.04.2017 seront mis à " 0 ". Nous allons nommer cette colonne amount_after.

SELECT
 customer,
 datetime,
 CASE WHEN datetime >= TIMESTAMP '2017-04-01'
  THEN amount
 ELSE 0
 END
  AS amount_after,
 amount
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
      AND customer = '1'
ORDER BY datetime;

Sinon, nous pouvons remplacer les zéros affichés dans les résultats par un NULL dans l'instruction ELSE:

SELECT
 customer,
 datetime,
 CASE WHEN datetime >= TIMESTAMP '2017-04-01'
  THEN amount
 ELSE null
 END
  AS amount_after,
 amount
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
      AND customer = '1'
ORDER BY datetime;

Maintenant, si nous résumons ces colonnes, nous obtiendrons :

1. Le total de toutes les transactions pour sum(amount).
2. La somme de toutes les transactions qui ont été enregistrées après le 01.04. Toutes les transactions enregistrées avant le 01.04 sont mises à zéro (ou NULL) pour sum(amount_after).

Si nous voulons compter combien de transactions ont été enregistrées après le 01.04, nous pouvons modifier la requête et créer une déclaration COUNT qui utilise la même CASE avec NULL dans la ELSE.

SELECT
  customer,
  count(CASE WHEN datetime >= TIMESTAMP '2017-04-01'
    THEN amount
        ELSE NULL
        END)
    AS count_after
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
GROUP BY customer
ORDER BY customer;

Remarque : cette requête est très rapide, car le SGBDR n'a besoin d'accéder qu'à une seule table. Construire des requêtes d'agrégation qui n'utilisent qu'une seule table est un bon moyen d'obtenir des résultats rapidement.

Essayez d'utiliser CASE avec un zéro dans l'instruction COUNT. Quel est le résultat et pourquoi ? Dites-le-nous dans la section des commentaires ci-dessous.