Generic functions

convert_label(const int|const float) - Convert from -1|1 to 0.0f|1.0f, or from 0.0f|1.0f to -1|1
each_top_k(int K, Object group, double cmpKey, *) - Returns top-K values (or tail-K values when k is less than 0)
generate_series(const int|bigint start, const int|bigint end) - Generate a series of values, from start to end. A similar function to PostgreSQL's generate_serics. http://www.postgresql.org/docs/current/static/functions-srf.html
```
select generate_series(1,9);

1
2
3
4
5
6
7
8
9
```

try_cast(ANY src, const string typeName) - Explicitly cast a value as a type. Returns null if cast fails.

Usage: select try_cast(array(1.0,2.0,3.0), 'array<string>')
     select try_cast(map('A',10,'B',20,'C',30), 'map<string,double>')

x_rank(KEY) - Generates a pseudo sequence number starting from 1 for each key

Array

array_append(array<T> arr, T elem) - Append an element to the end of an array
array_avg(array<number>) - Returns an array<double> in which each element is the mean of a set of numbers
array_concat(array<ANY> x1, array<ANY> x2, ..) - Returns a concatenated array
```
select array_concat(array(1),array(2,3));
> [1,2,3]
```
array_flatten(array<array<ANY>>) - Returns an array with the elements flattened.
array_intersect(array<ANY> x1, array<ANY> x2, ..) - Returns an intersect of given arrays
```
select array_intersect(array(1,3,4),array(2,3,4),array(3,5));
> [3]
```
array_remove(array<int|text> original, int|text|array<int> target) - Returns an array that the target is removed from the original array
```
select array_remove(array(1,null,3),array(null));
> [3]

select array_remove(array("aaa","bbb"),"bbb");
> ["aaa"]
```
array_slice(array<ANY> values, int offset [, int length]) - Slices the given array by the given offset and length parameters.
```
select array_slice(array(1,2,3,4,5,6), 2,4);
> [3,4]
```
array_sum(array<number>) - Returns an array<double> in which each element is summed up
array_union(array1, array2, ...) - Returns the union of a set of arrays
conditional_emit(array<boolean> conditions, array<primitive> features) - Emit features of a row according to various conditions
element_at(array<T> list, int pos) - Returns an element at the given position
first_element(x) - Returns the first element in an array
float_array(nDims) - Returns an array<float> of nDims elements
last_element(x) - Return the last element in an array
select_k_best(array<number> array, const array<number> importance, const int k) - Returns selected top-k elements as array<double>
sort_and_uniq_array(array<int>) - Takes array<int> and returns a sorted array with duplicate elements eliminated
```
select sort_and_uniq_array(array(3,1,1,-2,10));
> [-2,1,3,10]
```
subarray_endwith(array<int|text> original, int|text key) - Returns an array that ends with the specified key
```
select subarray_endwith(array(1,2,3,4), 3);
> [1,2,3]
```
subarray_startwith(array<int|text> original, int|text key) - Returns an array that starts with the specified key
```
select subarray_startwith(array(1,2,3,4), 2);
> [2,3,4]
```
to_string_array(array<ANY>) - Returns an array of strings

to_ordered_list(PRIMITIVE value [, PRIMITIVE key, const string options]) - Return list of values sorted by value itself or specific key

with t as (
    select 5 as key, 'apple' as value
    union all
    select 3 as key, 'banana' as value
    union all
    select 4 as key, 'candy' as value
    union all
    select 2 as key, 'donut' as value
    union all
    select 3 as key, 'egg' as value
)
select                                             -- expected output
    to_ordered_list(value, key, '-reverse'),       -- [apple, candy, (banana, egg | egg, banana), donut] (reverse order)
    to_ordered_list(value, key, '-k 2'),           -- [apple, candy] (top-k)
    to_ordered_list(value, key, '-k 100'),         -- [apple, candy, (banana, egg | egg, banana), dunut]
    to_ordered_list(value, key, '-k 2 -reverse'),  -- [donut, (banana | egg)] (reverse top-k = tail-k)
    to_ordered_list(value, key),                   -- [donut, (banana, egg | egg, banana), candy, apple] (natural order)
    to_ordered_list(value, key, '-k -2'),          -- [donut, (banana | egg)] (tail-k)
    to_ordered_list(value, key, '-k -100'),        -- [donut, (banana, egg | egg, banana), candy, apple]
    to_ordered_list(value, key, '-k -2 -reverse'), -- [apple, candy] (reverse tail-k = top-k)
    to_ordered_list(value, '-k 2'),                -- [egg, donut] (alphabetically)
    to_ordered_list(key, '-k -2 -reverse'),        -- [5, 4] (top-2 keys)
    to_ordered_list(key)                           -- [2, 3, 3, 4, 5] (natural ordered keys)
