揪出讓平均值漏算資料列的事件欄位改名

WITH normalized AS (
  SELECT
    job_id,
    schema_version,
    duration_ms,
    CASE schema_version
      WHEN 1 THEN duration_ms
      WHEN 2 THEN latency_ms
    END AS measured_ms
  FROM job_finished
)
SELECT
  COUNT(*) AS completed_jobs,
  COUNT(duration_ms) AS jobs_in_old_average,
  ROUND(AVG(duration_ms), 1) AS old_average_ms,
  COUNT(measured_ms) AS jobs_with_duration,
  SUM(CASE WHEN measured_ms IS NULL THEN 1 ELSE 0 END) AS unmeasured_jobs,
  ROUND(100.0 * COUNT(measured_ms) / NULLIF(COUNT(*), 0), 1)
    AS duration_coverage_pct,
  ROUND(AVG(measured_ms), 1) AS schema_aware_average_ms
FROM normalized;