sql 每小时的温度平均值

平均每小时的温度

方法一

SELECT DATE(dtime), AVG(temperature), hour(dtime)
FROM t1
GROUP BY DATE(dtime), hour(dtime);

 

方法二

select DATE_FORMAT(dtime, '%Y-%m-%d %H') as dtime, ROUND(AVG(`t1`.`temperature`),2) as temperature
from t1
GROUP BY DATE_FORMAT(dtime, '%Y-%m-%d %H');

今天每小时的平均温度

SELECT DATE_FORMAT(dtime, '%Y-%m-%d %H') as dtime, ROUND(AVG(`t1`.`temperature`),2) as temperature
from t1
where to_days(dtime) = to_days(now())
GROUP BY DATE_FORMAT(dtime, '%Y-%m-%d %H')

昨天每小时的平均温度

SELECT DATE_FORMAT(dtime, '%Y-%m-%d %H') as dtime, ROUND(AVG(`t1`.`temperature`),2) as temperature
FROM t1
WHERE TO_DAYS( NOW( ) ) - TO_DAYS( dtime ) <= 1
GROUP BY DATE_FORMAT(dtime, '%Y-%m-%d %H')

近七天每小时的平均温度

SELECT DATE_FORMAT(dtime, '%Y-%m-%d %H') as dtime, ROUND(AVG(`jiayi`.`temperature`),2) as temperature
FROM jiayi
where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(dtime)
GROUP BY DATE_FORMAT(dtime, '%Y-%m-%d %H')

本月每小时的平均温度

SELECT DATE_FORMAT(dtime, '%Y-%m-%d %H') as dtime, ROUND(AVG(`t1`.`temperature`),2) as temperature
FROM t1
WHERE DATE_FORMAT( dtime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
GROUP BY DATE_FORMAT(dtime, '%Y-%m-%d %H')

本月每天的平均温度

SELECT DATE(dtime) as dtime, ROUND(AVG(`t1`.`temperature`),2) as temperature
FROM  t1
WHERE DATE_FORMAT( dtime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
GROUP BY DATE(dtime)

上个月每天的平均温度

SELECT DATE(dtime) as dtime, ROUND(AVG(`t1`.`temperature`),2) as temperature
FROM t1
WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( dtime, '%Y%m' ) ) =1
GROUP BY DATE(dtime)

-- 去掉外键约束
SET FOREIGN_KEY_CHECKS=0;
-- 添加外键约束
SET FOREIGN_KEY_CHECKS=1;