BigQuery适配Looker约束计算日均去重用户的Array超限问题问询

关于大小限制调整 #

BigQuery的单行/单数组100MB是平台硬限制，无法通过配置调整，必须优化查询逻辑规避。

优化方案 #

方案一：基于HLL草图的近似计算（首推，适合大数据量场景） #

该方案利用BigQuery内置的HLL去重算法，误差可控制在0.1%_{1%之间（可通过精度参数调整），完全避免超大数组生成，性能是精确计算的5}10倍，直接嵌入Looker的SELECT字段即可使用：

-- 日均去重用户数（可调整最后一位精度参数，14对应误差1%/内存16KB，16对应误差0.5%/内存64KB）
(
  SELECT AVG(daily_uv)
  FROM (
    SELECT 
      HLL_COUNT.MERGE(sketch) AS daily_uv
    FROM UNNEST(
      ARRAY_AGG(DISTINCT 
        STRUCT(
          view_date,
          HLL_COUNT.INIT(full_visitor_id, 16) AS sketch
        )
      )
    )
    GROUP BY view_date
  )
) AS average_trend

方案二：精确值优化方案（适合需要严格精确值的场景） #

目前BigQuery已经支持STRUCT类型的DISTINCT操作，无需拼接字符串，可大幅降低数组体积，同时数组仅存储去重后的日期+用户组合，避免冗余数据：

(
  SELECT AVG(daily_uv)
  FROM (
    SELECT COUNT(DISTINCT user_id) AS daily_uv
    FROM UNNEST(
      -- 直接用STRUCT存储，比字符串拼接体积小30%以上，性能更高
      ARRAY_AGG(DISTINCT STRUCT(view_date, full_visitor_id AS user_id))
    )
    GROUP BY view_date
  )
) AS average_trend

如果用户ID是字符串类型，可以先对ID做哈希映射成INT64，进一步减小体积：FARM_FINGERPRINT(full_visitor_id) AS user_id，哈希碰撞概率极低可忽略。

废弃方案说明 #

你之前使用的按日期写CASE WHEN的方案性能极低的原因是：每个COUNT(DISTINCT)都会触发一次全表扫描，1000个日期对应1000次扫描，而上述两种方案均只需要扫描一次表，性能提升可达数百倍。

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