Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E10592-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

CLUSTER_SET

Syntax

Description of cluster_set.gif follows
Description of the illustration cluster_set.gif

mining_attribute_clause::=

Description of mining_attribute_clause.gif follows
Description of the illustration mining_attribute_clause.gif

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.

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. 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.