Hi,
Had a question regarding what I am trying to do. One thing that I have
noticed is that it is slow to do this. I do not have any indexes created and
there is no primary index on this table.
I am using a 'DateTime' variable for the date/time. I understand this
translates to a Numeric. It appears to be getting handled as a string, but not
sure. Would it be any better if I stored the date/time as a Integer (64bit
value). This would be using the C routine for generating a date/time based
upon the __time64 type (number of seconds since jan 1 1970 0:0:0).
Thanks,
Andrew S.
-----Original Message-----
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:[email protected]] On Behalf Of R Smith
Sent: Wednesday, November 18, 2015 2:06 PM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] 10 minute Avg
On 2015/11/18 9:23 PM, Andrew Stewart wrote:
> Ryan,
> Like the code. Was wondering what adjustment I would make to get if I
> had 2 (or more) firmwareIDs that I wanted to get the average for. Say get 10
> minute average for fwParameterID 1074 & fwParameterID 1073. I am having
> issues adjusting your code to achieve this. I need to see the average for
> each fwParameterID separately.
>
> Thanks,
> Andrew S.
Certainly, one way to do this is to simply list the entries consecutively for
the different fwParams in a makeshift CTE table, like
this:
WITH BDT(startDateTime,endDateTime,IntervalSeconds) AS (
SELECT '2015-11-17 00:00:00', '2015-11-18 00:00:00', '+600 seconds'
), PAR(fwParam) AS (
SELECT 1074 UNION ALL
SELECT 1075 UNION ALL -- This list includes all the fwParameterIDs
you would like to add...
SELECT 1076 UNION ALL
SELECT 1079 UNION ALL
SELECT 1080
), 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 PAR.fwParam, TIV.startTime, avg(DSR.data) AS dataAvg
FROM PAR,TIV
LEFT JOIN dataStreamRecord AS DSR ON DSR.datetime >= TIV.startTime AND
DSR.datetime < TIV.endTime AND fwParameterID = PAR.fwParam
GROUP BY PAR.fwParam, TIV.startTime
ORDER BY PAR.fwParam, TIV.startTime ASC ;
See if that does what is needed,
Cheers!
Ryan
>
> -----Original Message-----
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of R Smith
> Sent: Wednesday, November 18, 2015 10:06 AM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] 10 minute Avg
>
> 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
> _______________________________________________
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
