قواميس Naive Bayes

CREATE TABLE training_data (class_id UInt32, ngram String, count UInt64)
ENGINE = MergeTree ORDER BY (class_id, ngram);

INSERT INTO training_data VALUES
    (1,'good',10),(1,'great',8),(1,'excellent',6),(1,'love',7),(1,'happy',5),
    (1,'amazing',4),(1,'wonderful',3),(1,'best',3),(1,'fantastic',2),(1,'nice',4),
    (0,'bad',10),(0,'terrible',8),(0,'awful',6),(0,'hate',7),(0,'worst',5),
    (0,'horrible',4),(0,'poor',3),(0,'disappointing',3),(0,'ugly',2),(0,'sad',4);

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

SELECT naiveBayesClassifier('sentiment', 'this is great') as predicted_class;

   ┌─predicted_class─┐
1. │               1 │
   └─────────────────┘

SELECT naiveBayesClassifier('sentiment', 'this is terrible') as predicted_class;

   ┌─predicted_class─┐
1. │               0 │
   └─────────────────┘

SELECT dictGet('sentiment', 'class_id', 'this is great') as predicted_class;

   ┌─predicted_class─┐
1. │               1 │
   └─────────────────┘

SELECT naiveBayesClassifierWithProb('sentiment', 'amazing food but terrible service') as predicted_id_with_prob;

   ┌─predicted_id_with_prob─────────────┐
1. │ {                                 ↴│
   │↳  "class_id": 0,                  ↴│
   │↳  "probability": 0.642857145060626↴│
   │↳}                                  │
   └────────────────────────────────────┘

SELECT naiveBayesClassifierWithAllProbs('sentiment', 'amazing food but terrible service') as all_predicted_ids_with_probs;

   ┌─all_predicted_ids_with_probs─────────┐
1. │ [{                                  ↴│
   │↳  "class_id": 0,                    ↴│
   │↳  "probability": 0.642857145060626  ↴│
   │↳},{                                 ↴│
   │↳  "class_id": 1,                    ↴│
   │↳  "probability": 0.35714285493937414↴│
   │↳}]                                   │
   └──────────────────────────────────────┘

INSERT INTO training_data VALUES (1, 'awesome', 5);
SYSTEM RELOAD DICTIONARY sentiment;

CREATE TABLE docs (class_id UInt32, text String) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO docs VALUES
    (1, 'The food was amazing and the service was great'),
    (0, 'The service was terrible and the food was awful'),
    (1, 'I loved this cozy little place and the friendly staff'),
    (0, 'I hated the bad weather and the long wait'),
    (1, 'Best dinner we have had here, everything was delicious');

CREATE TABLE training_data (ngram String, class_id UInt32, count UInt64)
ENGINE = MergeTree ORDER BY (class_id, ngram);

INSERT INTO training_data
SELECT ngram, class_id, count()
FROM docs
ARRAY JOIN naiveBayesNgrams(text, 1, 'token') AS ngram
GROUP BY ngram, class_id;

SELECT * FROM training_data ORDER BY ngram LIMIT 5;

   ┌─ngram─┬─class_id─┬─count─┐
1. │ Best  │        1 │     1 │
2. │ I     │        1 │     1 │
3. │ I     │        0 │     1 │
4. │ The   │        0 │     1 │
5. │ The   │        1 │     1 │
   └───────┴──────────┴───────┘

CREATE DICTIONARY review_sentiment (ngram String, class_id UInt32, count UInt64)
PRIMARY KEY ngram
SOURCE(CLICKHOUSE(TABLE 'training_data'))
LAYOUT(NAIVE_BAYES(class_attribute 'class_id' n 1 mode 'token' priors_mode 'explicit' priors [(0, 0.4), (1, 0.6)]))
LIFETIME(0);

SELECT
    naiveBayesClassifier('review_sentiment', 'amazing food and friendly staff') AS positive_review,
    naiveBayesClassifier('review_sentiment', 'awful service and a terrible meal') AS negative_review;

   ┌─positive_review─┬─negative_review─┐
1. │               1 │               0 │
   └─────────────────┴─────────────────┘