from
    t

Map

map_get_sum(map<int,float> src, array<int> keys) - Returns sum of values that are retrieved by keys
map_tail_n(map SRC, int N) - Returns the last N elements from a sorted array of SRC
to_map(key, value) - Convert two aggregated columns into a key-value map

to_ordered_map(key, value [, const int k|const boolean reverseOrder=false]) - Convert two aggregated columns into an ordered key-value map

with t as (
    select 10 as key, 'apple' as value
    union all
    select 3 as key, 'banana' as value
    union all
    select 4 as key, 'candy' as value
)
select
    to_ordered_map(key, value, true),   -- {10:"apple",4:"candy",3:"banana"} (reverse)
    to_ordered_map(key, value, 1),      -- {10:"apple"} (top-1)
    to_ordered_map(key, value, 2),      -- {10:"apple",4:"candy"} (top-2)
    to_ordered_map(key, value, 3),      -- {10:"apple",4:"candy",3:"banana"} (top-3)
    to_ordered_map(key, value, 100),    -- {10:"apple",4:"candy",3:"banana"} (top-100)
    to_ordered_map(key, value),         -- {3:"banana",4:"candy",10:"apple"} (natural)
    to_ordered_map(key, value, -1),     -- {3:"banana"} (tail-1)
    to_ordered_map(key, value, -2),     -- {3:"banana",4:"candy"} (tail-2)
    to_ordered_map(key, value, -3),     -- {3:"banana",4:"candy",10:"apple"} (tail-3)
    to_ordered_map(key, value, -100)    -- {3:"banana",4:"candy",10:"apple"} (tail-100)
from t

Bitset

bits_collect(int|long x) - Returns a bitset in array<long>
bits_or(array<long> b1, array<long> b2, ..) - Returns a logical OR given bitsets
```
select unbits(bits_or(to_bits(array(1,4)),to_bits(array(2,3))));
> [1,2,3,4]
```
to_bits(int[] indexes) - Returns an bitset representation if the given indexes in long[]
```
select to_bits(array(1,2,3,128));
> [14,-9223372036854775808]
```
unbits(long[] bitset) - Returns an long array of the give bitset representation
```
select unbits(to_bits(array(1,4,2,3)));
> [1,2,3,4]
```

Compression

deflate(TEXT data [, const int compressionLevel]) - Returns a compressed BINARY object by using Deflater. The compression level must be in range [-1,9]
```
select base91(deflate('aaaaaaaaaaaaaaaabbbbccc'));
> AA+=kaIM|WTt!+wbGAA
```

inflate(BINARY compressedData) - Returns a decompressed STRING by using Inflater

select inflate(unbase91(base91(deflate('aaaaaaaaaaaaaaaabbbbccc'))));
> aaaaaaaaaaaaaaaabbbbccc

MapReduce

distcache_gets(filepath, key, default_value [, parseKey]) - Returns map<key_type, value_type>|value_type
jobconf_gets() - Returns the value from JobConf
jobid() - Returns the value of mapred.job.id
rowid() - Returns a generated row id of a form {TASK_ID}-{SEQUENCE_NUMBER}

rownum() - Returns a generated row number in long

returns sprintf(`%d%04d`,sequence,taskId) as long

taskid() - Returns the value of mapred.task.partition

Math

l2_norm(double xi) - Return L2 norm of a vector which has the given values in each dimension
sigmoid(x) - Returns 1.0 / (1.0 + exp(-x))

Matrix

