> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Other Models
> Reference for additional OcientML model types beyond classification, clustering, and regression, including association rules, time series, and neural networks.
export const OcientML = "OcientML™";
{OcientML} supports other machine learning models for advanced analysis that include association rules, multi-layer perceptron (MLP), and undersampling.
To create the model, use the `CREATE MLMODEL` syntax. For details, see [CREATE MLMODEL](/machine-learning-model-functions).
Model option names are case-sensitive.
## Association Rules
Model Type: `ASSOCIATION RULES`
The association rules model trains itself over rows of arrays to suggest other associated values. The model returns values commonly associated with a set that are absent in the provided value set. In practical use, this model is often used to help in retail transactions by suggesting related items or services for purchase based on similar past transactions.
### **Model Options**
`loadBalance` — If you set this option, the database appends the `USING load_balance_shuffle = ` clause to all intermediate SQL queries the model executes during training, where `value` is the specified option value (`true` or `false`). The default value is unspecified. In this case, the database does not add this clause.
`queryInternalParallelism` — If you set this option, the database appends the `USING parallelism = ` clause to all intermediate SQL queries the model executes during training, where `value` is the specified positive integer value. The default value is unspecified. In this case, the database does not add this clause.
### **Execute the Model**
Create an association rules model. When you create the model, there should be a single input column of type `array`, with any inner type.
```sql SQL theme={null}
CREATE MLMODEL my_model
TYPE ASSOCIATION RULES ON (
SELECT
array_agg(product)
FROM public.my_table
GROUP BY customer_id
);
```
Also, after you create the model, you can see its details by querying the `sys.machine_learning_models` and `sys.machine_learning_model_options` system catalog tables.
When you execute the model, you must provide an array with the same inner type as the input column. The array represents the current state of the transaction.
You must also provide a second argument, a positive integer representing the association ranking of the item to return. The value of `1` indicates that the model should add the most-associated item to the transaction.
The third and fourth arguments are optional constant Boolean values.
If the third argument is set to `true`, the model adds the most-associated item to the transaction, based on other transactions that include the same items as the current transaction. Otherwise, only some of the items must be the same. The third argument defaults to `true`.
If the fourth argument is set to `true`, the model counts duplicate items in a transaction only once. Otherwise, the model counts duplicate items multiple times. To specify the fourth argument, you must specify the third argument. The default value for the fourth argument is `true`, which means duplicate items are counted once.
```sql SQL theme={null}
SELECT my_model(array['sausage', 'mushrooms'], 1);
SELECT my_model(array['sausage', 'mushrooms'], 1, true, true);
```
The output from the model is a tuple `( v, p, c )`. The definitions of these tuple values are:
`v` — The associated item determined by the model based on your input arguments.
`p` — The proportion of arrays in the training data that contain the items of the input array and the associated item `v`, as compared to arrays in the training data that have the input items but not necessarily the associated item `v`.
`c` — The count of occurrences of the associated item `v` in the training data arrays based on your input arguments.
After you execute a model, you can find the details about the execution results in the `sys.association_rules_models` system catalog table.
For details, see the description of the associated system catalog tables in the Machine Learning section in the [System Catalog](/system-catalog).
## Multi-Layer Perceptron (MLP)
Model Type: `MLP`
The legacy model type name `FEEDFORWARD NETWORK` is also acceptable for backward compatibility.
The MLP model is a fully connected feedforward neural network in which data flows from the inputs through hidden layers to the outputs. The number of columns in the input result set determines the number of inputs. Each input must be numeric. The last column in the input result set is the target variable. For models with one output, the column is also numeric. For models with multiple outputs, the result must be a 1xN matrix (a row vector).
Common uses of multiple output models are:
* Multi-class classification — Multiple outputs are one-hot encoded values that represent the class of the record. The model uses results with argmax to select the highest probability class.
* Probability modeling — Multiple output values represent probabilities between 0 and 1 that sum to 1.
* Multiple numeric prediction — Multiple output values represent different numeric values to predict against.
### **Model Options**
#### Required
`hiddenLayers` — You must set this option to a positive integer that specifies how many hidden layers to use.
`hiddenLayerSize` — You must set this option to a positive integer that specifies the number of nodes in each hidden layer.
`outputs` — You must set this option to a positive integer that specifies the number of outputs.
`lossFunction` — This option specifies the loss function that all hidden layer nodes and all output layer nodes use. This function can be one of several predefined loss functions or a user-defined loss function.
The predefined loss functions are `squared_error` (regression), `vector_squared_error` (vector-valued regression), `log_loss` (binary classification with target values of 0 and 1), `logits_loss` (binary classification with target values of 0 and 1), `hinge_loss` (binary classification with target values of -1 and 1), and `cross_entropy_loss` (multi-class classification). If the value for this required parameter is none of these strings, the model assumes a user-defined loss function. The user-defined loss function specifies the per-sample loss. Then, the actual loss function is the sum of this function applied to all samples. The model should use the variable `y` to refer to the dependent variable in the training data, and the model should use the variable `f` to refer to the computed estimate for a specified sample.
#### Optional
`activationFunction` — If you set this option, the values are `linear`, `relu` (rectified linear unit), `leakyrelu` (leaky rectified linear unit), `tanh` (hyperbolic tangent function), or `sigmoid` (fast sigmoid approximation). This option defaults to `relu`. This option affects all layers except the output layer.
`outputActivationFunction` — If you set this option, the values are `linear`, `relu` (rectified linear unit), `leakyrelu` (leaky rectified linear unit), `tanh` (hyperbolic tangent function), or `sigmoid` (fast sigmoid approximation). Different activation functions have different output ranges. The chosen activation function should match the dependent variable of your data. For example, if the dependent variable can be anything, then choose the `linear` value. If the dependent variable is always positive, then choose the `relu` value. If your outputs range from -1 to 1 or you perform hinge loss classification, `tanh` is a good option because the hyperbolic tangent function has the same range. However, if your outputs range from 0 to 1 or you perform log loss classification, `sigmoid` is a better choice for the same reason. This option defaults to `linear`. The option only sets the activation function for the output layer.
`metrics` — If you set this option to `true`, the model calculates the average value of the loss function.
`useSoftmax` — If you set this option to true, the model applies a softmax function to the output of the output layer before computing the loss function. This option defaults to `true` if the `lossFunction` is set to `cross_entropy_loss` and `false` otherwise.
`minInitParamValue` — If you set this option, the value must be a floating-point number that sets the minimum for initial parameter values in the optimization algorithm. This option defaults to -1.
`maxInitParamValue` — If you set this option, the value must be a floating-point number that sets the maximum for initial parameter values in the optimization algorithm. This option defaults to 1.
`normalize` — If you set this option to `true`, this option applies z-score normalization to inputs by default, storing means and standard deviation, and automatically applying them at inference. This option defaults to `true`.
`finiteDifferenceH` — A `DOUBLE` value representing the step size (h) for approximating gradients using the finite difference method. The model uses this value only if analytical gradients are not active.
This value should generally be a small positive value, typically from 0.0001 (1e-4) to 0.0000001 (1e-7).
If you do not specify this value, the default is 0.00001 (1e-5).
`loadBalance` — If you set this option, the database appends the `USING load_balance_shuffle = ` clause to all intermediate SQL queries the model executes during training where `value` is the specified option value (`true` or `false`). The default value is unspecified. In this case, the database does not add this clause.
`queryInternalParallelism` — If you set this option, the database appends the `USING parallelism = ` clause to all intermediate SQL queries the model executes during training, where `value` is the specified positive integer value. The default value is unspecified. In this case, the database does not add this clause.
`skipDropTable` — If you set this option to `false`, the database deletes any intermediate tables created during model training. If you set this option to `true`, the database prevents the deletion of any intermediate tables created during model training. The default value is `false`.
### **Execute the Model**
Create a neural network to perform multi-class classification for three possible classes. `y1`, `y2`, and `y3` are one-hot encoded outputs. If the values are 1 and the rest are 0, the value 1 denotes the class that the training data belongs to from the three classes.
```sql SQL theme={null}
CREATE MLMODEL my_model
TYPE MLP
ON (
SELECT
x1,
x2,
{{y1,y2,y3}}
FROM public.my_table
)
options(
'hiddenLayers' -> '1',
'hiddenLayerSize' -> '8',
'outputs' -> '3',
'lossFunction' -> 'cross_entropy_loss',
'activationFunction' -> 'relu',
'useSoftmax' -> 'true'
);
```
Also, after you create the model, you can see its details by querying the `sys.machine_learning_models` and `sys.machine_learning_model_options` system catalog tables.
When you execute the model later, pass `N - 1` input variables, and the model returns the estimate of the target variable. In the case of multiple outputs, the result is a 1xN matrix (a row vector). If the model uses multiple outputs to perform multi-class classification, use `argmax` to get the integer that represents the class.
```sql SQL theme={null}
SELECT argmax(my_model(x1, x2)) FROM my_table;
```
After you execute a model, you can find the details about the execution results in the `sys.mlp_models` system catalog table.
For details, see the description of the associated system catalog tables in the Machine Learning section in the [System Catalog](/system-catalog).
## Undersampling
Model Type: `UNDERSAMPLING`
The undersampling model is a preprocessing primitive that addresses class imbalance — for example, when a fraud detection data set has far more legitimate transactions than fraudulent ones. The model produces a sampled output table with a more balanced class distribution. Unlike other model types, you cannot use the undersampling model as a function in a `SELECT` statement. Instead, the model creates an output table in the `temp` system schema that contains a subset of the input data, with non-target classes downsampled relative to the target class. You can query this output table directly, for example, as input to train a downstream classifier. To find the output table name, query the `sys.undersampling_models` system catalog table.
Sampling is non-deterministic. Actual row counts in the output table might vary by approximately the square root of the expected count.
### **Model Options**
The model-specific options for Undersampling (`classColumn`, `samplingRatio`, `strategy`, `stratumColumn`, and `targetClass`) are in beta and subject to change in a future release.
#### Required
`classColumn` — This option specifies the name of the column in the training `SELECT` statement whose values define the class labels. The column can be any comparable type (for example, `VARCHAR`, `INT`).
#### Optional
`strategy` — This option specifies the sampling strategy. Accepted values are `'random'` (per-row Bernoulli sampling) and `'stratified'` (quota-based sampling that preserves per-stratum proportions). The default value is `'random'`.
`stratumColumn` — This option specifies the name of the column that defines strata for stratified sampling. The option is required when you set the `strategy` option to `'stratified'` and is not valid otherwise.
`samplingRatio` — This option specifies the ratio of non-target class rows to target class rows in the output. The value must be a positive finite double. For example, a value of `1.0` produces approximately equal counts of target and non-target rows. The default value is `1.0`.
`targetClass` — This option specifies which class label to treat as the target (minority) class. If you set this option to `'auto'`, the model automatically selects the class with the fewest rows. You can also set this option to an explicit label value that matches a value in the `classColumn`. The default value is `'auto'`.
`loadBalance` — If you set this option, the database appends the `USING load_balance_shuffle = ` clause to all intermediate SQL queries the model executes during training, where `value` is the specified option value (`true` or `false`). The default value is unspecified. In this case, the database does not add this clause.
`queryInternalParallelism` — If you set this option, the database appends the `USING parallelism = ` clause to all intermediate SQL queries the model executes during training, where `value` is the specified positive integer value. The default value is unspecified. In this case, the database does not add this clause.
`skipDropTable` — If you set this option to `false`, the database deletes any intermediate tables created during model training. If you set this option to `true`, the database prevents the deletion of any intermediate tables created during model training. The default value is `false`.
### **Execute the Model**
Create an undersampling model to balance a binary classification data set. The `label` column contains the class labels. With the default `samplingRatio` of `1.0` and `targetClass` of `'auto'`, the model selects the minority class and down-samples the majority class to approximately the same count.
```sql SQL theme={null}
CREATE MLMODEL my_undersampler
TYPE UNDERSAMPLING
ON (
SELECT *
FROM public.my_table
)
options(
'classColumn' -> 'label'
);
```
After you create the model, the sampled output table is available in the `temp` schema. You can query it by looking up the table name in the `sys.undersampling_models` system catalog table.
```sql SQL theme={null}
SELECT * FROM temp.us_my_undersampler__output;
```
The database automatically drops the output table when you execute the `DROP MLMODEL` SQL statement on the undersampling model.
Also, after you create the model, you can see its details by querying the `sys.machine_learning_models` and `sys.machine_learning_model_options` system catalog tables.
For details, see the description of the associated system catalog tables in the Machine Learning section of the [System Catalog](/system-catalog).
## Related Links
[Machine Learning Model Functions](/machine-learning-model-functions)
[Machine Learning Models](/machine-learning-models)