Chapter 5 ■ MySQL priMer
So, this tells us that, for today, plant IDs 1 and 3 are dry. But we’re not done! Consider there are multiple
samples taken during the day. Some plants may be on the threshold where they are a little dry but not so
much that the sensor readings are consistent. So, we want plants that are consistently dry where there are
more DRY events read than OK or WET. We could use another view to get this information, but let’s see how
we can do this with a stored function. The following creates a function that returns the maximum number of
samples collected for today for a given plant:
DELiMITER //
CREATE FUNCTION plant_monitoring.max_samples_today (in_id int)
RETURNS int DETERMINISTIC READS SQL DATA
BEGIN
DECLARE num_samples int;
SELECT COUNT(*) into num_samples FROM plant_monitoring.readings
WHERE DATE(event_time) = CURRENT_DATE() AND readings.id = in_id;
RETURN num_samples;
END //
DELIMITER ;
Let’s see how this function works. Let’s create a query using the view and calculate the percentage of
occurrence for each value for each plant. The following SELECT statement accomplishes this with a bit of
mathematics. I include the rows to show you that it works.
mysql> SELECT *, max_samples_today(id) as max_samples, (num_events/max_samples_today(id)) as
percent_occurrence FROM plant_monitoring.soil_status_today;
+----+-------------+------------+-------------+--------------------+
| id | soil_status | num_events | max_samples | percent_occurrence |
+----+-------------+------------+-------------+--------------------+
| 1 | DRY | 10 | 10 | 1.0000 |
| 2 | OK | 10 | 10 | 1.0000 |
| 3 | DRY | 4 | 10 | 0.4000 |
| 3 | OK | 4 | 10 | 0.4000 |
| 3 | WET | 2 | 10 | 0.2000 |
| 4 | OK | 6 | 10 | 0.6000 |
| 4 | WET | 4 | 10 | 0.4000 |
| 5 | OK | 10 | 10 | 1.0000 |
+----+-------------+------------+-------------+--------------------+
8 rows in set (0.01 sec)
In this case, I have exactly ten sensor readings for each plant for today. This is because my test data
(which I will show you in a later section) is fixed. When I run this on my live plant-monitoring solution in my
home, my sensor readings average about one per hour with 18 to 24 readings per day.
Notice I added the function call to get the maximum number of samples (readings) and then another
column calculating the percentage of the total occurrences. But this is a lot of work. We can do it a bit easier
now that we know how to do mathematical operations in the query.
All we need to do now is add a check for the percentage, say more than 50 percent, and restrict the
rows to those with a soil_status of DRY. Thus, we take the previous query and add a few more conditions.