LEFT JOIN未返回左表全部行：BigQuery分钟级插槽统计异常

你的查询只返回一条记录，是因为WHERE子句过滤了LEFT JOIN后的结果。当左表（minutes）的时间点没有匹配的job时，右表的所有字段（包括reservation_id）都会是NULL，而WHERE reservation_id = "fake-res-id"会把这些NULL的行全部过滤掉，只保留有匹配job的那一行。

把reservation_id的过滤条件移到LEFT JOIN的ON子句中，同时用IFNULL将无匹配时的SUM结果转为0：

WITH
  minutes AS (
  SELECT
    ts
  FROM
    UNNEST( GENERATE_TIMESTAMP_ARRAY(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 MINUTE), CURRENT_TIMESTAMP(), INTERVAL 1 MINUTE) ) AS ts)
SELECT
  minutes.ts AS DAT,
  IFNULL(SUM(total_slot_ms/TIMESTAMP_DIFF(end_time,creation_time,MILLISECOND)), 0) AS AVG_SLOT_USAGE,
FROM
  minutes
LEFT JOIN
  `fake-project-id.region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT` job
ON
  job.creation_time <= minutes.ts
  AND job.end_time >= minutes.ts
  AND job.reservation_id = "fake-res-id" -- 移到ON子句里
GROUP BY
  minutes.ts
ORDER BY
  minutes.ts DESC

• 将reservation_id = "fake-res-id"从WHERE子句移至LEFT JOIN的ON子句，确保左表所有行都被保留，只关联符合预留ID的job
• 用IFNULL(..., 0)将无匹配时的平均插槽使用量转为0，满足你“即使为0也要显示”的需求

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