> ## Documentation Index
> Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt
> Use this file to discover all available pages before exploring further.

# ML Functions

<a id="sql-ml-functions" />

There are two supported machine learning functions:

* [PREDICT](/content/sql/query/ml-functions#sql-ml-functions-predict)
* [OUTLIERS](/content/sql/query/ml-functions#sql-ml-functions-outliers)

<AccordionGroup>
  <Accordion title="PREDICT" id="predict" defaultOpen>
    <a id="sql-ml-functions-predict" />

    The `PREDICT` table function will predict the values of the dependent
    variables that correspond to a given column of independent variables, using a
    given base table containing "historical" values of each.  This table will be
    used as the basis to calculate the prediction.

    To make the prediction, the slope & y-intercept of the least-squares-fit linear
    equation of the base table data will be calculated.  Then, that line will be
    used to calculate the dependent variable for each given independent variable,
    and the values of each will be returned in the result set as `Y` and `X`,
    respectively.

    The basic form of the `PREDICT` function, called within a `SELECT` statement
    follows.

    ```sql title="PREDICT Table Function Syntax" theme={null}
    SELECT x, y FROM TABLE
    (
    	PREDICT
    	(
    		HISTORY_TABLE     => INPUT_TABLE(<table name | select statement>),
    		X_COLUMN          => < '<column name>' | <column position> >,
    		Y_COLUMN          => < '<column name>' | <column position> >,
    		PREDICT_ON_TABLE  => INPUT_TABLE(<table name | select statement>),
    		PREDICT_ON_COLUMN => < '<column name>' | <column position> >,
    		[PREDICT_METHOD   => 'LINEAR']
    	)
    )
    ```

    <AccordionGroup>
      <Accordion title="HISTORY_TABLE" id="history_table" defaultOpen>
        The name of the table containing the "historical" data upon which the prediction will be made.

        To make a prediction from the data based on the *ticket\_prices* table, pass the name of the table
        to `INPUT_TABLE`:

        ```
        INPUT_TABLE(ticket_prices)
        ```

        To make a prediction from data that is the result of a query, pass the query to `INPUT_TABLE`:

        ```
        INPUT_TABLE
        (
            SELECT * FROM ticket_prices_local
            UNION
            SELECT * FROM ticket_prices_global
        )
        ```
      </Accordion>

      <Accordion title="X_COLUMN" id="x_column" defaultOpen>
        The name or position of the column in `HISTORY_TABLE` containing the independent variable that
        will be used as the basis for the prediction.
      </Accordion>

      <Accordion title="Y_COLUMN" id="y_column" defaultOpen>
        The name or position of the column in `HISTORY_TABLE` containing the dependent variable that
        will be used as the basis for the prediction.
      </Accordion>

      <Accordion title="PREDICT_ON_TABLE" id="predict_on_table" defaultOpen>
        The name of the table containing the independent variable against which the prediction will be
        made.

        To make a prediction against column data in the *future\_years* table, pass the name of the table
        to `INPUT_TABLE`:

        ```
        INPUT_TABLE(future_years)
        ```

        To make a prediction against data that is the result of a query, pass the query to
        `INPUT_TABLE`:

        ```
        INPUT_TABLE
        (
            SELECT * FROM future_years
            UNION
            SELECT * FROM future_decades
        )
        ```
      </Accordion>

      <Accordion title="PREDICT_ON_COLUMN" id="predict_on_column" defaultOpen>
        The name or position of the column in `PREDICT_ON_TABLE` containing the independent variable
        against which the prediction will be made.
      </Accordion>

      <Accordion title="PREDICT_METHOD" id="predict_method" defaultOpen>
        The optional calculation method to use to make the prediction.  The only supported (and default)
        method is `LINEAR`.
      </Accordion>
    </AccordionGroup>

    To predict ticket prices for years found in the *future\_years* table, based on
    historical ticket price data found in the *ticket\_prices* table:

    ```sql PREDICT Example theme={null}
    SELECT * FROM TABLE
    (
    	PREDICT
    	(
    		HISTORY_TABLE => INPUT_TABLE(example.ticket_prices),
    		X_COLUMN => 'year',
    		Y_COLUMN => 'cost',
    		PREDICT_ON_TABLE => INPUT_TABLE(example.future_years),
    		PREDICT_ON_COLUMN => 'year'
    	)
    )
    ```
  </Accordion>

  <Accordion title="OUTLIERS" id="outliers" defaultOpen>
    <a id="sql-ml-functions-outliers" />

    The `OUTLIERS` table function will calculate the outliers in a given data set,
    based on a specified calculation type, threshold, and partition column.  The
    partition column allows the data to be segmented into subsets, one per unique
    partition column value, and have the outliers for each subset calculated &
    determined independently from other subsets.

    The basic form of the `OUTLIERS` function, called within a `SELECT`
    statement follows.

    ```sql title="OUTLIERS Table Function Syntax" theme={null}
    SELECT * FROM TABLE
    (
    	OUTLIERS
    	(
    		DATA_TABLE        => INPUT_TABLE(<table name | select statement>),
    		DATA_COLUMN       => < '<column name>' | <column position> >,
    		[PARTITION_COLUMN => < '<column name>' | <column position> >,]
    		[OUTLIER_METHOD   => < 'ZSCORE' | 'PERCENTILE' >,]
    		[THRESHOLD_LOW    => <threshold value>,]
    		[THRESHOLD_HIGH   => <threshold value>,]
    		[OUTPUT_DATA      => < 'OUTLIERS' | 'NON_OUTLIERS' | 'ALL' >,]
    		[OUTPUT_SCORE     => < TRUE | FALSE >]
    	)
    )
    ```

    <AccordionGroup>
      <Accordion title="DATA_TABLE" id="data_table" defaultOpen>
        The name of the data table within which outliers will be detected.

        To detect outliers in the data in the *employee* table, pass the name of the table to
        `INPUT_TABLE`:

        ```
        INPUT_TABLE(employee)
        ```

        To detect outliers in the data that is the result of a query, pass the query to `INPUT_TABLE`:

        ```
        INPUT_TABLE
        (
            SELECT * FROM employee_east
            UNION
            SELECT * FROM employee_west
        )
        ```
      </Accordion>

      <Accordion title="DATA_COLUMN" id="data_column" defaultOpen>
        The name or position of the column in `DATA_TABLE` containing the outliers to detect.
      </Accordion>

      <Accordion title="PARTITION_COLUMN" id="partition_column" defaultOpen>
        The name or position of the column in `DATA_TABLE` containing the value to partition over, when
        detecting outliers.  Each unique `PARTITION_COLUMN` value will denote a subset of the source
        data, within which outliers will be determined independently from other subsets.
      </Accordion>

      <Accordion title="OUTLIER_METHOD" id="outlier_method" defaultOpen>
        The optional calculation method to use to detect outliers.  The default method is `ZSCORE`.

        <div>
          <table class="table w-full [&_td]:min-w-[150px] [&_th]:text-left [&_td[data-numeric]]:tabular-nums">
            <thead>
              <tr>
                <th>Method</th>
                <th>Description</th>
              </tr>
            </thead>

            <tbody>
              <tr>
                <td><code>ZSCORE</code></td>
                <td>Z-score calculation, indicating the number of standard deviations above the mean each value within the set is.  If a <code>PARTITION\_COLUMN</code> is given, the z-score will be calculated for each subset of data corresponding to a unique <code>PARTITION\_COLUMN</code> value.  The z-score uses the following formula: <pre><code>(DATA\_COLUMN - AVG(DATA\_COLUMN)) / STDDEV(DATA\_COLUMN)
                Scores will be decimals centered around `0`.</code></pre></td>
              </tr>

              <tr>
                <td><code>PERCENTILE</code></td>
                <td>The standard percentile calculation, performed within each <code>PARTITION\_COLUMN</code> group (if specified).  Scores will be decimals between <code>0</code>, inclusive, and <code>100</code>, exclusive.</td>
              </tr>
            </tbody>
          </table>
        </div>
      </Accordion>

      <Accordion title="THRESHOLD_LOW" id="threshold_low" defaultOpen>
        The lower bound to use for the determination of outliers.  Records with scores lower than this
        value will be considered outliers.  If not given, no lower bound will be applied.  The value
        should match the `OUTLIER_METHOD` used:

        * `ZSCORE` - Threshold is the negative number of standard deviations to the left of the mean,
          beyond which outliers are found; e.g., `-3` would indicate a threshold of *3* standard
          deviations lower than the mean.
        * `PERCENTILE` - Threshold is the percentage lower than which outliers are found;
          e.g., `25` would indicate outliers have percentile scores below *25%*.
      </Accordion>

      <Accordion title="THRESHOLD_HIGH" id="threshold_high" defaultOpen>
        The upper bound to use for the determination of outliers.  Records with scores higher than this
        value will be considered outliers.  If not given, no upper bound will be applied.  The value
        should match the `OUTLIER_METHOD` used:

        * `ZSCORE` - Threshold is the positive number of standard deviations to the right of the mean,
          beyond which outliers are found; e.g., `3` would indicate a threshold of *3* standard
          deviations higher than the mean.
        * `PERCENTILE` - Threshold is the percentage higher than which outliers are found;
          e.g., `75` would indicate outliers have percentile scores above *75%*.
      </Accordion>

      <Accordion title="OUTLIER_DATA" id="outlier_data" defaultOpen>
        The selection of source records to return in the result set.  The default is `OUTLIERS`.

        <div>
          <table class="table w-full [&_td]:min-w-[150px] [&_th]:text-left [&_td[data-numeric]]:tabular-nums">
            <thead>
              <tr>
                <th>Method</th>
                <th>Description</th>
              </tr>
            </thead>

            <tbody>
              <tr>
                <td><code>OUTLIERS</code></td>
                <td>Return only the records that are outliers.</td>
              </tr>

              <tr>
                <td><code>NON\_OUTLIERS</code></td>
                <td>Return only the records that are not outliers.</td>
              </tr>

              <tr>
                <td><code>ALL</code></td>
                <td>Return all records.</td>
              </tr>
            </tbody>
          </table>
        </div>
      </Accordion>

      <Accordion title="OUTPUT_SCORE" id="output_score" defaultOpen>
        If `TRUE`, the calculated score for each record will be added to the result set in a column
        named `OUTPUT_SCORE`.
      </Accordion>
    </AccordionGroup>

    To find the outlier employee salaries in the *employee* table that are
    *1 standard deviation* away from the mean, using the *z-score* method:

    ```sql Outliers Using Z-Score Example theme={null}
    SELECT * FROM TABLE
    (
    	OUTLIERS
    	(
    		DATA_TABLE     => INPUT_TABLE(example.employee),
    		DATA_COLUMN    => 'salary',
    		THRESHOLD_LOW  => -1,
    		THRESHOLD_HIGH => 1
    	)
    )
    ```

    To show the percentile for non-outlier employee salaries by department in the
    *employee* table, between the *25th* & *75th* percentiles, using the
    *percentile* method:

    ```sql Non-Outliers Using Percentile Example theme={null}
    SELECT * FROM TABLE
    (
    	OUTLIERS
    	(
    		DATA_TABLE       => INPUT_TABLE(example.employee),
    		DATA_COLUMN      => 'salary',
    		PARTITION_COLUMN => 'dept_id',
    		OUTLIER_METHOD   => 'PERCENTILE',
    		THRESHOLD_LOW    => 25,
    		THRESHOLD_HIGH   => 75,
    		OUTPUT_DATA      => 'NON_OUTLIERS',
    		OUTPUT_SCORE     => TRUE
    	)
    )
    ```
  </Accordion>
</AccordionGroup>


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.