BigQuery中如何对数组结构体列实现自定义计算？可通过存储过程吗？

你之前提到BigQuery内置函数不支持直接处理ARRAY<STRUCT>类型，而存储过程不适合直接作为列返回结果——**SQL用户定义函数（UDF）**正好解决这个问题，它支持复杂类型作为输入，且能像内置函数一样在SELECT语句中直接调用。

1. 定义自定义函数 #

以下是针对你提供的GCP价格层级结构定义的UDF，函数接收层级数组和总使用量两个参数，返回计算后的总费用（以USD为例，如需使用账户货币可替换为account_currency_amount）：

CREATE OR REPLACE FUNCTION `your-project.your-dataset.get_tiers_total_expense`(
  tiered_rates ARRAY<STRUCT<
    pricing_unit_quantity FLOAT64,
    start_usage_amount FLOAT64,
    usd_amount FLOAT64,
    account_currency_amount FLOAT64
  >>,
  total_usage FLOAT64
) RETURNS FLOAT64
LANGUAGE SQL AS $$
WITH sorted_tiers AS (
  -- 先按起始使用量排序，确保层级顺序正确（避免数组元素乱序）
  SELECT *
  FROM UNNEST(tiered_rates)
  ORDER BY start_usage_amount ASC
),
tier_with_limit AS (
  -- 为每个层级计算使用量上限：下一层级的起始量，最后一层无上限则用总使用量
  SELECT
    *,
    LEAD(start_usage_amount) OVER (ORDER BY start_usage_amount) AS end_usage_amount
  FROM sorted_tiers
)
SELECT SUM(
  -- 计算当前层级的实际使用量：不能为负，且不超过总使用量
  GREATEST(0, LEAST(total_usage, COALESCE(end_usage_amount, total_usage)) - start_usage_amount)
  * usd_amount / pricing_unit_quantity  -- 按单位数量换算单价
) AS total_expense
FROM tier_with_limit
WHERE start_usage_amount < total_usage;  -- 跳过超过总使用量的层级
$$;

2. 示例调用 #

按照你提供的层级示例（Tier1：0-20单位，单价10USD；Tier2：20+单位，单价5USD），假设总使用量为25，调用方式如下：

-- 示例：总使用量25，计算总费用
SELECT `your-project.your-dataset.get_tiers_total_expense`(
  ARRAY(
    SELECT AS STRUCT 1.0 AS pricing_unit_quantity, 0.0 AS start_usage_amount, 10.0 AS usd_amount, 9.0 AS account_currency_amount UNION ALL
    SELECT AS STRUCT 1.0, 20.0, 5.0, 4.0
  ),
  25.0
) AS total_usd_expense;

执行后将返回225.0（2010 + 55），完全符合层级计算逻辑。

3. 在视图查询中使用 #

直接在SELECT语句中调用函数，和你期望的用法完全一致：

SELECT
  `your-project.your-dataset.get_tiers_total_expense`(your_tier_column, your_usage_column) AS total_expense,
  *
FROM your_view;

关键说明 #

• 函数内部先对层级按start_usage_amount排序，避免数组元素顺序混乱导致计算错误。
• 使用LEAD窗口函数获取下一层级的起始量，作为当前层级的使用上限；最后一层级无后续层级时，用总使用量作为上限。
• 通过GREATEST(0, ...)确保不会出现负的使用量，LEAST(total_usage, ...)确保不超过总使用量。
• 若需要使用账户货币计算，只需将usd_amount替换为account_currency_amount即可。

内容的提问来源于stack exchange，提问作者Yak O'Poe