Nnmf.plsql

SET serveroutput ON
SET pages 10000
SET linesize 100
SET echo ON


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

-- usuniecie obiektow pozostalych po poprzednich uruchomieniach
BEGIN 
  EXECUTE IMMEDIATE 'DROP TABLE normalization';
EXCEPTION 
  WHEN OTHERS THEN 
	  NULL; 
END;
/

BEGIN 
  EXECUTE IMMEDIATE 'DROP VIEW v_normalized';
EXCEPTION 
  WHEN OTHERS THEN 
	  NULL; 
END;
/

--
-- normalizacja atrybutu numerycznego AGE
--
BEGIN
	-- utworzenie tabeli do przechowywania ustawien normalizacji
  DBMS_DATA_MINING_TRANSFORM.CREATE_NORM_LIN(
    norm_table_name => 'normalization');

	-- wyliczenie parametrow normalizacji
  DBMS_DATA_MINING_TRANSFORM.INSERT_NORM_LIN_MINMAX (
    norm_table_name  => 'normalization',
    data_table_name  => 'mining_data_build_v',
    exclude_list     => dbms_data_mining_transform.column_list (
                        'yrs_residence',
                        'affinity_card',
                        'bulk_pack_diskettes',
                        'flat_panel_monitor',
                        'home_theater_package',
                        'bookkeeping_application',
                        'printer_supplies',
                        'y_box_games',
                        'os_doc_set_kanji',
                        'cust_id')
  );

  -- utworzenie perspektywy przedstawiajacej znormalizowane dane
  DBMS_DATA_MINING_TRANSFORM.XFORM_NORM_LIN (
    norm_table_name => 'normalization',
    data_table_name => 'mining_data_build_v',
    xform_view_name => 'v_normalized');
END;
/

-- usuniecie starej tabeli przechowujacej ustawienia
BEGIN 
  EXECUTE IMMEDIATE 'DROP TABLE settings';
EXCEPTION 
  WHEN OTHERS THEN 
	  NULL; 
END;
/

-- utworzenie tabeli z ustawieniami i wypelnienie jej parametrami algorytmu
CREATE TABLE settings (
  setting_name  VARCHAR2(30),
  setting_value VARCHAR2(30));
 
BEGIN       
  INSERT INTO settings (setting_name, setting_value) VALUES (dbms_data_mining.feat_num_features, 10);
  INSERT INTO settings (setting_name, setting_value) VALUES (dbms_data_mining.nmfs_conv_tolerance,0.1);
  INSERT INTO settings (setting_name, setting_value) VALUES (dbms_data_mining.nmfs_num_iterations,50);
  INSERT INTO settings (setting_name, setting_value) VALUES (dbms_data_mining.nmfs_random_seed,-1);
  COMMIT;
END;
/

-- usuniecie poprzednio wygenerowanego modelu
BEGIN 
  DBMS_DATA_MINING.DROP_MODEL('NNMF_Model');
EXCEPTION 
  WHEN OTHERS THEN 
	  NULL; 
END;
/

-- zbudowanie modelu
BEGIN
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'NNMF_Model',
    mining_function     => dbms_data_mining.feature_extraction,
    data_table_name     => 'v_normalized',
    case_id_column_name => 'cust_id');
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('NNMF_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('NNMF_Model'))
ORDER BY attribute_name;

-- wyswietlenie szczegolowych danych o modelu
column attribute_name format a40;
column attribute_value format a20;
SELECT F.feature_id,
       A.attribute_name,
       A.attribute_value,
       A.coefficient
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_NMF('NNMF_Model')) F,
       TABLE(F.attribute_set) A
ORDER BY feature_id,attribute_name,attribute_value;

-----------------------------------------------------------------------
--                            Zastosowanie modelu
-----------------------------------------------------------------------

-- usuniecie starych struktur z bazy danych
BEGIN 
  EXECUTE IMMEDIATE 'DROP VIEW v_prepared';
EXCEPTION 
  WHEN OTHERS THEN 
	  NULL; 
END;
/

BEGIN 
  EXECUTE IMMEDIATE 'DROP TYPE Featattrs';
EXCEPTION 
  WHEN OTHERS THEN 
	  NULL; 
END;
/

BEGIN 
  EXECUTE IMMEDIATE 'DROP TYPE Featattr';
EXCEPTION 
  WHEN OTHERS THEN 
	  NULL; 
END;
/

-- utworzenie perspektywy z danymi do zastosowania
BEGIN
  DBMS_DATA_MINING_TRANSFORM.XFORM_NORM_LIN (
    norm_table_name => 'normalization',
    data_table_name => 'mining_data_apply_v',
    xform_view_name => 'v_prepared');
END;
/

--
-- wykorzystanie funkcji SQL do uzycia modelu i wyswietlenia szukanych cech
--


-- 
-- wyswietl cechy odpowiadajace klientom w podanym zbiorze danych
-- 
SELECT FEATURE_ID(NNMF_Model USING *) AS feat, COUNT(*) AS cnt
  FROM v_prepared
GROUP BY FEATURE_ID(NNMF_Model USING *)
ORDER BY cnt DESC;

-- 
-- wyswietl 10 klientow odpowiadajacych cesze nr.3 w kolejnosci dokladnosci dopasowania
--
SELECT *
  FROM (SELECT cust_id, FEATURE_VALUE(NNMF_Model, 3 USING *) match_quality
          FROM v_prepared
        ORDER BY match_quality DESC)
	WHERE ROWNUM < 11;

column value format 99.9999
column fid format 999
column attr format a25
column val format a20
column coeff format 9.9999

-- 
-- wyswietl 10 najwazniejszych cech opisujacych wybranego klienta (cust_id = 100002)
-- i zidentyfikuj najistotniejsze atrybuty (coefficient > 0.25) dla kazdej cechy
--
CREATE TYPE Featattr AS OBJECT (
  attr VARCHAR2(30),
  val  VARCHAR2(30),
  coeff NUMBER)
/

CREATE TYPE Featattrs AS TABLE OF Featattr
/

column val format a50
WITH
feat_tab AS (
SELECT F.feature_id fid,
       A.attribute_name attr,
       TO_CHAR(A.attribute_value) val,
       A.coefficient coeff
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_NMF('NNMF_Model')) F,
       TABLE(F.attribute_set) A
 WHERE A.coefficient > 0.25
),
feat AS ( SELECT fid, CAST(COLLECT(Featattr(attr, val, coeff)) AS Featattrs) f_attrs
          FROM feat_tab GROUP BY fid ),
cust_10_features AS (
SELECT T.cust_id, S.feature_id, S.value
  FROM (SELECT cust_id, FEATURE_SET(NNMF_Model, 10 USING *) pset
          FROM v_prepared
         WHERE cust_id = 100002) T,
       TABLE(T.pset) S
)
SELECT A.value, A.feature_id fid,
       B.attr, B.val, B.coeff
  FROM cust_10_features A,
       (SELECT T.fid, F.*
          FROM feat T,
               TABLE(T.f_attrs) F) B
 WHERE A.feature_id = B.fid
ORDER BY A.value DESC, A.feature_id ASC, coeff DESC, attr ASC, val ASC;