SQL
Join Models with Tables
SQL
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 bulk predictions, as shown in the examples.
Syntax
Here is the syntax:
SELECT t.column_name, p.column_name, ...
FROM integration_name.table_name [AS] t
JOIN project_name.model_name [AS] p;
On execution, we get:
+-----------------+-----------------+
| t.column_name | p.column_name |
+-----------------+-----------------+
| t.value | p.value |
+-----------------+-----------------+
Where:
| Name | Description |
|---|---|
integration_name.table_name | Name of the data source table used as input for making predictions. |
project_name.model_name | Name of the model table used to make predictions. |
p.value | Predicted value stored in the output table. |
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 |
+----------+------------------+