mysql查询，当条件满足时返回行

Lets say I have the following data in a table called wx_data (the temps aren't accurate to the dates & cities listed here)

假设我在一个名为wx_data的表中有以下数据(这些温度对这里列出的日期和城市并不准确)

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 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).

我有一个查询，它确定每个城市的最大和最小值，并返回标记为低和高的行，这取决于某一天的温度是否在观察到的最高或最低温度(在数据集中)的15%以内。

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.

我想修改查询，以便它返回上面的结果，但它只返回状态从低到高的行，反之亦然。因此，我不希望看到数据连续几天以低或高的状态返回。

How would I do that?

我该怎么做呢?

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

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.

我只希望它按城市返回它从高到低状态交换的行，反之亦然，因此如果查询正确，应该返回以下9行。

 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 an example of data and query results.

见http://www.sqlfiddle.com/ # !2/384fb/1用于数据和查询结果的示例。

2 个解决方案

#1

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 |
+-------------+----------+---------------+----------+---------------+

...and 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 extending the idea still further (bit with the sqlfiddle data set)...

编辑:…并且进一步扩展这个概念(使用sqlfiddle数据集)……

 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

http://www.sqlfiddle.com/ ! 2/384fb / 12

#2

Assuming your query is correct and you want to only show 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

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