38.3 CLUSTER_ID
Syntax
cluster_id::=
Analytic Syntax
cluster_id_analytic::=
mining_attribute_clause::=
mining_analytic_clause::=
See Also:
"Analytic Functions" for information on the syntax, semantics, and restrictions of 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
, wheren
is the number of clusters to compute, andmining_analytic_clause
, which specifies if the data should be partitioned for multiple model builds. Themining_analytic_clause
supports aquery_partition_clause
and anorder_by_clause
. (See "analytic_clause::=".)
The syntax of the CLUSTER_ID
function can use an optional GROUPING
hint when scoring a partitioned model. See GROUPING Hint.
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".)
See Also:
-
Oracle Data Mining User's Guide for information about scoring.
-
Oracle Data Mining Concepts for information about clustering.
Note:
The following examples are excerpted from the Data Mining sample programs. For more information about the sample programs, see Appendix A in Oracle Data Mining User's Guide.
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>