Comment regrouper le temps par heure ou par 10 minutes

Je suis super en retard à la fête, mais cela n'apparaît dans aucune des réponses existantes:

GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, date_column) / 10 * 10, 0)

• Le 10 et MINUTE, ces termes peuvent être modifiés à n'importe quel nombre et DATEPART, respectivement.
• c'est une valeur DATETIME, qui signifie:
  • cela fonctionne bien sur de longs intervalles de temps. (Il n'y a pas de collision entre les années.)
  • L'inclure dans l'instruction SELECT donnera à votre sortie une colonne avec une jolie sortie tronquée au niveau que vous spécifier.

SELECT   DATEADD(MINUTE, DATEDIFF(MINUTE, 0, aa.[date]) / 10 * 10, 0) AS [date_truncated],
         COUNT(*) AS [records_in_interval],
         AVG(aa.[value]) AS [average_value]
FROM     [friib].[dbo].[archive_analog] AS aa
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, aa.[date]) / 10 * 10, 0)
ORDER BY [date_truncated]

Dans T-SQL, vous pouvez:

SELECT [Date]
  FROM [FRIIB].[dbo].[ArchiveAnalog]
  GROUP BY [Date], DATEPART(hh, [Date])

Ou

Par minute DATEPART(mi, [Date])

Ou

Par 10 minutes Utilisez DATEPART(mi, [Date]) / 10 (comme Timothy suggéré)

Pour un intervalle de 10 minutes, vous

GROUP BY (DATEPART(MINUTE, [Date]) / 10)

Comme cela a déjà été mentionné par tzup et Pieter888... pour faire un intervalle d'une heure, juste

GROUP BY DATEPART(HOUR, [Date])

La réponse originale que l'auteur a donnée fonctionne plutôt bien. Juste pour étendre cette idée, vous pouvez faire quelque chose comme

group by datediff(minute, 0, [Date])/10

Qui vous permettra de grouper par une période plus longue puis 60 minutes, disons 720, ce qui correspond à une demi-journée, etc.

Devrait être quelque chose comme

select timeslot, count(*)  
from 
    (
    select datepart('hh', date) timeslot
    FROM [FRIIB].[dbo].[ArchiveAnalog]  
    ) 
group by timeslot

(pas sûr à 100% de la syntaxe-je suis plus un type D'Oracle)

Dans Oracle:

SELECT timeslot, COUNT(*) 
FROM
(  
    SELECT to_char(l_time, 'YYYY-MM-DD hh24') timeslot 
    FROM
    (
        SELECT l_time FROM mytab  
    )  
) GROUP BY timeslot 

Pour MySql:

GROUP BY
DATE(`your_date_field`),
HOUR(`your_date_field`),
FLOOR( MINUTE(`your_date_field`) / 10);

Ma solution consiste à utiliser une fonction pour créer une table avec les intervalles de date, puis joindre cette table aux données que je veux regrouper en utilisant l'intervalle de date dans la table. L'intervalle de date peut alors être facilement sélectionné lors de la présentation des données.

CREATE FUNCTION [dbo].[fn_MinuteIntervals]
    (
      @startDate SMALLDATETIME ,
      @endDate SMALLDATETIME ,
      @interval INT = 1
    )
RETURNS @returnDates TABLE
    (
      [date] SMALLDATETIME PRIMARY KEY NOT NULL
    )
AS
    BEGIN
        DECLARE @counter SMALLDATETIME
        SET @counter = @startDate
        WHILE @counter <= @endDate
            BEGIN
                INSERT INTO @returnDates VALUES ( @counter )
                SET @counter = DATEADD(n, @interval, @counter)
            END
        RETURN
    END

Pour SQL Server 2012, bien que je crois que cela fonctionnerait dans SQL Server 2008R2, j'utilise l'approche suivante pour réduire le temps à la milliseconde:

DATEADD(MILLISECOND, -DATEDIFF(MILLISECOND, CAST(time AS DATE), time) % @msPerSlice, time)

Cela fonctionne par:

• Obtenir le nombre de millisecondes entre un point fixe et l'heure cible:
  @ms = DATEDIFF(MILLISECOND, CAST(time AS DATE), time)
• prendre le reste de la division de ces millisecondes en tranches de temps:
  @rms = @ms % @msPerSlice
• ajouter le négatif de ce reste au temps cible pour obtenir la tranche temps:
  DATEADD(MILLISECOND, -@rms, time)

Malheureusement, comme cela déborde de microsecondes et d'unités plus petites, les ensembles de données plus grands et plus fins devraient utiliser un point fixe moins pratique.

Je n'ai pas rigoureusement comparé cela et je ne suis pas dans le big data, donc votre kilométrage peut varier, mais les performances n'étaient pas sensiblement pires que les autres méthodes essayées sur nos équipements et nos ensembles de données, et le gain En commodité du développeur pour le tranchage arbitraire en vaut la peine pour nous.