Trier les données de la base de données MYSQL par heure

Question

J'ai des utilisateurs dans la base de données que je veux trier par heure et afficher le nombre d'utilisateurs enregistrés à cette heure.

select
  date_format(create_time, '%Y-%m-%d %h%p') as date,
  count(id) as 'Number of registrations'
from users
group by 1
order by 1 desc
;

Le code ci-dessus fonctionnera ; cependant, ce que j'essaie de faire, c'est d'afficher des 0 pour les heures où il n'y a pas d'enregistrement d'utilisateur. Par exemple, s'il n'y a pas eu d'enregistrement à 17 heures, le code sautera la ligne de 17 heures, ce qui est logique. Existe-t-il un moyen de réaliser ce que j'essaie de faire ?

Answer 1

Vous pourriez utiliser une requête comme celle-ci :

select
  date_format(t.d + INTERVAL t.h HOUR, '%Y-%m-%d %h%p') as date,
  count(id) as 'Number of registrations'
from (
  SELECT *
  FROM
    (SELECT DISTINCT DATE(create_time) d FROM users) dates,
    (SELECT 0 h UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
    UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
    UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
    UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) hours
  ) t LEFT JOIN users
  ON DATE(users.create_time)=t.d AND HOUR(users.create_time)=t.h
group by t.d, t.h
order by t.d, t.h

Veuillez voir le violon aquí .

Answer 2

Vous devez générer toutes les combinaisons possibles de jours et d'heures.

En supposant que vous ayez au moins un enregistrement pour chaque jour et un enregistrement pour chaque heure, vous pouvez le faire :

select concat(d.theday, ' ', h.thehour) as date,
       count(id) as 'Number of registrations'
from (select distinct date_format(create_time, '%Y-%m-%d') as theday from users
     ) d cross join
     (select distinct date_format(create_time, '%h%p') as thehour from users
     ) h left outer join
     users u
     on date_format(u.create_time, '%Y-%m-%d %h%p) = concat(d.theday, ' ', h.thehour)
group by concat(d.theday, ' ', h.thehour)
order by 1 desc;