Google BigQuery：获取跨部门沟通TOP5员工的SQL WHERE子句咨询

首先，你需要额外关联一次employees表来获取收件人的部门信息，才能判断收件人是否和发件人同属一个部门。完整的SQL语句如下：

SELECT 
  m.sender, 
  COUNT(DISTINCT m.receiver) AS cross_dept_receivers_count, 
  e.department
FROM collaboration.messages as m
-- 关联发件人部门信息
JOIN collaboration.employees as e
  ON m.sender = e.id
-- 关联收件人部门信息
JOIN collaboration.employees as e_receiver
  ON m.receiver = e_receiver.id
-- 筛选出收件人与发件人不同部门的记录
WHERE e_receiver.department != e.department
GROUP BY m.sender, e.department
ORDER BY cross_dept_receivers_count DESC
LIMIT 5;

关键说明： #

• 新增的JOIN collaboration.employees as e_receiver用于获取每个收件人所属的部门，这是判断跨部门沟通的核心依据。
• WHERE子句的e_receiver.department != e.department直接筛选出收件人部门不等于发件人部门的消息记录，确保统计的是跨部门的收件人数量。
• 移除了原SQL中的DISTINCT，因为GROUP BY m.sender, e.department已经保证了每组的唯一性，无需额外去重。
• 将原字段名messages_received改为cross_dept_receivers_count，更准确反映统计的是跨部门收件人数量，避免歧义。

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