Swarm Detection
SQL
CREATE TABLE daytime1(row_id char(250),hive_id char(250),doy decimal, year decimal,stamp timestamp, hive_weight_lbs decimal,quality char(250));
create table splits2(early_row_id char(250), hive_id char(250),dayofyear decimal, year decimal, early_time_stamp timestamp, early_hive_weight_lbs decimal,early_quality char(250), later_row_id char(250), later_time_stamp timestamp, later_hive_weight_lbs decimal,later_quality char(250));
insert into daytime1 select row_id, hive_id,EXTRACT(doy FROM hive_observation_time_local),EXTRACT(year FROM hive_observation_time_local), hive_observation_time_local, hive_weight_lbs,quality from hivetool where EXTRACT(HOUR FROM hive_observation_time_local) BETWEEN 6 AND 18;
insert into splits2 select d1.row_id,d1.hive_id,d1.doy,d1.year,d1.stamp, d1.hive_weight_lbs,d1.quality, d2.row_id, d2.stamp, d2.hive_weight_lbs, d2.quality from daytime1 d1 inner join daytime1 d2 on d1.doy = d1.doy and d1.year = d2.year and d1.hive_id = d2.hive_id where (d1.hive_weight_lbs-d2.hive_weight_lbs) >2 and (d1.hive_weight_lbs-d2.hive_weight_lbs) <15 and (extract(epoch from d2.stamp- d1.stamp))<1200 and (extract(epoch from d2.stamp- d1.stamp))>30
Results
<colgroup><col width="101"/><col width="61"/><col width="81"/><col width="44"/><col width="151"/><col width="164"/><col width="102"/><col width="97"/><col width="151"/><col width="161"/><col width="98"/></colgroup>early_row_id | hive_id | dayofyear | year | early_time_stamp | early_hive_weight_lbs | early_quality | later_row_id | later_time_stamp | later_hive_weight_lbs | later_quality |
1717532 | 20 | 249 | 2014 | 2014-09-0616:10:01 | 119 | 5 | 1717566 | 2014-09-0616:20:01 | 114 | 5 |
1717532 | 20 | 249 | 2014 | 2014-09-0616:10:01 | 119 | 5 | 1717584 | 2014-09-0616:25:01 | 114 | 5 |
1717532 | 20 | 249 | 2014 | 2014-09-0616:10:01 | 119 | 5 | 1717550 | 2014-09-0616:15:01 | 115 | 5 |
1717516 | 20 | 249 | 2014 | 2014-09-0616:05:01 | 119 | 5 | 1717550 | 2014-09-0616:15:01 | 115 | 5 |
1717516 | 20 | 249 | 2014 | 2014-09-0616:05:01 | 119 | 5 | 1717566 | 2014-09-0616:20:01 | 114 | 5 |
1717498 | 20 | 249 | 2014 | 2014-09-0616:00:01 | 120 | 5 | 1717550 | 2014-09-0616:15:01 | 115 | 5 |
1717281 | 20 | 249 | 2014 | 2014-09-0614:55:01 | 47 | 5 | 1717294 | 2014-09-0615:00:02 | 33 | 5 |
1717230 | 20 | 249 | 2014 | 2014-09-0614:40:01 | 122 | 5 | 1717246 | 2014-09-0614:45:01 | 117 | 5 |
1717213 | 20 | 249 | 2014 | 2014-09-0614:35:01 | 122 | 5 | 1717246 | 2014-09-0614:45:01 | 117 | 5 |
1717196 | 20 | 249 | 2014 | 2014-09-0614:30:01 | 122 | 5 | 1717246 | 2014-09-0614:45:01 | 117 | 5 |
1459423 | 20 | 203 | 2014 | 2014-07-2211:20:01 | 131 | 5 | 1459442 | 2014-07-2211:25:01 | 128 | 5 |
1459400 | 20 | 203 | 2014 | 2014-07-2211:15:01 | 131 | 5 | 1459442 | 2014-07-2211:25:01 | 128 | 5 |
1459385 | 20 | 203 | 2014 | 2014-07-2211:10:02 | 131 | 5 | 1459442 | 2014-07-2211:25:01 | 128 | 5 |
1284648 | 20 | 174 | 2014 | 2014-06-2308:45:01 | 125 | 5 | 1284705 | 2014-06-2309:00:01 | 122 | 5 |
1284648 | 20 | 174 | 2014 | 2014-06-2308:45:01 | 125 | 5 | 1284683 | 2014-06-2308:55:01 | 122 | 5 |
1284625 | 20 | 174 | 2014 | 2014-06-2308:40:01 | 125 | 5 | 1284683 | 2014-06-2308:55:01 | 122 | 5 |
1224399 | 20 | 163 | 2014 | 2014-06-1213:20:01 | 153 | 5 | 1224455 | 2014-06-1213:35:01 | 150 | 5 |
1224399 | 20 | 163 | 2014 | 2014-06-1213:20:01 | 153 | 5 | 1224437 | 2014-06-1213:30:01 | 150 | 5 |
1224376 | 20 | 163 | 2014 | 2014-06-1213:15:01 | 158 | 5 | 1224437 | 2014-06-1213:30:01 | 150 | 5 |
1224376 | 20 | 163 | 2014 | 2014-06-1213:15:01 | 158 | 5 | 1224413 | 2014-06-1213:25:02 | 151 | 5 |
1224376 | 20 | 163 | 2014 | 2014-06-1213:15:01 | 158 | 5 | 1224399 | 2014-06-1213:20:01 | 153 | 5 |
1224364 | 20 | 163 | 2014 | 2014-06-1213:10:01 | 159 | 5 | 1224399 | 2014-06-1213:20:01 | 153 | 5 |
1224364 | 20 | 163 | 2014 | 2014-06-1213:10:01 | 159 | 5 | 1224413 | 2014-06-1213:25:02 | 151 | 5 |
1224346 | 20 | 163 | 2014 | 2014-06-1213:05:02 | 159 | 5 | 1224399 | 2014-06-1213:20:01 | 153 | 5 |
1083586 | 20 | 137 | 2014 | 2014-05-1710:35:01 | 128 | 5 | 1083609 | 2014-05-1710:40:01 | 115 | 5 |
1083568 | 20 | 137 | 2014 | 2014-05-1710:30:01 | 128 | 5 | 1083609 | 2014-05-1710:40:01 | 115 | 5 |
1083554 | 20 | 137 | 2014 | 2014-05-1710:25:01 | 128 | 5 | 1083609 | 2014-05-1710:40:01 | 115 | 5 |
883815 | 20 | 101 | 2014 | 2014-04-1111:35:02 | 105 | 6 | 883859 | 2014-04-1111:45:01 | 102 | 6 |
883815 | 20 | 101 | 2014 | 2014-04-1111:35:02 | 105 | 6 | 883881 | 2014-04-1111:50:01 | 102 | 6 |
883803 | 20 | 101 | 2014 | 2014-04-1111:30:02 | 105 | 6 | 883881 | 2014-04-1111:50:01 | 102 | 6 |
883803 </ td> | 20 | 101 | 2014 | 2014-04-1111:30:02 | 105 | 6 | 883859 | 2014-04-1111:45:01 | 102 | 6 |
866760 | 20 | 98 | 2014 | 2014-04-0811:35:01 | 104 | 6 | 866776 | 2014-04-0811:40:01 | 100 | 6 |
866760 | 20 | 98 | 2014 | 2014-04-0811:35:01 | 104 | 6 | 866795 | 2014-04-0811:45:01 | 100 | 5 |
866760 | 20 | 98 | 2014 | 2014-04-0811:35:01 | 104 | 6 | 866817 | 2014-04-0811:50:01 | 100 | 5 |
866738 | 20 | 98 | 2014 | 2014-04-0811:30:01 | 105 | 6 | 866795 | 2014-04-0811:45:01 | 100 | 5 |
866738 | 20 | 98 | 2014 | 2014-04-0811:30:01 | 105 | 6 | 866776 | 2014-04-0811:40:01 | 100 | 6 |
866718 | 20 | 98 | 2014 | 2014-04-0811:25:01 | 106 | 6 | 866776 | 2014-04-0811:40:01 | 100 | 6 |
851884 | 20 | 95 | 2014 | 2014-04-0513:25:01 | 106 | 6 | 851896 | 2014-04-0513:30:01 | 103 | 6 |
851871 | 20 | 95 | 2014 | 2014-04-0513:20:01 | 106 | 5 | 851896 | 2014-04-0513:30:01 | 103 | 6 |
851846 | 20 | 95 | 2014 | 2014-04-0513:15:01 | 106 | 5 | 851896 | 2014-04-0513:30:01 | 103 | 6 |
851830 | 20 | 95 | 2014 | 2014-04-0513:10:02 | 106 | 5 | 851896 | 2014-04-0513:30:01 | 103 | 6 |
842731 | 20 | 93 | 2014 | 2014-04-0317:35:01 | 111 | 5 | 842753 | 2014-04-0317:40:02 | 108 | 5 |
842731 | 20 | 93 | 2014 | 2014-04-0317:35:01 | 111 | 5 | 842785 | 2014-04-0317:50:01 | 108 | 5 |
842731 | 20 | 93 | 2014 | 2014-04-0317:35:01 | 111 | 5 | 842762 | 2014-04-0317:45:01 | 108 | 5 |
842715 | 20 | 93 | 2014 | 2014-04-0317:30:01 | 111 | 5 | 842762 | 2014-04-0317:45:01 | 108 | 5 |
842715 | 20 | 93 | 2014 | 2014-04-0317:30:01 | 111 | 5 | 842753 | 2014-04-0317:40:02 | 108 | 5 |
841919 | 20 | 93 | 2014 | 2014-04-0313:50:01 | 107 | 6 | 841978 | 2014-04-0314:05:01 | 104 | 6 |
841902 | 20 | 93 | 2014 | 2014-04-0313:45:01 | 108 | 6 | 841961 | 2014-04-0314:00:01 | 105 | 6 |
808364 | 20 | 86 | 2014 | 2014-03-2712:40:01 | 121 | 6 | 808396 | 2014-03-2712:50:02 | 111 | 6 |
808364 | 20 | 86 | 2014 | 2014-03-2712:40:01 | 121 | 6 | 808380 | 2014-03-2712:45:01 | 112 | 6 |
808364 | 20 | 86 | 2014 | 2014-03-2712:40:01 | 121 | 6 | 808411 | 2014-03-2712:55:02 | 111 | 5 |
808348 | 20 | 86 | 2014 | 2014-03-2712:35:01 | 121 | 6 | 808396 | 2014-03-2712:50:02 | 111 | 6 |
808348 | 20 | 86 | 2014 | 2014-03-2712:35:01 | 121 | 6 | 808380 | 2014-03-2712:45:01 | 112 | 6 |
808332 | 20 | 86 | 2014 | 2014-03-2712:30:01 | 122 | 6 | 808380 | 2014-03-2712:45:01 | 112 | 6 |
808316 | 20 | 86 | 2014 | 2014-03-2712:25:02 | 121 | 5 | 808380 | 2014-03-2712:45:01 | 112 | 6 |
694971 | 20 | 59 | 2014 | 2014-02-2816:00:02 | 125 | 5 | 695017 | 2014-02-2816:15:01 | 111 | 5 |
694971 | 20 | 59 | 2014 | 2014-02-2816:00:02 | 125 | 5 | 695001 | 2014-02-2816:10:01 | 111 | 5 |
694971 | 20 | 59 | 2014 | 2014-02-2816:00:02 | 125 | 5 | 694987 | 2014-02-2816:05:01 | 111 | 5 |
694951 | 20 | 59 | 2014 | 2014-02-2815:55:01 | 125 | 5 | 694987 | 2014-02-2816:05:01 | 111 | 5 |
694951 | 20 | 59 | 2014 | 2014-02-2815:55:01 | 125 | 5 | 695001 | 2014-02-2816:10:01 | 111 | 5 |
694936 | 20 | 59 | 2014 | 2014-02-2815:50:02 | 125 | 5 | 695001 | 2014-02-2816:10:01 | 111 | 5 |
694936 | 20 | 59 | 2014 | 2014-02-2815:50:02 | 125 | 5 | 694987 | 2014-02-2816:05:01 | 111 | 5 |
650901 | 20 | 49 | 2014 | 2014-02-1812:40:01 | 127 | 5 | 650914 | 2014-02-1812:45:01 | 116 | 5 |
650886 | 20 | 49 | 2014 | 2014-02-1812:35:01 | 128 | 5 | 650914 | 2014-02-1812:45:01 | 116 | 5 |
650870 | 20 | 49 | 2014 | 2014-02-1812:30:02 | 128 | 5 | 650914 | 2014-02-1812:45:01 | 116 | 5 |
