J'ai une table avec une colonne visit_date qui est un objet datetime au format YYYY-MM-DD HH:MI:SS qui ressemble à ce qui suit:

visit_date         |visit_id
-------------------|-----
2010-11-01 00:02:00|92314
2010-11-01 23:05:21|23498
2010-11-01 12:42:31|12343
2010-11-02 05:13:21|79881
2010-11-02 14:35:15|22134
2010-11-02 16:12:23|12348
2010-11-03 01:22:44|12384
2010-11-03 05:23:41|12394
2010-11-03 15:13:55|99384

Je souhaite grouper par date et par fenêtre de 8 heures à cette date de manière à avoir:

interval           |count
-------------------|-----
2010-11-01 00:00:00|1
2010-11-01 08:00:00|2
2010-11-01 16:00:00|3
2010-11-02 00:00:00|4
2010-11-02 08:00:00|5
2010-11-02 16:00:00|6
2010-11-03 00:00:00|7
2010-11-03 08:00:00|8
2010-11-03 16:00:00|9

Ma requête d'origine (en utilisant uniquement des dates) était:

SELECT CAST(visit_date as DATE), count(1) as count
FROM table
GROUP BY CAST(visit_date as DATE)
ORDER BY CAST(visit_date as DATE)

Mais cela ne regroupe que par date.

Existe-t-il une méthode recommandée pour obtenir le nombre d'intervalles pour chaque intervalle par jour? J'ai vu des implémentations utilisant DATEADD et DATEPART mais je ne suis pas sûr de ce qui a le plus de sens dans cette situation.

Merci!

1
BeginnersMindTruly 20 nov. 2018 à 01:19

3 réponses

Meilleure réponse

Ajoutez les heures à ce que vous regroupez et comptez:

SELECT 
  CAST(visit_date as DATE), 
  HOUR(visit_date)/8 as ival8h
  count(1) as count
FROM table
  GROUP BY CAST(visit_date as DATE), HOUR(visit_date)/8
  ORDER BY CAST(visit_date as DATE)

La fonction heure renvoie le nombre d'heures de la date passée, divisez-le par 8 pour obtenir un entier de l'intervalle, donc 0 à 7 devient 0, 8 à 16 devient 1 etc.

Si vous voulez qu'il revienne comme une heure liée à un tour de 8h, multipliez-le à nouveau par 8 et formatez-le à NN: 00: 00, ou ajoutez-le à la date, ainsi:

SELECT 
  DATEADD(hour, (HOUR(visit_date)/8)*8, CAST(CAST(visit_date as DATE) as DATETIME) as quantized_date,
  count(1) as count
FROM table
  GROUP BY DATEADD(hour, (HOUR(visit_date)/8)*8, CAST(CAST(visit_date as DATE) as DATETIME)
  ORDER BY CAST(visit_date as DATE)

Cela arrondit essentiellement les heures au moindre marché de 8 heures et l'ajoute à minuit. Deux transtypages sont nécessaires (probablement) à la date car DATEADD n'ajoutera pas d'heures à une date, seulement une date / heure, mais nous avons besoin du cast à ce jour pour fixer l'élément tine à minuit

Si vous voulez qu'il y ait une date et un décompte de 0 pour les périodes où aucun événement n'a eu lieu, utilisez une table de nombres ou un générateur de lignes et créez une séquence de dates pour joindre à gauche vos données réelles, puis comptez les données réelles regroupées par le faux Rendez-vous

2
Caius Jard 20 nov. 2018 à 08:56

Utilisez un cross apply pour former 4 valeurs limites de décalage, puis utilisez celles d'un case expression pour générer les valeurs group by

SELECT
      case
         when visit_date >= s1 and visit_date < s2 then s1 
         when visit_date >= s2 and visit_date < s3 then s2
         when visit_date >= s3 and visit_date < s4 then s3 
      end as shift
    , count(1) as count
FROM mytable
CROSS APPLY (
    select 
          cast(CAST(visit_date as DATE)as datetime) s1
        , dateadd(hh,8,cast(CAST(visit_date as DATE)as datetime)) s2
        , dateadd(hh,16,cast(CAST(visit_date as DATE)as datetime)) s3
        , dateadd(hh,24,cast(CAST(visit_date as DATE)as datetime)) s4  
    ) ca
GROUP BY
      case
         when visit_date >= s1 and visit_date < s2 then s1 
         when visit_date >= s2 and visit_date < s3 then s2
         when visit_date >= s3 and visit_date < s4 then s3 
      end
ORDER BY shift

Résultat:

+----+---------------------+-------+
|    |        shift        | count |
+----+---------------------+-------+
|  1 | 01.11.2010 00:00:00 |     1 |
|  2 | 01.11.2010 08:00:00 |     1 |
|  3 | 01.11.2010 16:00:00 |     1 |
|  4 | 02.11.2010 00:00:00 |     1 |
|  5 | 02.11.2010 08:00:00 |     1 |
|  6 | 02.11.2010 16:00:00 |     1 |
|  7 | 03.11.2010 00:00:00 |     2 |
|  8 | 03.11.2010 08:00:00 |     1 |
+----+---------------------+-------+
0
Used_By_Already 19 nov. 2018 à 23:20

Je pense que la méthode canonique dans SQL Server est d'utiliser dateadd() et datediff():

select dateadd(hour, 0, 3 * (datediff(hour, 0, visit_date) / 3)) as day_hour8,
       count(*)
from t
group by dateadd(hour, 0, 3 * (datediff(hour, 0, visit_date) / 3))
order by day_hour8;
0
Gordon Linoff 20 nov. 2018 à 03:04