Martyna
145 lines
6.1 KiB
Plaintext
145 lines
6.1 KiB
Plaintext
---
|
|
title: Join Models with Tables
|
|
sidebarTitle: 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.
|
|
|
|
```sql
|
|
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 ...]
|
|
[ON d1.input_data = m1.expected_argument];
|
|
```
|
|
|
|
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. |
|
|
|
|
### Mapping input data to model arguments
|
|
|
|
If the input data contains a column named `question` and the model requires an argument named `input`, you can map these columns, as explained below.
|
|
|
|
We have a model that expects to receive `input`:
|
|
|
|
```sql
|
|
CREATE MODEL model_name
|
|
PREDICT answer
|
|
USING
|
|
engine = 'openai',
|
|
prompt_template = 'provide answers to an input from a user: {{input}}';
|
|
```
|
|
|
|
We have an input data table that has the following columns:
|
|
|
|
```sql
|
|
+----+-------------------------------------------+
|
|
| id | question |
|
|
+----+-------------------------------------------+
|
|
| 1 | How many planets are in the solar system? |
|
|
| 2 | How many stars are in the solar system? |
|
|
+----+-------------------------------------------+
|
|
```
|
|
|
|
Now if you want to get answers to these questions using the model, you need to join the input data table with the model and map the `question` column onto the `input` argument.
|
|
|
|
```sql
|
|
SELECT *
|
|
FROM input_table AS d
|
|
JOIN model_name AS m
|
|
ON d.question = m.input;
|
|
```
|
|
|
|
## Example 1
|
|
|
|
Let's join the `home_rentals` table with the `home_rentals_model` model using this statement:
|
|
|
|
```sql
|
|
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:
|
|
|
|
```sql
|
|
+------------+-----------------+-----------------+---------------------+------+----------+----------------+
|
|
| 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:
|
|
|
|
```sql
|
|
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:
|
|
|
|
```sql
|
|
+----------+------------------+
|
|
|date |forecast |
|
|
+----------+------------------+
|
|
|2019-12-31|517506.31349071994|
|
|
|2019-12-31|627822.6592658638 |
|
|
|2019-12-31|953426.9545788583 |
|
|
|2019-12-31|767252.4205039773 |
|
|
+----------+------------------+
|
|
```
|
|
|
|
<Tip>
|
|
Follow [this doc page](/generative-ai-tables#working-with-generative-ai-tables) to see examples of joining multiple data table with multiple models.
|
|
</Tip>
|