如何按每行不同日期筛选数据集？BigQuery学年在校人数统计

核心思路 #

直接用你已有的学年日期数据集和学生记录数据集做关联，通过判断日期是否落在学生的入学/离校区间内，分组统计每日在校人数——无需复杂递归（当然如果没有现成日期表，也可以用递归生成）。

假设表结构 #

• 学生记录表：student_enrollments，字段包括student_id（学生ID）、enroll_date（入学日期，DATE类型）、leave_date（离校日期，DATE类型，NULL表示仍在校）
• 学年日期表：school_year_dates，字段date（DATE类型，包含学年所有日期）

最终查询语句 #

WITH daily_enrollments AS (
  SELECT
    d.date,
    COUNT(s.student_id) AS enrolled_students
  FROM
    `your_project.your_dataset.school_year_dates` d
  LEFT JOIN
    `your_project.your_dataset.student_enrollments` s
  ON
    d.date >= s.enroll_date
    AND (d.date <= s.leave_date OR s.leave_date IS NULL)
  GROUP BY
    d.date
  ORDER BY
    d.date
)
SELECT * FROM daily_enrollments

语句解释 #

1. 关联逻辑：将每个学年日期与所有学生记录匹配，判断该日期是否在学生的在校区间内（入学后、离校前，离校日期为NULL则视为当前仍在校）
2. 分组统计：按日期分组，统计每个日期符合条件的学生数量
3. 排序输出：按日期排序，方便查看每日人数变化

无现成日期表时的递归写法 #

如果没有预先准备的学年日期表，可以用递归WITH生成日期范围：

WITH RECURSIVE date_range AS (
  SELECT DATE('2024-08-01') AS date  -- 替换为你的学年开始日期
  UNION ALL
  SELECT DATE_ADD(date, INTERVAL 1 DAY)
  FROM date_range
  WHERE date < DATE('2025-06-30')  -- 替换为你的学年结束日期
),
daily_enrollments AS (
  SELECT
    dr.date,
    COUNT(s.student_id) AS enrolled_students
  FROM
    date_range dr
  LEFT JOIN
    `your_project.your_dataset.student_enrollments` s
  ON
    dr.date >= s.enroll_date
    AND (dr.date <= s.leave_date OR s.leave_date IS NULL)
  GROUP BY
    dr.date
  ORDER BY
    dr.date
)
SELECT * FROM daily_enrollments

注意事项 #

• 确保所有日期字段为DATE类型，避免因类型不匹配导致关联错误
• 离校日期为NULL的学生必须单独处理，否则会漏掉当前在校的学生
• 递归生成日期适合小范围区间，若学年跨度较大，优先使用预先生成的日期表提升查询效率

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