在BigQuery中按部门课程条件判定员工合规性的实现方案

需求回顾 #

不同部门员工需完成对应指定课程集合，全部完成则合规，否则不合规：

• 烘焙部（Bakery）：需完成课程 100、200、300
• 果蔬部（Fruit & Veg）：需完成课程 100、300
• 杂货部（Grocery）：需完成课程 75、85、95
• 夜间补货部（Nightfill）：需完成课程 101、102

实现思路 #

1. 去重员工已修课程：同一员工重复完成同一课程仅需统计一次
2. 关联部门课程要求：建立部门与必学课程的映射关系
3. 合规性校验：对比员工已修课程与部门必学课程，判断是否完全覆盖

BigQuery SQL 实现 #

假设你已有员工部门映射表（或通过CTE临时定义），员工学习表为employee_courses：

-- 1. 定义各部门的必学课程要求
WITH department_requirements AS (
  SELECT 
    'Bakery' AS department,
    [100, 200, 300] AS required_courses
  UNION ALL
  SELECT 
    'Fruit & Veg' AS department,
    [100, 300] AS required_courses
  UNION ALL
  SELECT 
    'Grocery' AS department,
    [75, 85, 95] AS required_courses
  UNION ALL
  SELECT 
    'Nightfill' AS department,
    [101, 102] AS required_courses
),

-- 2. 去重员工已完成的课程
employee_completed_courses AS (
  SELECT 
    Employee,
    ARRAY_AGG(DISTINCT course ORDER BY course) AS completed_courses
  FROM `your-project.your-dataset.employee_courses`
  GROUP BY Employee
),

-- 3. 关联员工部门信息（若有现成表可直接替换此CTE）
employee_departments AS (
  SELECT 1 AS Employee, 'Bakery' AS department UNION ALL
  SELECT 2 AS Employee, 'Bakery' AS department UNION ALL
  SELECT 3 AS Employee, 'Bakery' AS department UNION ALL
  SELECT 4 AS Employee, 'Grocery' AS department UNION ALL
  SELECT 5 AS Employee, 'Fruit & Veg' AS department UNION ALL
  SELECT 6 AS Employee, 'Fruit & Veg' AS department UNION ALL
  SELECT 7 AS Employee, 'Nightfill' AS department UNION ALL
  SELECT 8 AS Employee, 'Nightfill' AS department UNION ALL
  SELECT 9 AS Employee, 'Bakery' AS department
)

-- 4. 最终合规性判定
SELECT 
  ec.Employee,
  ed.department,
  ec.completed_courses,
  dr.required_courses,
  -- 检查必学课程是否全部被已完成课程覆盖
  IF(
    ARRAY_LENGTH(ARRAY(SELECT * FROM UNNEST(dr.required_courses) WHERE NOT IN UNNEST(ec.completed_courses))) = 0,
    '合规',
    '不合规'
  ) AS compliance_status
FROM employee_completed_courses ec
JOIN employee_departments ed ON ec.Employee = ed.Employee
JOIN department_requirements dr ON ed.department = dr.department
ORDER BY ec.Employee;

关键逻辑说明 #

• ARRAY_AGG(DISTINCT course)：对员工重复修读的课程去重，生成已完成课程数组
• ARRAY(SELECT * FROM UNNEST(dr.required_courses) WHERE NOT IN UNNEST(ec.completed_courses))：筛选出部门必学但员工未完成的课程，数组长度为0则表示全部完成
• 若员工部门信息已存储在业务表中，直接替换employee_departments CTE为实际表即可

示例结果预览 #

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