CLUSTER_ID

Syntax

cluster_id::=

Analytic Syntax

cluster_id_analytic::=

mining_attribute_clause::=

mining_analytic_clause::=

Purpose

CLUSTER_ID returns the identifier of the highest probability cluster for each row in the selection. The cluster identifier is returned as an Oracle NUMBER.

Syntax Choice

CLUSTER_ID can score the data in one of two ways: It can apply a mining model object to the data, or it can dynamically mine the data by executing an analytic clause that builds and applies one or more transient mining models. Choose Syntax or Analytic Syntax:

• Syntax — Use the first syntax to score the data with a pre-defined model. Supply the name of a clustering model.

• Analytic Syntax — Use the analytic syntax to score the data without a pre-defined model. Include INTO n, where n is the number of clusters to compute, and mining_analytic_clause, which specifies if the data should be partitioned for multiple model builds. The mining_analytic_clause supports a query_partition_clause and an order_by_clause. (See "analytic_clause::=".)

mining_attribute_clause

mining_attribute_clause identifies the column attributes to use as predictors for scoring. When the function is invoked with the analytic syntax, these predictors are also used for building the transient models. The mining_attribute_clause behaves as described for the PREDICTION function. (See "mining_attribute_clause::=".)

Example

The following example lists the clusters into which the customers in mining_data_apply_v have been grouped.

SELECT CLUSTER_ID(km_sh_clus_sample USING *) AS clus, COUNT(*) AS cnt 
 FROM mining_data_apply_v
 GROUP BY CLUSTER_ID(km_sh_clus_sample USING *)
 ORDER BY cnt DESC;
 CLUS CNT
---------- ----------
 2 580
 10 216
 6 186
 8 115
 19 110
 12 101
 18 81
 16 39
 17 38
 14 34

Analytic Example

This example divides the customer database into four segments based on common characteristics. The clustering functions compute the clusters and return the score without a predefined clustering model.

SELECT * FROM (
 SELECT cust_id,
 CLUSTER_ID(INTO 4 USING *) OVER () cls,
 CLUSTER_DETAILS(INTO 4 USING *) OVER () cls_details
 FROM mining_data_apply_v)
WHERE cust_id <= 100003
ORDER BY 1; 
 
CUST_ID CLS CLS_DETAILS
------- --- -----------------------------------------------------------------------------
 100001 5 <Details algorithm="K-Means Clustering" cluster="5">
 <Attribute name="FLAT_PANEL_MONITOR" actualValue="0" weight=".349" rank="1"/>
 <Attribute name="BULK_PACK_DISKETTES" actualValue="0" weight=".33" rank="2"/>
 <Attribute name="CUST_INCOME_LEVEL" actualValue="G: 130,000円 - 149,999円"
 weight=".291" rank="3"/>
 <Attribute name="HOME_THEATER_PACKAGE" actualValue="1" weight=".268" rank="4"/>
 <Attribute name="Y_BOX_GAMES" actualValue="0" weight=".179" rank="5"/>
 </Details>
 100002 6 <Details algorithm="K-Means Clustering" cluster="6">
 <Attribute name="CUST_GENDER" actualValue="F" weight=".945" rank="1"/>
 <Attribute name="CUST_MARITAL_STATUS" actualValue="NeverM" weight=".856" rank="2"/>
 <Attribute name="HOUSEHOLD_SIZE" actualValue="2" weight=".468" rank="3"/>
 <Attribute name="AFFINITY_CARD" actualValue="0" weight=".012" rank="4"/>
 <Attribute name="CUST_INCOME_LEVEL" actualValue="L: 300,000円 and above" 
 weight=".009" rank="5"/>
 </Details>
 
 100003 7 <Details algorithm="K-Means Clustering" cluster="7">
 <Attribute name="CUST_MARITAL_STATUS" actualValue="NeverM" weight=".862" rank="1"/>
 <Attribute name="HOUSEHOLD_SIZE" actualValue="2" weight=".423" rank="2"/>
 <Attribute name="HOME_THEATER_PACKAGE" actualValue="0" weight=".113" rank="3"/>
 <Attribute name="AFFINITY_CARD" actualValue="0" weight=".007" rank="4"/>
 <Attribute name="CUST_ID" actualValue="100003" weight=".006" rank="5"/>
 </Details>