mysql - 使用大数据集Mysql在两组日期中找到最接近的较低日期

Question

我有两张 table

• “访问”，基本上将每次访问存储在网站上

    | visitdate           | city     |    ----------------------------------    | 2014-12-01 00:00:02 | Paris    |    | 2015-01-03 00:00:02 | Marseille|

• "cityweather" that stores weather infos 3 times a day for a lot of cities

    | weatherdate           | city     | temp |    -------------------------------------------    | 2014-12-01 09:00:02   | Paris    | 20   |    | 2014-12-01 09:00:02   | Marseille| 22   |

I precise that there can be cities in the table visit that are not in cityweather and vice versa and I need to only take cities that are common to both tables.

So my question is :

How can I SELECT for each visitdate the MAX(weatherdate) that is inferior to the visitdate ?

It should look like this :

    | visitdate           | city     | beforedate          |    --------------------------------------------------------    | 2014-12-01 00:00:02 | Paris    | 2014-11-30 21:00:00 |    | 2015-01-03 15:07:26 | Marseille| 2015-01-03 09:00:00 |

I tried something like this :

SELECT t.city, t.visitdate, d.weatherdate as beforedate
    FROM visitsub as t
    JOIN cityweatherfrsub as d
    ON  d.weatherdate = 
        ( SELECT MAX(d.weatherdate)
            FROM cityweatherfrsub
            WHERE d.weatherdate <= t.visitdate AND d.city=t.city
        )
    AND d.city = t.city;

但是表的大小使得不可能在“合理”的时间内(10^14 步)计算它:

    | id | select_type        | table       | type  | possible_keys         | key          | key_len | ref          | rows    | Extra                     |    ---------------------------------------------------------------------------------------------------------------------------------------------------------    | 1  | PRIMARY            | d           | ALL   | idx_city,Idx_citydate | NULL         | NULL    | NULL         | 1204305 | Using where               |    | 1  | PRIMARY            | t           | ref   | Idxcity, Idxcitydate  | Idxcitydate  | 303     | meteo.d.city | 111     | Using where; Using index  |    | 2  | DEPENDANT SUBQUERY | cityweather | index | NULL                  | Idx_date     | 6       | NULL         | 1204305 | Using where; Using index  |

I am now investigating the field of user-variable like @variable but I am very new to it and only wrote something that is not working Error Code: 1111. Invalid use of group function:

SET @j :=0;
SET @k :=0;
SET @l :=0;
SET @m :=0;
CREATE TABLE intermedweather
    SELECT @l as city, @k as visitdate, @j as beforedate
    FROM visitsub t
    JOIN cityweatherfrsub d
    WHERE (@j := d.weatherdate) <= (@k := t.visitdate) 
      AND (@l := d.city) = (@m := t.city) 
      AND  @j = MAX(d.weatherdate);

You can find here a similar post but it can't work for my problem

Answer 1

我不确定这是否是您所需要的，但它应该可以解决问题。

SELECT t.visitdate, d.city, MAX(d.weatherdate) as beforedate
   FROM cityweather d
   JOIN visit t
   ON d.weatherdate <= t.visitdate
   AND d.city=t.city
   GROUP BY t.visitdate, d.city;