Query to obtain average each 30 seconds

Question

How can i calculate average of each 30 second? The following is the table structure

    Price  TTime

every minute 5-60 records inserted. The time is inserted by getDate(). I have to calculate average of every 30 seconds.

Answer 1

You need to do 2 things:

• Create a column (in your SELECT result, not in the table) that contains the time in half-minutes;

• calculate the average of Price using AVG(Price) and GROUP BY:

  SELECT <function returning half minutes from TTime> AS HalfMinute, AVG(Price) FROM <Table> GROUP BY HalfMinute`
  

I don't know SQL Server's time functions. If you can get the time returned in seconds, you could go with SECONDS/30. Maybe someone else can step in with details here.

Answer 2

Something like:

SELECT 
  AVG(Price)   AS AvgPrice, 
  COUNT(Price) AS CountPrice,
  MIN(TTIME)   AS PeriodBegin,
  (SECOND(TTime) % 30) * 30 AS PeriodType  /* either 0 or 30 */
FROM 
  PriceTable
GROUP BY
  YEAR(TTime), MONTH(TTime), DAY(TTime), HOUR(TTime), MINUTE(TTime)
  SECOND(TTime) % 30  /* either 0 or 1 */
ORDER BY
 MIN(TTime)

In place of:

GROUP BY
  YEAR(TTime), MONTH(TTime), DAY(TTime), HOUR(TTime), MINUTE(TTime)

you could also use, for example:

GROUP BY
  LEFT(CONVERT(varchar, TTime, 120), 16)

In any case these are operation that invoke a table scan, since they are not indexable. A WHERE clause to determine the valid TTime range is advisable.

You could also make a column that contains the calculated date ('…:00.000' or '…:30.000') and fill that on INSERT with help of a trigger. Place an index on it, GROUP BY it, done.