Oracle Data Mining Functions
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
The data mining functions operate on models that have been built using the DBMS_DATA_MINING package or the Oracle Data Mining Java API.

For a close to complete list of Oracle built-in functions and demos in the library, both stand-alone and in built-in packages: [ Click Here ].
System Privileges
ALTER ANY MINING MODEL CREATE ANY MINING MODEL DROP ANY MINING MODEL
COMMENT ANY MINING MODEL CREATE MINING MODEL SELECT ANY MINING MODEL
 
CLUSTER_DETAILS (new 12.1)
Predicts cluster membership for each row. It can use a pre-defined clustering model or perform
dynamic clustering. Returns an XML string that describes the predicted cluster or a specified cluster.
CLUSTER_DETAILS ([schema.]<model> [,<cluster_id>[,<topN>]] [<ABS | ASC | DESC>] USING <mining_attribute_clause>)
SELECT s.cluster_id, probability prob,
  CLUSTER_DETAILS
(em_sh_clus_sample, s.cluster_id, 5 USING t.*) det
FROM (
  SELECT v.*, CLUSTER_SET(em_sh_clus_sample, NULL, 0.2 USING *) pset
  FROM mining_data_apply_v v
  WHERE cust_id = 100955) t,
  TABLE(t.pset) s
ORDER BY 2 DESC;
 
CLUSTER_DISTANCE (new 12.1)
Predicts cluster membership for each row. It can use a pre-defined clustering model or perform dynamic clustering. Returns the raw distance between each row and the centroid of either the predicted cluster or a specified. CLUSTER_DISTANCE([schema.]<model> [,<cluster_id>] USING <mining_attribute_clause>)
SELECT cust_id
FROM (
SELECT cust_id,
rank() over
(order by CLUSTER_DISTANCE(km_sh_clus_sample USING *) DESC) rnk
FROM mining_data_apply_v)
WHERE rnk <= 11
ORDER BY rnk;
 
CLUSTER_ID
Returns the cluster identifier of the predicted cluster with the highest probability for the set of predictors specified in the mining_attribute_clause CLUSTER_ID(<schema.model> <mining_attribute_clause>)
SELECT CLUSTER_ID(km_sh_clus_sample USING *) AS clus, COUNT(*) AS cnt
FROM km_sh_sample_apply_prepared
GROUP BY CLUSTER_ID(km_sh_clus_sample USING *)
ORDER BY cnt DESC;
 
CLUSTER_PROBABILITY
Returns a measure of the degree of confidence of membership of an input row in a cluster associated with the specified model CLUSTER_PROBABILITY(<schema.model>, <cluster_id> <mining_attribute_clause>)
SELECT *
FROM (
  SELECT cust_id, CLUSTER_PROBABILITY(km_sh_clus_sample, 2 USING *) prob
  FROM km_sh_sample_apply_prepared
  ORDER BY prob DESC)
WHERE ROWNUM < 11;
 
CLUSTER_SET
Returns a varray of objects containing all possible clusters that a given row belongs to. Each object in the varray is a pair of scalar values containing the cluster ID and the cluster probability. The object fields are named CLUSTER_ID and PROBABILITY, and both are Oracle NUMBER CLUSTER_SET(<schema.model>, <top N>, <cutoff>
<mining_attribute_clause>);
WITH clus_tab AS (
  SELECT id, a.attribute_name aname, a.conditional_operator op,
  NVL(A.attribute_str_value,
  ROUND(DECODE(a.attribute_name, n.col,
               a.attribute_num_value * N.scale + N.shift,
               a.attribute_num_value),4)) val,
               a.attribute_support support,
               a.attribute_confidence confidence
FROM TABLE(dbms_data_mining.get_model_details_km('km_sh_clus_sample')) t,
     TABLE(t.rule.antecedent) a, km_sh_sample_norm n
WHERE a.attribute_name = n.col(+)
AND a.attribute_confidence > 0.55), clust AS (
  SELECT id, CAST(COLLECT(Cattr(aname, op, TO_CHAR(val), support, confidence)) AS cattrs) cl_attrs
  FROM clus_tab
  GROUP BY id), custclus AS (
    SELECT T.cust_id, S.cluster_id, S.probability
    FROM (
      SELECT cust_id, CLUSTER_SET(km_sh_clus_sample, NULL, 0.2 USING *) pset
      FROM km_sh_sample_apply_prepared
      WHERE cust_id = 101362) t,
      TABLE(t.pset) s)
    SELECT a.probability prob, a.cluster_id cl_id, b.attr, b.op, b.val, b.supp, b.conf
    FROM custclus a, (
      SELECT t.id, c.*
      FROM clust t, TABLE(t.cl_attrs) c) b
      WHERE a.cluster_id = B.id
      ORDER BY prob DESC, cl_id ASC, conf DESC, attr ASC, val ASC;
 
CUBE_TABLE
Extracts data from a cube or dimension and returns it in the two-dimensional format of a relational table, which can be used by SQL-based applications (Mar 29) CUBE_TABLE('<schema_name>.<cube_name> [<HIERARCHY | HRR> <DIMENSION> <HIERARCHY>');

CUBE_TABLE('<schema_name>.<dimension_name> [<HIERARCHY | HRR> [<DIMENSION>] <HIERARCHY>');
conn global/global

SELECT * FROM TABLE(CUBE_TABLE('global.channel'));

-- the docs claim this works ... but from my experience it does not

