mysql查询，满足条件时返回行 [英] mysql query, return rows when condition is satisfied
问题描述
假设我在名为 wx_data 的表中有以下数据(临时数据与此处列出的日期和城市不准确)
Lets say I have the following data in a table called wx_data (the temps aren't accurate to the dates & 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
我有一个查询，它确定每个城市的最大值和最小值，并返回标记为低和高的行，具体取决于给定日期的温度是否落在观察到的最高或最低温度的 15% 以内(在数据集).
And I have a query, that determines the max and min value for each city, and returns rows marked Low and High depending on if the temperature for a given day falls within 15% of the highest or lowest observed temp (in the 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'd like to modify the query so that it returns the above results, but it only returns the rows where the state changes from Low to High or vice versa. Therefore, I don't want to see the data returned where its a Low or High condition for several days in a row.
我该怎么做?
澄清一下，现在我的查询返回以下数据:
To clarify, right now my query returns 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
我只希望它按城市返回从高状态到低状态交换的行，反之亦然，因此如果我的查询正确，则应返回以下 9 行.
I only want it to return the rows by city where its swapped from High to Low state, and Vice Versa, so the following 9 rows should be returned if I get the query right.
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
见http://www.sqlfiddle.com/#!2/384fb/1 数据和查询结果示例.
see http://www.sqlfiddle.com/#!2/384fb/1 for an example of data and query results.
推荐答案
核心查询会不会更像这样——根据你的定义......?
Wouldn't the core query be more like this - according to 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 |
+-------------+----------+---------------+----------+---------------+
...等等...
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 |
+-------------+------------+----------+--------+
...并进一步扩展这个想法(使用 sqlfiddle 数据集)...
... and extending the idea still further (bit with the sqlfiddle data set)...
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 <> '';
http://www.sqlfiddle.com/#!2/384fb/12
这篇关于mysql查询，满足条件时返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
