oml4sql-r-extensible-association-rules.sql

-----------------------------------------------------------------------
-- Oracle Machine Learning for SQL (OML4SQL) 21c
--
-- OML R Extensible - Association Rules Algorithm - dmrardemo.sql
--
-- Copyright (c) 2021 Oracle Corporation and/or its affilitiates.
--
-- The Universal Permissive License (UPL), Version 1.0
--
-- https://oss.oracle.com/licenses/upl/
-----------------------------------------------------------------------
SET serveroutput ON
SET trimspool ON
SET pages 10000
SET linesize 140
SET LONG 10000
SET echo ON


-----------------------------------------------------------------------
-- SET UP THE DATA
-----------------------------------------------------------------------

-- Cleanup old training data view for repeat runs
BEGIN EXECUTE IMMEDIATE 'DROP VIEW ar_build_v';
EXCEPTION WHEN OTHERS THEN NULL; END;
/

-- Create a view for building association rules model
-- The data for this sample is composed from a small subset of
-- sales transactions in the SH schema - listing the (multiple)
-- items bought by a set of customers with ids in the range
-- 100001-104500.
--
CREATEVIEWar_build_vAS
SELECT cust_id, prod_name, prod_category, amount_sold
FROM (SELECTa.cust_id, b.prod_name, b.prod_category,
a.amount_sold
FROMsh.sales a, sh.products b
WHEREa.prod_id=b.prod_idAND
a.cust_id between 100001AND104500);


--
-- We will build two separate models for rules and itemsets, respectively.
--
-----------------------------------------------------------------------
-- BUILD THE MODEL for RULES
-----------------------------------------------------------------------

-- Cleanup old model with same name for repeat runs
BEGINDBMS_DATA_MINING.DROP_MODEL('RAR_SH_AR_SAMPLE');
EXCEPTION WHEN OTHERS THEN NULL; END;
/

-------------------
-- SPECIFY SETTINGS
--
-- Cleanup old settings table and R scripts for repeat runs
BEGIN EXECUTE IMMEDIATE 'DROP TABLE Rar_sh_sample_settings';
EXCEPTION WHEN OTHERS THEN NULL; END;
/

BEGIN
sys.rqScriptDrop('RAR_BUILD');
sys.rqScriptDrop('RAR_DETAILS');
EXCEPTION WHEN OTHERS THEN NULL; END;
/

CREATETABLERar_sh_sample_settings (
 setting_name VARCHAR2(30),
 setting_value VARCHAR2(4000));

------------
-- R scripts
--
-- The R scripts are created by users using sys.rqScriptCreate to define
-- their own approaches in R for building Association Rules models in 
-- ODM framework.

BEGIN
INSERT INTO Rar_sh_sample_settings VALUES
 ('ALGO_EXTENSIBLE_LANG', 'R');

-- The BUILD script will be invoked during CREATE_MODEL
-- Our script here uses the apriori algorithm in R's arules package 
-- to mine rules
sys.rqScriptCreate('RAR_BUILD', 
'function(dat){
 library(arules)
 trans <- as(split(dat[["PROD_NAME"]], dat[["CUST_ID"]]), "transactions")
 r <- apriori(trans, parameter = list(minlen=2, supp=0.1, conf=0.5, target="rules"))
 as(r, "data.frame")}');

-- The DETAILS script, along with the FORMAT script below will be 
-- invoked during CREATE_MODEL. A model view will be generated with 
-- the output of the DETAILS script. We deliver the mined rules through
-- the model view 
sys.rqScriptCreate('RAR_DETAILS',
'function(mod) {mod}');

INSERT INTO Rar_sh_sample_settings VALUES
 (dbms_data_mining.ralg_build_function, 'RAR_BUILD');
INSERT INTO Rar_sh_sample_settings VALUES
 (dbms_data_mining.ralg_details_function, 'RAR_DETAILS');
INSERT INTO Rar_sh_sample_settings VALUES
 (dbms_data_mining.ralg_details_format, 
'select cast(''a'' as varchar2(100)) rules, 1 support, 1 confidence, 1 lift from dual');
END;
/

