字段说明:
- 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