transpose_and_dot(array<number> matrix0_row, array<number> matrix1_row) - Returns dot(matrix0.T, matrix1) as array<array<double>>, shape = (matrix0.#cols, matrix1.#cols)

Text processing

base91(BINARY bin) - Convert the argument from binary to a BASE91 string

select base91(deflate('aaaaaaaaaaaaaaaabbbbccc'));
> AA+=kaIM|WTt!+wbGAA

is_stopword(string word) - Returns whether English stopword or not
normalize_unicode(string str [, string form]) - Transforms str with the specified normalization form. The form takes one of NFC (default), NFD, NFKC, or NFKD
```
select normalize_unicode('ﾊﾝｶｸｶﾅ','NFKC');
> ハンカクカナ

select normalize_unicode('㈱㌧㌦Ⅲ','NFKC');
> (株)トンドルIII
```
singularize(string word) - Returns singular form of a given English word
```
select singularize(lower("Apples"));

> "apple"
```
split_words(string query [, string regex]) - Returns an array<text> containing split strings
tokenize(string englishText [, boolean toLowerCase]) - Returns tokenized words in array<string>

unbase91(string) - Convert a BASE91 string to a binary

select inflate(unbase91(base91(deflate('aaaaaaaaaaaaaaaabbbbccc'))));
> aaaaaaaaaaaaaaaabbbbccc

word_ngrams(array<string> words, int minSize, int maxSize]) - Returns list of n-grams for given words, where minSize <= n <= maxSize

select word_ngrams(tokenize('Machine learning is fun!', true), 1, 2);

> ["machine","machine learning","learning","learning is","is","is fun","fun"]

Regression

train_arow_regr(array<int|bigint|string> features, float target [, constant string options]) - Returns a relation consists of <{int|bigint|string} feature, float weight, float covar>
train_arowe2_regr(array<int|bigint|string> features, float target [, constant string options]) - Returns a relation consists of <{int|bigint|string} feature, float weight, float covar>
train_arowe_regr(array<int|bigint|string> features, float target [, constant string options]) - Returns a relation consists of <{int|bigint|string} feature, float weight, float covar>
train_pa1_regr(array<int|bigint|string> features, float target [, constant string options]) - Returns a relation consists of <{int|bigint|string} feature, float weight>
train_pa1a_regr(array<int|bigint|string> features, float target [, constant string options]) - Returns a relation consists of <{int|bigint|string} feature, float weight>
train_pa2_regr(array<int|bigint|string> features, float target [, constant string options]) - Returns a relation consists of <{int|bigint|string} feature, float weight>
train_pa2a_regr(array<int|bigint|string> features, float target [, constant string options]) - Returns a relation consists of <{int|bigint|string} feature, float weight>
train_regressor(list<string|int|bigint> features, double label [, const string options]) - Returns a relation consists of <string|int|bigint feature, float weight>
```
Build a prediction model by a generic regressor
```

Classification

Binary classification

kpa_predict(@Nonnull double xh, @Nonnull double xk, @Nullable float w0, @Nonnull float w1, @Nonnull float w2, @Nullable float w3) - Returns a prediction value in Double
train_arow(list<string|int|bigint> features, int label [, const string options]) - Returns a relation consists of <string|int|bigint feature, float weight, float covar>
```
Build a prediction model by Adaptive Regularization of Weight Vectors (AROW) binary classifier
```
train_arowh(list<string|int|bigint> features, int label [, const string options]) - Returns a relation consists of <string|int|bigint feature, float weight, float covar>
```
Build a prediction model by AROW binary classifier using hinge loss
```
train_classifier(list<string|int|bigint> features, int label [, const string options]) - Returns a relation consists of <string|int|bigint feature, float weight>
```
Build a prediction model by a generic classifier
```
train_cw(list<string|int|bigint> features, int label [, const string options]) - Returns a relation consists of <string|int|bigint feature, float weight, float covar>
```
Build a prediction model by Confidence-Weighted (CW) binary classifier
```
train_kpa(array<string|int|bigint> features, int label [, const string options]) - returns a relation <h int, hk int, float w0, float w1, float w2, float w3>
train_pa(list<string|int|bigint> features, int label [, const string options]) - Returns a relation consists of <string|int|bigint feature, float weight>
```
Build a prediction model by Passive-Aggressive (PA) binary classifier
```
train_pa1(list<string|int|bigint> features, int label [, const string options]) - Returns a relation consists of <string|int|bigint feature, float weight>
```
Build a prediction model by Passive-Aggressive 1 (PA-1) binary classifier
```
train_pa2(list<string|int|bigint> features, int label [, const string options]) - Returns a relation consists of <string|int|bigint feature, float weight>
```
Build a prediction model by Passive-Aggressive 2 (PA-2) binary classifier
```
train_perceptron(list<string|int|bigint> features, int label [, const string options]) - Returns a relation consists of <string|int|bigint feature, float weight>
```
Build a prediction model by Perceptron binary classifier
```
train_scw(list<string|int|bigint> features, int label [, const string options]) - Returns a relation consists of <string|int|bigint feature, float weight, float covar>
```
Build a prediction model by Soft Confidence-Weighted (SCW-1) binary classifier
```
train_scw2(list<string|int|bigint> features, int label [, const string options]) - Returns a relation consists of <string|int|bigint feature, float weight, float covar>
```
Build a prediction model by Soft Confidence-Weighted 2 (SCW-2) binary classifier
```

