如何通过BigQuery ML的KMeans计算向量欧氏距离并实现数据集抽样？

一、随机划分训练集与测试集（75%/25%） #

在BigQuery中，利用RAND()生成0-1的随机数，即可实现按比例随机抽样划分数据集：

WITH split_dataset AS (
  SELECT 
    V_1, 
    V_2,
    -- 75%概率分配到训练集，剩余25%为测试集
    CASE WHEN RAND() <= 0.75 THEN 'TRAIN' ELSE 'TEST' END AS dataset_type
  FROM `Training.training dataset`
)
-- 按需筛选训练/测试集，或直接查看全部分配结果
SELECT * FROM split_dataset;

如果需要固定划分结果（方便复现实验），可以结合HASH()函数锁定随机种子：

CASE WHEN MOD(ABS(HASH(CONCAT(V_1, V_2))), 100) <= 75 THEN 'TRAIN' ELSE 'TEST' END AS dataset_type

二、基于BigQuery ML KMeans计算欧氏距离 #

你之前的SQL逻辑有误——用MIN(V_1)-MAX(V_2)计算的是全局极值的差值，并非向量间的欧氏距离。正确流程是先训练KMeans模型，再计算样本到对应聚类中心的欧氏距离：

1. 训练KMeans聚类模型 #

使用训练集训练模型（可根据需求调整聚类数量NUM_CLUSTERS）：

CREATE OR REPLACE MODEL `Training.kmeans_cluster_model`
OPTIONS(
  MODEL_TYPE='KMEANS',
  NUM_CLUSTERS=3, -- 自定义聚类数量
  STANDARDIZE_FEATURES=True -- 标准化特征，优化聚类效果（可选）
) AS
SELECT V_1, V_2
FROM `Training.training dataset`
-- 用划分好的训练集训练
WHERE (SELECT CASE WHEN RAND() <= 0.75 THEN 'TRAIN' ELSE 'TEST' END) = 'TRAIN';
-- 若用前面的split_dataset CTE，可替换为：FROM split_dataset WHERE dataset_type = 'TRAIN'

2. 计算样本到聚类中心的欧氏距离 #

通过ML.PREDICT获取聚类结果，再套用欧氏距离公式计算：

SELECT
  input.V_1,
  input.V_2,
  pred.cluster_id,
  -- 欧氏距离公式：sqrt((x1-x2)² + (y1-y2)²)
  ROUND(
    SQRT(
      POW(input.V_1 - pred.centroid_V_1, 2) + 
      POW(input.V_2 - pred.centroid_V_2, 2)
    ), 4
  ) AS euclidean_distance_to_centroid
FROM ML.PREDICT(
  MODEL `Training.kmeans_cluster_model`,
  (SELECT V_1, V_2 FROM `Training.training dataset`)
) AS pred
JOIN `Training.training dataset` AS input
ON input.V_1 = pred.V_1 AND input.V_2 = pred.V_2;

补充：计算任意两个样本间的欧氏距离 #

如果需要计算数据集内两两样本的欧氏距离，可通过自连接实现：

SELECT
  a.V_1 AS sample_a_v1,
  a.V_2 AS sample_a_v2,
  b.V_1 AS sample_b_v1,
  b.V_2 AS sample_b_v2,
  ROUND(
    SQRT(POW(a.V_1 - b.V_1, 2) + POW(a.V_2 - b.V_2, 2)), 4
  ) AS euclidean_distance_between_samples
FROM `Training.training dataset` a
CROSS JOIN `Training.training dataset` b
-- 排除样本与自身的距离
WHERE NOT (a.V_1 = b.V_1 AND a.V_2 = b.V_2);

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