如何在BigQuery中判断员工是否完成指定合规课程集合

我正在使用BigQuery从员工学习表提取数据，需要判断员工是否完成部门要求的一组课程（如课程100、200、300），完成全部则归类为合规，否则为不合规。

数据结构示例：

Employee course 
1        100 
1        101 
1        200 
1        300
1        300 
1        400 
2        100 
2        200
3        100
3        200
3        300
4        75
4        85
4        95
4        105 
4        115
4        125  
5        200
5        200
5        100
5        100
5        100
5        300
5        300
6        100
7        100
8        300
8        200
8        100
8        101
8        102
9        100
9        200
9        300 

我的初步思路是用CASE语句生成0/1列，再按员工维度对这三类列求和，希望得到更好的建议。

解决方案 #

方法1：优化你的初步思路 #

原思路可以调整为用MAX替代SUM，因为只要员工完成过该课程（无论重复多少次）就标记为完成，避免重复选课导致的数值误差：

WITH employee_course_status AS (
  SELECT
    Employee,
    MAX(CASE WHEN course = 100 THEN 1 ELSE 0 END) AS completed_100,
    MAX(CASE WHEN course = 200 THEN 1 ELSE 0 END) AS completed_200,
    MAX(CASE WHEN course = 300 THEN 1 ELSE 0 END) AS completed_300
  FROM
    `你的项目ID.你的数据集.你的表名`
  GROUP BY
    Employee
)
SELECT
  Employee,
  CASE
    WHEN completed_100 = 1 AND completed_200 = 1 AND completed_300 = 1 THEN '合规'
    ELSE '不合规'
  END AS compliance_status
FROM
  employee_course_status;

方法2：集合对比法（扩展性更强） #

利用BigQuery的数组操作，直接判断员工完成的课程集合是否包含所有目标课程，后续修改目标课程只需调整数组内容：

WITH target_courses AS (
  SELECT [100, 200, 300] AS required_courses
),
employee_completed AS (
  SELECT
    Employee,
    ARRAY_AGG(DISTINCT course) AS completed_courses
  FROM
    `你的项目ID.你的数据集.你的表名`
  GROUP BY
    Employee
)
SELECT
  ec.Employee,
  CASE
    WHEN ARRAY(SELECT * FROM UNNEST(tc.required_courses) EXCEPT DISTINCT SELECT * FROM UNNEST(ec.completed_courses)) = [] THEN '合规'
    ELSE '不合规'
  END AS compliance_status
FROM
  employee_completed ec,
  target_courses tc;

方法3：简洁计数法 #

通过统计员工完成的目标课程数量，和要求的课程总数对比，写法最精简：

DECLARE required_count INT64 DEFAULT 3; -- 目标课程总数

SELECT
  Employee,
  CASE
    WHEN COUNT(DISTINCT CASE WHEN course IN (100, 200, 300) THEN course END) = required_count THEN '合规'
    ELSE '不合规'
  END AS compliance_status
FROM
  `你的项目ID.你的数据集.你的表名`
GROUP BY
  Employee;

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