SQL> SELECT * FROM TABLE(CUBE_TABLE('global.units_cube HIERARCHY customer market_rollup HIERARCHY time calendar'));
SELECT * FROM TABLE(CUBE_TABLE('global.units_cube HIERARCHY customer market_rollup HIERARCHY time ca
*
ERROR at line 1:
ORA-36842: Hierarchy GLOBAL.CUSTOMER.MARKET_ROLLUP was not found


-- the error has been reported to the tech writer
 
FEATURE_DETAILS (new 12.1)
Predicts feature matches for each row. Can use a pre-defined feature extraction model or perform dynamic feature extraction. Returns an XML string that describes the predicted feature or a specified feature. FEATURE_DETAILS(<schema.model><model>[feature_id[,topN]][<ABS | ACS | DESC>]<mining_attribute_clause>
TBD
 
FEATURE_ID
Returns an Oracle NUMBER that is the identifier of the feature with the highest coefficient value FEATURE_ID(<schema.model> <mining_attribute_clause>)
SELECT FEATURE_ID(nmf_sh_sample USING *) AS feat, COUNT(*) AS cnt
FROM nmf_sh_sample_apply_prepared
GROUP BY FEATURE_ID(nmf_sh_sample USING *)
ORDER BY cnt DESC;
 
FEATURE_SET
Returns a varray of objects containing all possible features. Each object in the varray is a pair of scalar values containing the feature ID and the feature value. The object fields are named FEATURE_ID and VALUE, and both are Oracle NUMBERs. FEATURE_SET(<schema.model>, <top N>, <cutoff>
<mining_attribute_clause>)
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('nmf_sh_sample')) 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(nmf_sh_sample, 10 USING *) pset
        FROM nmf_sh_sample_apply_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;
 
FEATURE_VALUE
Returns the value of a given feature. If you omit the feature_id argument, then the function returns the highest feature value. You can use this form in conjunction with the FEATURE_ID function to obtain the largest feature/value combo. FEATURE_VALUE(<schema.model>,<feature_id><mining_attribute_clause>)
SELECT *
FROM (
  SELECT cust_id, FEATURE_VALUE(nmf_sh_sample,3 USING *) match_qual
  FROM nmf_sh_sample_apply_prepared
  ORDER BY match_quality DESC)
WHERE ROWNUM < 11;
 
PREDICTION
Returns the best prediction for the model. The datatype returned depends on the target value type used during the build of the model. For regression models, this function returns the expected value. PREDICTION(<schema.model>, <cost_matrix_clause>
<mining_attribute_clause>)
SELECT cust_gender, COUNT(*) AS cnt, ROUND(AVG(age)) AS avg_age
FROM mining_data_apply_v
WHERE PREDICTION(DT_SH_Clas_sample COST MODEL
USING cust_marital_status, education, household_size) = 1
GROUP BY cust_gender
ORDER BY cust_gender;
 
PREDICTION_BOUNDS
For use only with generalized linear models. It returns an object with two NUMBER fields LOWER and UPPER PREDICTION_BOUNDS(<schema.model>, <confidence_level>, <class_value>
USING <* | schema.table.* | expression AS alias>
SELECT cust_marital_status, COUNT(cust_id) AS CUST_COUNT
FROM (
  SELECT cust_id, cust_marital_status
  FROM mining_data_apply_v
  WHERE PREDICTION_BOUNDS(glmr_sh_regr_sample,0.98 USING *).LOWER > 24
  AND PREDICTION_BOUNDS(glmr_sh_regr_sample,0.98 USING *).UPPER < 46)
GROUP BY cust_marital_status;
 
PREDICTION_COST
Returns a measure of cost for a given prediction as an Oracle NUMBER PREDICTION_COST(<schema.model>, <class> <cost_matrix_clause>
<mining_attribute_clause>)
WITH cust_italy AS (
  SELECT cust_id
  FROM mining_data_apply_v
  WHERE country_name = 'Italy'
  ORDER BY PREDICTION_COST(DT_SH_Clas_sample,1 COST MODEL USING *) ASC,1)
SELECT cust_id
FROM cust_italy
WHERE rownum < 11;
 
PREDICTION_DETAILS
Returns an XML string containing model-specific information related to the scoring of the input row PREDICTION_DETAILS(<schema.model> <mining_attribute_clause>)
SELECT cust_id, education,
PREDICTION_DETAILS(DT_SH_Clas_sample using *) treenode
FROM mining_data_apply_v
WHERE occupation = 'TechSup' AND age < 25
ORDER BY cust_id;
 
PREDICTION_PROBABILITY
Returns the probability for a given prediction as an Oracle NUMBER PREDICTION_PROBABILITY(<schema.model> <class>
<mining_attribute_clause>)
SELECT cust_id
FROM (
  SELECT cust_id
  FROM mining_data_apply_v
  WHERE country_name = 'Italy'
  ORDER BY PREDICTION_PROBABILITY(DT_SH_Clas_sample, 1 USING *)
  DESC, cust_id)
WHERE rownum < 11;
 
PREDICTION_SET
Returns a varray of objects containing all classes in a multiclass classification scenario PREDICTION_SET(<schema.model>, <best N>, <cutoff>
<cost_matrix_clause> <mining_attribute_clause>)
SELECT T.cust_id, S.prediction, S.probability, S.cost
FROM (
  SELECT cust_id,
 PREDICTION_SET(dt_sh_clas_sample COST MODEL USING *) pset
  FROM mining_data_apply_v
  WHERE cust_id < 100011) T,
TABLE(T.pset) S
ORDER BY cust_id, S.prediction;

Related Topics
All Functions
Analytic Functions
Cast
Character Functions
Collection Functions
Conversion Functions
Date Functions
Miscellaneous Functions
Nested Table Functions
Numeric Functions
OLAP Functions
String Functions
Timestamp Functions
XML Functions

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved