Skip to content

Instantly share code, notes, and snippets.

@loiclefevre
Created April 14, 2022 20:30
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save loiclefevre/0e925987f6bc7e4fb1dfcbc5927a1eda to your computer and use it in GitHub Desktop.
Save loiclefevre/0e925987f6bc7e4fb1dfcbc5927a1eda to your computer and use it in GitHub Desktop.
Oracle Machine Learning for 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/
--
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment