Find closest lower date among two sets of dates with big data set Mysql

Question

I have two tables

• "visit" which basically stores each visit on a website

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

But the size of the tables make it impossible to compute it in a "reasonnable" time (10^14 steps):

    | 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

Maybe something like this:

select
    V.*,
    (
        select
            MAX(weatherdate) 
        from Weather W
        where
            W.weatherdate < V.visitdate and
            W.city = V.city
    ) beforedate
from Visit V
where
    exists ( select 1 from Weather where V.city = W.city)

Answer 2

Try this:

 SELECT t.visitdate, t.city, max(d.weatherdate) beforedate
  FROM visit t inner JOIN cityweather d
  on t.city=d.city
  group by t.city,t.visitdate

Answer 3

I'm not sure if that's what you need, but it should do the trick.

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;

Answer 4

Alternative method, avoiding the MAX()

SELECT v.visitdate, v.city, w.weatherdate AS beforedate
FROM visit v
JOIN cityweather w
        ON v.city = w.city
        AND v.visitdate >= w.weatherdate
        AND NOT EXISTS ( SELECT * FROM cityweather nx
                WHERE nx.city = v.city
                AND nx.weatherdate <= v.visitdate
                AND nx.weatherdate > w.weatherdate
        );

Answer 5

I ended up finding the answer myself. It all comes down to narrowing the selection on the table cityweather. So I did it in two steps to avoid the O(n^2) problem we had up until now and I reduce the size of the first table (sometimes virtual table) found in other answers:

First step (the crucial one):

CREATE TABLE intermedtable 
   SELECT t.city, t.visitdate, d.weatherdate
      FROM visit as t 
      JOIN cityweather as d
      WHERE d.city=t.city AND d.weatherdate <= t.visitdate AND d.weatherdate +  interval 1 day >= t.visitdate;

What is crucial here compared to what we had before is the d.weatherdate + interval 1 day >= t.visitdate conditions. It "only" took 22 minutes.

The second step is to find the MAX(weatherdate) for each pair (city, visitdate) :

Create table beforedatetable
   SELECT city, visitdate, max(weatherdate) as beforedate 
       FROM intermedtable
       GROUP BY city, visitdate;

With this solution I go from the 16 hours computation (with crash at the end) down to 32 minutes.

The core of this answer was to reduce the size of the virtual table created in previous answer by adding the d.weatherdate + interval 1 day >= t.visitdate condition. This is based on the fact that the weatherdate of interest can't be more than one day away from the visitdate.