ML Functions - Kinetica Docs

There are two supported machine learning 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.

PREDICT Table Function Syntax

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']
	)
)

HISTORY_TABLE

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
)

X_COLUMN

The name or position of the column in HISTORY_TABLE containing the independent variable that will be used as the basis for the prediction.

Y_COLUMN

The name or position of the column in HISTORY_TABLE containing the dependent variable that will be used as the basis for the prediction.

PREDICT_ON_TABLE

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
)

PREDICT_ON_COLUMN

The name or position of the column in PREDICT_ON_TABLE containing the independent variable against which the prediction will be made.

PREDICT_METHOD

The optional calculation method to use to make the prediction. The only supported (and default) method is LINEAR.

To predict ticket prices for years found in the future_years table, based on historical ticket price data found in the ticket_prices table:

PREDICT Example

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'
	)
)

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.

OUTLIERS Table Function Syntax

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 >]
	)
)

DATA_TABLE

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
)

DATA_COLUMN

The name or position of the column in DATA_TABLE containing the outliers to detect.

PARTITION_COLUMN

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.

OUTLIER_METHOD

The optional calculation method to use to detect outliers. The default method is ZSCORE.

Method	Description
`ZSCORE`	Z-score calculation, indicating the number of standard deviations above the mean each value within the set is. If a `PARTITION_COLUMN` is given, the z-score will be calculated for each subset of data corresponding to a unique `PARTITION_COLUMN` value. The z-score uses the following formula: `(DATA_COLUMN - AVG(DATA_COLUMN)) / STDDEV(DATA_COLUMN) Scores will be decimals centered around 0.`
`PERCENTILE`	The standard percentile calculation, performed within each `PARTITION_COLUMN` group (if specified). Scores will be decimals between `0`, inclusive, and `100`, exclusive.

THRESHOLD_LOW

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%.

THRESHOLD_HIGH

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%.

OUTLIER_DATA

The selection of source records to return in the result set. The default is OUTLIERS.

Method	Description
`OUTLIERS`	Return only the records that are outliers.
`NON_OUTLIERS`	Return only the records that are not outliers.
`ALL`	Return all records.

OUTPUT_SCORE

If TRUE, the calculated score for each record will be added to the result set in a column named OUTPUT_SCORE.

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

Outliers Using Z-Score Example

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:

Non-Outliers Using Percentile Example

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
	)
)

​PREDICT

​OUTLIERS

PREDICT

OUTLIERS