如何在BigQuery SQL中按条件关联数据以统计每日在办任务数

你需要统计每个日期下仍处于活跃状态的任务数（即任务创建日期≤当前日期，且完成日期≥当前日期），原来的SQL仅按创建日期关联，无法覆盖“任务在当天未完成”的情况，以下是修正后的BigQuery SQL：

WITH date_range AS (
  -- 生成目标日期范围的所有日期
  SELECT newdate
  FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', '2022-12-31', INTERVAL 1 DAY)) AS newdate
),
tasks AS (
  -- 取出任务表数据，提前转换日期格式避免重复计算
  SELECT 
    CAST(created_at AS DATE) AS created_date,
    CAST(completed_at AS DATE) AS completed_date
  FROM `data.task_merge`
)
-- 按日期分组统计活跃任务数
SELECT
  dr.newdate,
  COUNT(t.created_date) AS active_task_count
FROM date_range dr
LEFT JOIN tasks t
  ON dr.newdate BETWEEN t.created_date AND t.completed_date
GROUP BY dr.newdate
ORDER BY dr.newdate

关键说明： #

• 把任务表的时间字段提前转成DATE类型，减少关联时的计算开销
• 关联条件改用BETWEEN（等价于dr.newdate >= t.created_date AND dr.newdate <= t.completed_date），确保每个日期匹配所有在当天处于活跃状态的任务
• 最后通过GROUP BY按日期聚合，统计每个日期的活跃任务总数

如果需要查看每个日期对应的具体任务列表，去掉GROUP BY和COUNT即可：

WITH date_range AS (
  SELECT newdate
  FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', '2022-12-31', INTERVAL 1 DAY)) AS newdate
),
tasks AS (
  SELECT 
    *,
    CAST(created_at AS DATE) AS created_date,
    CAST(completed_at AS DATE) AS completed_date
  FROM `data.task_merge`
)
SELECT
  dr.newdate,
  t.*
FROM date_range dr
LEFT JOIN tasks t
  ON dr.newdate BETWEEN t.created_date AND t.completed_date
ORDER BY dr.newdate, t.created_at

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