Ar.plsql

--
-- ustawienia wyswietlania
--
set pages 10000
set linesize 150
column item format a30
column setting_value format a30

--
-- utworzenie perspektyw podlegajacych eksploracji
--
CREATE OR REPLACE VIEW MARKET_BASKET_DATA AS 
SELECT cust_id, prod_name, 1 has_it 
FROM (SELECT a.cust_id, b.prod_name 
      FROM SH.SALES a, SH.PRODUCTS b 
      WHERE a.prod_id = b.prod_id AND a.cust_id between 100001 AND 104500) 
GROUP BY cust_id, prod_name ;

CREATE OR REPLACE VIEW MARKET_BASKET_DATA_AR AS 
SELECT D.CUST_ID, CAST(MULTISET( SELECT DM_Nested_Numerical(SUBSTR(C.PROD_NAME, 1, 30), has_it) 
                                 FROM MARKET_BASKET_DATA C 
                                 WHERE C.CUST_ID = D.CUST_ID ) AS DM_Nested_Numericals) CUSTPRODUS 
FROM MARKET_BASKET_DATA D;

--
-- usuniecie modelu z repozytorium ODM
--
BEGIN
  dbms_data_mining.drop_model('Asocjacje');
EXCEPTION
  WHEN OTHERS THEN Null;
END;
/

--
-- utworzenie tabeli z ustawieniami algorytmu
--
DROP TABLE settings;

CREATE TABLE settings (
  setting_name VARCHAR2(30),
  setting_value VARCHAR2(128) );

BEGIN
  INSERT INTO settings VALUES (dbms_data_mining.asso_max_rule_length,3);
  INSERT INTO settings VALUES (dbms_data_mining.asso_min_support,0.1);
  INSERT INTO settings VALUES (dbms_data_mining.asso_min_confidence,0.1);
  COMMIT;
END;
/

--
-- utworzenie modelu eksploracji (faktyczne uruchomienie algorytmu)
--
BEGIN
  dbms_data_mining.create_model(
    model_name => 'Asocjacje',
    mining_function => dbms_data_mining.association,
    data_table_name => 'market_basket_data_ar',
		case_id_column_name => 'cust_id',
    settings_table_name => 'settings' );
END;
/

--
-- wyswietlenie ustawien modelu
--
SELECT * FROM TABLE (
  dbms_data_mining.get_model_settings('Asocjacje'))
ORDER BY setting_name;

--
-- wyswietlenie odkrytych zbiorow czestych
--
SELECT t.itemset_id, i.column_value AS item, t.support, t.number_of_items
FROM TABLE (dbms_data_mining.get_frequent_itemsets('Asocjacje')) t, TABLE (t.items) i
GROUP BY t.itemset_id, i.column_value, t.support, t.number_of_items
ORDER BY t.itemset_id, i.column_value, t.support, t.number_of_items;

--
-- wyswietlenie odkrytych regul asocjacyjnych
--
SELECT t.rule_id, t.rule_support, t.rule_confidence, a.attribute_name, c.attribute_name
FROM TABLE (dbms_data_mining.get_association_rules('Asocjacje')) t, 
     TABLE (t.antecedent) a, TABLE (t.consequent) c
ORDER BY t.rule_id, t.rule_support DESC, t.rule_confidence;