寻求更简洁SQL方案：合并数值与类别特征为STRUCT数组

嘿，我有几个更清爽的方案来替代你当前的SQL，既能实现合并(feature,numerical_value)与categorical_value结构体数组、跳过空category的需求，还能减少重复调用ML.CENTROIDS(MODEL \modelv1`)`的次数，代码也更好维护：

方案一：用LATERAL子句整合两类特征的处理逻辑 #

这个方案只调用一次模型中心函数，把分类特征和数值特征的处理逻辑集中在一个子句里，结构更清晰：

SELECT
  centroid_id,
  ARRAY_AGG(
    struct(name, value) 
    ORDER BY name -- 可根据需求调整排序字段，比如centroid_id
  ) AS cluster
FROM ML.CENTROIDS(MODEL `modelv1`),
  LATERAL (
    -- 同时处理分类特征和数值特征，过滤无效条目
    SELECT * FROM (
      -- 处理分类特征：跳过空category，拼接feature和category作为name
      SELECT concat(feature, '_', category) AS name, value 
      FROM UNNEST(categorical_value) 
      WHERE length(category) > 0
      UNION ALL
      -- 处理数值特征：仅保留非空的数值条目
      SELECT feature AS name, numerical_value AS value 
      WHERE numerical_value IS NOT NULL
    )
  ) AS all_valid_features
GROUP BY centroid_id
ORDER BY centroid_id

方案优势： #

• 仅调用一次ML.CENTROIDS，避免重复计算，提升查询性能
• 把两类特征的过滤、转换逻辑整合在一起，代码结构更紧凑，易读易维护
• 可以灵活调整两类特征的顺序（调整UNION ALL前后的子查询顺序即可）

方案二：用数组拼接直接合并特征数组 #

这个方案更贴近“合并两个数组”的直观需求，用ARRAY_CONCAT把处理后的分类特征数组和数值特征数组合并，再展开聚合：

SELECT
  centroid_id,
  ARRAY_AGG(
    struct(name, value) 
    ORDER BY name
  ) AS cluster
FROM ML.CENTROIDS(MODEL `modelv1`),
  LATERAL (
    SELECT UNNEST(
      ARRAY_CONCAT(
        -- 生成过滤后的分类特征数组
        ARRAY(
          SELECT struct(concat(feature, '_', category) AS name, value)
          FROM UNNEST(categorical_value)
          WHERE length(category) > 0
        ),
        -- 生成数值特征数组（非空时才生成，否则为空数组）
        IF(numerical_value IS NOT NULL, [struct(feature AS name, numerical_value AS value)], [])
      )
    ) AS feature_entry
  )
GROUP BY centroid_id
ORDER BY centroid_id

方案优势： #

• 用数组操作直接表达“合并两个特征集合”的业务逻辑，语义更直观
• 同样仅调用一次ML.CENTROIDS，性能更优
• 可以通过调整ARRAY_CONCAT的参数顺序，轻松控制分类特征和数值特征在结果数组中的先后顺序

对比你原来的SQL，这两个方案都避免了重复调用模型中心函数，代码行数更少，逻辑也更清晰，你可以根据自己的偏好选择其中一种。

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