BigQuery关联表格执行第3个脚本时空白问题排查求助

一、BigQuery查询稳定性排查 #

• 验证查询结果一致性：手动固定日期参数（比如将_TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))替换为具体日期如'20240520'），多次运行查询，确认每次返回的结果是否一致，是否存在某次返回空结果的情况。
• 检查分区表可用性：确认XXX_BookedDeliveredV2_*和XXX_active_media_quartiles_*对应的昨日分区表已生成且包含数据，避免因分区延迟或数据缺失导致查询无输出。
• 排查聚合逻辑异常：
  • 检查STRING_AGG(DISTINCT ARRAY_TO_STRING(FrequencyCaps, ', '), ', ')：若FrequencyCaps为数组类型，确认是否存在空数组或NULL值导致聚合逻辑异常；
  • 验证MIN(IF(DeliveredImpressions > 0, DeliveryDate, NULL))：若所有行的DeliveredImpressions均为0，该字段会返回NULL，但不会导致整表无数据，需排查其他聚合字段是否存在异常；
  • 核对GROUP BY字段：确认所有非聚合字段均已包含在GROUP BY中，无字段遗漏或类型不匹配（如字符串/数值类型混用）。
• 检查关联逻辑兼容性：
  • 确认filtered_data.LineItemId与XXX_AdsReachFrequencyV2.DeliveredOrder.LineItemId的字段类型完全一致，避免因类型不匹配导致关联失败；
  • 验证insights子查询中CAST(line_item_id AS STRING)后的格式，是否与fd.LineItemId完全匹配（如无多余空格、格式差异）。
• 排查查询性能问题：查看BigQuery查询历史，确认查询是否存在超时、资源不足或扫描数据量过大的情况。Connected Sheet可能因查询超时中断加载，导致显示空白，可尝试优化过滤条件减少数据扫描量。

二、脚本与Connected Sheet交互排查 #

• 隔离脚本步骤：单独执行前两个函数，确认Connected Sheet数据正常；再单独执行第三个函数，观察是否触发空白，定位是否为第三个函数的操作导致问题。
• 检查脚本操作逻辑：查看第三个函数是否包含清空Connected Sheet数据、修改BigQuery链接参数或调整表格范围的代码（如clear()方法、修改查询筛选条件），这些操作可能直接导致表格空白。
• 避免刷新冲突：关闭Connected Sheet的自动刷新功能，手动完成刷新后再运行脚本，排查是否因自动刷新与脚本操作同时执行导致数据冲突。
• 检查数据量限制：确认查询返回的行数未超过Connected Sheet的上限（默认100万行），超量加载可能导致表格显示空白。

用户提供的BigQuery查询代码 #

WITH base_data AS (
  SELECT
    SalesPerson.CountryName,    
    SalesPerson.FullName, 
    Campaign.TypeLevel3, 
    Opportunity.Id AS OpportunityId,
    Opportunity.Code AS OpportunityCode,
    DeliveredOrder.Name AS OrderName,
    DeliveredOrder.Id AS OrderID,
    DeliveredOrder.LineItemId, 
    DeliveredOrder.LineItemName,
    Opportunity.NonStandardProducts, 
    Opportunity.PrimaryKPI, 
    BookedOrder.StartTime, 
    BookedOrder.EndTime, 
    BookedOrder.LineItemStartTime, 
    BookedOrder.LineItemEndTime, 
    BookedOrder.RateTypeCode, 
    BookedOrder.NetEurRate, 
    BookedOrder.FulFilmentItemStatus, 
    DeliveredImpressions,
    DeliveredClicks,
    DeliveredNetEurRevenue,
    DeliveredOrder.DeliveryIndicatorPct,
    BookedOrder.IsOverbookedYn, 
    BookedOrder.AdServer, 
    DeliveredOrder.LineItemPriority,
    DeliveredOrder.DeliveryRateType,
    DeliveredOrder.ImpressionsGoal, 
    Product.Subcategory, 
    DeliveredOrder.FrequencyCaps,
    TargetCountry.Name AS TargetCountryName,
    DeliveredOrder.LineItemStatus,
    DeliveryDate
  FROM `XXX_BookedDeliveredV2_*`
  WHERE 
    _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND SalesPerson.SalesTeamRegion = 'EMEA'
    AND SalesPersonSalesOffice.IsReseller = FALSE
    AND SalesPersonSalesOffice.SubRegionName != 'EMEA Partner Sales' 
    AND Campaign.TypeLevel3 != 'Labels'
    AND BookedOrder.AdServer IN ('DFP', 'Megaphone', 'Megaphone (MTM)')
    AND BookedOrder.FulFilmentItemStatus IN ('Inactive', 'Paused', 'Ready', 'Completed', 'Delivering')
    AND (DATE(BookedOrder.LineItemEndTime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
         OR BookedOrder.LineItemEndTime >= CURRENT_TIMESTAMP())
),

filtered_data AS (
  SELECT
    CountryName,    
    FullName, 
    TypeLevel3, 
    OpportunityId,
    OpportunityCode,
    OrderName,
    OrderID,
    LineItemId, 
    LineItemName,
    NonStandardProducts, 
    PrimaryKPI, 
    StartTime, 
    EndTime, 
    LineItemStartTime, 
    LineItemEndTime, 
    RateTypeCode, 
    NetEurRate, 
    FulFilmentItemStatus, 
    SUM(DeliveredImpressions) AS DeliveredImpressions, 
    SUM(DeliveredClicks) AS DeliveredClicks,
    SUM(DeliveredNetEurRevenue) AS DeliveredNetRevenue,
    AVG(DeliveryIndicatorPct) AS DeliveryIndicatorPct,
    IsOverbookedYn, 
    AdServer, 
    LineItemPriority,
    DeliveryRateType,
    ImpressionsGoal, 
    Subcategory, 
    STRING_AGG(DISTINCT ARRAY_TO_STRING(FrequencyCaps, ', '), ', ') AS FrequencyCaps,
    TargetCountryName,
    LineItemStatus,
    MIN(IF(DeliveredImpressions > 0, DeliveryDate, NULL)) AS first_delivery_date
  FROM base_data
  GROUP BY
    CountryName, FullName, TypeLevel3, OpportunityId, OpportunityCode, OrderName, OrderID, 
    LineItemId, LineItemName, NonStandardProducts, PrimaryKPI, StartTime, EndTime, 
    LineItemStartTime, LineItemEndTime, RateTypeCode, NetEurRate, FulFilmentItemStatus, 
    IsOverbookedYn, AdServer, LineItemPriority, DeliveryRateType, ImpressionsGoal, 
    Subcategory, TargetCountryName, LineItemStatus
)

SELECT
  fd.*,
  Reach.ReachLifetime,
  IFNULL(insights.events_started, 0) AS events_started,
  IFNULL(insights.events_ended, 0) AS events_ended,
  IFNULL(insights.events_ended / NULLIF(insights.events_started, 0), 0) AS CR
FROM filtered_data fd
LEFT JOIN `XXX_AdsReachFrequencyV2` AS Reach
  ON fd.LineItemId = Reach.DeliveredOrder.LineItemId
LEFT JOIN (
  SELECT
    line_item_id,
    SUM(events_started) AS events_started,
    SUM(events_ended) AS events_ended
  FROM `XXX_active_media_quartiles_*`
  WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  GROUP BY line_item_id
) AS insights
  ON fd.LineItemId = CAST(insights.line_item_id AS STRING)

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