sqlite实现按5分钟进行统计汇总数据的方法

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值