Join Models with Tables

Description

The JOIN clause combines rows from the database table and the model table on a column defined in its implementation. It is used to make batch predictions, as shown in the examples.

Syntax

Here is the syntax that lets you join multiple data tables with multiple models to get all predictions at once.

SELECT d1.column_name,
       d2.column_name,
       m1.column_name,
       m2.column_name,
       ...
FROM integration_name.table_name_1 [AS] d1
[JOIN integration_name.table_name_2 [AS] d2 ON ...]
[JOIN ...]
JOIN project_name.model_name_1 [AS] m1
[JOIN project_name.model_name_2 [AS] m2]
[JOIN ...];

Where:

Name	Description
integration_name.table_name_1	Name of the data source table used as input for making predictions.
integration_name.table_name_2	Optionally, you can join arbitrary number of data source tables.
project_name.model_name_1	Name of the model table used to make predictions.
project_name.model_name_2	Optionally, you can join arbitrary number of models.

Example 1

Let’s join the home_rentals table with the home_rentals_model model using this statement:

SELECT t.rental_price AS real_price, 
       m.rental_price AS predicted_price,
       t.number_of_rooms,  t.number_of_bathrooms,
       t.sqft, t.location, t.days_on_market 
FROM example_db.demo_data.home_rentals AS t 
JOIN mindsdb.home_rentals_model AS m 
LIMIT 20;

On execution, we get:

+------------+-----------------+-----------------+---------------------+------+----------+----------------+
| real_price | predicted_price | number_of_rooms | number_of_bathrooms | sqft | location | days_on_market |
+------------+-----------------+-----------------+---------------------+------+----------+----------------+
| 3901       | 3886            | 2               | 1                   | 917  | great    | 13             |
| 2042       | 2007            | 0               | 1                   | 194  | great    | 10             |
| 1871       | 1865            | 1               | 1                   | 543  | poor     | 18             |
| 3026       | 3020            | 2               | 1                   | 503  | good     | 10             |
| 4774       | 4748            | 3               | 2                   | 1066 | good     | 13             |
| 4382       | 4388            | 3               | 2                   | 816  | poor     | 25             |
| 2269       | 2272            | 0               | 1                   | 461  | great    | 6              |
| 2284       | 2272            | 1               | 1                   | 333  | great    | 6              |
| 5420       | 5437            | 3               | 2                   | 1124 | great    | 9              |
| 5016       | 4998            | 3               | 2                   | 1204 | good     | 7              |
| 1421       | 1427            | 0               | 1                   | 538  | poor     | 43             |
| 3476       | 3466            | 2               | 1                   | 890  | good     | 6              |
| 5271       | 5255            | 3               | 2                   | 975  | great    | 6              |
| 3001       | 2993            | 2               | 1                   | 564  | good     | 13             |
| 4682       | 4692            | 3               | 2                   | 953  | good     | 10             |
| 1783       | 1738            | 1               | 1                   | 493  | poor     | 24             |
| 1548       | 1543            | 1               | 1                   | 601  | poor     | 47             |
| 1492       | 1491            | 0               | 1                   | 191  | good     | 12             |
| 2431       | 2419            | 0               | 1                   | 511  | great    | 1              |
| 4237       | 4257            | 3               | 2                   | 916  | poor     | 36             |
+------------+-----------------+-----------------+---------------------+------+----------+----------------+

Example 2

Let’s query a time series model using this statement:

SELECT m.saledate as date,
       m.ma AS forecast
FROM mindsdb.house_sales_model AS m 
JOIN example_db.demo_data.house_sales AS t
WHERE t.saledate > LATEST
AND t.type = 'house'
LIMIT 4;

On execution, we get:

+----------+------------------+
|date      |forecast          |
+----------+------------------+
|2019-12-31|517506.31349071994|
|2019-12-31|627822.6592658638 |
|2019-12-31|953426.9545788583 |
|2019-12-31|767252.4205039773 |
+----------+------------------+

Follow this doc page to see examples of joining multiple data table with multiple models.