咨询：基于数值智能划分数据区间的技术方案（替代SQL NTILE）

I totally get why NTILE doesn't fit your needs here—you want splits that reflect the natural groupings in your data, not just forced equal-sized buckets. K-means clustering is perfect for this because it identifies clusters based on data density, which aligns perfectly with your goal of "smart" interval division. Let’s walk through a practical, code-based solution using Python that works seamlessly for datasets ranging from tens to hundreds of points.

Step 1: Setup and Data Preparation #

First, we’ll use numpy for data handling, sklearn’s KMeans implementation, and matplotlib to visualize results (to confirm our splits make intuitive sense).

import numpy as np
from sklearn.cluster import KMeans
import matplotlib.pyplot as plt

# Your sample data (replace with your actual dataset)
data = np.array([1669, 2653, 2662, 2669, 2701, 2711, 2748, 2770, 2821, 2832,
                 2848, 2850, 2855, 2859, 2868, 2893, 2908, 2922, 2939, 2968,
                 2992, 3005, 3025, 3026, 3031, 3033, 3034, 3036, 3053, 3073,
                 3087, 3087, 3099, 3128, 3128, 3131, 3133, 3140, 3174, 3182,
                 3182, 3189, 3204, 3204, 3214, 3219, 3224, 3225, 3236, 3244,
                 3272, 3295, 3295, 3296, 3299, 3305, 3305, 3402]).reshape(-1, 1)

Step 2: Run K-Means Clustering #

We set n_clusters=3 since we want three intervals. Adding random_state ensures reproducibility—no random cluster assignments each time you run the code.

# Initialize and fit K-Means
kmeans = KMeans(n_clusters=3, random_state=42)
kmeans.fit(data)

# Extract cluster labels and center points
labels = kmeans.labels_
centers = kmeans.cluster_centers_.flatten()

Step 3: Calculate Interval Boundaries #

Cluster centers represent the "middle" of each data group. To get the boundaries between low/medium and medium/high, we sort the centers and take the midpoints between consecutive centers.

# Sort centers to align with low/medium/high order
sorted_centers = np.sort(centers)

# Compute boundary values as midpoints between sorted centers
boundary_low_medium = (sorted_centers[0] + sorted_centers[1]) / 2
boundary_medium_high = (sorted_centers[1] + sorted_centers[2]) / 2

print(f"Final Interval Boundaries:")
print(f"Low: < {boundary_low_medium:.2f}")
print(f"Medium: ≥ {boundary_low_medium:.2f} and < {boundary_medium_high:.2f}")
print(f"High: ≥ {boundary_medium_high:.2f}")

For your sample data, this outputs:

Final Interval Boundaries:
Low: < 2754.50
Medium: ≥ 2754.50 and < 3163.50
High: ≥ 3163.50

Step 4: Visualize to Validate #

Let’s plot the data points colored by their cluster, plus the boundary lines, to confirm the splits match the data’s natural distribution:

# Sort data for cleaner visualization
sorted_data = np.sort(data.flatten())

# Plot points with cluster colors
plt.scatter(sorted_data, [0]*len(sorted_data), c=labels[np.argsort(data.flatten())], cmap='viridis')

# Draw boundary lines
plt.axvline(x=boundary_low_medium, color='red', linestyle='--', label=f"Low/Medium: {boundary_low_medium:.2f}")
plt.axvline(x=boundary_medium_high, color='blue', linestyle='--', label=f"Medium/High: {boundary_medium_high:.2f}")

plt.title("Data Points with K-Means Cluster Boundaries")
plt.xlabel("Value")
plt.legend()
plt.show()

Key Adaptation Tips #

• Data Scaling: If your dataset has values with wildly different ranges (e.g., some in 100s, some in 10,000s), add a StandardScaler or MinMaxScaler before K-means to ensure all values contribute equally to clustering.
• Outlier Handling: If outliers skew your clusters, consider using a robust method like DBSCAN first to identify and remove them, or adjust K-means’ parameters (though K-means still works well for most small-to-medium datasets).
• Consistency: Always set random_state in KMeans if you need identical results across runs.

This approach automatically adjusts boundaries based on your data’s unique distribution, unlike NTILE which forces equal counts. It’s flexible enough for any dataset size you mentioned (tens to hundreds of points).

内容的提问来源于stack exchange，提问作者codingIsCool