sqlite实现按5分钟进行统计汇总数据的方法:
//300是秒数,即5分钟的秒数为300秒,这个间隔数可以自己定义
select id,cameraName,checkPointName,datetime(strftime('%s', countTime)/300*300,'unixepoch') as countTime,sum(todayCount) as 'todaycount' from cardetectinfo where cameraName='南' and checkPointName='左' and countTime>='2022-04-01 01:01:01' and countTime<='2022-08-31 01:01:01' group by strftime('%s', countTime)/300 order by countTime limit 0,20
group by strftime(‘%s’, countTime)/300是按照5分钟的间隔来统计汇总数据,%s是自1970年0点0分0秒至countTime这个时间的秒数,按照5分钟一段分隔,countTime属于哪个时间段就统计到哪个时间段内,datetime(strftime(‘%s’, countTime)/300*300,‘unixepoch’) as countTime这句是显示所属的时间段,先除300,会取整,再乘300即得到时间段的日期时间,这种方法运行速度非常快,之前的思路是自自一个时间开始向后推5分钟,依次类推,运行速度相当慢,通过代码改良极大的改变了运行速度。
下列代码为按照年月日时分秒分求和:
按年月日时分秒分组求和
SELECT countTime,sum(todayCount) as todayCount from cardetectinfo GROUP BY (select strftime('%Y-%m-%d %H:%M:
%s', countTime) as aa)
按年月日时分分组求和
SELECT countTime,sum(todayCount) as todayCount from cardetectinfo GROUP BY (select strftime('%Y-%m-%d %H:%M',
countTime) as aa)
按年月日时分组求和
SELECT countTime,sum(todayCount) as todayCount from cardetectinfo GROUP BY (select strftime('%Y-%m-%d %H',
countTime) as aa)
按年月日分组求和
SELECT countTime,sum(todayCount) as todayCount from cardetectinfo GROUP BY (select strftime('%Y-%m-%d',
countTime) as aa)
按年月分组求和
SELECT countTime,sum(todayCount) as todayCount from cardetectinfo GROUP BY (select strftime('%Y-%m', countTime)
as aa)
按年分组求和
SELECT countTime,sum(todayCount) as todayCount from cardetectinfo GROUP BY (select strftime('%Y', countTime) as
aa)
秋风写于淄博,技术交流与业务咨询:Q375172665