跨部门任职员工致报表总计重复计数的SQL统计方案咨询

问题本质 #

• 原始明细表粒度为「员工ID-单部门归属」，1人多部门的场景下会出现同一员工ID（Col1）对应多条部门（Col2）记录，直接不带去重计数会重复统计跨部门员工，导致总人数从实际值30虚高至32
• 采用LISTAGG()按员工ID聚合部门为逗号分隔字符串的方案，仅能解决全员工维度的去重计数问题，聚合生成的组合部门值无法直接匹配单部门维度的分组统计需求，后续硬拆分字符串属于冗余操作，性能和稳定性都存在缺陷。

最优实现方案 #

直接基于原始明细表做分层分组统计，全程使用COUNT(DISTINCT Col1)做员工去重，完全跳过聚合-拆分字符串的冗余步骤，可一次性输出所有层级的统计指标：

所有比例类指标（Probation (%)、Suspended (%)）统一口径：对应状态的去重员工数 / 当前分组维度下的总去重员工数 * 100，全程保持去重逻辑一致即可避免计数偏差。

各层级统计逻辑 #

1. 全员工（All Employees）层级
   不设置分组维度，全表计算即可：
   • 总人数：COUNT(DISTINCT Col1)，计算结果为正确值30，自动跳过跨部门员工的重复记录
   • Probation (%)：COUNT(DISTINCT CASE WHEN 员工状态字段 = '试用期' THEN Col1 END) / COUNT(DISTINCT Col1) * 100
   • Suspended (%)：COUNT(DISTINCT CASE WHEN 员工状态字段 = '停职' THEN Col1 END) / COUNT(DISTINCT Col1) * 100
2. 全团队聚合层级
   过滤掉非团队统计范围的无效部门记录后，复用全员工层级的去重计算逻辑即可。
3. 男/女团队等大类合计层级
   先给单部门打上大类归属标签（例如Men's Wear归为男装大类、Women's Wear归为女装大类、UniSex Wear归为中性服饰大类），再按大类标签分组，沿用COUNT(DISTINCT Col1)的去重逻辑，跨大类归属的员工会自动计入其所属的多个大类统计结果中，不会生成无效组合分组。
4. 各独立部门层级
   直接按部门字段Col2分组，同样使用COUNT(DISTINCT Col1)去重计数：
   • 同时归属两个部门的员工（如ID 1234、ID 9876）会自动计入其所属的两个独立部门的统计值中，不会出现Men's Wear,UniSex Wear这类无效组合分组
   • 注意：各独立部门的人数累加值会大于全员工总人数，属于多归属场景下的正常统计结果，和全员工维度的去重口径不冲突。

关于LISTAGG聚合后拆分方案的说明 #

该方案技术上可实现，通过各SQL引擎自带的字符串拆分+列转行函数即可完成，例如：

• Spark/Presto：LATERAL VIEW explode(split(聚合部门字段, ',')) 拆分为单部门多行记录后分组
• PostgreSQL：unnest(string_to_array(聚合部门字段, ',')) 完成拆分行转换
• MySQL 8.0+：可通过递归CTE结合SUBSTRING_INDEX()实现字符串拆分
  但完全不推荐该方案，核心问题如下：
• 存在冗余计算：先聚合字符串再拆分属于反向操作，数据量级大时性能远低于直接基于原始明细分组统计的方案
• 存在逻辑风险：如果部门名称本身包含逗号（如部门名为Retail, East China），会出现拆分错误，导致统计结果偏差
• 维护成本高：后续调整部门层级、新增多归属规则时，字符串聚合拆分的逻辑调整成本远高于直接基于原始明细打标签的方案

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