Oracle Machine Learning for SQL

oracle_machine_learning_for_sql.sql

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


--
-- Find MUCH MUCH more examples below:
-- GitHub repository: https://github.com/marancibia/oracle-db-examples/tree/main/machine-learning
-- Oracle LiveLabs: https://apexapps.oracle.com/pls/apex/dbpm/r/livelabs/home
-- Oracle Machine Learning blog: https://blogs.oracle.com/machinelearning/
-- Documentation: https://docs.oracle.com/en/database/oracle/machine-learning/
--