如何用BigQuery SQL实现R语言中的学生重复班级记录筛选？

首先假设三张表的关联逻辑如下（若你的实际字段不同，替换对应字段即可）：

• student：包含student_id（主键）、学生基本信息
• class：包含class_id、student_id（关联student表）、subject_id（关联subject表）
• subject：包含subject_id（主键）、subject_name（如Math）

实现步骤 #

1. 关联三张表，获取完整的学生-班级-科目数据
2. 计算每个学生在同一班级的出现次数
3. 筛选出出现次数≥2的记录，再去重保留唯一的学生-班级-科目组合

BigQuery SQL 代码 #

WITH merged_data AS (
  -- 合并三张表
  SELECT 
    s.student_id,
    c.class_id,
    sub.subject_name
  FROM student s
  JOIN class c ON s.student_id = c.student_id
  JOIN subject sub ON c.subject_id = sub.subject_id
),
count_records AS (
  -- 统计每个学生在同一班级的出现次数
  SELECT 
    *,
    COUNT(*) OVER (PARTITION BY student_id, class_id) AS class_occurrence
  FROM merged_data
)
-- 筛选次数≥2的记录并去重
SELECT DISTINCT
  student_id,
  class_id,
  subject_name
FROM count_records
WHERE class_occurrence >= 2;

说明 #

• merged_data 负责把三张表的关联数据整合到一起
• count_records 用窗口函数统计每个学生对应同一班级的出现次数，不用提前分组就能保留原数据并附加统计值
• 最后通过DISTINCT去重，确保每个学生-班级-科目组合只留一条，同时仅保留出现次数达标记录

如果你的表结构和假设不同（比如class表直接存subject_name而非subject_id），去掉和subject表的关联、调整SELECT字段即可。

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