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.
