Slight alteration, the Left Join should contain the full filter to show
empty time-slots, if that is what you want rather, 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, max(DSR.data) AS dataAvg
FROM TIV
LEFT JOIN dataStreamRecord AS DSR ON DSR.datetime >= TIV.startTime
AND DSR.datetime < TIV.endTime AND fwParameterID = 1074
GROUP BY TIV.startTime
ORDER BY TIV.startTime ASC
;
On 2015/11/18 7:43 PM, R Smith wrote:
> 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
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