CREATE TABLE byte_patterns_src (class_id UInt32, ngram String, count UInt64) ENGINE = MergeTree ORDER BY (class_id, ngram);
INSERT INTO byte_patterns_src VALUES (0,'ab',5),(0,'bc',5),(0,'cd',5),(1,'xy',5),(1,'yz',5),(1,'zw',5);

CREATE DICTIONARY byte_patterns (ngram String, class_id UInt32, count UInt64)
PRIMARY KEY ngram SOURCE(CLICKHOUSE(TABLE 'byte_patterns_src'))
LAYOUT(NAIVE_BAYES(class_attribute 'class_id' n 2 mode 'byte')) LIFETIME(0);

SELECT naiveBayesClassifier('byte_patterns', 'abcd') AS abcd, naiveBayesClassifier('byte_patterns', 'xyzw') AS xyzw;

   ┌─abcd─┬─xyzw─┐
1. │    0 │    1 │
   └──────┴──────┘

CREATE TABLE script_src (class_id UInt32, ngram String, count UInt64) ENGINE = MergeTree ORDER BY (class_id, ngram);
INSERT INTO script_src VALUES (0,'a',5),(0,'b',5),(0,'c',5),(0,'d',5),(1,'а',5),(1,'б',5),(1,'в',5),(1,'г',5);

CREATE DICTIONARY script (ngram String, class_id UInt32, count UInt64)
PRIMARY KEY ngram SOURCE(CLICKHOUSE(TABLE 'script_src'))
LAYOUT(NAIVE_BAYES(class_attribute 'class_id' n 1 mode 'codepoint')) LIFETIME(0);

SELECT naiveBayesClassifier('script', 'abcd') AS latin, naiveBayesClassifier('script', 'абвг') AS cyrillic;

   ┌─latin─┬─cyrillic─┐
1. │     0 │        1 │
   └───────┴──────────┘

CREATE TABLE stored_src (class_id UInt32, ngram String, count UInt64) ENGINE = MergeTree ORDER BY (class_id, ngram);
INSERT INTO stored_src VALUES (0,'alpha',3),(0,'beta',2),(1,'gamma',4);

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

SELECT ngram, class_id, count FROM stored ORDER BY ngram;

   ┌─ngram─┬─class_id─┬─count─┐
1. │ alpha │        0 │     3 │
2. │ beta  │        0 │     2 │
3. │ gamma │        1 │     4 │
   └───────┴──────────┴───────┘

CREATE TABLE words (class_id UInt32, text String) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO words VALUES
    (0,'dog'),(0,'cat'),(0,'fish'),(0,'bird'),(0,'book'),(0,'hand'),(0,'tree'),(0,'milk'),(0,'duck'),(0,'frog'),(0,'lamp'),(0,'desk'),
    (1,'gato'),(1,'casa'),(1,'perro'),(1,'libro'),(1,'mano'),(1,'leche'),(1,'arbol'),(1,'agua'),(1,'queso'),(1,'fuego'),(1,'mesa'),(1,'silla');

CREATE TABLE word_ngrams (ngram String, class_id UInt32, count UInt64) ENGINE = MergeTree ORDER BY (class_id, ngram);
INSERT INTO word_ngrams
SELECT ngram, class_id, count()
FROM words
ARRAY JOIN naiveBayesNgrams(text, 2, 'codepoint', '0x10FFFE', '0x10FFFF') AS ngram
GROUP BY ngram, class_id;

CREATE DICTIONARY lang (ngram String, class_id UInt32, count UInt64)
PRIMARY KEY ngram SOURCE(CLICKHOUSE(TABLE 'word_ngrams'))
LAYOUT(NAIVE_BAYES(class_attribute 'class_id' n 2 mode 'codepoint' start_token '0x10FFFE' end_token '0x10FFFF')) LIFETIME(0);

SELECT naiveBayesClassifier('lang', 'window') AS window, naiveBayesClassifier('lang', 'fiesta') AS fiesta;

   ┌─window─┬─fiesta─┐
1. │      0 │      1 │
   └────────┴────────┘