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

旧城等待， 2024-04-07 11:30 91阅读 0赞

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