Mysql query, return rows when condition is met
Let's say I have the following data in the wx_data table (rates do not match the dates and cities listed here)
city, wx_date, avg_temp
Kansas City, 2012-11-01, 28
Kansas City, 2012-11-02, 42
Kansas City, 2012-11-03, 86
Kansas City, 2012-11-04, 39
Kansas City, 2012-11-05, 27
Kansas City, 2012-11-06, 65
Kansas City, 2012-11-07, 62
Kansas City, 2012-11-08, 55
Kansas City, 2012-11-09, 95
Kansas City, 2012-11-10, 90
Kansas City, 2012-11-11, 29
Saint Louis, 2012-11-01, 88
Saint Louis, 2012-11-02, 42
Saint Louis, 2012-11-03, 30
Saint Louis, 2012-11-04, 60
Saint Louis, 2012-11-05, 85
Saint Louis, 2012-11-06, 65
Saint Louis, 2012-11-07, 62
Saint Louis, 2012-11-08, 32
Saint Louis, 2012-11-09, 80
Saint Louis, 2012-11-10, 80
Saint Louis, 2012-11-11, 33
And I have a query that defines the maximum and minimum value for each city and returns rows marked "Low" and "High" depending on whether the temperature for a given day is within 15% of the highest or lowest observed temperature (to dataset).
select
temp.city,
wx_date,
avg_tmp,
if(avg_tmp >=.85*temp.High, "High", "Low")
from
wx_data
inner join (select city,
Min(avg_tmp) as Low,
Max(avg_tmp) as High
from
wx_data
where
wx_date between '2012-11-02' and '2013-12-01'
group by city) as temp
on wx_data.city=temp.city
where
avg_tmp >= .85 * temp.High
or avg_tmp <= 1.15 * temp.Low
order by
city,
wx_date;
I would like to modify the query so that it returns the above results, but it only returns rows where the state changes from Low to High or vice versa. So I don't want the data to go back to where its Low or High state has been for several days in a row.
How should I do it?
To clarify, right now my query is returning the following data:
Kansas City November, 01 2012 28 Low
Kansas City November, 03 2012 86 High
Kansas City November, 04 2012 29 Low
Kansas City November, 05 2012 27 Low
Kansas City November, 09 2012 95 High
Kansas City November, 10 2012 90 High
Kansas City November, 11 2012 29 Low
Saint Louis November, 01 2012 33 Low
Saint Louis November, 02 2012 88 High
Saint Louis November, 03 2012 87 High
Saint Louis November, 05 2012 85 High
Saint Louis November, 08 2012 32 Low
Saint Louis November, 09 2012 80 High
Saint Louis November, 10 2012 80 High
Saint Louis November, 11 2012 33 Low
I want it to return rows by city where it swapped from high to low and vice versa, so the next 9 rows should be returned if I get the correct query.
Kansas City November, 01 2012 28 Low
Kansas City November, 03 2012 86 High
Kansas City November, 04 2012 29 Low
Kansas City November, 09 2012 95 High
Kansas City November, 11 2012 29 Low
Saint Louis November, 02 2012 88 High
Saint Louis November, 08 2012 32 Low
Saint Louis November, 09 2012 80 High
Saint Louis November, 11 2012 33 Low
see http://www.sqlfiddle.com/#!2/384fb/1 for example data and query results.
+3
source to share
2 answers
Wouldn't the main query be more like this - as per your definition ...?
SELECT city
, MAX(avg_temp) max_temp
, 0.85*MAX(avg_temp) max_threshold
, MIN(avg_temp) min_temp
, 1.15 * MIN(avg_temp) min_threshold
FROM wx_data
GROUP
BY city ;
+-------------+----------+---------------+----------+---------------+
| city | max_temp | max_threshold | min_temp | min_threshold |
+-------------+----------+---------------+----------+---------------+
| Kansas City | 95 | 80.75 | 27 | 31.05 |
| Saint Louis | 88 | 74.80 | 30 | 34.50 |
+-------------+----------+---------------+----------+---------------+
... so...
SELECT x.*
, CASE WHEN x.avg_temp BETWEEN y.min_temp AND y.min_threshold THEN 'Low'
WHEN x.avg_temp BETWEEN y.max_threshold AND y.max_temp THEN 'High'
ELSE ''
END status
FROM wx_data x
JOIN
( SELECT city
, MAX(avg_temp) max_temp
, 0.85*MAX(avg_temp) max_threshold
, MIN(avg_temp) min_temp
, 1.15 * MIN(avg_temp) min_threshold
FROM wx_data GROUP BY city
) y
ON y.city = x.city;
+-------------+------------+----------+--------+
| city | wx_date | avg_temp | status |
+-------------+------------+----------+--------+
| Kansas City | 2012-11-01 | 28 | Low |
| Kansas City | 2012-11-02 | 42 | |
| Kansas City | 2012-11-03 | 86 | High |
| Kansas City | 2012-11-04 | 39 | |
| Kansas City | 2012-11-05 | 27 | Low |
| Kansas City | 2012-11-06 | 65 | |
| Kansas City | 2012-11-07 | 62 | |
| Kansas City | 2012-11-08 | 55 | |
| Kansas City | 2012-11-09 | 95 | High |
| Kansas City | 2012-11-10 | 90 | High |
| Kansas City | 2012-11-11 | 29 | Low |
| Saint Louis | 2012-11-01 | 88 | High |
| Saint Louis | 2012-11-02 | 42 | |
| Saint Louis | 2012-11-03 | 30 | Low |
| Saint Louis | 2012-11-04 | 60 | |
| Saint Louis | 2012-11-05 | 85 | High |
| Saint Louis | 2012-11-06 | 65 | |
| Saint Louis | 2012-11-07 | 62 | |
| Saint Louis | 2012-11-08 | 32 | Low |
| Saint Louis | 2012-11-09 | 80 | High |
| Saint Louis | 2012-11-10 | 80 | High |
| Saint Louis | 2012-11-11 | 33 | Low |
+-------------+------------+----------+--------+
EDIT: ... and expanding on the idea even further (a bit with the sqlfiddle dataset) ...
SELECT a.city,a.wx_date,a.avg_tmp FROM
(
SELECT x.*
, IF(@prev = CASE WHEN x.avg_tmp BETWEEN y.min_tmp AND y.min_threshold THEN 'Low'
WHEN x.avg_tmp BETWEEN y.max_threshold AND y.max_tmp THEN 'High'
ELSE ''
END, @i := 0, @i:=1) flag
, @prev := CASE WHEN x.avg_tmp BETWEEN y.min_tmp AND y.min_threshold THEN 'Low'
WHEN x.avg_tmp BETWEEN y.max_threshold AND y.max_tmp THEN 'High'
ELSE ''
END status
FROM wx_data x
JOIN
( SELECT city
, MAX(avg_tmp) max_tmp
, 0.85*MAX(avg_tmp) max_threshold
, MIN(avg_tmp) min_tmp
, 1.15 * MIN(avg_tmp) min_threshold
FROM wx_data GROUP BY city
) y
ON y.city = x.city
JOIN (SELECT @i:=NULL,@prev:=NULL) vars
ORDER
BY city,wx_date
) a
WHERE flag = 1 AND status <> '';
+2
source to share
Assuming your query is correct and you only want to show the rows where the high / low value changes:
select city, wx_date, avg_tmp, hi_lo
from (
select temp.city, wx_date, avg_tmp,
if(avg_tmp >=.85*temp.High,"High","Low") hi_lo,
@prevHiLo = (avg_tmp >=.85*temp.High and @prevCity = temp.city) same_as_prev,
@prevHiLo := (avg_tmp >=.85*temp.High),
@prevCity := temp.city
from wx_data
inner join
(select city, Min(avg_tmp) as Low, Max(avg_tmp) as High from wx_data
where (wx_date between '2012-11-02' and '2013-12-01') group by city)
as temp on wx_data.city=temp.city
where (avg_tmp >= .85*temp.High or avg_tmp <= 1.15*temp.Low)
order by city, wx_date
) t1
where same_as_prev = 0
order by city, wx_date
http://www.sqlfiddle.com/#!2/b6f42/2
CITY WX_DATE AVG_TMP HI_LO
Kansas City November, 03 2012 00:00:00+0000 86 High
Kansas City November, 04 2012 00:00:00+0000 29 Low
Kansas City November, 09 2012 00:00:00+0000 95 High
Kansas City November, 11 2012 00:00:00+0000 29 Low
Saint Louis November, 02 2012 00:00:00+0000 88 High
Saint Louis November, 08 2012 00:00:00+0000 32 Low
Saint Louis November, 09 2012 00:00:00+0000 80 High
Saint Louis November, 11 2012 00:00:00+0000 33 Low
+1
source to share