Quite easy to do with a CTE, like this:
WITH BDT(startDateTime,endDateTime,IntervalSeconds) AS (
SELECT '2015-11-17 00:00:00', '2015-11-18 00:00:00', '+600 seconds'
), TIV(startTime,endTime) AS (
SELECT startDateTime, datetime(startDateTime,IntervalSeconds) FROM BDT
UNION ALL
SELECT endTime, datetime(endTime,IntervalSeconds) FROM TIV,BDT
WHERE endTime < endDateTime
)
SELECT TIV.startTime, avg(DSR.data) AS dataAvg
FROM TIV
LEFT JOIN dataStreamRecord AS DSR ON DSR.datetime >= TIV.startTime
AND DSR.datetime < TIV.endTime
WHERE fwParameterID = 1074
GROUP BY TIV.startTime
ORDER BY TIV.startTime ASC
;
Of course, if you do not wish to list 10-minute intervals where there
wasn't any activity, then add " AND DSR.dateTime IS NOT NULL" to the
WHERE clause.
You can change the start and end dates and the interval by changing just
the first line inside the CTE. Just inject those values via your code.
Let us know if there's anything unclear or not working as expected,
Cheers!
Ryan
On 2015/11/18 6:15 PM, Andrew Stewart wrote:
> Hi,
> I am trying to generate 10 minute average data for a day
> from a data set. Table is created by the following:
>
> CREATE TABLE dataStreamRecord (fwParameterID INTEGER NOT NULL, dateTime
> DATETIME NOT NULL, data INTEGER NOT NULL);
>
> Sample Data
> fwParameterID,dateTime,data
> 1074,2015-11-17 00:00:01,8192
> 1074,2015-11-17 00:33:18,0
> 1074,2015-11-17 00:33:19,8192
> 1074,2015-11-17 00:41:00,0
> 1074,2015-11-17 00:41:01,8192
> 1074,2015-11-17 01:11:34,0
> 1074,2015-11-17 01:11:35,8192
> 1074,2015-11-17 01:19:10,0
> 1074,2015-11-17 01:19:11,8192
> 1074,2015-11-17 01:26:44,0
> 1074,2015-11-17 01:26:45,8192
> 1074,2015-11-17 01:34:24,0
> 1074,2015-11-17 01:34:25,8192
> 1074,2015-11-17 02:12:44,0
> 1074,2015-11-17 02:12:45,8192
> 1074,2015-11-17 02:43:21,0
> 1074,2015-11-17 02:43:22,8192
> 1074,2015-11-17 03:06:19,0
> 1074,2015-11-17 03:06:20,8192
> 1074,2015-11-17 03:37:02,0
> 1074,2015-11-17 03:37:03,8192
> 1074,2015-11-17 05:08:58,0
> 1074,2015-11-17 05:08:59,8192
> 1074,2015-11-17 05:16:35,0
> 1074,2015-11-17 05:16:36,8192
> 1074,2015-11-17 05:16:37,0
> 1074,2015-11-17 05:16:38,8192
> 1074,2015-11-17 06:25:29,0
> 1074,2015-11-17 06:25:30,8192
> 1074,2015-11-17 07:41:58,0
> 1074,2015-11-17 07:41:59,8192
> 1074,2015-11-17 07:43:02,0
> 1074,2015-11-17 07:43:03,8192
> 1074,2015-11-17 07:43:19,0
> 1074,2015-11-17 07:43:20,8192
> 1074,2015-11-17 07:43:55,0
> 1074,2015-11-17 07:43:56,8192
> 1074,2015-11-17 07:44:31,0
> 1074,2015-11-17 07:44:33,8192
> 1074,2015-11-17 08:20:43,0
> 1074,2015-11-17 08:20:44,8192
> 1074,2015-11-17 08:27:49,0
> 1074,2015-11-17 08:27:50,8192
> 1074,2015-11-17 08:35:23,0
> 1074,2015-11-17 08:35:24,8192
> 1074,2015-11-17 09:04:47,0
> 1074,2015-11-17 09:04:48,8192
> 1074,2015-11-17 09:13:35,0
> 1074,2015-11-17 09:13:36,8192
> 1074,2015-11-17 09:17:41,0
> 1074,2015-11-17 09:17:42,8192
>
> Query for generating the above data
> SELECT [fwParameterID], [dateTime], [data]
> FROM dataStreamRecord
> WHERE fwParameterID = 1074 AND
> dateTime >= '2015-11-17 00:00:00' AND
> dateTime < '2015-11-18 00:00:00'
> ORDER BY dateTime ASC
>
> Any assistance in generating a query to do 10 minute averages for 'data' on
> November 17 for fwParameterID 1074 would be appreciated.
>
>
> Thanks,
> Andrew S
> Notice: This electronic transmission contains confidential information,
> intended only for the person(s) named above. If you are not the intended
> recipient, you are hereby notified that any disclosure, copying,
> distribution, or any other use of this email is strictly prohibited. If you
> have received this transmission by error, please notify us immediately by
> return email and destroy the original transmission immediately and all copies
> thereof.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
