在BigQuery中使用UNNEST时出现成本值不匹配问题

一、基础查询Cost值不一致的核心原因 #

对比以下两个查询：
查询1：

select  sum(cost) as cost, sum(credits.amount) as credit FROM `TABLENAME`
        LEFT JOIN UNNEST(credits) AS credits
        WHERE  invoice.month="202404"

查询2：

select  sum(cost) as cost FROM `TABLENAME`
        WHERE  invoice.month="202404"

问题出在UNNEST(credits)的行展开逻辑：

• GCP账单的单条记录可能关联多个credit条目（比如多笔优惠抵扣），LEFT JOIN UNNEST(credits)会把这条原始记录拆分成多行，每行对应一个credit。
• 查询1中SUM(cost)会将拆分后的每条重复记录的cost累加，最终结果是原始cost值乘以该记录关联的credit数量，导致数值虚高。
• 查询2直接对原始表聚合，没有行展开，计算的是真实的原始cost总和。

二、实际聚合查询的重复行问题与优化 #

原查询的重复行根源 #

原查询要将小时数据聚合为日数据，同时过滤cost或credit为0的记录，但出现重复行：

SELECT
              billing_account_id,
              service.id AS service_id,
              service.description AS service_des,
              sku.id AS sku_id,
              sku.description AS sku_des,
        FORMAT_DATETIME('%Y-%m-%d', usage_start_time) AS usage_date,
              location.location AS location,
              location.region AS region,
              location.country AS country,
              location.zone AS zone,
              invoice.month AS invoice_mon,
              currency,
              AVG(currency_conversion_rate) as currency_conversion_rate,
              cost_type,
        resource.name AS resource_name,
              resource.global_name AS res_global_name,
              SUM(usage.amount_in_pricing_units) AS usage_amount_pricing_units,
              usage.pricing_unit AS pricing_unit,
              SUM(cost) AS cost,
              SUM(cost_at_list) AS cost_at_list,
              transaction_type,
              seller_name,
              adjustment_info.id AS adjustment_info_id,
              adjustment_info.description AS adjustment_des,
              adjustment_info.type AS adjustment_type,
              adjustment_info.mode AS adjustment_mode,
              SUM(price.effective_price) AS effective_price,
              SUM(price.pricing_unit_quantity) AS pricing_unit_quantity,
              project.id AS project_id,
              project.number AS project_number,
              project.name AS project_name,
              SUM(IFNULL(credits.amount, 0)) AS credit_amount,
              credits.type  AS credit_type,
              credits.id AS credit_id,
              credits.name as credit_name,
              credits.full_name AS credit_full_name,
              TO_JSON_STRING(labels) AS labels,
              TO_JSON_STRING(system_labels) AS system_labels
        FROM `TABLENAME`
        LEFT JOIN  UNNEST(credits) AS credits
        WHERE (cost!=0 OR credits.amount!=0)  and invoice.month="202404"

        GROUP BY
              billing_account_id,
              service_id,
              service_des,
              sku_id,
              sku_des,
              usage_date,
              location,  
              region,
              country,
              zone,
              invoice_mon,
              currency,
              cost_type,
              resource_name,
              res_global_name,
              usage.pricing_unit,
              transaction_type,
              seller_name,
              adjustment_info_id,
              adjustment_des,
              adjustment_type,
              adjustment_mode,
              project_id,
              project_number,
              project_name,
              credit_type,
              credit_full_name,
              credit_id,
              credit_name,
              labels,
              system_labels   
        HAVING 
            (cost!=0 OR credit_amount!=0)
        ORDER BY
              usage_date

重复行的原因：

• UNNEST(credits)展开后，同一条原始记录会生成多条带不同credit属性（credit_type、credit_id等）的行。
• GROUP BY中包含了这些credit属性字段，导致每个credit条目单独形成一个分组，最终输出多条看似重复但credit属性不同的记录。

编辑后查询的优化逻辑 #

修改后的查询通过WITH OFFSET解决了cost重复计算的问题，同时保留credit明细：

SELECT
              billing_account_id,
              service.id AS service_id,
              service.description AS service_des,
              sku.id AS sku_id,
              sku.description AS sku_des,
        FORMAT_DATETIME('%Y-%m-%d', usage_start_time) AS usage_date,
              location.location AS location,
              location.region AS region,
              location.country AS country,
              location.zone AS zone,
              invoice.month AS invoice_mon,
              currency,
              AVG(currency_conversion_rate) as currency_conversion_rate,
              cost_type,
        resource.name AS resource_name,
              resource.global_name AS res_global_name,
              SUM(usage.amount_in_pricing_units) AS usage_amount_pricing_units,
              usage.pricing_unit AS pricing_unit,
              SUM(IF(IFNULL(credits_offset, 0) = 0, cost, 0)) AS cost,
              SUM(cost_at_list) AS cost_at_list,
              transaction_type,
              seller_name,
              adjustment_info.id AS adjustment_info_id,
              adjustment_info.description AS adjustment_des,
              adjustment_info.type AS adjustment_type,
              adjustment_info.mode AS adjustment_mode,
              SUM(price.effective_price) AS effective_price,
              SUM(price.pricing_unit_quantity) AS pricing_unit_quantity,
              project.id AS project_id,
              project.number AS project_number,
              project.name AS project_name,
              SUM(IFNULL(credits.amount, 0)) AS credit_amount,
              credits.type  AS credit_type,
              credits.id AS credit_id,
              credits.name as credit_name,
              credits.full_name AS credit_full_name,
              TO_JSON_STRING(labels) AS labels,
              TO_JSON_STRING(system_labels) AS system_labels
        FROM `TABLENAME`
    LEFT JOIN UNNEST(credits) credits WITH OFFSET AS credits_offset
        WHERE (cost!=0 OR credits.amount!=0) 

        GROUP BY
              billing_account_id,
              service_id,
              service_des,
              sku_id,
              sku_des,
              usage_date,
              location,  
              region,
              country,
              zone,
              invoice_mon,
              currency,
              cost_type,
              resource_name,
              res_global_name,
              usage.pricing_unit,
              transaction_type,
              seller_name,
              adjustment_info_id,
              adjustment_des,
              adjustment_type,
              adjustment_mode,
              project_id,
              project_number,
              project_name,
              credit_type,
              credit_full_name,
              credit_id,
              credit_name,
              labels,
              system_labels   
        HAVING 
            (cost!=0 OR credit_amount!=0)
        ORDER BY
              usage_date

关键优化点：

1. 使用LEFT JOIN UNNEST(credits) credits WITH OFFSET AS credits_offset获取每个credit条目的偏移量。
2. 通过SUM(IF(IFNULL(credits_offset, 0) = 0, cost, 0))仅在第一条credit条目（或无credit时）累加cost，避免同一原始记录的cost被重复计算。
3. 保留GROUP BY中的credit属性字段，确保每个credit明细能单独展示，同时不会导致cost值虚高。

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