查询每月数据总和统计、平均值统计，查询时间范围数据

字段说明：

• startTime：数据插入时间
• responseTime ：统计字段
• day(startTime)  ：取时间字段的天值
• month(startTime) ：取时间字段的月值
•  year(startTime) ：取时间字段的年值

每月数据总数统计

select 
			sum(case month(startTime) when '1'  then responseTime else 0 end) as Jan,
			sum(case month(startTime) when '2'  then responseTime else 0 end) as Feb,
			sum(case month(startTime) when '3'  then responseTime else 0 end) as Mar,
			sum(case month(startTime) when '4'  then responseTime else 0 end) as Apr,
			sum(case month(startTime) when '5'  then responseTime else 0 end) as May,
			sum(case month(startTime) when '6'  then responseTime else 0 end) as June,
			sum(case month(startTime) when '7'  then responseTime else 0 end) as July,
			sum(case month(startTime) when '8'  then responseTime else 0 end) as Aug,
			sum(case month(startTime) when '9'  then responseTime else 0 end) as Sept,
			sum(case month(startTime) when '10' then responseTime  else 0 end) as Oct,
			sum(case month(startTime) when '11' then responseTime  else 0 end) as Nov,
			sum(case month(startTime) when '12' then responseTime  else 0 end) as Dece
from play_data_statistics where year(startTime) = '2021';

每月数据平均值统计

select 
			avg(case month(startTime) when '1'  then responseTime else 0 end) as Jan,
			avg(case month(startTime) when '2'  then responseTime else 0 end) as Feb,
			avg(case month(startTime) when '3'  then responseTime else 0 end) as Mar,
			avg(case month(startTime) when '4'  then responseTime else 0 end) as Apr,
			avg(case month(startTime) when '5'  then responseTime else 0 end) as May,
			avg(case month(startTime) when '6'  then responseTime else 0 end) as June,
			avg(case month(startTime) when '7'  then responseTime else 0 end) as July,
			avg(case month(startTime) when '8'  then responseTime else 0 end) as Aug,
			avg(case month(startTime) when '9'  then responseTime else 0 end) as Sept,
			avg(case month(startTime) when '10' then responseTime  else 0 end) as Oct,
			avg(case month(startTime) when '11' then responseTime  else 0 end) as Nov,
			avg(case month(startTime) when '12' then responseTime  else 0 end) as Dece
from play_data_statistics where year(startTime) = '2021';

查询某个时间范围内的数据

// 24小时之前的数据
SELECT * FROM play_data_statistics where startTime <= (now()-INTERVAL 24 hour) order by startTime desc

// 七天之前的数据
SELECT * FROM play_data_statistics where startTime <= (now()-INTERVAL 7 DAY) order by startTime desc