Unsupervised clustering function

Clustering function

• This function is available only in the China (Shanghai) region.

• The function supports a maximum data volume of 500,000 rows and 30 columns. A maximum of six columns can be used for clustering.

• This clustering function is used for offline scheduling.

• Density-based clustering is an unsupervised method used to identify patterns and abnormal data. Because the algorithms are sensitive to the input order of the data, different input orders may produce different clustering results.

Function format

SELECT DBSCAN(config,col_list_for_cluster,
col_for_cluster,other_col) 
as(cluster_result,col_for_cluster,other_col)
FROM table;

Input parameters

• config: Required. A JSON string with the following parameters:

  • "eps": The maximum distance between two points to be considered part of the same cluster.

  • "min_samples": The minimum number of points required to form a cluster.

• col_list_for_cluster: Required. A JSON string that specifies the ordinal numbers of the numeric columns to use for clustering.

• col_for_cluster: Required. The numeric columns to use for clustering. The data type must be double or int. You can specify a maximum of six columns.

• other_col: Required. Other input columns to include in the output along with the clustering results. This parameter accepts any data type.

Output parameters

• cluster_result: The clustering result. The data type is int.

• col_for_cluster: The columns used for clustering. The data type is double or int. A maximum of six columns are returned.

• other_col: The other input columns. The data type is string.

Function example: Device data clustering

Download processed device usage information. A single type of Internet of Things (IoT) device can have different data features depending on the user group. Clustering the data reported by devices helps create user personas for your business. You can import the data into a custom storage table in DataService Studio and use it for analysis. Because this example reads data from a custom storage table, the data must be re-sorted after it is read.

• Cluster device data.

  • To use the first and second input columns for clustering, set col_list_for_cluster to '[0,1]'. Then, enter the column names feature1 and feature2.

    SELECT DBSCAN('{"eps":0.1,"min_samples":17}','[0,1]',
    feature1,feature2) 
    as(cluster_result,feature1,feature2)
    FROM 
    (SELECT feature1, feature2 FROM dbscandata_400 order by idx);

  • This example does not include extra columns. If you want to include other columns, you can enter their names after feature2.

    SELECT DBSCAN('{"eps":0.1,"min_samples":17}','[0,1]',
    feature1,feature2) 
    as(cluster_result,feature1,feature2,other_col)
    FROM 
    (SELECT feature1, feature2 FROM dbscandata_400 order by idx);

  • You can use Python to plot a graph based on the output data. The graph shows the input data divided into two groups. You can use the clustering results to gain more insight into the data and understand the user information it contains.

    

• Configure eps.

  You can configure eps to set the maximum distance between any two sample points in the same cluster. This determines the cluster density. Decreasing this maximum distance makes the points within a cluster more similar, which results in a higher cluster density. In this example, eps is set to 0.2 to include as much data as possible in the resulting clusters.

  SELECT DBSCAN('{"eps":0.2,"min_samples":17}','[0,1]',
  feature1,feature2) 
  as(cluster_result,feature1,feature2)
  FROM (SELECT feature1, feature2 FROM dbscandata_400 order by idx);

  A graph is plotted based on the output data. The graph on the right shows that points that were originally outside the clusters are now included. This helps you analyze the impact of user data scattered around the cluster centers on the overall user profile. If eps is set too large, the border between two clusters becomes less clear. This affects the final clustering result.

  

• Configure min_samples.

  You can configure min_samples to set the minimum number of data points required for a cluster. This determines the cluster size. Decreasing the minimum number of data points results in smaller and more numerous clusters. In this example, min_samples is set to 3.

  SELECT DBSCAN('{"eps":0.10,"min_samples":3}','[0,1]',
  feature1,feature2) 
  as(cluster_result,feature1,feature2)
  FROM (SELECT feature1, feature2 FROM dbscandata_400 order by idx);

  A graph is plotted based on the output data. The graph on the right shows that data that did not originally belong to the two clusters is now included. The effect is similar to that of configuring eps. If min_samples is set too small, multiple clusters may appear. This can make the user profile too detailed and affect the final analysis result.