Chapter 5 ■ MySQL priMerExample 2: A Complex Query
Let’s now look at a more complex query. Or rather one that is simple in its formation but not trivial in
implementation. In this case, consider the query, “Which plants need watering?” For this, we need to know
which of the plants have a moisture value that is below our threshold of DRY/OK/WET. Since we use a
calculated column, we do not have to see the actual sensor value. That is, we can query the readings table
and discover which are marked DRY.
You may think that we just need to query the readings table for any value of DRY for the sensor
readings for today. This will get you close, but it likely result in a number of rows, but what if you or an
automatic plant-watering system waters the plant or what if it rains? You could have only a few readings of
DRY but more readings of OK or WET. In this case, the plant may not need watering at all.
You may also consider selecting the most recent sensor reading for each plant. Indeed, this is what most
people would do. But that won’t cover situations where the sensors are reading values on the borderline
or produce spurious readings. For example, the hobbyist-level soil moisture sensors (in other words, the
affordable ones) are not 100 percent accurate and can produce slightly inconsistent readings. They are
perfectly fine for a “broad” reading so long as you understand you should review several readings either
averaging or considering the percentage of readings of one range versus another.
What we need is a way to determine those plant readings of DRY that are more frequent than the other
values, which requires a bit of mathematics. And you thought this was simple. Well, it is if you break it down
into smaller parts, which is why I chose this example. Let’s break this down into parts. We begin with the
most basic statement of what we want.
More specifically, we want to know the current day’s soil status for all plants. We will count the values
of soil_status and determine the percentage of the occurrence based on the total readings for the day. We
can use that information later to determine which plants need watering.
To make this work, we’re going to use a concept in MySQL called a view. Recall a view is a logical
representation of a result set and can be treated like a table in other SELECT statements. The following shows
the view to retrieve the current day’s soil status:
CREATE VIEW soil_status_today AS
SELECT id, soil_status, count(soil_status) as num_events FROM plant_monitoring.readings
WHERE DATE(event_time) = CURRENT_DATE() GROUP BY id, soil_status;
Notice also I used a GROUP BY clause to aggregate the values counting how many were each status value.
Let’s see an example result. Notice I query the view just like a table.
mysql> SELECT * FROM plant_monitoring.soil_status_today;
+----+-------------+------------+
| id | soil_status | num_events |
+----+-------------+------------+
| 1 | DRY | 10 |
| 2 | OK | 10 |
| 3 | DRY | 4 |
| 3 | OK | 4 |
| 3 | WET | 2 |
| 4 | OK | 6 |
| 4 | WET | 4 |
| 5 | OK | 10 |
+----+-------------+------------+
8 rows in set (0.01 sec)