Séb.

Ho ho ! Au hasard de mes tribulations sur le forum SQL, je viens de découvrir une série d'exercices proposée par SQLPro.

SQL étant mon 2nd amour, je ne peux résister à l'envie d'en réaliser quelques uns.

Le 1er exercice intitulé un dans dix est assez classique. Des lignes doivent être sélectionnées selon différents critères, la difficulté étant que ces données sont stockées en colonnes plutôt qu'en lignes. Bien souvent une mauvaise idée, que l'on retrouve sur les forums

Une table T_CELKO_TEN_IN_ON_TIO, 1 colonne TIO_ID pour l'ID, 10 autres colonnes TIO_[1..10] pour les valeurs numériques.
A. Sortir les lignes ayant en tout et pour tout 9 valeurs à zéro
B. Sortir les lignes ayant 9 valeurs à zéro et 1 valeur à un

Bien sûr, l'objectif est d'éviter une requête telle que la suivante. Le résultat est correct, mais elle est indigeste, les risques d'erreur à la rédaction sont importants et elle est difficilement évolutive.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT ALL *
FROM T_CELKO_TEN_IN_ON_TIO
WHERE FALSE
    OR (TIO_1 <> 0 AND (TIO_2, TIO_3, TIO_4, TIO_5, TIO_6, TIO_7, TIO_8, TIO_9, TIO_10) = (0, 0, 0, 0, 0, 0, 0, 0, 0))
    OR (TIO_2 <> 0 AND (TIO_1, TIO_3, TIO_4, TIO_5, TIO_6, TIO_7, TIO_8, TIO_9, TIO_10) = (0, 0, 0, 0, 0, 0, 0, 0, 0))
    OR (TIO_3 <> 0 AND (TIO_2, TIO_1, TIO_4, TIO_5, TIO_6, TIO_7, TIO_8, TIO_9, TIO_10) = (0, 0, 0, 0, 0, 0, 0, 0, 0))
    OR (TIO_4 <> 0 AND (TIO_2, TIO_3, TIO_1, TIO_5, TIO_6, TIO_7, TIO_8, TIO_9, TIO_10) = (0, 0, 0, 0, 0, 0, 0, 0, 0))
    OR (TIO_5 <> 0 AND (TIO_2, TIO_3, TIO_4, TIO_1, TIO_6, TIO_7, TIO_8, TIO_9, TIO_10) = (0, 0, 0, 0, 0, 0, 0, 0, 0))
    OR (TIO_6 <> 0 AND (TIO_2, TIO_3, TIO_4, TIO_5, TIO_1, TIO_7, TIO_8, TIO_9, TIO_10) = (0, 0, 0, 0, 0, 0, 0, 0, 0))
    OR (TIO_7 <> 0 AND (TIO_2, TIO_3, TIO_4, TIO_5, TIO_6, TIO_1, TIO_8, TIO_9, TIO_10) = (0, 0, 0, 0, 0, 0, 0, 0, 0))
    OR (TIO_8 <> 0 AND (TIO_2, TIO_3, TIO_4, TIO_5, TIO_6, TIO_7, TIO_1, TIO_9, TIO_10) = (0, 0, 0, 0, 0, 0, 0, 0, 0))
    OR (TIO_9 <> 0 AND (TIO_2, TIO_3, TIO_4, TIO_5, TIO_6, TIO_7, TIO_8, TIO_1, TIO_10) = (0, 0, 0, 0, 0, 0, 0, 0, 0))
    OR (TIO_10 <> 0 AND (TIO_2, TIO_3, TIO_4, TIO_5, TIO_6, TIO_7, TIO_8, TIO_9, TIO_1) = (0, 0, 0, 0, 0, 0, 0, 0, 0))
;

Le principe est le même pour les deux problèmes de cet exercice :

• Pivoter les colonnes en lignes afin de bénéficier des agrégats et ainsi identifier les lignes répondant aux critères. Ça nous fera une bonne tripotée de UNION.
• Filtrer selon COUNT(), sachant qu'un COUNT() sur le marqueur NULL n'est pas comptabilisé dans le décompte.

A. Sortir les lignes ayant en tout et pour tout 9 valeurs à zéro -- Ma solution

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
WITH dataset (id, value) AS ( -- Pivot du jeu de données fourni
    SELECT ALL TIO_ID, TIO_1 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_2 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_3 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_4 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_5 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_6 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_7 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_8 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_9 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_10 FROM T_CELKO_TEN_IN_ON_TIO
)
SELECT ALL *
FROM T_CELKO_TEN_IN_ON_TIO
WHERE TIO_ID IN (
    -- Recherche des lignes ayant précisément 9 zéros
    SELECT ALL id
    FROM dataset
    GROUP BY id
    HAVING COUNT(CASE WHEN value = 0 THEN TRUE ELSE NULL END) = 9
);

B. Sortir les lignes ayant 9 valeurs à zéro et 1 valeur à un -- Ma solution

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
WITH dataset (id, value) AS ( -- Pivot du jeu de données fourni
    SELECT ALL TIO_ID, TIO_1 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_2 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_3 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_4 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_5 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_6 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_7 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_8 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_9 FROM T_CELKO_TEN_IN_ON_TIO
    UNION ALL
    SELECT ALL TIO_ID, TIO_10 FROM T_CELKO_TEN_IN_ON_TIO
)
SELECT ALL *
FROM T_CELKO_TEN_IN_ON_TIO
WHERE TIO_ID IN (
    SELECT ALL id
    FROM dataset
    GROUP BY id
    HAVING TRUE
        -- Recherche des lignes ayant précisément 9 zéros et 1 un
        AND COUNT(CASE WHEN value = 0 THEN TRUE ELSE NULL END) = 9
        AND COUNT(CASE WHEN value = 1 THEN TRUE ELSE NULL END) = 1
);