Comment utiliser efficacement SQL pour récupérer des données sur des intervalles d'une demi-heure?

Un moyen de le faire pure SQL est d'utiliser une table de recherche. Je ne connais pas mysql que oui peut-être peut-être beaucoup d'amélioration du code. Tout mon code sera MS SQL .. Je le ferais comme ceci:

   /* Mock salesTable */
   Declare @SalesTable TABLE (SubTotal int, SaleDate datetime)
Insert into @SalesTable (SubTotal, SaleDate) VALUES (1, '2012-08-01 12:00')
Insert into @SalesTable (SubTotal, SaleDate) VALUES (2, '2012-08-01 12:10')
Insert into @SalesTable (SubTotal, SaleDate) VALUES (3, '2012-08-01 12:15')
Insert into @SalesTable (SubTotal, SaleDate) VALUES (4, '2012-08-01 12:30')
Insert into @SalesTable (SubTotal, SaleDate) VALUES (5, '2012-08-01 12:35')
Insert into @SalesTable (SubTotal, SaleDate) VALUES (6, '2012-08-01 13:00')
Insert into @SalesTable (SubTotal, SaleDate) VALUES (7, '2012-08-01 14:00')

/* input data */
declare @From datetime, @To DateTime, @intervall int 
set @from = '2012-08-01' 
set @to = '2012-08-02'
set @intervall = 30

/* Create lookup table */
DECLARE @lookup TABLE (StartTime datetime, EndTime datetime) 
DECLARE @tmpTime datetime
SET @tmpTime = @from
WHILE (@tmpTime <= @To) 
BEGIN
 INSERT INTO @lookup (StartTime, EndTime) VALUES (@tmpTime, dateAdd(mi, @intervall, @tmpTime))
 set @tmpTime = dateAdd(mi, @intervall, @tmpTime)
END

/* Get data */
select l.StartTime, l.EndTime, sum(subTotal) from @SalesTable as SalesTable 
    join @lookUp as l on SalesTable.SaleDate >= l.StartTime and SalesTable.SaleDate < l.EndTime
    group by l.StartTime, l.EndTime

Dans ma requête, je suppose un domaine DateTime nommé date. Cela vous donnera tous les groupes à partir de toute date de date à partir de laquelle vous le donnez pour commencer:

SELECT 
  ABS(FLOOR(TIMESTAMPDIFF(MINUTE, date, '2011-08-01 00:00:00') / 30)) AS GROUPING
  , SUM(subtotal) AS subtotals 
FROM 
  Receipts 
GROUP BY 
  ABS(FLOOR(TIMESTAMPDIFF(MINUTE, date, '2011-08-01 00:00:00') / 30))
ORDER BY
  GROUPING

Utilisez toujours les types de données appropriés pour vos données. Dans le cas de vos colonnes de date / heure, il est préférable de les stocker comme (de préférence UTC zoné). Cela est particulièrement vrai en ce que certains moments n'existent pas pour certaines dates (pour certains horloges, d'où l'UTC). Vous voudrez un index sur cette colonne.

En outre, votre plage de date / heure ne vous donnera pas ce que vous voulez - à savoir, vous ne vous manquez pas exactement l'heure (parce que vous utilisez un strict supérieur à Comparaison). Définissez toujours des gammes en tant que «EXCLUSIVE INCLUS INCLUSIVE INCLUS INCLUS INCLUSIMENT INCLUSIVÉE» (SO, TIME> = '07: 00: 00 'et heure <'07: 30: 00' ). Ceci est particulièrement important pour les horodatages, qui ont un nombre supplémentaire de champs à traiter.

Parce que MySQL n'a pas de requêtes récursives, vous voulez que quelques tables supplémentaires tirent celles-ci. Je les référencez de tables «permanentes», mais il serait certainement possible de les définir en ligne, si nécessaire.

Vous allez vouloir une table de calendrier. Celles-ci sont utiles pour un certain nombre de raisons, mais ici, nous les voulons pour leur liste de dates. Cela nous permettra de montrer des dates qui ont des sous-totales de 0, si nécessaire. Vous allez également vouloir avoir une valeur de fois des incréments de demi-heure, pour les mêmes raisons.

Ceci devrait vous permettre d'interroger vos données comme: xxx

( Exemple de fonctionnement sur SQLFIDDLE , qui utilise un régulier rejoindre pour brièveté)