Multiclass classification

train_multiclass_arow(list<string|int|bigint> features, {int|string} label [, const string options]) - Returns a relation consists of <{int|string} label, {string|int|bigint} feature, float weight, float covar>
```
Build a prediction model by Adaptive Regularization of Weight Vectors (AROW) multiclass classifier
```
train_multiclass_arowh(list<string|int|bigint> features, int|string label [, const string options]) - Returns a relation consists of <int|string label, string|int|bigint feature, float weight, float covar>
```
Build a prediction model by Adaptive Regularization of Weight Vectors (AROW) multiclass classifier using hinge loss
```
train_multiclass_cw(list<string|int|bigint> features, {int|string} label [, const string options]) - Returns a relation consists of <{int|string} label, {string|int|bigint} feature, float weight, float covar>
```
Build a prediction model by Confidence-Weighted (CW) multiclass classifier
```
train_multiclass_pa(list<string|int|bigint> features, {int|string} label [, const string options]) - Returns a relation consists of <{int|string} label, {string|int|bigint} feature, float weight>
```
Build a prediction model by Passive-Aggressive (PA) multiclass classifier
```
train_multiclass_pa1(list<string|int|bigint> features, {int|string} label [, const string options]) - Returns a relation consists of <{int|string} label, {string|int|bigint} feature, float weight>
```
Build a prediction model by Passive-Aggressive 1 (PA-1) multiclass classifier
```
train_multiclass_pa2(list<string|int|bigint> features, {int|string} label [, const string options]) - Returns a relation consists of <{int|string} label, {string|int|bigint} feature, float weight>
```
Build a prediction model by Passive-Aggressive 2 (PA-2) multiclass classifier
```
train_multiclass_perceptron(list<string|int|bigint> features, {int|string} label [, const string options]) - Returns a relation consists of <{int|string} label, {string|int|bigint} feature, float weight>
```
Build a prediction model by Perceptron multiclass classifier
```
train_multiclass_scw(list<string|int|bigint> features, {int|string} label [, const string options]) - Returns a relation consists of <{int|string} label, {string|int|bigint} feature, float weight, float covar>
```
Build a prediction model by Soft Confidence-Weighted (SCW-1) multiclass classifier
```
train_multiclass_scw2(list<string|int|bigint> features, {int|string} label [, const string options]) - Returns a relation consists of <{int|string} label, {string|int|bigint} feature, float weight, float covar>
```
Build a prediction model by Soft Confidence-Weighted 2 (SCW-2) multiclass classifier
```

Matrix factorization

bprmf_predict(List<Float> Pu, List<Float> Qi[, double Bi]) - Returns the prediction value
mf_predict(List<Float> Pu, List<Float> Qi[, double Bu, double Bi[, double mu]]) - Returns the prediction value
train_bprmf(INT user, INT posItem, INT negItem [, String options]) - Returns a relation <INT i, FLOAT Pi, FLOAT Qi [, FLOAT Bi]>
train_mf_adagrad(INT user, INT item, FLOAT rating [, CONSTANT STRING options]) - Returns a relation consists of <int idx, array<float> Pu, array<float> Qi [, float Bu, float Bi [, float mu]]>
train_mf_sgd(INT user, INT item, FLOAT rating [, CONSTANT STRING options]) - Returns a relation consists of <int idx, array<float> Pu, array<float> Qi [, float Bu, float Bi [, float mu]]>

Factorization machines

ffm_predict(float Wi, array<float> Vifj, array<float> Vjfi, float Xi, float Xj) - Returns a prediction value in Double
fm_predict(Float Wj, array<float> Vjf, float Xj) - Returns a prediction value in Double
train_ffm(array<string> x, double y [, const string options]) - Returns a prediction model
train_fm(array<string> x, double y [, const string options]) - Returns a prediction model

