DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 964,423 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
LoΓ―c
LoΓ―c

Posted on

Machine Learning for SQL: train, deploy, score your models using SQL...😍

Machine Learning for SQL - Training

πŸ‘‰MachineLearning for SQL: train, deploy and score models right inside the database, no need to move data over the network, stronger security, scalable platform, python and R APIs as wellβœ…

Machine Learning for SQL - Scoring

CREATE TABLE customers (
    id                NUMBER  PRIMARY KEY,
    children          NUMBER,
    age               NUMBER,
    gender            CHAR(1),
    salary            NUMBER,
    married           CHAR(1),
    will_buy_an_house NUMBER
);

-- Inserting a very small data set to train 
-- the machine learning model...
INSERT INTO customers VALUES (1,0,20,'F',1200,'N',0);
INSERT INTO customers VALUES (2,3,48,'M',2800,'N',0);
INSERT INTO customers VALUES (3,1,33,'F',2500,'Y',1);
INSERT INTO customers VALUES (4,2,37,'F',1800,'Y',1);
INSERT INTO customers VALUES (5,0,52,'M',2000,'Y',0);
INSERT INTO customers VALUES (6,1,61,'M',1700,'Y',0);
COMMIT;

SELECT * FROM customers;

 ID  CHILDREN  AGE  GENDER  SALARY  MARRIED  WILL_BUY_AN_HOUSE
--- --------- ---- ------- ------- -------- ------------------
  1         0   20       F    1200        N                  0
  2         3   48       M    2800        N                  0
  3         1   33       F    2500        Y                  1
  4         2   37       F    1800        Y                  1
  5         0   52       M    2000        Y                  0
  6         1   61       M    1700        Y                  0

-- Configuration...
CREATE TABLE config (
    setting_name  VARCHAR2(30),
    setting_value VARCHAR2(4000)
);

-- Neural Network algorithm for classification
INSERT INTO config VALUES ('ALGO_NAME', 'ALGO_NEURAL_NETWORK');
-- Automatic data preparation: enabled!
INSERT INTO config VALUES ('PREP_AUTO','ON');
COMMIT;

-- Now training...
BEGIN
    DBMS_DATA_MINING.CREATE_MODEL(
        model_name          => 'NN_Classification',
        mining_function     => DBMS_DATA_MINING.classification,
        data_table_name     => 'customers',
        case_id_column_name => 'id',
        target_column_name  => 'will_buy_an_house',
        settings_table_name => 'config' 
    );
END;
/

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.742

-- Likely to buy an house?
SELECT 100 * 
       PREDICTION_PROBABILITY(NN_Classification, 
              1 USING 
             43 AS age, 
              2 AS children, 
            'M' AS gender, 
           2300 AS salary, 
            'Y' AS married) AS probability_to_buy_an_house
  FROM dual;


              PROBABILITY_TO_BUY_AN_HOUSE
             ----------------------------
                        79.05203574218477

Enter fullscreen mode Exit fullscreen mode

πŸ“– Documentation
πŸ’» Gist with code above to reproduce
😺OML GitHub repository
🀯Oracle LiveLabs
😎Oracle Machine Learning blog

Top comments (0)

Take a look at this:

Settings

Go to your customization settings to nudge your home feed to show content more relevant to your developer experience level. πŸ›