HIVE DATA DAILY

From CHBR
Jump to: navigation, search

HIVE_DATA_DAILY table

Create Table

CREATE TABLE IF NOT EXISTS `hivetool_raw`.`HIVE_DATA_DAILY` (
 `row_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
 `hive_id` INT(11) NOT NULL,
 `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `hive_observation_time_local` DATETIME NULL DEFAULT NULL,
 `hive_observation_time_utc` DATETIME NULL DEFAULT NULL,
 `year` INT(4) NULL,
 `day_of_year` INT(3) NULL,
 `growing_degree_days` DECIMAL(4,1) NULL,
 `growing_degree_days_total` DECIMAL(7,1) NULL,
 `hive_weight_avg` DECIMAL(6,3) NULL DEFAULT NULL,
 `hive_weight_delta` DECIMAL(5,2) NULL DEFAULT NULL,
 `hive_weight_end` DECIMAL(5,2) NULL DEFAULT NULL,
 `hive_weight_filtered_avg` DECIMAL(5,2) NULL DEFAULT NULL,
 `hive_weight_filtered_delta` DECIMAL(5,2) NULL DEFAULT NULL,
 `hive_weight_filtered_end` DECIMAL(6,3) NULL DEFAULT NULL,
 `hive_weight_filtered_manipulation` DECIMAL(5,2) NULL DEFAULT NULL,
 `hive_weight_max` DECIMAL(5,2) NULL DEFAULT NULL,
 `hive_weight_min` DECIMAL(5,2) NULL DEFAULT NULL,
 `hive_temp_avg` DECIMAL(4,1) NULL DEFAULT NULL,
 `hive_temp_max` DECIMAL(4,1) NULL DEFAULT NULL,
 `hive_temp_min` DECIMAL(4,1) NULL DEFAULT NULL,
 `hive_humidity_avg` DECIMAL(4,1) NULL DEFAULT NULL,
 `hive_humidity_max` DECIMAL(4,1) NULL DEFAULT NULL,
 `hive_humidity_min` DECIMAL(4,1) NULL DEFAULT NULL,
 `hive_battery_voltage_avg` DECIMAL(5,2) NULL DEFAULT NULL,
 `hive_battery_voltage_max` DECIMAL(4,1) NULL DEFAULT NULL,
 `hive_battery_voltage_min` DECIMAL(4,1) NULL DEFAULT NULL,
 `ambient_temperature_avg` DECIMAL(4,1) NULL DEFAULT NULL,
 `ambient_temperature_max` DECIMAL(5,2) NULL DEFAULT NULL,
 `ambient_temperature_min` DECIMAL(5,2) NULL DEFAULT NULL,
 `ambient_humidity_avg` DECIMAL(4,1) NULL DEFAULT NULL,
 `ambient_humidity_max` DECIMAL(4,1) NULL DEFAULT NULL,
 `ambient_humidity_min` DECIMAL(4,1) NULL DEFAULT NULL,
 `ambient_luminance_avg` INT(11) NULL DEFAULT NULL,
 `ambient_luminance_max` INT(11) NULL DEFAULT NULL,
 `ambient_luminance_min` INT(11) NULL DEFAULT NULL,
 `ambient_precip` DECIMAL(5,2) NULL DEFAULT NULL,
 `wx_station_id` CHAR(40) NULL DEFAULT NULL,
 `wx_temperature_avg` DECIMAL(4,1) NULL DEFAULT NULL,
 `wx_temperature_max` DECIMAL(4,1) NULL DEFAULT NULL,
 `wx_temperature_min` DECIMAL(4,1) NULL DEFAULT NULL,
 `wx_humidity_avg` DECIMAL(4,1) NULL DEFAULT NULL,
 `wx_wind_dir` CHAR(6) NULL DEFAULT NULL,
 `wx_wind_degrees_avg` DECIMAL(4,1) NULL DEFAULT NULL,
 `wx_wind_speed_avg` DECIMAL(4,1) NULL DEFAULT NULL,
 `wx_wind_gust_avg` DECIMAL(4,1) NULL DEFAULT NULL,
 `wx_pressure_avg` DECIMAL(5,1) NULL DEFAULT NULL,
 `wx_dewpoint_avg` DECIMAL(4,1) NULL DEFAULT NULL,
 `wx_solar_radiation` DECIMAL(4,1) NULL DEFAULT NULL,
 `wx_precip` DECIMAL(5,2) NULL DEFAULT NULL,
 `quality` INT(2) NOT NULL DEFAULT '5',
 PRIMARY KEY (`row_id`),
 INDEX `OBSERVATION_TIME_LOCAL` (`hive_observation_time_local` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 13954192
DEFAULT CHARACTER SET = latin1

Populate Table

avg, max and min

--
-- insert the avg, max and min values
-- need to add rest of weather station averages (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');

ending hive weight

NOT RIGHT - NOW HAVE SEPARATE YEAR and DAY_OF_YEAR

--
-- 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

HIVE_DATA_COUNTS table

create table HIVE_DATA_COUNTS_2013 select hive_id, count(*) as count from HIVE_DATA where hive_observation_time_local like "2013%" group by hive_id;
create table HIVE_DATA_COUNTS_2014 select hive_id, count(*) as count from HIVE_DATA where hive_observation_time_local like "2014%" group by hive_id;
create table HIVE_DATA_COUNTS_2015 select hive_id, count(*) as count from HIVE_DATA where hive_observation_time_local like "2015%" group by hive_id;
create table HIVE_DATA_COUNTS_2016 select hive_id, count(*) as count from HIVE_DATA where hive_observation_time_local like "2016%" group by hive_id;
create table HIVE_DATA_COUNTS_2017 select hive_id, count(*) as count from HIVE_DATA where hive_observation_time_local like "2017%" group by hive_id;
create table HIVE_DATA_COUNTS_2018 select hive_id, count(*) as count from HIVE_DATA where hive_observation_time_local like "2018%" group by hive_id;
create table HIVE_DATA_COUNTS select hive_id, count(*) as count from HIVE_DATA group by hive_id;