Recommendation

train_slim( int i, map<int, double> r_i, map<int, map<int, double>> topKRatesOfI, int j, map<int, double> r_j [, constant string options]) - Returns row index, column index and non-zero weight value of prediction model

Anomaly detection

changefinder(double|array<double> x [, const string options]) - Returns outlier/change-point scores and decisions using ChangeFinder. It will return a tuple <double outlier_score, double changepoint_score [, boolean is_anomaly [, boolean is_changepoint]]
sst(double|array<double> x [, const string options]) - Returns change-point scores and decisions using Singular Spectrum Transformation (SST). It will return a tuple <double changepoint_score [, boolean is_changepoint]>

Topic modeling

lda_predict(string word, float value, int label, float lambda[, const string options]) - Returns a list which consists of <int label, float prob>
plsa_predict(string word, float value, int label, float prob[, const string options]) - Returns a list which consists of <int label, float prob>
train_lda(array<string> words[, const string options]) - Returns a relation consists of <int topic, string word, float score>
train_plsa(array<string> words[, const string options]) - Returns a relation consists of <int topic, string word, float score>

Preprocessing

add_bias(feature_vector in array<string>) - Returns features with a bias in array<string>
add_feature_index(ARRAY[DOUBLE]: dense feature vector) - Returns a feature vector with feature indices
extract_feature(feature_vector in array<string>) - Returns features in array<string>
extract_weight(feature_vector in array<string>) - Returns the weights of features in array<string>
feature(<string|int|long|short|byte> feature, <number> value) - Returns a feature string
feature_index(feature_vector in array<string>) - Returns feature indices in array<index>
sort_by_feature(map in map<int,float>) - Returns a sorted map

Data amplification

amplify(const int xtimes, *) - amplify the input records x-times
rand_amplify(const int xtimes [, const string options], *) - amplify the input records x-times in map-side

Feature binning

build_bins(number weight, const int num_of_bins[, const boolean auto_shrink = false]) - Return quantiles representing bins: array<double>
feature_binning(array<features::string> features, const map<string, array<number>> quantiles_map) / FUNC(number weight, const array<number> quantiles) - Returns binned features as an array<features::string> / bin ID as int

Feature format conversion

conv2dense(int feature, float weight, int nDims) - Return a dense model in array<float>
quantify(boolean output, col1, col2, ...) - Returns an identified features
to_dense_features(array<string> feature_vector, int dimensions) - Returns a dense feature in array<float>
to_sparse_features(array<float> feature_vector) - Returns a sparse feature in array<string>

Feature hashing

array_hash_values(array<string> values, [string prefix [, int numFeatures], boolean useIndexAsPrefix]) returns hash values in array<int>
feature_hashing(array<string> features [, const string options]) - returns a hashed feature vector in array<string>
mhash(string word) returns a murmurhash3 INT value starting from 1
prefixed_hash_values(array<string> values, string prefix [, boolean useIndexAsPrefix]) returns array<string> that each element has the specified prefix
sha1(string word [, int numFeatures]) returns a SHA-1 value

Feature paring

feature_pairs(feature_vector in array<string>, [, const string options]) - Returns a relation <string i, string j, double xi, double xj>
polynomial_features(feature_vector in array<string>) - Returns a feature vectorhaving polynomial feature space
powered_features(feature_vector in array<string>, int degree [, boolean truncate]) - Returns a feature vector having a powered feature space

Ranking

bpr_sampling(int userId, List<int> posItems [, const string options])- Returns a relation consists of <int userId, int itemId>
item_pairs_sampling(array<int|long> pos_items, const int max_item_id [, const string options])- Returns a relation consists of <int pos_item_id, int neg_item_id>
populate_not_in(list items, const int max_item_id [, const string options])- Returns a relation consists of <int item> that item does not exist in the given items

Feature scaling

l1_normalize(ftvec string) - Returned a L1 normalized value
l2_normalize(ftvec string) - Returned a L2 normalized value
rescale(value, min, max) - Returns rescaled value by min-max normalization
zscore(value, mean, stddev) - Returns a standard score (zscore)

Feature selection

chi2(array<array<number>> observed, array<array<number>> expected) - Returns chi2_val and p_val of each columns as <array<double>, array<double>>
snr(array<number> features, array<int> one-hot class label) - Returns Signal Noise Ratio for each feature as array<double>

