Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
mining_attribute_clause::=
Purpose
This function is for use with clustering models that have been created with the DBMS_DATA_MINING
package or with the Oracle Data Mining Java API. It 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
.
For the optional topN
argument, specify a positive integer. Doing so restricts the set of predicted clusters to those that have one of the top N
probability values. If you omit topN
or set it to NULL
, then all clusters are returned in the collection. If multiple clusters are tied for the Nth
value, the database still returns only N
values.
For the optional cutoff
argument, specify a positive integer to restrict the returned clusters to those with a probability greater than or equal to the specified cutoff. You can filter only by cutoff
by specifying NULL
for topN
and the desired cutoff value for cutoff
.
You can specify topN
and cutoff
together to restrict the returned clusters to those that are in the top N
and have a probability that passes the threshold.
The mining_attribute_clause
behaves as described for the PREDICTION
function. Please refer to mining_attribute_clause.
See Also:
|
Examples
The following example lists the most relevant attributes (with confidence > 55%) of each cluster to which customer 101362 belongs with > 20% likelihood.
This example, and the prerequisite data mining operations, including the creation of the dm_sh_clus_sample
model and the views and type, can be found in the demo file $ORACLE_HOME/rdbms/demo/dmkmdemo.sql
. General information on data mining demo files is available in Oracle Data Mining Administrator's Guide. The example is presented here to illustrate the syntactic use of the function.
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; PROB CL_ID ATTR OP VAL SUPP CONF ------- ---------- --------------- --- --------------- ---------- ------- .7873 8 HOUSEHOLD_SIZE IN 9+ 126 .7500 .7873 8 CUST_MARITAL_ST IN Divorc. 118 .6000 ATUS .7873 8 CUST_MARITAL_ST IN NeverM 118 .6000 ATUS .7873 8 CUST_MARITAL_ST IN Separ. 118 .6000 ATUS .7873 8 CUST_MARITAL_ST IN Widowed 118 .6000 ATUS .2016 6 AGE >= 17 152 .6667 .2016 6 AGE <= 31.6 152 .6667 .2016 6 CUST_MARITAL_ST IN NeverM 168 .6667 ATUS 8 rows selected.