Clustering and Dimension Reduction Models

{{ocientml}} supports these machine learning models for clustering and dimension reduction to create the model, use the create mlmodel syntax for details, see docid\ dsqzzzggetdfwh5qasvhg model option names are case sensitive principal component analysis model type principal component analysis principal component analysis (pca) is typically not used as a model on its own pca is most commonly used on the inputs to other models pca serves two purposes pca normalizes all numeric feature data some types of models are sensitive to the scale of numeric features, and when different features have different scales, the results end up skewed pca normalizes all features to the same scale pca is used for dimensionality reduction pca computes linear combinations of the original features to put the most signal into a smaller number of new features the input result set when creating a pca model is n numeric columns, which are all features there is no label or dependent variable after you create a pca model, the sys principal component analysis models system catalog table contains information on the percentage of the signal in each pca feature you can use this information to figure out how many of the output features to keep also, a fter 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 principal component analysis and logistic regression you can also use pca models as inputs to other models for example, if you have three features and you want to use pca to reduce the number to two features, you can execute the following sql statements create mlmodel reduceto2 type principal component analysis on ( select c1, c2, c3 from public my table ); you can use this model as input for another model, for example, logistic regression create mlmodel binaryclass type logistic regression on ( select reduceto2(c1, c2, c3, 1), reduceto2(c1, c2, c3, 2), label from … ); to correctly use this model later, you must pass the original features through the pca model when you execute the logistic regression model select binaryclass( reduceto2(x1, x2, x3, 1), reduceto2(x1, x2, x3, 2) ) from … ; similarly, to create a pca analysis over four variables, execute this sql statement create mlmodel my model type principal component analysis on ( select c1, c2, c3, c4 from public my table ); model options loadbalance — if you set this option , the database appends the using load balance shuffle = \<value> 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 = \<value> 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 false , the database deletes any intermediate tables created during model training if true , the database prevents the deletion of any intermediate tables created during model training this option defaults to false k means clustering model type kmeans k means is an unsupervised clustering algorithm all of the columns in the input result set are features, and there is no label all the input columns must be numeric the algorithm finds k points such that all points are classified by the closest k points distance calculations are euclidean by default model options required k — this option must be a positive integer the option specifies the algorithm for how many clusters to make optional epsilon — if you specify this option, the value must be a valid positive floating point value when the maximum distance that a centroid moves from one iteration of the algorithm to the next is less than this value, the algorithm terminates this parameter defaults to 1e 8 l1norm — if you set this option to true , the model uses the l1 distance instead of the euclidian distance this option defaults to false normalize — if you set this option to true , the model normalizes the data before the start of training normalization means to shift the mean to 0, and scale the data so the standard deviation is 1 loadbalance — if you set this option , the database appends the using load balance shuffle = \<value> 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 = \<value> 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 false , the database deletes any intermediate tables created during model training if true , the database prevents the deletion of any intermediate tables created during model training this option defaults to false execute the model create a k means model with 8 clusters create mlmodel my model type kmeans on ( select x1, x2, x3, x4 from public my table ) options( 'k' > '8' ); also, a fter 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 because there are no labels for clusters, when you execute this function after training with the same number (and same order) of features as input, the result is an integer that specifies the cluster to which the point belongs select my model(x1, x2, x3, x4) from my table; after you execute a model, you can find the details about the execution results in the sys k means models system catalog table for details, see the description of the associated system catalog tables in the machine learning section in the https //docs ocient com/system catalog#pfo j gaussian mixture model type gaussian mixture model gaussian mixture models (gmms) find the mixture of n gaussian distributions that best fit the data the most common use is unsupervised clustering, but there are some key differences compared to k means clustering gmms can handle clusters that are not circular, i e , clusters in the shape of ovals or higher dimensional analogs with variances in different directions clusters can have an arbitrary rotation, i e , they can have covariance gmms can handle instances when clusters are not as equally likely if a point is right between two clusters, it is more likely to be more common in the training data gmms can show the probability of a new point belonging to each cluster instead of providing only a single cluster value gmms generally handle more complex data than k means models, but they require more training and processing time during execution model options required numdistributions — must be a positive integer this value specifies the number of clusters of gaussian distributions for the model to make optional epsilon — if you specify this option, the value must be a valid positive floating point value when the maximum distance that the entire best model moves in its n dimensional space is less than this value, the algorithm terminates this parameter defaults to 1e 8 (0 00000001 as a decimal number) loadbalance — if you set this option , the database appends the using load balance shuffle = \<value> 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 = \<value> 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 false , the database deletes any intermediate tables created during model training if true , the database prevents the deletion of any intermediate tables created during model training this option defaults to false execute the model create a gmm with three clusters of gaussian distributions create mlmodel gmm type gaussian mixture model on ( select x, y, z from my table ) options( 'numdistributions' > '3' ); also, a fter 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 w hen you execute this function after training with the same number (and same order) of features as input, the result is an integer that specifies the probable cluster to which the point belongs select gmm(x,y,z) from my table; after you execute a model, you can find the details about the execution results in the sys gaussian mixture models system catalog table for details, see the description of the associated system catalog tables in the machine learning section in the https //docs ocient com/system catalog#pfo j linear discriminant analysis model type linear discriminant analysis linear discriminant analysis (lda) is a dimension reduction technique similar to pca while pca is unsupervised, lda can classify and use class labels to find linear combinations of features that best separate the classes the input result set when you create an lda model is n numeric columns that are all features the last column is a class label and can be any data type after you create an lda model, the sys linear discriminant analysis models system catalog table contains information on the percentage of the signal that is in each lda feature you can use this information to figure out how many of the output features to keep also, a fter 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 linear discriminant analysis and logistic regression you can use lda models as inputs to other models for example, to reduce the number of features from three to two using lda, you can execute these sql statements create mlmodel reduceto2 type linear discriminant analysis on ( select c1, c2, c3, label from public my table ); you can use this model as input for another model, for example, logistic regression create mlmodel binaryclass type logistic regression on ( select reduceto2(c1, c2, c3, 1), reduceto2(c1, c2, c3, 2), label from … ); to correctly use this model later, you must pass the original features through the lda model when you execute the logistic regression model select binaryclass( reduceto2(x1, x2, x3, 1), reduceto2(x1, x2, x3, 2) ) from … ; similarly, to create an lda model over four variables, execute this sql statement create mlmodel my model type linear discriminant analysis on ( select c1, c2, c3, c4, label from public my table ); model options loadbalance — if you set this option , the database appends the using load balance shuffle = \<value> 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 = \<value> 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 false , the database deletes any intermediate tables created during model training if true , the database prevents the deletion of any intermediate tables created during model training this option defaults to false execute the model when you execute the trained lda model, you must provide the same original input features in the same order, followed by a positive integer argument that specifies the lda component to return the lda component index starts at 1 select my model(col1, col2, col3, col4, 2) as component2, my model(col1, col2, col3, col4, 3) as component3, from public my table; related links docid 67w1y2nzakexornpizk e docid\ pahgmmey3oefcapzzu50i docid\ rqzgc5r0vigmehqdy3g5j