Feature transformation and vectorization

add_field_indices(array<string> features) - Returns arrays of string that field indices (<field>:<feature>)* are augmented
binarize_label(int/long positive, int/long negative, ...) - Returns positive/negative records that are represented as (..., int label) where label is 0 or 1
categorical_features(array<string> featureNames, feature1, feature2, .. [, const string options]) - Returns a feature vector array<string>
ffm_features(const array<string> featureNames, feature1, feature2, .. [, const string options]) - Takes categorical variables and returns a feature vector array<string> in a libffm format <field>:<index>:<value>
indexed_features(double v1, double v2, ...) - Returns a list of features as array<string>: [1:v1, 2:v2, ..]
onehot_encoding(PRIMITIVE feature, ...) - Compute onehot encoded label for each feature
quantified_features(boolean output, col1, col2, ...) - Returns an identified features in a dense array<double>
quantitative_features(array<string> featureNames, feature1, feature2, .. [, const string options]) - Returns a feature vector array<string>
vectorize_features(array<string> featureNames, feature1, feature2, .. [, const string options]) - Returns a feature vector array<string>

Geospatial functions

haversine_distance(double lat1, double lon1, double lat2, double lon2, [const boolean mile=false])::double - return distance between two locations in km [or miles] using haversine formula
```
Usage: select latlon_distance(lat1, lon1, lat2, lon2) from ...
```
lat2tiley(double lat, int zoom)::int - Returns the tile number of the given latitude and zoom level
lon2tilex(double lon, int zoom)::int - Returns the tile number of the given longitude and zoom level

map_url(double lat, double lon, int zoom [, const string option]) - Returns a URL string

OpenStreetMap: http://tile.openstreetmap.org/${zoom}/${xtile}/${ytile}.png
Google Maps: https://www.google.com/maps/@${lat},${lon},${zoom}z

tile(double lat, double lon, int zoom)::bigint - Returns a tile number 2^2n where n is zoom level. FUNC(lat,lon,zoom) = xtile(lon,zoom) + ytile(lat,zoom) * 2^zoom
```
refer http://wiki.openstreetmap.org/wiki/Slippy_map_tilenames for detail
```
tilex2lon(int x, int zoom)::double - Returns longitude of the given tile x and zoom level
tiley2lat(int y, int zoom)::double - Returns latitude of the given tile y and zoom level

Distance measures

angular_distance(ftvec1, ftvec2) - Returns an angular distance of the given two vectors
cosine_distance(ftvec1, ftvec2) - Returns a cosine distance of the given two vectors
euclid_distance(ftvec1, ftvec2) - Returns the square root of the sum of the squared differences: sqrt(sum((x - y)^2))
hamming_distance(A, B [,int k]) - Returns Hamming distance between A and B
jaccard_distance(A, B [,int k]) - Returns Jaccard distance between A and B
kld(double m1, double sigma1, double mu2, double sigma 2) - Returns KL divergence between two distributions
manhattan_distance(list x, list y) - Returns sum(|x - y|)
minkowski_distance(list x, list y, double p) - Returns sum(|x - y|^p)^(1/p)
popcnt(a [, b]) - Returns a popcount value

Locality-sensitive hashing

bbit_minhash(array<> features [, int numHashes]) - Returns a b-bits minhash value
minhash(ANY item, array<int|bigint|string> features [, constant string options]) - Returns n different k-depth signatures (i.e., clusterid) for each item <clusterid, item>
minhashes(array<> features [, int numHashes, int keyGroup [, boolean noWeight]]) - Returns minhash values

Similarity measures

angular_similarity(ftvec1, ftvec2) - Returns an angular similarity of the given two vectors
cosine_similarity(ftvec1, ftvec2) - Returns a cosine similarity of the given two vectors
dimsum_mapper(array<string> row, map<int col_id, double norm> colNorms [, const string options]) - Returns column-wise partial similarities
distance2similarity(float d) - Returns 1.0 / (1.0 + d)
euclid_similarity(ftvec1, ftvec2) - Returns a euclid distance based similarity, which is 1.0 / (1.0 + distance), of the given two vectors
jaccard_similarity(A, B [,int k]) - Returns Jaccard similarity coefficient of A and B

Evaluation

