SQLite笔记，查询语句：日、周、月

一、查询当天

SELECT sum(NumericCount),TrainingDate FROM TableTrainingRecord WHERE strftime('%Y-%m-%d','2017-01-01','localtime') <= strftime('%Y-%m-%d',TrainingDate)

二、按天统计，合并一天的多条数据:(%Y 年, 数字, 4 位 ;%y 年, 数字, 2 位 )

1. 

 select strftime('%Y-%m-%d',TrainingDate) ,strftime('%m',TrainingDate) ,strftime('%d',TrainingDate) ,sum(NumericCount)
 from TableTrainingRecord group by strftime('%Y-%m-%d', TrainingDate) ,strftime('%m', TrainingDate) ,strftime('%d', TrainingDate)

2. 

select strftime('%Y年%m-%d',TrainingDate) as Y_M_D ,strftime('%m',TrainingDate) as month ,strftime('%d',TrainingDate) as day ,sum(NumericCount) as number
 from TableTrainingRecord group by strftime('%Y-%m-%d',TrainingDate) ,strftime('%m',TrainingDate) ,strftime('%d',TrainingDate)

3.简化

select strftime('%Y年%m-%d',TrainingDate) as TrainingDate ,sum(NumericCount) as NumericCount
 from TableTrainingRecord group by strftime('%Y-%m-%d',TrainingDate)

三、按周统计（%W：一年中的第几周，比如，01-53，第53周）

select strftime('%Y-%W',TrainingDate) as TrainingDate ,sum(NumericCount) as NumericCount, sum(ReadTime) as ReadTime, sum(WriteTime) as WriteTime, sum(ErrorNumericCount) as ErrorNumericCount
 from TableTrainingRecord group by strftime('%Y-%W',TrainingDate)

四、按月，统计一整个月：上面按天语句，去掉:%d

select strftime('%Y年%m',TrainingDate) as Y_M_D ,strftime('%m',TrainingDate) as month ,sum(NumericCount) as number,sum(ReadTime)
 from TableTrainingRecord group by strftime('%Y-%m',TrainingDate) ,strftime('%m',TrainingDate)

五、统计最大，总数、总量、平均

SELECT sum(ReadTime+WriteTime)/60 as sumTime,count(systemId) as sumCount,sum(NumericCount) as sumNumeric,max(ReadTime+WriteTime)/60 as maxTime, max(NumericCount) as maxNumeric, (1-1.0*sum(ErrorNumericCount)/sum(NumericCount))*100 as avgCorrectPercent,sum(NumericCount)/(sum(ReadTime)/60) as avgRead,sum(NumericCount)/(sum(WriteTime)/60) as avgWrite FROM TableTrainingRecord

MySql按周，按月，按日分组统计数据

SQLite 常用函数