按查询分析 BigQuery 费用

-- Per-query cost breakdown with slot and duration metrics

DECLARE lookback_days INT64 DEFAULT 7;

WITH base_jobs AS (
  SELECT
    project_id,
    query,
    start_time,
    end_time,
    COALESCE(total_bytes_billed, 0) AS bytes_billed,
    total_slot_ms,
    TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS duration_ms,
    ROUND(SAFE_DIVIDE(total_slot_ms,
      TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)), 2) AS avg_slots
  FROM `your-project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL lookback_days DAY)
    AND state = 'DONE' AND total_slot_ms IS NOT NULL
),
with_billing AS (
  SELECT *,
    CEIL(avg_slots / 100) * 100 AS billed_slots,
    GREATEST(duration_ms, 60000) / 3.6e6 AS billed_hours,
    GREATEST(bytes_billed, 10 * POW(1024, 2)) AS billed_bytes
  FROM base_jobs
),
freq AS (
  SELECT query, COUNT(*) AS exec_count FROM base_jobs GROUP BY query
)
SELECT
...