Machine Learning Functions

SELECT assignCentroid([1.0, 2.0]::Array(Float32), [[0.0, 0.0], [1.0, 2.0]]::Array(Array(Float32)))

1

CREATE TABLE trips (pickup_datetime DateTime('UTC'), trip_distance Float64, total_amount Float64) ENGINE = Memory;

-- A fare of 3, plus 2.5 for every unit of distance.
INSERT INTO trips
SELECT toDateTime('2020-01-01 00:00:00', 'UTC') + number * 60, number % 10 + 1, 2.5 * (number % 10 + 1) + 3
FROM numbers(1000);

-- One model per year of the data.
CREATE TABLE models ENGINE = Memory AS
SELECT
    toYear(pickup_datetime) AS year,
    stochasticLinearRegressionState(0.01, 0.0, 10, 'SGD')(total_amount, trip_distance) AS model
FROM trips
GROUP BY year;

SELECT
    trip_distance,
    round(evalMLMethod(model, trip_distance), 2) AS predicted,
    total_amount
FROM trips
LEFT JOIN models ON year = toYear(pickup_datetime)
ORDER BY pickup_datetime
LIMIT 5

┌─trip_distance─┬─predicted─┬─total_amount─┐
│             1 │      4.05 │          5.5 │
│             2 │      6.79 │            8 │
│             3 │      9.53 │         10.5 │
│             4 │     12.28 │           13 │
│             5 │     15.02 │         15.5 │
└───────────────┴───────────┴──────────────┘

-- A dictionary built from the token counts of two classes: 0 for a positive review, 1 for a negative one.
CREATE TABLE review_tokens (ngram String, class_id UInt32, count UInt64) ENGINE = Memory;
INSERT INTO review_tokens VALUES ('good', 0, 5), ('great', 0, 4), ('excellent', 0, 3), ('bad', 1, 5), ('awful', 1, 4), ('terrible', 1, 3);

CREATE DICTIONARY sentiment (ngram String, class_id UInt32 DEFAULT 0, count UInt64 DEFAULT 0)
PRIMARY KEY ngram
SOURCE(CLICKHOUSE(TABLE 'review_tokens'))
LAYOUT(NAIVE_BAYES(class_attribute 'class_id' n 1 mode 'token'))
LIFETIME(0);

SELECT naiveBayesClassifier('sentiment', 'a good and great film') AS class_id;

┌─class_id─┐
│        0 │
└──────────┘

-- A dictionary built from the token counts of two classes: 0 for a positive review, 1 for a negative one.
CREATE TABLE review_tokens (ngram String, class_id UInt32, count UInt64) ENGINE = Memory;
INSERT INTO review_tokens VALUES ('good', 0, 5), ('great', 0, 4), ('excellent', 0, 3), ('bad', 1, 5), ('awful', 1, 4), ('terrible', 1, 3);

CREATE DICTIONARY sentiment (ngram String, class_id UInt32 DEFAULT 0, count UInt64 DEFAULT 0)
PRIMARY KEY ngram
SOURCE(CLICKHOUSE(TABLE 'review_tokens'))
LAYOUT(NAIVE_BAYES(class_attribute 'class_id' n 1 mode 'token'))
LIFETIME(0);

SELECT arrayMap(p -> (p.1, round(p.2, 4)), naiveBayesClassifierWithAllProbs('sentiment', 'a good and great film')) AS predictions;

┌─predictions─────────────┐
│ [(0,0.9677),(1,0.0323)] │
└─────────────────────────┘

-- A dictionary built from the token counts of two classes: 0 for a positive review, 1 for a negative one.
CREATE TABLE review_tokens (ngram String, class_id UInt32, count UInt64) ENGINE = Memory;
INSERT INTO review_tokens VALUES ('good', 0, 5), ('great', 0, 4), ('excellent', 0, 3), ('bad', 1, 5), ('awful', 1, 4), ('terrible', 1, 3);

CREATE DICTIONARY sentiment (ngram String, class_id UInt32 DEFAULT 0, count UInt64 DEFAULT 0)
PRIMARY KEY ngram
SOURCE(CLICKHOUSE(TABLE 'review_tokens'))
LAYOUT(NAIVE_BAYES(class_attribute 'class_id' n 1 mode 'token'))
LIFETIME(0);

WITH naiveBayesClassifierWithProb('sentiment', 'a good and great film') AS p
SELECT (p.1, round(p.2, 4)) AS prediction;

┌─prediction─┐
│ (0,0.9677) │
└────────────┘

-- A model trained on the linear target y = 2 * x1 + 3 * x2. The XGBoost integration is experimental.
SET enable_xgboost = 1;

CREATE TABLE training_data (x1 Float64, x2 Float64, y Float64) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO training_data SELECT number AS x1, number * 2 AS x2, 2 * x1 + 3 * x2 AS y FROM numbers(100);

CREATE DICTIONARY model (x1 Float64, x2 Float64, y Float64)
PRIMARY KEY (x1, x2)
SOURCE(CLICKHOUSE(TABLE 'training_data'))
LAYOUT(XGBOOST(objective 'reg:squarederror' num_iterations 100 max_depth 6))
LIFETIME(0);

-- The ground truth is 2 * 1 + 3 * 2 = 8.
SELECT round(predictXGBoost('model', 1.0, 2.0), 1) AS prediction;

┌─prediction─┐
│          8 │
└────────────┘

-- The raw margin of the model above, before the objective's transformation. For `reg:squarederror` it is the
-- prediction itself, while for `binary:logistic` it is the log-odds rather than the probability.
SELECT round(predictXGBoost('model', 1.0, 2.0, map('type', 1)), 1) AS margin;

┌─margin─┐
│      8 │
└────────┘