Talk:HiveTool

From CHBR
Jump to: navigation, search
--
-- insert the avg, max and min values
-- (need to ad wx_xxx_avg) 

INSERT INTO HIVE_DATA_DAILY(
hive_id,hive_observation_time_local,day_of_year,
hive_weight_avg,hive_weight_max,hive_weight_min,
hive_temp_avg,hive_temp_max,hive_temp_min,
hive_humidity_avg,hive_humidity_max,hive_humidity_min,
hive_battery_voltage_avg,hive_battery_voltage_max,hive_battery_voltage_min,
ambient_temperature_avg, ambient_temperature_max, ambient_temperature_min,
ambient_humidity_avg, ambient_humidity_max, ambient_humidity_min,
ambient_luminance_avg,ambient_luminance_max,ambient_luminance_min,
ambient_precip,
wx_temperature_avg, wx_temperature_max, wx_temperature_min
)
SELECT hive_id,DATE_FORMAT(`hive_observation_time_local`, '%Y-%m-%d'),DATE_FORMAT(`hive_observation_time_local`, '%Y')*1000+ DAYOFYEAR(DATE_FORMAT(`hive_observation_time_local`, '%Y-%m-%d')) AS doy, 
avg(hive_weight_lbs), max(hive_weight_lbs) , min(hive_weight_lbs),
avg(hive_temp_c)*1.8+32, max(hive_temp_c)*1.8+32 , min(hive_temp_c)*1.8+32,
avg(hive_humidity), max(hive_humidity) , min(hive_humidity),
avg(hive_battery_voltage), max(hive_battery_voltage) , min(hive_battery_voltage),
avg(ambient_temp_c)*1.8+32, max(ambient_temp_c)*1.8+32 , min(ambient_temp_c)*1.8+32,
avg(ambient_humidity), max(ambient_humidity) , min(ambient_humidity),
avg(ambient_luminance), max(ambient_luminance) , min(ambient_luminance),
ambient_precip_in,
avg(wx_temp_f), max(wx_temp_f) , min(wx_temp_f)
from HIVE_DATA  group by hive_id, DATE_FORMAT(`hive_observation_time_local`, '%Y-%m-%d');   

--
-- add ending hive weight
--

update HIVE_DATA_DAILY as daily
inner join 
(
SELECT 
t1.hive_id,
DATE_FORMAT(t1.`hive_observation_time_local`, '%Y')*1000+ DAYOFYEAR(DATE_FORMAT(t1.`hive_observation_time_local`, '%Y-%m-%d')) AS doy,
t1.hive_weight_lbs
  FROM HIVE_DATA t1
  JOIN (SELECT hive_id, MAX(hive_observation_time_local) Max_time
          FROM HIVE_DATA 
          group by hive_id, DATE_FORMAT(`hive_observation_time_local`, '%Y-%m-%d')) t2
  ON t1.hive_id = t2.hive_id
 AND t1.hive_observation_time_local = t2.Max_time
) as end on end.hive_id = daily.hive_id and
daily.day_of_year = end.doy
set daily.hive_weight_end = end.hive_weight_lbs 
  
 
--
-- set GDD for each day for each year
--
update HIVE_DATA_DAILY as daily
inner join 
(
SELECT
  q1.hive_id,
  q1.doy,
  q1.gdd
FROM
 (SELECT
 hive_id,
 DATE_FORMAT(`hive_observation_time_local`, '%Y-%m-%d'),DATE_FORMAT(`hive_observation_time_local`, '%Y')*1000+ DAYOFYEAR(DATE_FORMAT(`hive_observation_time_local`, '%Y-%m-%d')) AS doy,
 
  (least(greatest(max(wx_temp_c),10),30) - least(greatest(min(wx_temp_c),10),30))/2 AS gdd
  from HIVE_DATA 
  where hive_observation_time_local like "2017%"
  group by hive_id, DATE_FORMAT(`hive_observation_time_local`, '%Y-%m-%d') ) AS q1
  ) as gdd on gdd.hive_id = daily.hive_id and
daily.day_of_year = gdd.doy
set daily.growing_degree_days = gdd.gdd 

--
-- sum gdd
-- works but not efficient
--
update HIVE_DATA_DAILY as hdd
inner join
(
select 
  daily.hive_id,
  daily.day_of_year as doy,
  (select sum(growing_degree_days) from HIVE_DATA_DAILY where hive_id=daily.hive_id and day_of_year <= doy) as gdd_tot
from HIVE_DATA_DAILY as daily
where  day_of_year like "2017%"
) as gdd on gdd.hive_id = hdd.hive_id and
hdd.day_of_year = gdd.doy
set hdd.growing_degree_days_total = gdd.gdd_tot
--
--