---------------
-- CREATE MODEL
--
-- let case_id_column_name be NULL, as the case_id_column_name should be
-- identified in the R BUILD script
BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
 model_name =>'RAR_SH_AR_SAMPLE',
 mining_function =>dbms_data_mining.association,
 data_table_name =>'AR_BUILD_V',
 case_id_column_name =>NULL,
 settings_table_name =>'Rar_sh_sample_settings');
END;
/

-------------------------
-- DISPLAY MODEL SETTINGS
--
column setting_name format a30
column setting_value format a40
select setting_name, setting_value from Rar_sh_sample_settings
order by setting_name;

-------------------------
-- DISPLAY MODEL METADATA
--
column model_name format a20
column mining_function format a20
column algorithm format a20
select model_name, mining_function, algorithm from user_mining_models
where model_name ='RAR_SH_AR_SAMPLE';

------------------------------------
-- DISPLAY THE RULES USING MODEL VIEW
-- The model view was generated during CREATE_MODEL
--
column partition_name format a5
column rules format A30
select*from DM$VDRAR_SH_AR_SAMPLE order by confidence desc;



-----------------------------------------------------------------------
-- BUILD THE MODEL for ITEMSETS
-----------------------------------------------------------------------

-- Cleanup old model with same name for repeat runs
BEGINDBMS_DATA_MINING.DROP_MODEL('RAR_SH_FI_SAMPLE');
EXCEPTION WHEN OTHERS THEN NULL; END;
/

-------------------
-- SPECIFY SETTINGS
--
-- Cleanup old settings table for repeat runs
BEGIN EXECUTE IMMEDIATE 'DROP TABLE Rar_sh_sample_settings';
EXCEPTION WHEN OTHERS THEN NULL; END;
/


CREATETABLERar_sh_sample_settings (
 setting_name VARCHAR2(30),
 setting_value VARCHAR2(4000));

BEGIN
INSERT INTO Rar_sh_sample_settings VALUES
 ('ALGO_EXTENSIBLE_LANG', 'R');

-- Our script here uses the apriori algorithm in R's arules package to 
-- mine itemsets
sys.rqScriptCreate('RAR_BUILD', 
'function(dat){
 library(arules)
 trans <- as(split(dat[["PROD_NAME"]], dat[["CUST_ID"]]), "transactions")
 items <- apriori(trans, parameter = list(supp=0.1, target="frequent"))
 as(items, "data.frame")}', v_overwrite => TRUE);

sys.rqScriptCreate('RAR_DETAILS',
'function(mod) {mod}', v_overwrite => TRUE);

INSERT INTO Rar_sh_sample_settings VALUES
 (dbms_data_mining.ralg_build_function, 'RAR_BUILD');
INSERT INTO Rar_sh_sample_settings VALUES
 (dbms_data_mining.ralg_details_function, 'RAR_DETAILS');
INSERT INTO Rar_sh_sample_settings VALUES
 (dbms_data_mining.ralg_details_format, 
'select cast(''a'' as varchar2(100)) items, 1 support from dual');
END;
/


---------------
-- CREATE MODEL
--
BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
 model_name =>'RAR_SH_FI_SAMPLE',
 mining_function =>dbms_data_mining.association,
 data_table_name =>'AR_BUILD_V',
 case_id_column_name =>NULL,
 settings_table_name =>'Rar_sh_sample_settings');
END;
/

-------------------------
-- DISPLAY MODEL SETTINGS
--
column setting_name format a30
column setting_value format a40
select setting_name, setting_value from Rar_sh_sample_settings
order by setting_name;

-------------------------
-- DISPLAY MODEL METADATA
--
column model_name format a20
column mining_function format a20
column algorithm format a20
select model_name, mining_function, algorithm from user_mining_models
where model_name ='RAR_SH_FI_SAMPLE';

---------------------------------------
-- DISPLAY THE ITEMSETS USING MODEL VIEW
--
column partition_name format a5
column items format a50
select*from DM$VDRAR_SH_FI_SAMPLE order by support desc;