distnaces.sql

distnaces.sql

WITH
  Distances AS (
  SELECT
    DISTINCT ML.CENTROID_ID,
    sepal_length,
    sepal_width,
    petal_length,
    petal_width,
    species,
    MIN(NEAREST_CENTROIDS_DISTANCE.DISTANCE) AS distance_from_closest_centroid
  FROM
    ML.PREDICT(MODEL public.iris_clusters,
      (
      SELECT
        DISTINCT sepal_length,
        sepal_width,
        petal_length,
        petal_width,
        species
      FROM
        `public.iris` )) AS ML
  CROSS JOIN
    UNNEST(NEAREST_CENTROIDS_DISTANCE) AS NEAREST_CENTROIDS_DISTANCE
  GROUP BY
    ML.CENTROID_ID,
    sepal_length,
    sepal_width,
    petal_length,
    petal_width,
    species )