按标签分析 BigQuery 费用

-- Break down on-demand cost by job label

DECLARE lookback_days INT64 DEFAULT 30;

WITH flattened AS (
  SELECT
    job_id,
    lbl.key AS label_key,
    lbl.value AS label_value,
    COALESCE(total_bytes_billed, 0) AS bytes_billed,
    ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY end_time DESC) AS rn
  FROM `your-project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  CROSS JOIN UNNEST(labels) AS lbl
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL lookback_days DAY)
    AND state = 'DONE'
    AND total_slot_ms IS NOT NULL
    AND lbl.key IS NOT NULL
),
deduplicated AS (
  SELECT label_key, label_value, bytes_billed
  FROM flattened WHERE rn = 1
)
SELECT
  label_key,
  label_value,
  COUNT(*) AS job_count,
  ROUND(SUM(bytes_billed) / POW(1024, 3), 2) AS gib_scanned,
  ROUND(SUM(bytes_billed) / POW(1024, 4) * 6.25, 2) AS estimated_cost_usd
FROM deduplicated
GROUP BY label_key, label_value
...