Google Big Query实现按经理分组标记员工性别类型需求

一、新增Variation列（按经理分组标记） #

方法1：基于性别数量判断 #

利用窗口函数按经理分组，计算组内不同性别的数量，再通过CASE语句标记：

SELECT
  Manager_,
  Employee_,
  Gender___,
  CASE
    WHEN COUNT(DISTINCT Gender___) OVER (PARTITION BY Manager_) = 1 THEN
      CASE 
        WHEN MAX(Gender___) OVER (PARTITION BY Manager_) = 'Male' THEN 'Only_male'
        WHEN MAX(Gender___) OVER (PARTITION BY Manager_) = 'Female' THEN 'Only_female'
      END
    ELSE 'Mixed'
  END AS Variation
FROM
  `your-project.your-dataset.your-table`;

方法2：基于BOOL_AGG判断存在性 #

用BOOL_AGG函数直接判断组内是否同时存在男/女性，逻辑更直观：

SELECT
  Manager_,
  Employee_,
  Gender___,
  CASE
    WHEN BOOL_AGG(Gender___ = 'Male') OVER (PARTITION BY Manager_) 
         AND BOOL_AGG(Gender___ = 'Female') OVER (PARTITION BY Manager_) THEN 'Mixed'
    WHEN BOOL_AGG(Gender___ = 'Male') OVER (PARTITION BY Manager_) THEN 'Only_male'
    ELSE 'Only_female'
  END AS Variation
FROM
  `your-project.your-dataset.your-table`;

二、筛选混合性别组的其他方法 #

方法1：GROUP BY + HAVING 关联原表 #

先筛选出存在混合性别的经理，再关联原表获取对应员工记录：

WITH mixed_managers AS (
  SELECT Manager_
  FROM `your-project.your-dataset.your-table`
  GROUP BY Manager_
  HAVING COUNT(DISTINCT Gender___) > 1
)
SELECT *
FROM `your-project.your-dataset.your-table`
WHERE Manager_ IN (SELECT Manager_ FROM mixed_managers);

方法2：窗口函数子查询筛选 #

在子查询中计算每个经理组的性别数量，外层筛选数量大于1的记录：

SELECT *
FROM (
  SELECT
    *,
    COUNT(DISTINCT Gender___) OVER (PARTITION BY Manager_) AS gender_count
  FROM `your-project.your-dataset.your-table`
)
WHERE gender_count > 1;

方法3：EXISTS子查询判断 #

通过EXISTS检查当前经理下是否存在不同性别的员工：

SELECT *
FROM `your-project.your-dataset.your-table` t1
WHERE EXISTS (
  SELECT 1
  FROM `your-project.your-dataset.your-table` t2
  WHERE t2.Manager_ = t1.Manager_
    AND t2.Gender___ != t1.Gender___
);

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