Solution simple pour ne pas afficher les comptes 0 dans MySQL

Question

J'ai créé une requête dans laquelle je compte le nombre de nouveaux encarts par heure. Le problème est que ma requête n'affiche pas les heures sans nouvelles entrées.

Comment puis-je résoudre ce problème facilement ?

SELECT
count(HOUR(delivered))as count,
CASE 
    WHEN HOUR(delivered) = 0 THEN '0'
    WHEN HOUR(delivered) = 1 THEN '1'
    WHEN HOUR(delivered) = 2 THEN '2'
    WHEN HOUR(delivered) = 3 THEN '3'
    WHEN HOUR(delivered) = 4 THEN '4'
    WHEN HOUR(delivered) = 5 THEN '5'
    WHEN HOUR(delivered) = 6 THEN '6'
    WHEN HOUR(delivered) = 7 THEN '7'
    WHEN HOUR(delivered) = 8 THEN '8'
    WHEN HOUR(delivered) = 9 THEN '9'
    WHEN HOUR(delivered) = 10 THEN '10'
    WHEN HOUR(delivered) = 11 THEN '11'
    WHEN HOUR(delivered) = 12 THEN '12'
    WHEN HOUR(delivered) = 13 THEN '13'
    WHEN HOUR(delivered) = 14 THEN '14'
    WHEN HOUR(delivered) = 15 THEN '15'
    WHEN HOUR(delivered) = 16 THEN '16'
    WHEN HOUR(delivered) = 17 THEN '17'
    WHEN HOUR(delivered) = 18 THEN '18'
    WHEN HOUR(delivered) = 19 THEN '19'
    WHEN HOUR(delivered) = 20 THEN '20'
    WHEN HOUR(delivered) = 21 THEN '21'
    WHEN HOUR(delivered) = 22 THEN '22'
    WHEN HOUR(delivered) = 23 THEN '23'

END AS intervals
FROM
    Wardrobe_CloakTable
WHERE
    payingcustomerID = 2
    AND DAY(delivered) = 09
    AND MONTH(delivered) = 09
    AND YEAR(delivered) = 2016
GROUP BY intervals

Answer 1

Vous pouvez commencer par un tableau contenant des valeurs comprises entre 0 et 23, et joindre à gauche votre tableau de données.

Editer : Essayons de changer COUNT(delivered) à SUM(delivered IS NOT NULL) pour voir si nous pouvons obtenir une valeur numérique sur chaque ligne.

Commençons par simplifier votre demande. Cela s'avérera utile dans un instant, car nous devrons l'utiliser pour créer une requête plus importante.

            SELECT SUM(delivered IS NOT NULL) AS count,
                   HOUR(delivered) AS intervals
              FROM Wardrobe_CloakTable
             WHERE payingcustomerID = 2
               AND delivered >= '2016-09-09'
               AND delivered <  '2016-09-09' + INTERVAL 1 DAY
             GROUP BY HOUR(delivered)

(D'ailleurs, un index sur les (payingcustomerID, delivered) rendra cette requête très rapide).

Nous avons maintenant besoin d'un petit tableau virtuel avec les chiffres de 0 à 23. Ce tableau fera l'affaire : il utilise les combinatoires JOIN pour générer des nombres entiers 2 x 2 x 2 x 3. Nous appellerons ce petit tableau seq_0_to_23 . Pourquoi ? parce que ces tableaux de séquence sont intégrés dans le MariaDB de MySQL.

          SELECT A.N + 2*(B.N + 2*(C.N +2*(D.N))) AS seq
            FROM (SELECT 0 AS N UNION SELECT 1) AS A
            JOIN (SELECT 0 AS N UNION SELECT 1) AS B
            JOIN (SELECT 0 AS N UNION SELECT 1) AS C
            JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2) AS D

Enfin, vous utilisez un JOINT DE GAUCHE, comme ceci.

SELECT SUM(delivered IS NOT NULL) AS count,
       seq AS intervals
 FROM (
                SELECT A.N + 2*(B.N + 2*(C.N +2*(D.N))) AS seq
                  FROM (SELECT 0 AS N UNION SELECT 1) AS A
                  JOIN (SELECT 0 AS N UNION SELECT 1) AS B
                  JOIN (SELECT 0 AS N UNION SELECT 1) AS C
                  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2) AS D
       ) seq_0_to_23
  LEFT JOIN Wardrobe_CloakTable ON HOUR(delivered) = seq
 WHERE payingcustomerID = 2
   AND delivered >= '2016-09-09'
   AND delivered <  '2016-09-09' + INTERVAL 1 DAY
 GROUP BY seq
 ORDER BY seq

Il utilise le tableau des séquences comme source de toutes les heures de la journée, puis tire les valeurs correspondantes de vos données réelles.

Si vous utilisez MariaDB, voici à quoi cela ressemble. Comme vous pouvez le constater, la table de séquence intégrée rend la requête beaucoup plus élégante.

SELECT SUM(delivered IS NOT NULL) AS count,
       seq AS intervals
  FROM seq_0_to_23
  LEFT JOIN Wardrobe_CloakTable ON HOUR(delivered) = seq
 WHERE payingcustomerID = 2
   AND delivered >= '2016-09-09'
   AND delivered <  '2016-09-09' + INTERVAL 1 DAY
 GROUP BY seq
 ORDER BY seq

Nous utilisons SELECT SUM(delivered IS NOT NULL) car nous avons besoin de résultats numériques dans la fonction d'agrégation pour chaque ligne. delivered IS NOT NULL renvoie soit 1, soit 0, de sorte que le SUM() devrait faire l'affaire.

Voici une une explication complète de cette technique.