auc(array rankItems | double score, array correctItems | int label [, const int recommendSize = rankItems.size ]) - Returns AUC
average_precision(array rankItems, array correctItems [, const int recommendSize = rankItems.size]) - Returns MAP
f1score(array[int], array[int]) - Return a F1 score
fmeasure(array|int|boolean actual, array|int| boolean predicted [, const string options]) - Return a F-measure (f1score is the special with beta=1.0)
hitrate(array rankItems, array correctItems [, const int recommendSize = rankItems.size]) - Returns HitRate
logloss(double predicted, double actual) - Return a Logrithmic Loss
mae(double predicted, double actual) - Return a Mean Absolute Error
mrr(array rankItems, array correctItems [, const int recommendSize = rankItems.size]) - Returns MRR
mse(double predicted, double actual) - Return a Mean Squared Error
ndcg(array rankItems, array correctItems [, const int recommendSize = rankItems.size]) - Returns nDCG
precision_at(array rankItems, array correctItems [, const int recommendSize = rankItems.size]) - Returns Precision
r2(double predicted, double actual) - Return R Squared (coefficient of determination)
recall_at(array rankItems, array correctItems [, const int recommendSize = rankItems.size]) - Returns Recall
rmse(double predicted, double actual) - Return a Root Mean Squared Error

Sketching

approx_count_distinct(expr x [, const string options]) - Returns an approximation of count(DISTINCT x) using HyperLogLogPlus algorithm

Ensemble learning

argmin_kld(float mean, float covar) - Returns mean or covar that minimize a KL-distance among distributions
```
The returned value is (1.0 / (sum(1.0 / covar))) * (sum(mean / covar)
```
max_label(double value, string label) - Returns a label that has the maximum value
maxrow(ANY compare, ...) - Returns a row that has maximum value in the 1st argument

Bagging

voted_avg(double value) - Returns an averaged value by bagging for classification
weight_voted_avg(expr) - Returns an averaged value by considering sum of positive/negative weights

Decision trees and RandomForest

train_gradient_tree_boosting_classifier(array<double|string> features, int label [, string options]) - Returns a relation consists of <int iteration, int model_type, array<string> pred_models, double intercept, double shrinkage, array<double> var_importance, float oob_error_rate>
train_randomforest_classifier(array<double|string> features, int label [, const string options, const array<double> classWeights])- Returns a relation consists of <string model_id, double model_weight, string model, array<double> var_importance, int oob_errors, int oob_tests>
train_randomforest_regression(array<double|string> features, double target [, string options]) - Returns a relation consists of <int model_id, int model_type, string pred_model, array<double> var_importance, int oob_errors, int oob_tests>

guess_attribute_types(ANY, ...) - Returns attribute types

select guess_attribute_types(*) from train limit 1;
> Q,Q,C,C,C,C,Q,C,C,C,Q,C,Q,Q,Q,Q,C,Q

rf_ensemble(int yhat [, array<double> proba [, double model_weight=1.0]]) - Returns ensembled prediction results in <int label, double probability, array<double> probabilities>
tree_export(string model, const string options, optional array<string> featureNames=null, optional array<string> classNames=null) - exports a Decision Tree model as javascript/dot]
tree_predict(string modelId, string model, array<double|string> features [, const string options | const boolean classification=false]) - Returns a prediction result of a random forest in <int value, array<double> a posteriori> for classification and <double> for regression

XGBoost

train_multiclass_xgboost_classifier(string[] features, double target [, string options]) - Returns a relation consisting of <string model_id, array<byte> pred_model>
train_xgboost_classifier(string[] features, double target [, string options]) - Returns a relation consisting of <string model_id, array<byte> pred_model>
train_xgboost_regr(string[] features, double target [, string options]) - Returns a relation consisting of <string model_id, array<byte> pred_model>
xgboost_multiclass_predict(string rowid, string[] features, string model_id, array<byte> pred_model [, string options]) - Returns a prediction result as (string rowid, string label, float probability)
xgboost_predict(string rowid, string[] features, string model_id, array<byte> pred_model [, string options]) - Returns a prediction result as (string rowid, float predicted)

Others

hivemall_version() - Returns the version of Hivemall
```
Usage: SELECT hivemall_version();
```

lr_datagen(options string) - Generates a logistic regression dataset

WITH dual AS (SELECT 1) SELECT lr_datagen('-n_examples 1k -n_features 10') FROM dual;

tf(string text) - Return a term frequency in <string, float>

takuti/hivemall.md