Svm.clas.plsql

SET serveroutput ON
SET trimspool ON  
SET pages 10000
SET echo ON

-----------------------------------------------------------------------
--                            Budowa modelu
-----------------------------------------------------------------------

-- usuniecie starej tabeli, utworzenie nowej tabeli i wypelnienie jej wartosciami
BEGIN 
  EXECUTE IMMEDIATE 'DROP TABLE svm_build';
EXCEPTION 
  WHEN OTHERS THEN NULL; 
END;
/

CREATE TABLE svm_build (
  id NUMBER PRIMARY KEY,
  x  NUMBER NOT NULL,
	y  NUMBER NOT NULL,
	c  NUMBER NOT NULL CHECK (c IN (-1,1))
	);

BEGIN
  INSERT INTO svm_build VALUES (1,1,1,-1);
  INSERT INTO svm_build VALUES (2,1,3,-1);
  INSERT INTO svm_build VALUES (3,2,1,-1);
  INSERT INTO svm_build VALUES (4,3,2,-1);
  INSERT INTO svm_build VALUES (5,5,1,-1);
  INSERT INTO svm_build VALUES (6,5,3,1);
  INSERT INTO svm_build VALUES (7,1,4,1);
  INSERT INTO svm_build VALUES (8,2,4,1);
  INSERT INTO svm_build VALUES (9,7,1,1);
  INSERT INTO svm_build VALUES (10,8,4,1);
  COMMIT;
END;
/

-- usuniecie starej tabeli z ustawieniami algorytmu
BEGIN 
  EXECUTE IMMEDIATE 'DROP TABLE settings';
EXCEPTION 
  WHEN OTHERS THEN NULL; 
END;
/

-- utworzenie i wypelnienie tabeli z ustawieniami algorytmu
CREATE TABLE settings (
  setting_name  VARCHAR2(30),
  setting_value VARCHAR2(30));
set echo on

BEGIN 
  INSERT INTO settings (setting_name, setting_value) VALUES
    (dbms_data_mining.algo_name, dbms_data_mining.algo_support_vector_machines);
  INSERT INTO settings (setting_name, setting_value) VALUES
    (dbms_data_mining.svms_kernel_function, dbms_data_mining.svms_linear);
  COMMIT;
END;
/

-- usuniecie starego modelu z repozytorium
BEGIN 
  DBMS_DATA_MINING.DROP_MODEL('SVMC_Model');
EXCEPTION 
  WHEN OTHERS THEN NULL; 
END;
/

-- stworzenie nowego modelu
BEGIN
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'SVMC_Model',
    mining_function     => dbms_data_mining.classification,
    data_table_name     => 'svm_build',
    case_id_column_name => 'id',
    target_column_name  => 'c',
    settings_table_name => 'settings');
END;
/

--
-- wyswietlenie ustawien modelu
--
column setting_name format a30
column setting_value format a30
SELECT setting_name, setting_value
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_SETTINGS('SVMC_Model'))
ORDER BY setting_name;

--
-- wyswietlenie sygnatury modelu
--
column attribute_name format a40
column attribute_type format a20
SELECT attribute_name, attribute_type
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_SIGNATURE('SVMC_Model'))
ORDER BY attribute_name;

--
-- wyswietlenie szczegolowych informacji o modelu
SET line 120
column class format a10
column aname format a25
column aval  format a25
column coeff format 9.99

WITH
mod_dtls AS (
  SELECT * FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_SVM('SVMC_Model'))
),
model_details AS (
SELECT D.class, A.attribute_name, A.attribute_value, A.coefficient
  FROM mod_dtls D, TABLE(D.attribute_set) A
ORDER BY D.class, ABS(A.coefficient) DESC
)
SELECT class, attribute_name aname, attribute_value aval, coefficient coeff
FROM model_details
WHERE ROWNUM < 11;	        

-----------------------------------------------------------------------
--                               Testowanie modelu
-----------------------------------------------------------------------

-- usuniecie starej tabeli testowej, utworzenie nowej tabeli i 
-- wypelnienie jej wartosciami
BEGIN 
  EXECUTE IMMEDIATE 'DROP TABLE svm_test';
EXCEPTION 
  WHEN OTHERS THEN NULL; 
END;
/

CREATE TABLE svm_test (
  id NUMBER PRIMARY KEY,
  x  NUMBER NOT NULL,
	y  NUMBER NOT NULL,
	c  NUMBER NOT NULL CHECK (c IN (-1,1))
	);

BEGIN
  INSERT INTO svm_test VALUES (1,2,2,-1);
  INSERT INTO svm_test VALUES (2,6,1,-1);
  INSERT INTO svm_test VALUES (3,7,0,-1);
  INSERT INTO svm_test VALUES (4,3,3,1);
  INSERT INTO svm_test VALUES (5,5,2,1);
  INSERT INTO svm_test VALUES (6,8,1,1);
  COMMIT;
END;
/

--
-- wyliczenie macierzy pomylek
--
SELECT c AS actual_target_value,
       PREDICTION(SVMC_Model USING *) AS predicted_target_value,
       COUNT(*) AS value
FROM svm_test
GROUP BY c, PREDICTION(SVMC_Model USING *)
ORDER BY 1, 2;

-- 
-- wyliczenie dokladnosci modelu
--
column accuracy format 9.9999

SELECT SUM(correct)/COUNT(*) AS accuracy
FROM (SELECT DECODE(c, PREDICTION(SVMC_Model USING *), 1, 0) AS correct
      FROM svm_test);